What does mysql_real_escape_string() do that addslashes() doesn't?

Question

Why do we need a DB-specific functions like mysql_real_escape_string()? What can it do that addslashes() doesn't?

Ignoring for the moment the superior alternative of parameterized queries, is a webapp that uses addslashes() exclusively still vulnerable to SQL injection, and if yes, how?

Answer 1

When using PHP PDO use $conn->quote($string). See: https://www.php.net/manual/en/pdo.quote.php

Answer 2

mysql_real_escape_string does a lot more than addslashes does.

addslashes operates on pure ascii, without knowing anything about the database. It escapes:

• ' → \'
• " → \"
• \ → \\
• ASCII 0 → \0.

mysql_real_escape_string's purpose is to "create a legal SQL string that you can use in an SQL statement." mysql_real_escape_string takes into account the current character set of the connection (which is why you must always pass a valid $mysql object).

It does the following (taken from the MySQL C API documentation):

• Encodes: \, ', ", ASCII 0, \n, \r, and Control+Z in a way that won't cause problems
• ensures a 0 byte terminates the string (in the C api)
• may perform a multibyte (ascii) to wide character conversion if the DB linked to in $mysql uses a wide character set.

I don't really know how PHP stores strings internally, but the point is all of this is available to PHP when you use mysql_real_escape_string. I guess the main point of difference is mysql_real_escape_string considers the character set of the connection, where addslashes cannot (it doesn't even know what DB you are connected to).

Answer 3

Why do we need a DB-specific functions like mysql_real_escape_string()?

Actually, most of time we don't.
This function is required for a few extremely rare encodings, and to prettify mysql logs and dumps a bit.

is a webapp that uses addslashes() exclusively still vulnerable to SQL injection?

As long as it is using any single-byte charset or utf8 - it is perfectly safe with addslashes().

What can it do that addslashes() doesn't?

It can protect SQL string literal in case of some rare encodings.

However, it can't do it by itself. A proper encoding have to be set using mysql_set_charset() function first. If this function haven't been used, mysql_real_escape_string() would behave exactly the same way as addslashes() in terms of charset handling - there would be no difference at all.

Answer 4

According to my understanding mysql_real_escape_string() do the work more precisely, as it communicate with db to first check that what needs to be encoded and then encode accordingly, isn't it? So there for it work more effeciently

why you want to first do addslashes and then you will remove that slashes before showing that data and still addslashes is not as efficient as mysql_real_escape_string , use mysql_real_escape_string if you are using mysql_query like db functions for quering, or I think PDO with prepare is better way, as mysql_real_escape_string is db specific

Answer 5

somedb_real_escape_string() is database specific, addslashes() is not.

In the case of MySQL this means:

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 the manual.)

Answer 6

PHP's mysql_real_escape_string function will, more or less, ask mysql what character(s) needs to be escaped, where the addslashses function will just add a backslash in front of and any single quote ('), double quote ("), backslash () or NUL (the NULL byte) character.

The two practical effects are, addslashes tends not to work well with multibyte characters, and, more importantly, by asking mysql what characters need to be escaped, you avoid a possible future compatibility. Using assslashes is kind of like hardcoding a couple of specific characters into the escape sequence.

Answer 7

gs's harshly downvoted answer is actually kinda right.

Standard SQL uses doubling to escape a literal apostrophe. MySQL's non-standard use of backslashes for escaping is the default setting, but it can be disabled and often is, in particular in sql_mode ANSI.

In this case only the doubled syntax will work, and any app you have using addslashes (or other ad-hoc escaping method) will break. mysql_real_escape_string will use whichever escaping method is best for the connection's sql_mode.

The multibyte encoding issue is also important if you're still using those nasty East Asian encodings that re-use the lower 128 characters, but then really you want to be using UTF-8 instead. \n-escaping, on the other hand, is of no concern since MySQL can perfectly happily cope with a raw newline in a statement.

Answer 8

Addslashes is generally not good enough when dealing with multibyte encoded strings.

Answer 9

It's supposed to escape strings for MySQL in a way that other quoting facilities don't.

Much preferable, however, is to use the mysqli interface, and use parametrized prepared queries instead of trying to make sure all your strings are properly escaped. Using parametrized queries obviates the need for such messy string work and strongly mitigates the risk of SQL injection.

Edit: I'll clarify a little on why I consider quoting a bad idea: It's easy to forget when and where you need to quote - whether your variable is to be a string or number, whether it has already been quoted, etc. A parametrized query has none of these issues, and the need for quoting is completely obviated.

Answer 10

According to the PHP manual:

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.

Answer 11

It adds slashes to:

\x00, \n, \r, \, ', " and \x1a. characters.

Where addslashes only adds slashes to

' \ and NUL

Ilias article is also pretty detailed on its functionality

Answer 12

The only real difference that I know of is that mysql_real_escape_string() will take the database's character set into consideration when escaping the input string. Neither function will escape wild card characters % and _ which still leaves the script open to some SQL injection.