Do backslashes escape quotes in mysql?

Question

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?

if your sql is `'INSERT INTO...'` as opposed to `"INSERT INTO..."` in double quotes, then that would explain it. use prepared statements and be done with it. — Funk Forty Niner, Jul 23 '15 at 18:22
@Fred-ii- I'll update the question to show the line of code creating the query string. I'm using double quotes. — Brian Hannay, Jul 23 '15 at 18:24
@Fred-ii- I believe that is the result of the var_dump. But for the question, 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. — Jonathan Kuhn, Jul 23 '15 at 18:25
Be careful, it is often very hard to spot the difference in documentation between '' and " where the prior is two consecutive single quotes and the latter is a double quote. — Cobusve, Jul 23 '15 at 18:26
@JonathanKuhn this is the answer. If you submit it as such, I'll mark it. — Brian Hannay, Jul 23 '15 at 18:27
depending on which MySQL API you're connecting with, `mysql_real_escape_string` - `mysqli_real_escape_string` or stripslashes or prepared statements (mysqli-PDO), which you should be using, rather than passing your variables directly in like that being prone to SQL injection. *that's.. IF you're not already doing that*. — Funk Forty Niner, Jul 23 '15 at 18:29
In the error message it says the syntax error is near 's Weight Loss Class, which is the image column's input. Just another way you could have spotted the error. — AdamMc331, Jul 23 '15 at 18:32
You really should learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) then you never have to worry about escaping *ever* again. — Jay Blanchard, Jul 23 '15 at 18:46
@JayBlanchard I know about prepared statements -- I had a bunch of queries to write, so I thought I'd do all the escaping in a different place and only need one line of code per query. Just a design choice. — Brian Hannay, Jul 23 '15 at 19:33

score 2 · Accepted Answer · answered Jul 23 '15 at 18:28

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.

score 1 · Answer 2 · edited Jul 23 '15 at 18:47

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.

Do backslashes escape quotes in mysql?

2 Answers2