Why is MySQL Escaping Wildcards Behaving Unexpectedly?

Understanding MySQL Escaping for Wild Cards

Problem:

While escaping user input using mysql_real_escape_string, the output in MySQL shows unexpected behavior with certain characters. Specifically, the underscore character is preceded by a backslash, while single and double quotes are not.

Explanation:

Contrary to the assumption, and % are not MySQL wildcards in general usage and should not be escaped when inserting them into string literals. mysql_real_escape_string is sufficient for this purpose, and adding addcslashes for and % should be avoided.

Wildcards only become relevant in the context of LIKE-matching. When expressing strings as literal LIKE expressions, an additional layer of escaping is required where _ and % must be escaped. This is done separately from general string escaping using the same backslash character.

Solution:

For LIKE-matching, follow these steps:

1. Use LIKE escaping, where _ and % must be escaped, and the escape character must also be escaped.
2. Perform general string escaping using mysql_real_escape_string in MySQL or appropriate functions for other databases.

Example:

To match a literal percent sign, double-backslash-escape it in LIKE escaping (e.g., LIKE 'something\%' in MySQL) or use a different escape character with the LIKE ... ESCAPE ... construct for portability.

Portable LIKE Escaping Function:

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

Example with Parameters:

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

The above is the detailed content of Why is MySQL Escaping Wildcards Behaving Unexpectedly?. For more information, please follow other related articles on the PHP Chinese website!