Concrete example of where mysql_real_escape_string

Question

I've been sanitising my inputs using mysql_real_escape_string amongst other functions and have recently read that Prepared Statements are the only way to be truly secure. However I am reluctant to use these for two reasons: I don't want to rewrite code and I read that using them can have an impact on performance (queries are used extensively in this application).

So I am looking for a recent, concrete example of where mysql_real_escape_string fails in a query and the resolution is to use Prepared Statements (i.e. there is no way to further sanitise the input and guarantee it's safe).

I ask this because the only example I could find which met the above criteria were from some time ago and have since been patched out in more up to date versions of php.

Answer 1

The entire purpose of mysql_real_escape_string() is to 100% guarantee that there can be no SQL injection in the data you escape with it. It is far superior to addslashes() because it takes into account the current connection's character encoding.

Provided there is not a critical bug in the actual implementation of this function, then it is safe to use.

The reason people do not want you to use it is that you might forget at some place or another, opening up a security hole. Prepared statements are harder to screw up, but there is nothing fundamentally wrong with constructing your own SQL if you properly escape the data.

Edit:

Please take note of the potential ability to change the character set of the connection after it is established, and therefore go out of sync with mysql_real_escape_string. I have not confirmed this is still the case.

Also note that mysql_real_escape_string() must be the last operation performed on the data prior to concatenating it with the SQL.

Also, don't forget the quotes! Eg. AND name = "' . mysql_real_escape_string($name) . '"

Answer 2

mysql_real_escape_string is 100% reliable. It's what you DO with the the escaped data aftewards that fails.

e.g.

$safe = mysql_real_escape_string('some nasty injection-riddled data here');
$unsafe = htmlspecialchars($safe);

m_r_e_s will have done its job perfectly, and then you've potentially undone everything by fiddling with the string again afterwards.

m_r_e_s should be the VERY last operation performed on the string before it's used in an SQL query.

Answer 3

From the PHP Documentation for the function (http://php.net/mysql_real_escape_string), it says "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." Thus, it's usefulness depends on the context. So, for example, if you had the following scenario:

$user_input = mysql_real_escape_string($_GET['user_input']);
$query = "SELECT * FROM `users` WHERE `id` = $user_input";
$result = mysql_query($query);

Well, that would be pretty stupid. Because if the string was 0 or 9=9, then that does not contain any of the characters which the function escapes. However, when injected into the $query literal, it produces :

$query = "SELECT * FROM `users` WHERE `id` = 0 or 9=9";

This would return all rows in the users table...... Which is probably not the intention of the programmer.

So, to sumarise, if the user input is handled by mysql_real_escape_string() but made use of in the context of an integer or other numeric data type, then it would fail to provide sufficient protection.

With prepared statements, you can ensure that each value is not only escaped, but validated to be the correct data type.

In the example above, the safest way to have handled that would have been to use the intval() function instead of the mysql_real_escape_string() function.

Hope that made sense.