How to Properly Escape MySQL Wildcards in LIKE Clauses?

Escaping MySQL Wild Cards

Problem

On legacy servers where prepared statements are unavailable, escaping user input before sending it to MySQL is essential. However, using mysql_real_escape_string alone doesn't escape MySQL wildcards (_ and %) and certain patterns return unexpected results.

For instance, when the input "test_test ' is sent, the database shows "test_test '." Why does the underscore have a backslash prefix while the single and double quotes don't?

Answer

_ and % are not wildcards in MySQL and should not be escaped when inserted into string literals. mysql_real_escape_string is sufficient for this purpose.

However, _ and % do require special handling when used in LIKE-matching expressions to ensure they match only their literal values, not patterns. This involves two levels of escaping:

LIKE escaping:

• Escapes underscore, percent, and any escape character used in LIKE expressions.
• This must be done inside SQL, even with parameterized queries.

String literal escaping:

• Escapes characters used in string literals, including the LIKE escaping character.
• This is done outside SQL, for example, using mysql_real_escape_string.

Example

For MySQL, if you want to match against a literal percent sign, you would use double backslash escaping, e.g., LIKE 'something\%`.

To avoid this confusion and ensure portability, you can override the default escape character using the LIKE ... ESCAPE ... construct.

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 ...";

or with parameters in PDO:

$q= $db->prepare("... WHERE name LIKE ? ESCAPE '=' AND ...");
$q->bindValue(1, '%'.like($name, '=').'%', PDO::PARAM_STR);

The above is the detailed content of How to Properly Escape MySQL Wildcards in LIKE Clauses?. For more information, please follow other related articles on the PHP Chinese website!