How to Properly Escape MySQL Wildcards in LIKE Statements?

Escaping MySQL Wild Cards

In an environment where prepared statements are not an option, it's essential to thoroughly escape user input before submitting it to MySQL to prevent SQL injection. PHP's mysql_real_escape_string function is commonly used for this purpose, but it falls short of escaping MySQL wild card characters '%' and '_'.

To account for this, addcslashes can be used additionally. However, as observed by a user, when input containing wild cards is sent to the database and retrieved, there's a discrepancy in the displayed results.

The Escaping Enigma

The user encountered a peculiar behavior where the '_' character was prefixed with a backslash ('_'), while the '"' and "'" characters were not, even though all three were escaped with ''. This raises the question: why are these characters handled differently?

Understanding MySQL Context

The key to resolving this conundrum lies in understanding the context of LIKE-matching in MySQL. '_' and '%' are not considered wild cards in general MySQL usage and should not be escaped when constructing a string literal. mysql_real_escape_string addresses the escaping needs for this purpose.

However, when preparing strings for use in a LIKE statement, a different set of escaping rules apply. To ensure that literals are interpreted correctly, additional LIKE escaping is required.

Double Escaping Dilemma

In the context of LIKE-matching, '_' and '%' become special characters. MySQL uses the backslash ('') as the escape character for both the LIKE-escaping and string literal-escaping steps. This can lead to confusion, as demonstrated by the user's example where matching a literal percent sign with LIKE requires double-backslash escaping.

Proper LIKE Escaping

To avoid portability issues, ANSI SQL dictates the use of a designated escape character for LIKE statements. One way to achieve this in PHP is to use the following function:

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

Usage with Prepared Statements

For added security and portability, it's advisable to use prepared statements when available. This eliminates the need for manual escaping while ensuring proper data handling.

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