Inserting special characters into SQL database from form

Question

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?

See if this info helps: [http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Matt Jackson, Jul 08 '16 at 16:19
There's two interface libraries: mysqli and PDO. And it looks like you are attempting to use PDO supported features which aren't supported in mysqli. I don't believe mysqli supports named placeholders like PDO does e.g. **`:firstname`**. With mysqli, use positional placeholder **`?`**. PDO has a **`bindValue`** function. mysqli uses **`bind_param`** function. See the example in my updated answer. — spencer7593, Jul 08 '16 at 16:41

spencer7593 · Accepted Answer · 2016-07-08T16:34:37.763

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.

Thank you, this is really helpful. I have updated my code using mysqli prepared statements but now I am getting a 500 error when I submit the form. Could you take a look and let me know if you see any issues? — user13286, Jul 08 '16 at 16:35
Never mind, used your updated answer and it's working great now, thanks again! — user13286, Jul 08 '16 at 16:40

Inserting special characters into SQL database from form

1 Answers1