What's the difference between PHP's addslashes and mysql(i)_escape_string?

Question

Possible Duplicate:
mysql_real_escape_string VS addslashes

If they don't do exactly the same, what's the difference? The delimiter for values inside a MySQL query is the ' isn't it? Or maybe the " but that's also escaped with addslashes.

In other database engines I understand (and definitely inside a db wrapper like PDO), but why are so many people so adament on using mysql(i)_escape_string instead of addslashes?

Answer 1

First of all: do not use mysql_escape_string, it is deprecated (for a reason)!

If you have to support a legacy application that connects to the database through the mysql extension (which has been deprecated), use mysql_real_escape_string instead. Otherwise switch immediately to mysqli, where prepared statements and bound parameters provide a more robust mechanism for escaping user input.

That said, the answer can be found by reading the description of mysql_real_escape_string and addslashes:

Difference #1

addslashes does not know anything about MySql connection encodings. If you pass it a string containing bytes representing an encoding other than the encoding used by the MySql connection, it will happily escape all bytes having the values of the characters ', ", \ and \x00. This may not be the same as all the characters ', ", \ and \x00 if you are using an encoding other than 8-bit encodings and UTF-8. The result will be that the string received by MySql will be corrupted.

To trigger this bug, try using iconv to convert your variable to UTF-16 and then escape it with addslashes. See what your database receives.

This is one reason why addslashes should not be used for escaping.

Difference #2

In contrast to addslashes, mysql_real_escape_string also escapes the characters \r, \n, and \x1a. It appears that these characters have to be escaped as well when talking to MySql, otherwise a malformed query may be the result

This is the other reason why addslashes should not be used for escaping.