user13286 user13286 - 5 months ago 23
SQL Question

Inserting special characters into SQL database from form

I am submitting form values into a database using PHP but I am running into an issue when user's enter special characters such as an apostrophe. For example if someone enters

Bill's Pet Supply
into organization, there will be an SQL error.

Here is my code:

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

if(isset($_POST['submit'])) {
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];
$organization = $_POST['organization'];

$sql = $conn->prepare("INSERT INTO submissions VALUES (:firstname, :lastname, :email, :organization)");

$sql->bindValue(':firstname', $firstname);
$sql->bindValue(':lastname', $lastname);
$sql->bindValue(':email', $email);
$sql->bindValue(':organization', $organization);

$sql->execute();
}

$conn->close();


How can I change this code so that apostrophes and other special characters will be supported?

Answer

Use prepared statements with bind placeholders. Both PDO and mysqli provide support for those.

Your SQL text would look like this:

$sql = "INSERT INTO submissions (firstname, lastname, email, organization)
VALUES (?, ?, ?, ?)";

If you are using mysqli

mysqli_prepare

myslqi_bind_param

myslqi_execute

$sth = $mysqli->prepare($sql);
if(!$sth) {
  // handle error
}
$sth->bind_param("ssss", $firstname, $lastname, $email, $organization);
if( $res = $sth->execute() ) {
  // process resultset
}

Similar functions available in PDO, but you can use "bind value" instead of "bind param".


If there's some reason you can't use prepared statements with bind placeholders, then at a minimum, you will need to properly escape any potentially unsafe values included in the SQL text.

If you are using mysqli, then generating the SQL text would look something like this:

$sql = "INSERT INTO submissions (firstname, lastname, email, organization)
VALUES ('" . $mysqli->real_escape_string( $firstname )
  . "', '" . $mysqli->real_escape_string( $lastname ) 
  . "', '" . $mysqli->real_escape_string( $email )
  . "', '" . $mysqli->real_escape_string( $organization )
  . "')";

But don't do that. Use a prepared statement with bind placeholders.