php's mysqli->real_escape_string() converting quotes to htmlchars

Question

Pretty simple piece of code; accepting user input for a name (quotes/apostrophe's valid), and then storing it in a mysql db;

$this->sql = new mysqli($this->host, $this->user, $this->pass, $this->dbname);

$firstname = $this->sql->real_escape_string($firstname);
$lastname = $this->sql->real_escape_string($lastname);

$sql = "INSERT INTO users
        (firstname, lastname)
        ('{$firstname}', '{$lastname}')";

$this->sql->query($sql);

However, when in use, this is converting all quotes/apostrophe's into '.

Would anyone have any ideas as to what could cause this? I've checked through the script for any htmlspecialchars or other methods being called.

RTM on http://dev.mysql.com/doc/en/insert.html you're missing a crucial piece here if that's your actual code. — Funk Forty Niner, Oct 28 '15 at 20:40

score 1 · Accepted Answer · edited May 23 '17 at 12:23

That is what real_escap_string does. All characters which can be used to perform an SQL Injection attack have to be escaped. See here for a full list of these "symbols" http://www.w3.org/MarkUp/html-spec/html-spec_13.html If you display them the browser renders them to what they are supposed to look like, but they are can not effect your Query any more.

If you do not want to change the characters I suggest the use of prepared statements. They are the best way to prevent SQL Injection attacks. More on this topic can be found here: http://php.net/manual/de/mysqli.quickstart.prepared-statements.php and here: How can I prevent SQL injection in PHP?

php's mysqli->real_escape_string() converting quotes to htmlchars

1 Answers1