MySql error while string is escaped already?

Question

Why this:

$query = "SET NAMES 'utf8'";
$query = str_replace("'", "\'", $query);
$pdo->query($query);

Would cause problem?

I'm currently getting this error:

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 '\'utf8\''

If I don't escape it, everything's fine, but the problem exists with further queries!

No need to escape the quotes. You just need a final semicolon; — Pekka, Dec 10 '12 at 14:54
You need to escape the inputs of the users, not the queries themselves. — Bgi, Dec 10 '12 at 14:55
There is no need to escape this query, but as I said, the problem exists with further queries. — MahanGM, Dec 10 '12 at 14:55
btw this is not the right way to escape strings, you should use PDO::prepare(): http://stackoverflow.com/questions/1742066/why-is-pdo-better-for-escaping-mysql-queries-querystrings-than-mysql-real-escape — Bgi, Dec 10 '12 at 14:56

score 1 · Accepted Answer · answered Dec 10 '12 at 14:55

1

The sql you are trying to run is perfectly safe as is, it contains no user input and as such can be run without escaping.

Also you are actually escaping the delimiters of a string, not the value of the string itself.

answered Dec 10 '12 at 14:55

Dale

I know this, but what's the problem with it? – MahanGM Dec 10 '12 at 14:56
1

The problem (funnily enough described in the error message) is your final query looks like this `SET NAMES \'utf-8\'` which is broken, it should look like `SET NAMES 'utf-8'` – Dale Dec 10 '12 at 14:57
The problem is you HAVE to quote NAMES value. So if it has been escaped, it became wrong. – ozahorulia Dec 10 '12 at 14:57

score 1 · Answer 2 · answered Dec 10 '12 at 14:57

1

You don't have to escape every single quote in a query, some are valid such as:

UPDATE table SET field='blah' WHERE id=10

Where field would be a varchar or similar. You would escape the quotes if they need to be part of the value of the field, such as:

UPDATE table SET field='This \'value\' uses quotes.' WHERE id=10

Hope that makes sense.

answered Dec 10 '12 at 14:57

Michael

You mean `field=\'This \'value\' uses quotes.\'` would throw error? I think it does make sense! – MahanGM Dec 10 '12 at 14:58
Yes, what you just put in your comment would be invalid. – Michael Dec 10 '12 at 15:00

2 Answers2