Is `mysql_real_escape_string()` truly secure against SQL injection, or are there subtle vulnerabilities?

Is mysql_real_escape_string() Flawless?

While commonly employed to safeguard against SQL injection attacks, mysql_real_escape_string() can be circumvented, albeit in rare and intricate cases.

The Ingenious Attack

With a carefully crafted payload in a highly specific character set, mysql_real_escape_string() can be exploited. Here's a breakdown:

1. Character Set Selection: The server's connection character set must be set to one that supports both an ASCII backslash ('') and a character whose final byte is an ASCII '. For instance, 'gbk' fulfills this criteria.
2. Payload: Apayload composed of the byte sequence '0xbf27' is chosen. In 'gbk', it's an invalid character, while in 'latin1', it represents '¿''.
3. mysql_real_escape_string(): MySQL's implementation of this function recognizes the connection character set (in this case, 'gbk') and applies escaping according to its rules. However, the client still believes the connection is using 'latin1'. Therefore, when it escapes the byte sequence with 'mysql_real_escape_string()', it inserts a backslash.
4. The Query: The resultant string contains a free-hanging ' character: '縗' OR 1=1 /*' in 'gbk'. This is the malicious payload needed for the attack.

The Glaring Vulnerabilities

1. PDO Emulated Statements:PDO, by default, emulates prepared statements using mysql_real_escape_string(), making it vulnerable to this attack.
2. mysql_real_escape_string() Bug: Prior to MySQL 4.1.20 and 5.0.22, mysql_real_escape_string() had a bug that treated invalid multibyte characters as single bytes, even if the client was informed of the correct connection encoding, allowing this attack to succeed.
3. PDO's Limited Safeguards: PDO's DSN character set parameter, introduced in PHP ≥ 5.3.6, is not consistently supported for all commands. This leaves room for exploitability.

Dispelling the Shadows

1. Safe Character Sets: Utilizing invulnerable character sets like 'utf8' or 'utf8mb4' mitigates this attack.
2. NO_BACKSLASH_ESCAPES SQL Mode: Enabling this mode alters how mysql_real_escape_string() functions, preventing the creation of valid characters in vulnerable encodings.
3. Modern MySQL Versions and Prepared Statements: MySQL versions 5.1 (late), 5.5, and higher, along with true prepared statements (e.g., in MySQLi), are immune to this exploit.

In conclusion, although mysql_real_escape_string() is generally effective, it may be circumvented in specific scenarios. Modern versions of MySQL, appropriate character set selection, and true prepared statements ensure complete protection against this intricate attack.

The above is the detailed content of Is `mysql_real_escape_string()` truly secure against SQL injection, or are there subtle vulnerabilities?. For more information, please follow other related articles on the PHP Chinese website!