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?
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_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.
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.
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.
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
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
0xbf5c27—but if the connection encoding is GBK, this will have converted an invalid string to
縗' (note the unescaped
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:
mysql_real_escape_string()will provide no protection whatsoever (and could furthermore munge your data) if:
NO_BACKSLASH_ESCAPESSQL mode is enabled (which it might be, unless you explicitly select another SQL mode); and
your SQL string literals are quoted using double-quote
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?