Why mysql_real_escape_string() shouldn't avoid any SQL Injection?

Question

I've heard people say (in relation to C#/SQL Server, but also in relation to PHP/MySql): Don't escape strings manually - use stored procedures instead.

Ok, I can accept this suggestion, but why? Many say (including on SO) mysql_real_escape_string() is quite enough, mysql_real_escape_string() is good, mysql_real_escape_string() is the first way of protection.

Why? Is there a case where mysql_real_escape_string() can fail? At least one... I don't need many :)

Answer 1

When mysql_real_escape_string FAIL:

$sql = "SELECT * FROM users WHERE id=" + mysql_real_escape_string($_GET['id']);

If $_GET['user_id'] is set to 1 OR 1=1, there are no special chars and it's not filtered.

The result: All rows are returned.

It gets worse. How about this... what if $_GET['user_id'] is set to 1 OR is_admin = 1?

The function is only designed to be used when inside single quotes.

Answer 2

There are two things that can go wrong with mysql_real_escape_string:

• You can forget to use it
• If you are using some specific multibyte connection encodings, and you have set these encodings with SET NAMES instead of mysql_set_charset as is proper, it can still leave you vulnerable

Update:

• You are safe with UTF-8 (although this does not mean that you should continue using SET NAMES!)
• For an explanation, see here

Answer 3

Just for info: mysql_real_escape_string() does not escape % and _. These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.