Shortcomings of MySQL_Real_Escape_String

Shortcomings of mysql_real_escape_string?

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.

mysql(i)_real_escape_string, safe to rely on?

A really great day today - second good attempt to create a sensible database abstraction layer in a row.

should I use mysqli_real_escape_string for sanitization?

Nope.

Just because this function doesn't sanitize anything.

But to format SQL string literals this function is a must and cannot be avoided or replaced.

So, you are using this function exactly the right way, formatting strings only and formatting them unconditionally.

So, you have you queries perfectly safe, as long as you can use a ? mark to substitute the actual data (and - to make even nitpick complains idle - as long as you set SQL encoding using mysql(i)_set_charset() function).

If someone calls your approach broken - just ask them for the complete snippet of proof-code to show the certain vulnerability.

However, let me draw your attention to a couple of important things.

1. Dynamic SQL query parts are not limited to strings only. For example, these 2 queries won't work with your function:

   SELECT * FROM table LIMIT ?,?
   SELECT * FROM table ORDER BY ?
   

   just because numbers and identifiers require different formatting.
   
   So, it's better to use type-hinted placeholders, to tell your function, which format to apply

2. To run a query is only a part of the job. You need to get results as well. Why not to get them already, without bloating your code with unnecessary calls?
3. There should be a way to insert literal ? marks into query without parsing them.

Please, take a look at my class, which built on the very same principle as yours but with improvements I mentioned above. I hope you will find it useful or at least worth to borrow an idea or two.

what is the option for mysql_real_escape_string() function in newest version of php?

mysqli_real_escape_string() if you are using the mysqli API.

Or take a good step forward and use prepared statements :) The URL commented by @DevDonkey is a great introduction to that.

Python: Mysql Escape function generates corrupted query

You can't escape the entire query! You can't construct a query by randomly concatenating strings and then wave a magic wand over it and make it "injection secure". You need to escape every individual value before you put it into the query. E.g.:

"INSERT ... VALUES ('%s', ...)" % self.conn.escape_string(foo)

But really, your MySQL API probably offers prepared statements, which are much easier to use and less error prone. Something like:

self.conn.execute('INSERT ... VALUES (%s, %s, %s, ...)',
                  (foo, bar, baz))

---

Related Topics