Josh Josh - 10 months ago 64
SQL Question

PHP/SQL prepared statement with double quotes

I'm using an HTML input element to accept data from a user and save it in a mySQL database. To do this, I'm using PHP and mysqli to create a prepared statement.

This works very well until I try to accept an input string containing a double quote("). If the input string contains a double quote, the data saved will be the string up to the double quote character.

For example:

I saw a 7'9" person.

Will be saved as:

I saw a 7'9

I've searched high and low, but can't find a solution. I've tried mysqli_real_escape_string, but that just adds backslashes to escape meaningful characters. When coupled with the prepared statement, the backslashes are interpreted literally and the double quote still interrupts the string.
So my example would be:

I saw a 7\'9

I even thought maybe the PHP wasn't getting the whole string from the input element, but when I echo the element's value, I do see the whole string.

Here is some example code. I've removed some business details, but this is generally what I'm trying to do.

$sqlSave = $objConnection->prepare('update tbl set Answer=? where ID=?;');
$sqlSave->bind_param("si", $sqlAnswer, $sqlID);


I feel like I'm missing something obvious, so please forgive my ignorance. Any help or additional information is appreciated.

Answer Source

Yes, indeed it's something very obvious. Just look at the generated HTML code and you will immediately get a clue.

And then learn by heart that HTML attributes have to be always encoded using htmlspecialchars()