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.

Lutero

This question is deprecated. Why finding solutions for mysql_* when you have good alternatives? – Hinze

This is a great question. I have seen the same FUD several times as well without any concrete backup that was not simply based on incorrect usage. Regardless of the fact that you should not be using mysql_ to begin with, I'd like to see an answer to this. – Burgas

Mihai, I am not disputing there are better alternatives such as PDO, that is not the point of this question. – Lutero

"I have seen a few people on here state ..." Have you tried asking those people? Good question though – Ivanivana

I would guess they don't know a real example and I didn't want to pollute other questions with this discussion. Rather than asking one person in comments, I'd rather address the whole community with my own question. – Lutero

This function doesn't escape the percent and the underscore (%_), which are wildcards in LIKE. Does that count as a shortcoming? – Ivanivana

I guess that is a minor shortcoming but not the full-blown SQL injection attack I had in mind. – Lutero

Pitty you ask this question now, a tad late since the deprecation process for mysql_* has begun, but I fully understand your wanting to know about possible malicious requests and how given functions fend them off – Bushwhack

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.

Burgas

Thank you deceze, it seems the claims on here of mysql_real_escape_string being open to injection only apply when it is used incorrectly. I am going to accept this as the answer. – Lutero

See also additional vulnerability when using NO_BACKSLASH_ESCAPES. – Phosphorylase

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.

Bushwhack

How exactly would you exploit the code snippet you show? The question (and I) asks for a specific, practical example. – Burgas

The first two references you link to are classic attacks through incorrect application, the third even states "To avoid this type of vulnerability, use mysql_real_escape_string() [...]". I'd consider this yet another non-answer. – Burgas

@deceze: in itself, the snippet I posted can't be exploited, AFAIK, but can produce errors, and ppl with bad intentions like a good call stack when probing for vulnerabilities. It's also quite likely that you're going to be using more then 1 param in the WHERE clause, each of which requires three additional operations, (string format, type cast, and escape call), so this approach is error prone (get a cast or placeholder wrong and it's no longer safe). In the question I linked too, the point of collation is raised, too, which my snippet doesn't deal with at all – Bushwhack

@deceze: as far as the third link goes, I was mainly focussing on the comment section to be honest, there somebody linked too this follow-up which, I must admit, would have been more informative – Bushwhack

The best these articles can point to is the well-known incorrect application of the GBK/charset problem. I.e. when you get yourself into a situation where mysql_real_escape_string assumes a different charset when doing its job than the database. This is easily avoided by using the mysql_ API to change the charset instead of bypassing it using a SET NAMES query. I'd agree that that's is a shortcoming of the API, but not really a vulnerability of _real_escape_string. The mysql_ API may be harder to apply correctly than PDO, but I have yet to see a fundamental vulnerability in it. – Burgas

@deceze: I agree with you, I'm not going to say there is a fundamental vulnerability in the extension. That's why I summarized by saying the greatest liability lies in the developers, because there's a lot more things they have to consider at any given moment. – Bushwhack