Does mysql_escape_string have any security vulnerabilities if all tables using Latin1 encoding?

Question

I have heard that the PHP function mysql_escape_string has security vulnerabilities related to mult-byte characters. Are there any vulnerabilities if all tables are using Latin1 encoding?

Answer 1

The issue is that mysql_escape_string() does not check the database for character encoding. As a result mysql_escape_string() would be unaware of your database encoding and may treat multi-byte characters as single byte characters. This could result in the last two bytes being escaped into a reserved character such as ’ as well as countless other character which have special relevance for the SQL engine.

In such a case, the attacker could append additional SQL commands to gain access to data or functions not intended. This is why the function is deprecated and the use of mysql_real_escape_string() is recommended.

mysql_real_escape_string() works almost identically except that it connects to the database to determine what encoding is used by the database, preventing known multi-byte escaping issues.

Also note that mysql_escape_string() and mysql_real_escape_string() do not escape the % and _ characters (as per the manual reference http://php.net/manual/en/function.mysql-escape-string.php and http://php.net/manual/en/function.mysql-real-escape-string.php see notes). This could allow access to data that not intended when used with keywords such as LIKE, so special care should be taken.

---

Update

So, what encoding does mysql_escape_string assume?

I believe it is ASCII, however I am unable to confirm this. Regardless it makes no difference to the result which is; using Latin1 for database encoding could result in a security vulnerability if not properly encoded.

Also note that this is not just vulnerability with the database. Within the application/server-side script you must be careful when handling input to be aware of how strings are passed from function to function and ensure that correct encoding is preserved. Ideally using UTF-8 end-to-end would be good but not always an option.

Answer 2

Personally, I think you are asking the wrong question. If you want to avoid SQL injection vulnerabilities -- as well you should -- the answer is not to use mysql_escape_string more carefully. The right answer is to use prepared statements.

The fundamental problem is that mysql_escape_string is fragile, and it is hard to predict all of the specific ways in which it might break. You have learned one source of fragility in mysql_escape_string, and now wonder if that is the only source.

Personally, I take away a different lesson. The lesson I draw is that, if security is the job, mysql_escape_string is the wrong tool for the job. Instead, you should be using prepared statements (parametrized queries).

Among the security world, it is pretty widely accepted that the most robust way to avoid SQL injection is to use prepared statements. Don't try to escape/encode your data and then build up a SQL query using string concatenation; that approach is fragile and can easily break if, e.g., the database interprets your query differently than you expected. So my advice is: don't try to get clever -- just use prepared statements and be happy.

Answer 3

Per @BernieWhite's answer, mysql_escape_string() performs escaping in complete ignorance of the string encoding that is actually being used for the database connection (which is separate from that used in your tables).

Therefore, it attempts to escape ' characters by replacing them with \' whenever they occur. It does this on a bytewise basis, replacing every occurrence of 0x27 with 0x5c27 (i.e. it is effectively operating under the assumption that the string is encoded in a single-byte superset of ASCII); for example, it will convert the string 0xbf27 to 0xbf5c27—but if the connection encoding is GBK, this will have converted an invalid string to 縗' (note the unescaped ' character).

Thus mysql_real_escape_string() was introduced to perform escaping properly, i.e. according to the connection character encoding. However, one must therefore inform the client library of the character encoding by calling mysql_set_charset()—this step is often overlooked, and leaves one just as vulnerable as if the original mysql_escape_string() was being used!

But even if one uses mysql_real_escape_string(), there are still other edge cases that can leave you vulnerable. As explained in my answer to "SQL injection that gets around mysql_real_escape_string()" over on StackOverflow:

TL;DR

mysql_real_escape_string() will provide no protection whatsoever (and could furthermore munge your data) if:

• MySQL's NO_BACKSLASH_ESCAPES SQL mode is enabled (which it might be, unless you explicitly select another SQL mode); and

• your SQL string literals are quoted using double-quote " characters.

Because of these practical difficulties in ensuring that embedded string literals are handled safely, it is generally recommended that one not even attempt it! Instead, one can send literal values to the database connection in packets that are entirely separate from the SQL; consequently, the server will not even attempt to parse those values for anything else: this is known as "query parameterisation", is what @D.W.'s answer suggests, and is nicely explained in How can I prevent SQL injection in PHP?