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

3 Answers 3

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

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?

share|improve this answer

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

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.