Does `mysql_real_escape_string()` Really Prevent SQL Injection?

Does mysql_real_escape_string() Suffice for SQL Injection Prevention?

While many believe that employing mysql_real_escape_string() safeguards against SQL injection, there is indeed a bypass possibility.

The Flaw

In specific cases, such as:

mysql_query('SET NAMES gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

The issue arises due to a mismatch between the server's expected character set and the client's perception. By using 'SET NAMES' to modify the character set on the server, mysql_real_escape_string() may escape ' as in ASCII, while the server expects it as plain text. This creates a discrepancy, permitting a free-hanging ' and injections.

The Bad

PDO's default emulation of prepared statements can also lead to this loophole. Disabling emulation is recommended, but fallback to emulation for unsupported queries can prevent full protection.

The Ugly

Prior to MySQL 4.1.20, a bug allowed invalid multibyte characters like those seen in the payload to be escaped as single bytes, even with correct client character set information.

The Saving Grace

Using impervious character sets like utf8mb4 or utf8, or enabling the NO_BACKSLASH_ESCAPES SQL mode, provides protection against this vulnerability.

Conclusions

Employing mysql_real_escape_string() remains effective in many situations, but it is crucial to be aware of potential edge cases. Using secure techniques such as enumerated fields and prepared statements, along with proper character set and mode configuration, is essential for comprehensive SQL injection protection.

The above is the detailed content of Does `mysql_real_escape_string()` Really Prevent SQL Injection?. For more information, please follow other related articles on the PHP Chinese website!