Is mysql_real_escape_string vulnerable to invalid UTF-8 exploitation eg overlong UTF-8 or ill formed UTF-8 sequences?

Question

Assuming I have my database set up as follows to use utf-8 (the full 4mb version in mysql)

mysql_query("SET CHARACTER SET utf8mb4");
mysql_query("SET NAMES utf8mb4");

I am using mysql_real_escape_string to escape unwanted characters before putting a string into sql (note - I am not looking for advice to switch to PDO, I want to establish whether mysql_real_escape_string is safe against overlong utf8 etc).

$input = mysql_real_escape_string($_POST['field']);
$sql = "SELECT * FROM `table` WHERE `header`='$input'";

Is there any validation I need to do to $_POST['field'] (eg. to check if the string is valid UTF-8 and is not overlong and does not contain invalid sequences etc) before doing my mysql_real_escape_string or is that sufficient?

Answer 1

A public service announcement before my answer. You're still using mysql_query. You will, eventually, have to upgrade to mysqli at the very least, even if you don't want to go PDO. All the mysql_ functions are depreciated (see the big red scary box in the previous link) and will likely be removed in PHP 5.6. This is important because the main reason to suggest PDO in your case is prepared statements, which mysqli can also do. A prepared statement is far less vulnerable to injection than escaping, but requires more queries (small performance hit) to do.

As to UTF8, what I would recommend is using mb_check_encoding to ensure the string is at least valid UTF8 before attempting to insert it.

Finally, there's this answer, which offers these words of wisdom

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.

Answer 2

All the input validation and anti-SQL injection has been subject to soo many misconceptions. In fact, all this boils down to one single thing:

Ensure correct SQL query syntax

If you are able ensure correct SQL syntax for any input data, you are safe and you don't need to read or study anything about validation or sql injection at all. Because all these vulnerabilites are only possible in situations where you allow incorrect SQL syntax.

To ensure correct SQL query syntax in your case, you have to make sure your $input is escaped in the query correct way. Look at the PHP docs: http://php.net/mysql_real_escape_string:

caution Security: the default character set

The character set must be set either at the server level, or with the API function mysql_set_charset() for it to affect mysql_real_escape_string(). See the concepts section on character sets for more information.

So, mysql_real_escape_string has to be informed correctly on your character set to be able to escape properly. So, instead of your mysql_query("SET NAMES utf8mb4");, you should do:

mysql_set_charset("utf8mb4");