How Can I Safely Escape MySQL Special Characters, Including Wildcards, for Database Insertion?

Escaping MySQL Special Characters for Database Insertion

When inserting user input into a MySQL database, it's crucial to prevent potential vulnerabilities by properly escaping special characters. The PHP function mysql_real_escape_string effectively handles this task, but it does not escape the MySQL wildcards % and _.

To address this, many developers employ the addcslashes function in conjunction with mysql_real_escape_string to escape these wildcards as well. However, this approach can lead to unexpected results.

Understanding Wildcard Escaping in MySQL

Contrary to common belief, _ and % are not considered wildcards in MySQL for general string literals. They should only be escaped when used in LIKE statements for pattern matching.

The Hierarchy of Escaping

Escaping special characters in the context of LIKE statements involves two levels:

1. LIKE Escaping: Within LIKE statements, _ and % must be escaped, along with the escape character. The escape character itself must be escaped as well.
2. String Literal Escaping: After LIKE escaping, strings must undergo regular string literal escaping based on the specific database's requirements. For MySQL, this is done using mysql_real_escape_string.

The Confusion with Double Backslashes

MySQL uses the backslash character as the escape character for both LIKE escaping and string literal escaping. This can lead to confusion, as seen when inserting a string containing %. Double-backslashes must be used to escape it for LIKE purposes, and then the string as a whole must be escaped again for string literal insertion.

ANSI SQL and Portable LIKE Escaping

ANSI SQL defines that backslashes within string literals represent literal backslashes, and single quotes are escaped using ''. However, MySQL deviates from this standard. To ensure portability, it's recommended to override MySQL's default behavior and specify a custom escape character using the LIKE ... ESCAPE ... construct. For example:

function like($s, $e) {
    return str_replace(array($e, '_', '%'), array($e.$e, $e.'_', $e.'%'), $s);
}

$escapedname = mysql_real_escape_string(like($name, '='));
$query = "... WHERE name LIKE '%$escapedname%' ESCAPE '=' AND ...";

The above is the detailed content of How Can I Safely Escape MySQL Special Characters, Including Wildcards, for Database Insertion?. For more information, please follow other related articles on the PHP Chinese website!