You have an error in your SQL syntax - single quote in text issue

Written by Claudio Kuenzler - 2 comments

Published on September 19th 2013 - Listed in PHP MySQL Database

---

Since I moved my blog to a new web server, the PHP version has jumped from 5.2 to 5.4. I previously wrote about changes from mysql to mysqli where I already replace some mysql queries by mysqli queries. But shortly after these obvious changes, I stumbled upon a bug in my comment form: The form didn't accept single quotes (') anymore. But I was pretty sure, this has worked before.

So I compared the same form on the old web server again:

 

On the PHP 5.2 server the insert of the comment immediately worked. The output of the entered comment text looked like this:

This is a test on a web server. It\'s PHP version is 5.2.

But on the PHP 5.4 server, the following error message was shown:

Fehlermeldung=You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's PHP version is 5.4.','1379575549')' at line 3

After some research, I came across the function mysql_real_escape_string (or mysqli_real_escape_string for mysqli), which does the job of escaping special characters.
Right before the SQL insert, I added the following line to let the whole comment text getting parset by mysql_real_escape_string:

$iText = mysql_real_escape_string($iText);

The comment form now worked again and the output looked kind of familiar:

This is a test on a web server. It\'s PHP version is 5.4.

It's actually funny that this has worked in PHP 5.2 without mysql_real_escape_string.

Add a comment

Show form to leave a comment

Comments (newest first)

Claudio from Switzerland wrote on Sep 19th, 2013:

Yes, the backslash is correct. This is how it is stored in MySQL.

---

Alexander from Zürich wrote on Sep 19th, 2013:

Sure that this is correct? You now have:

This is a test on a web server. It\'s PHP version is 5.4.

With a backslash "\" in front of the '.

Should it really be like this?

---