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?

Solution 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.

Solution 2

Chris Shiflett demonstrates a real world case where escaping SQL using addslashes() fails, and mysql_real_escape_string() is the only way to go.

How does this help? If I want to attempt an SQL injection attack against a MySQL database, having single quotes escaped with a backslash is a bummer. If you're using addslashes(), however, I'm in luck. All I need to do is inject something like 0xbf27, and addslashes() modifies this to become 0xbf5c27, a valid multi-byte character followed by a single quote. In other words, I can successfully inject a single quote despite your escaping. That's because 0xbf5c is interpreted as a single character, not two. Oops, there goes the backslash.

....

Despite the use of addslashes(), I'm able to log in successfully without knowing a valid username or password. I can simply exploit the SQL injection vulnerability.

To avoid this type of vulnerability, use mysql_real_escape_string(), prepared statements, or any of the major database abstraction libraries.

now this is admittedly a rare edge case, but a demonstration of why people are so adamant about using the database specific escape functions. Only the database library can know for sure what kind of escaping is needed. Different wrappers, character sets and SQL flavours (like MS SQL server) need different escaping. Ignoring that fact is how vulnerabilities are born.

Solution 3

They are identical in that you should be using neither of them, because you should be using placeholders rather than building SQL from untrusted data.

See http://bobby-tables.com/php.html for how to do it the right way.

Comments

[Marc B]

addslashes() is a glorified wrapper to what amounts to str_replace($var, "'", "\\'"), while mysql_real_escape_string() calls the actual MySQL API function that does escaping, and does the job properly.

[Rudie]

I did mean mysql_real_escape_string. My bad. Very clear answer!

[Rudie]

Placeholders to replace with what? Something a db layer does? And what do you think the db layer does? Use some kind of mysql_real_escape_string I assume...