MySQL Bugs: #8378: String escaped incorrectly with client character set 'gbk'

Bug #8378	String escaped incorrectly with client character set 'gbk'
Submitted:	8 Feb 2005 23:07	Modified:	31 May 2006 20:09
Reporter:	Jim Winstead	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.9	OS:	Windows (Windows)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
Report from Freddie Bingham as comment to Bug #8317:

Your contention is that mysql_real_escape_string is going off of the character
set that was used to connect with, being latin1 by default.  Is the assumption
then if I was to connect with gbk, then this insert would function? It doesn't
as far as my tests go.

How to repeat:

Example:

c:\windows\my.ini:

[client]
port=3306
default-character-set=gbk

PHP Code:

$mysqli = mysqli_init();
$mysqli->options(MYSQLI_READ_DEFAULT_FILE, "C:\windows\my.ini");
$mysqli->real_connect('localhost', 'user', 'password', 'database');
echo $mysqli->character_set_name();

This returns:

gbk_chinese_ci

Insertion code (field is mediumblob):

$string = "ï»¿'";
UPDATE field SET filedata = '" . $mysqli->real_escape_string($string) . "'

Result:

Errormessage: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
''ï»¿\''' at line 2 

This is the same error that is reported when the client character set is not set
and defaults to latin_swedish_ci

This is ugly. The problem is that 0xbf27 is not a valid multi-byte GBK character, but 0xbf5c is. So mysql_real_escape_string() returns 0xbf5c27 when given 0xbf27 as input, but then when the server parses the string, it sees 0xbf5c as a valid multibyte character followed by 0x27.

The workaround for this is to use the prepared statements API, and avoid this issue entirely.

I think that this is probably quite a major issue. Take for example, the following SQL query:

SELECT * FROM user WHERE username = '¿\' OR userid = 1 -- '

That could easily be produced by:
$username="¿' OR userid = 1 -- ";
mysql_query("SELECT * FROM USER WHERE username = _latin1'" . mysql_real_escape_string($username) . "'");

What I'm saying is that this bug opens the door to SQL injection attacks, when running in GBK and some other multi-byte charsets.

I have posted this comment in bugs #8317 and #8378 as I think that it's pertinent to both: mysql_real_escape_string() needs to throw an error if it can't properly escape the string in the current charset; the parser needs to be able to use the _latin1 introducer to determine how it parses the following literal string.

Jim, the fix looks fine. But It seems it can lose some tricky
combinations: if the first byte is a MB-head character,
but the second bite is  \0 or \r or any other character from
the following 'switch (*from) {...}', then the result will
not be correct. Will you fix it please?
Thank you!

I'm changing the status back to In-progress.

Fix for the problem that was pointed out: http://lists.mysql.com/internals/21971

Pushed, will be in 4.1.11.

Noted in 4.1.11 changelog.

$binary = "\xa3\x27"; and escape it correctly with gbk connection.

still cannot be escape, at least, in both 4.1.14 and 4.1.18

can mysql.com write a test case enuming+escaping 1/2/3/4 bytes binary in all connection character sets, to make sure all binary data works? it may take some time to complete the test.

i'd reopen this bug if i can.

Same problem in sjis, the character 圭 (0x8c5c)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6786

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6802

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/33584

4.1.20, 5.0.22, 5.1.11

Cannot tell the nature of the problem or what was fixed.
Could you provide a sentence for the changelog entry, please?
Thanks.

Noted in 4.1.20, 5.0.22, 5.1.11 changelogs.

Security fix: An SQL-injection security hole has been found in
multi-byte encoding processing. The bug was in the server,
incorrectly parsing the string escaped with the
mysql_real_escape_string() C API function.

The changelogs also contain additional discussion of the
exploit and workarounds.

Since PHP MySQL extensions don't read my.cnf, this security issue is still present in PHP, also if build against latest libmysql, right?

PHP extensions allways use the default character-set, compiled into libmysql, so you have no chance to make c-api function mysql_real_escape_string() character-set aware.
AFAIK ext/mysql and ext/pdo don't provide means to change the character-set used by PHP function mysql_real_escape_string(), respectively PDO::quote().

How could this be solved for PHP?

Hi,

what's about 4.0? Is any fix for this production release avaible?

Dimi

PHP 5's mysqli extension provides a function mysqli_set_charset, which fixes this problem for PHP.

4.0 is not affected

Hello,

We have quite a large installed customer base with 4.1.14.  Outside of reinstalling with 4.1.20 or later would anybody know if simply disabling multibyte charsets would remedy this issue?  I am assuming this can be done by removing the offending characters sets from ../share/mysql/charsets/Index.xml.