Shortcomings of mysql_real_escape_string?

by

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.

More Related Contents:

  1. Do I have to use mysql_real_escape_string if I bind parameters?
  2. How can I prevent SQL injection in PHP?
  3. SQL injection that gets around mysql_real_escape_string()
  4. Is “mysqli_real_escape_string” enough to avoid SQL injection or other SQL attacks?
  5. Reference: What is a perfect code sample using the MySQL extension? [closed]
  6. SQL injections in ADOdb and general website security
  7. Is mysql_real_escape_string() broken?
  8. Why is using a mysql prepared statement more secure than using the common escape functions?
  9. Does mysql_real_escape_string() FULLY protect against SQL injection?
  10. how safe are PDO prepared statements
  11. Alternative to mysql_real_escape_string without connecting to DB
  12. Do I have to guard against SQL injection if I used a dropdown?
  13. MySQL Prepared Statements
  14. Is mysql_real_escape_string enough to Anti SQL Injection?
  15. What is the PDO equivalent of function mysql_real_escape_string?
  16. How can I do a migration in laravel 5.5?
  17. Best way to store IP in database?
  18. Which is fastest in PHP- MySQL or MySQLi?
  19. How to backup MySQL database in PHP?
  20. Auto Increment skipping numbers?
  21. SQL Query with binary data (PHP and MySQL)
  22. PDO bindParam into one statement?
  23. Convert Time String to Decimal Hours PHP [closed]
  24. How to get last inserted id from table MySQL [duplicate]
  25. How to make mysqli connect function?
  26. How can I list data in sections by an ID, with a while loop in PHP? [closed]
  27. Save array in mysql database
  28. Using PHP to upload file and add the path to MySQL database
  29. Installing PDO driver on MySQL Linux server
  30. find in set in laravel ? example

Leave a Comment