Take the 2-minute tour ×
Information Security Stack Exchange is a question and answer site for Information security professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

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.

share|improve this answer
    
So, what encoding does mysql_escape_string assume? –  user5385 Oct 11 '11 at 5:00
add comment

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.

share|improve this answer
1  
When code is live, updating it requires a huge amount of testing. I need to know whether fixing this issue is critical or just something that would be nice to do –  user5385 Oct 11 '11 at 6:32
    
Replacing mysql_escape_string() correctly with mysql_real_escape_string() can be done almost fully automatically - and it's straightforward to detect the (v. rare in the wild) scenarios where a specific connection must be passed. OTOH using prepared statements requires a major manual rewrite and testing. –  symcbean Oct 11 '11 at 11:04
    
@symcbean: it's not that big of a deal, really. You can always replace an existing set of database access functions with an API compatible set that uses parametrized queries under the hood (and exposes them in a compatible way), and then phase out the non-parametrized queries at a rate that can be managed comfortably. Writing such a wrapper should take less than a day, and if you have unit tests for the old code, you can just keep them intact. –  tdammers Oct 11 '11 at 17:52
1  
@user5385: we can't judge your security decisions, but using mysql_real_escape_string over mysql_escape_string is the least you can do - it is trivial to implement, and you defend at least against basic threats. The added benefit of parametrized queries, in this context, is that the code is neater and more convenient, and that it is harder to forget to escape (and quote!) a value. –  tdammers Oct 11 '11 at 17:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.