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?

Freehearted

Answer 1

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

Morelos

Any encoding with a valid multibyte character ending in 0x5c can sneak quotes past addslashes(). Chris Shiflett has an excellent example on his blog using GBK. shiflett.org/blog/2006/jan/… – Paintbrush

Quite contrary - in general addslashes are all right but in some extremely rare cases we actually need mres. – Navar

Answer 2

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

Backhander

Answer 3

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.

Ethiopic

Answer 4

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

Imprudent

Most of points makes no sense. addslashes creates no less legal SQL string, whatever it means. mres does no character conversion at all. and there is no point in terminating strings with NUL. Where did you get all that? – Navar

Straight from the documentation. Multibyte may be converted to wide. "The string pointed to by from must be length bytes long. You must allocate the to buffer to be at least length*2+1 bytes long. (In the worst case, each character may need to be encoded as using two bytes, and you need room for the terminating null byte.)". I'm assuming that the PHP library invokes the mysql_real_escape_string C API function under the hood. – Imprudent

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

Dolmen

Answer 6

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.

Navar

As long as it is using any single-byte charset or utf8 - it is perfectly safe with addslashes(). Why would you use addslashes as a practice where you suddenly have to change your practice as soon as you work with Unicode? – Imprudent

It is unfortunate that from PHP you must set the character set at the server level or use mysqli_set_charset. This isn't the case from C. – Imprudent

@Imprudent "[addslashes is perfectly safe with] utf8 … Why would you use addslashes [if] you suddenly have to change your practice as soon as you work with Unicode?" You misunderstand; UTF-8 is Unicode. The only vulnerable cases are with non-Unicode character sets such as GBK. That is, addslashes() is perfectly safe for anybody who uses Unicode, and is only unsafe for people who choose to use weird national character sets in place of Unicode. – Tasiatasiana

One of the extremely rare answer that addresses objectively the addslashes() case. Plain common sense! – Oriel

Answer 7

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.

Thaumatology

% and _ have nothing to do with SQL injection. – Mcelhaney

In MySQL, % can be used as a wild card in GRANT statements. Also MySQL treats _ as a single character wild card in statements. – Thaumatology

% and _ have nothing to do with SQL injection. – Navar

Answer 8

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.

Alphaalphabet

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.

Ossy

I guess I should clarify that I'm perfectly aware of the superiority of parameterized queries :) – Freehearted

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.

Provinciality

Answer 11

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

Chavers

Answer 12

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

Electioneer

Why not to use prepared statements already? – Navar

prepare() didn't gave me the same good result quote() for a string with quotes. – Electioneer

That's 100% impossible. Definitely, it is not "prepare" but you did something wrong on your part. When working with PDO, you are supposed to use prepared statements for the data variables all the time. – Navar