CODEWITHSUNDEEP
phpmysql
Brian Hannay
Brian Hannay

Reputation: 600

Do backslashes escape quotes in mysql?

Here's a var_dump of the query that is failing

string 'INSERT INTO event(name, description, location, image, datetime) VALUES('Nica\'s Weight Loss Class', 'Description goes here!', 'Location', 'Nica's Weight Loss Class.png', '2015-01-01T01:00')' (length=189)`

The error is

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 Weight Loss Class.png', '2015-01-01T01:00')'

And the line of code creating the query string is: (all variables escaped before-hand)

$query = "INSERT INTO event(name, description, location, ".($imageForEvent?"image, ":"")."datetime) VALUES('$evName', '$evDescription', '$evLocation', ".($imageForEvent?"'".basename($imageForEvent)."', ":"")."'$evDatetime')";

According to the mysql docs a backslash character is used to escape single quotes.

However, according How to escape apostrophe (') in MySql?, the correct way seems to be to double the quotation mark.

I've tested both ways and they give the same error in the same place. Can anyone shed some light on this subject?

Upvotes: 1

Views: 3180

Answers (2)

halk
halk

Reputation: 111

Your MySQL query itself is correct. Since the single quote is within a single-quoted string literal, the backslash is required to escape it, so that it is parsed as part of the string literal instead of the end of the string literal. Your first reference, the MySQL documentation link states that you can also use two single quotes to achieve the same thing.

However, the problem lies in the fact that you probably assigned the MySQL query into a PHP variable as a string literal (the output you shared in the question is from var_dump only so it is not clear where the SQL query is assigned). Similar to MySQL, PHP requires you to escape the single quote character within a single-quoted string literal, otherwise it is interpreted as the end of the string literal.

In order to make it work, you need to escape twice. Once for PHP, then for MySQL:

$query = 'INSERT INTO event(name, description, location, image, datetime) VALUES('Nica\\\'s Weight Loss Class', 'Description goes here!', 'Location', 'Nica's Weight Loss Class.png', '2015-01-01T01:00')';

Notice \\\'. When PHP interprets the line and sends the query to MySQL it will be \'; The first \\ are escaping a single backlash.

UPDATE

The OP has updated the question. That part of the SQL query comes from variables. This is bad practice and can cause a lot of problems up to XSS exploits. It's better to use binding with PDO or other SQL libraries which are doing the escaping automatically. However to answer the question, running your variables through addslashes would solve the MySQL error.

Upvotes: 1

Jonathan Kuhn
Jonathan Kuhn

Reputation: 15301

You are only escaping the first ' inside of a string. There are two places that you have Nica's... but you are only escaping the first. The second would appear to be for the image name. If you insist on using the mysql_* functions, then you should be using mysql_real_escape_string on each variable in the query. Quotes are not the only character that needs escaping and that will escape them all properly.

But what you should really be doing is use mysqli or pdo and bind your values in the query. The mysql_* functions have been deprecated, will throw errors and will be removed in a future version. Avoid them. Binding is easy to do and will solve this issue completely to the point that you don't need to worry about it ever.

Upvotes: 2

Related Questions