mysql_real_escape_string VS addslashes

Question

Can someone shed some light on the differences between these 2 functions, from the PHP manual:

addslashes: Returns a string with backslashes before characters that need to be quoted in database queries etc. These characters are single quote ('), double quote ("), backslash () and NUL (the NULL byte).

mysql_real_escape_string: mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, , ', " and \x1a.

From what I gather the major difference is \x00, \n \r \x1a which addslashes doesn't escape, can you tell me what the significance of that is?

Honna

Answer 1

What you quote is probably from the doc, but as far as I know it's not necessarily true.

addslashes adds slashes to characters that are commonly disturbing. mysql_real_escape_string escapes whatever MySQL needs to be escaped. This may be more or less characters than what addslashes takes care of.

Also, mysql_real_escape_string will not necessarily add slashes to escape. While I think it works if you do it that way, recent versions of MySQL escape quotes by putting two of them together instead of by putting a slash before it.

I believe you should always use your data provider's escape function instead of addslashes, because addslashes may either do too much or not enough work for the purpose you use it. On the other hand, mysql_real_escape_string knows what to do to prepare a string for embedding it in a query. Even if the specs change about how to escape stuff and suddenly it's not backslashes that you should use anymore, your code will still work because mysql_real_escape_string will be aware of it.

Blisse

ANSI SQL specifies that a single quote can be represented by two single quotes in a row, but mysql_real_escape_string() doesn't do that -- it always escapes using a backslash as far as I have seen. If you have an example of a version of MySQL that escapes by doubling the quote character, I'd be interested in seeing the example. – Roof

@BillKarwin, PHP's mysql_real_escape_string calls MySQL's mysql_real_escape_string, which does whatever it wants to escape the strings. The PHP documentation now says that it uses blackslashes, but the MySQL documentation says nothing about that. I do remember that at some point it would escape with '' instead of \', maybe that's not the case anymore, or maybe it requires specific connection settings (mysql_real_escape_string takes a connection argument so it can check them after all). – Blisse

Aha, I tested and found out that if you set SQL_MODE=NO_BACKSLASH_ESCAPES it behaves like you describe. My confusion was because mysqldump still doesn't do the double-quote characters even if you have that sql mode set. Which means you could create a dump file that can't be reimported on the server that generated it! – Roof

Answer 2

mysql_real_escape_string() also takes into account the character set used by the current connection to the database.

The PHP function mysql_real_escape_string() uses the MySQL C API function of the same name: http://dev.mysql.com/doc/refman/5.1/en/mysql-real-escape-string.html

Also read addslashes() Versus mysql_real_escape_string() by noted PHP security expert Chris Shiflett, for a demonstration that you can get SQL injection exploits even if you use addslashes().

---

Other folks recommend using query parameters, and then you don't have to do any escaping of dynamic values. I recommend this too, but in PHP you'd have to switch to PDO or ext/mysqli, because the plain ext/mysql API doesn't support query parameters.

Also there may be some corner cases where you can't use query parameters for a dynamic string value, like your search pattern in a fulltext search.

Roof

+1 for reading material. It's good to be informed about these topics. – Materials

"have to swtich to PDO" sounds like a benefit to me! – Prefrontal

@Tom: Yeah, I prefer PDO and I do recommend it. But if a project already has lots of code using ext/mysql, it's hard to justify the time to rewrite it all. – Roof

I just started rewriting my DB wrapper to use PDO, if you app deals with critical user data then IMHO it's worth it for the peace of mind. I can't trust myself to always remember to use mysql_real_escape_string... – Excurrent

Answer 3

There was a bunch of history with mysql_escape_string and mysql_real_escape_string. They were both attempts at providing a "general" escaping mechanism that would minimize the probability of sql injection attacks.

mysql_real_escape_string and addslashes are ok, if they're what you really need -- but they probably aren't.

As @afrazier says, you should use prepared statements

Antre

Answer 4

Instead of prepare quer"ies using PDO you can use this while your application uses MySQLi (beware! "i" at and of Mysql")

$nick = $connect->real_escape_string($nick);
$nick= addcslashes($nick, '%_');

$pass = $connect->real_escape_string($pass);
$pass = addcslashes($pass, '%_');

Apothecary

Answer 5

Ignore both and just use parameterized queries. Unless, of course, you like injection attacks.

Saintmihiel

For completeness, could you reference the functions/classes to switch to? I come from a Postgres background so I know to use ps_ functions or PDO, but I don't know what supports parametrized queries for a MySQL user. – Prefrontal

Consistent usage of mysql_real_escape_string will prevent injection attacks. – Et

Vague generalizations aren't very helpful. – Materials

@Ryan Tenney: Escaping is not a full solution to prevent injection attacks. See my presentation: slideshare.net/billkarwin/sql-injection-myths-and-fallacies – Roof

@Tom: You can use PDO for MySQL as well as one can use it for PostgreSQL. @Bill Karwin: Thanks for the link. That's useful to have. – Saintmihiel

@Bill: That's a great presentation, thanks for sharing! I should have been more clear and specified that escaping won't help in some situations, but your presentation covers that in more depth than one could here in a comment. – Et