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.