Use character encoding problem to bypass the SQL injection of mysql_real_escape_string()

Although the mysql_real_escape_string() function prevents SQL injection, it may be bypassed in certain cases.

Consider the following PHP code:

$login = mysql_real_escape_string(GetFromPost('login'));
$password = mysql_real_escape_string(GetFromPost('password'));

$sql = "SELECT * FROM table WHERE login='$login' AND password='$password'";

This code seems safe, but it may be exploited due to the edge case of character set encoding.

Attack method:

Attacks depend on the following steps:

1. Set character set: Select an encoding (for example, gbk) where the same byte sequence represents both non-ASCII characters and ASCII backslash ('\').
2. Construct the payload: Use a carefully constructed payload that contains invalid multibyte characters, ensuring that its last byte represents an ASCII backslash.
3. call mysql_real_escape_string(): client believes that the connection uses a different character set (for example, latin1), so mysql_real_escape_string() will be single Insert a backslash before the quotes, resulting in a syntactically valid string.
4. Submit query: The escaped payload becomes part of the SQL statement, allowing the attacker to bypass the expected protection.

Working principle:

The key problem is that the character set expected by the server does not match the character set considered by the client. Although mysql_real_escape_string() is escaped based on the connection encoding set by the client, in some cases it treats invalid multibyte characters as a single byte, including using SET NAMES instead of mysql_set_charset() case.

as a result of:

This attack can bypass PDO's simulated preprocessing statement even if the simulated preprocessing statement is disabled.

Remedy:

Use non-vulnerable character sets, such as utf8mb4 or utf8, to mitigate this problem. Enable NO_BACKSLASH_ESCAPES SQL mode also provides protection.

Safe Example:

Always set the character set correctly using mysql_set_charset() or PDO's DSN character set parameters. Real preprocessing statements in MySQLi are also immune to this attack.

in conclusion:

While mysql_real_escape_string() usually provides strong protection, it is important to be aware of such potential edge cases to ensure full defense against SQL injection.