0

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.

user991987
  • 213
  • 1
  • 3
  • 7
  • Can you source where you read "Prepared Statements are the only way to be truly secure"? Seems odd that a statement like this would be made without offering some insight as to why mysql_real_escape_string is considered less secure. – Mike Purcell Jan 30 '12 at 21:03
  • Please see the top answer of this stackoverflow question: http://stackoverflow.com/questions/2353666/php-is-mysql-real-escape-string-sufficient-for-cleaning-user-input – user991987 Jan 30 '12 at 21:08

3 Answers3

7

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.

ralfe
  • 1,412
  • 2
  • 15
  • 25
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) . '"

gahooa
  • 131,293
  • 12
  • 98
  • 101
  • besides, you need to pass your data to the prepared statement anyway, so if you can't escape content being passed to the database, you're screwed – Cal Jan 30 '12 at 21:05
  • Please see the top answer of this stackoverflow question: http://stackoverflow.com/questions/2353666/php-is-mysql-real-escape-string-sufficient-for-cleaning-user-input – user991987 Jan 30 '12 at 21:07
  • But data passed correctly to a prepared statement does not need to be escaped (in terms of SQL Injection). A prepared statement knows what is data and what is part of the query syntax. Nevertheless, you CAN PASS DATA IN AN INCORRECT WAY to a prepared statement. i.e., CONCATENATING the data to the query string. If you HAVE to do that, use mysql_real_escape_string() on it. – Sebastián Grignoli Jan 30 '12 at 21:10
  • Or intval() for integers and floatval() for floating point numbers. – Sebastián Grignoli Jan 30 '12 at 21:46
0

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.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • Thanks. But what do you think about this: http://stackoverflow.com/questions/2353666/php-is-mysql-real-escape-string-sufficient-for-cleaning-user-input – user991987 Jan 30 '12 at 21:07
  • XSS has absolutely nothing to do with sql injection. They're completely different threats, and m_r_e_s will neither prevent nor encourage the possibility of xss. That's not its job. – Marc B Jan 30 '12 at 21:14