Shortcomings of mysql_real_escape_string?

Question

I have seen a few people on here state that concatenating queries using mysql_real_escape_string will not protect you (entirely) from SQL injection attacks.

However, I am yet to see an example of input that illustrates an attack that mysql_real_escape_string would not protect you from. The majority of examples forget that mysql_query is limited to one query and use mysql_real_escape_string incorrectly.

The only example I can think of is the following:

mysql_query('DELETE FROM users WHERE user_id = '.mysql_real_escape_string($input));

This would not protect you from the following input:

5 OR 1=1

I would see this as incorrect usage of mysql_real_escape_string rather than a shortcoming, it is designed for strings not numeric values. You should either cast to a numeric type or if you are going to treat the input as a string when sanitising you should do the same in your query and wrap quotation marks around it.

Can anyone provide an example of input that can get around mysql_real_escape_string that does not rely on incorrect handling of numeric values or forget that mysql_query can only execute one query?

Edit: I am interested in the limitations of mysql_real_escape_string and not comparing it to alternatives, I realise there are better options for new projects and am not disputing that.

Answer 1

The main shortcoming of mysql_real_escape_string, or of the mysql_ extension in general, is that it is harder to apply correctly than other, more modern APIs, especially prepared statements. mysql_real_escape_string is supposed to be used in exactly one case: escaping text content that is used as a value in an SQL statement between quotes. E.g.:

$value = mysql_real_escape_string($value, $link);
$sql = "... `foo` = '$value' ...";
                     ^^^^^^

mysql_real_escape_string makes sure that the $value in the above context does not mess up the SQL syntax. It does not work as you may think here:

$sql = "... `foo` = $value ...";

or here:

$sql = "... `$value` ...";

or here:

$sql = mysql_real_escape_string("... `foo` = '$value' ...");

If applied to values which are used in any context other than a quoted string in an SQL statement, it is misapplied and may or may not mess up the resulting syntax and/or allow somebody to submit values which may enable SQL injection attacks. The use case of mysql_real_escape_string is very narrow, but is seldom correctly understood.

Another way to get yourself into hot water using mysql_real_escape_string is when you set the database connection encoding using the wrong method. You should do this:

mysql_set_charset('utf8', $link);

You can also do this though:

mysql_query("SET NAMES 'utf8'", $link);

The problem is that the latter bypasses the mysql_ API, which still thinks you're talking to the database using latin1 (or something else). When using mysql_real_escape_string now, it will assume the wrong character encoding and escape strings differently than the database will interpret them later. By running the SET NAMES query, you have created a rift between how the mysql_ client API is treating strings and how the database will interpret these strings. This can be used for injection attacks in certain multibyte string situations.

There are no fundamental injection vulnerabilities in mysql_real_escape_string that I am aware of if it is applied correctly. Again though, the main problem is that it is terrifyingly easy to apply it incorrectly, which opens up vulnerabilities.

Answer 2

Ok, so apart from mysql_* being deprecated, I understand your wanting to know about any possible workaround that might exist. perhaps this blog post and the slides might reveal some of them.
But as this older question here shows, casting and quoting isn't full proof. There's just So many things that can wrong, and Murphy's law, twined with that ever valid mantra "Never trust the network", will go horribly wrong.

Perhaps this article, but most importantly, the follow-up to that article can reveal even more security issues. To be honest, I know mysql_real_escape_string isn't fullproof, even in combination with type casting and string formats:

printf('WHERE id = \'%d\'',(int)mysql_real_escape_string($_REQUEST['id']));

doesn't cover every possible attack.
I'm no expert on this matter, but what I can tell you is sanitizing every input, will, if anything, give you a FALSE sense of security. Most of the time, you'll know (initially) what and why and how you protect against the attacks, but your colleagues might not. They might forget something, and your entire system is compromized.

In summary: Yes, you might be able to prevent any form of malicious input from getting to your DB, but every additional action it requires is an added risk. In that scenario, the greatest liability (as always) is the developer that hasn't had is fourth cup of coffee on a monday morning. No code, no matter how defensive and well thought out, can protect itself from the monster that is a tired developer with a bad temper, going cold turkey on caffeine and nicotine.