How to Properly Escape MySQL Wildcard Characters in Queries and LIKE Statements?

Escaping MySQL Escapable Characters

MySQL contains a set of characters that are considered wildcards, capable of matching multiple inputs. These characters include % (percent) and _ (underscore). While mysql_real_escape_string() is designed to handle the escaping of most characters for inclusion in SQL queries, it does not address these wildcards.

To fully escape user input and prevent these characters from being interpreted as metacharacters, it is recommended to use the mysql_real_escape_string() function in conjunction with the addcslashes() function.

However, when escaping characters for inclusion in a LIKE statement, a different approach must be taken. In LIKE statements, _ and % are not considered wildcards but literal characters. To match these characters literally, they must be escaped using the ESCAPE clause.

For example, to match a literal percent sign, the LIKE statement should use the following syntax:

LIKE 'something\%' ESCAPE '\'

Note the use of the backslash () as both the LIKE escape character and the escape character for the SQL string literal. This allows the percent sign (%) to be interpreted literally within the LIKE statement.

When using PHP's prepared statements, escaping is handled automatically, eliminating the need for manual escaping. However, if prepared statements are unavailable, the combination of mysql_real_escape_string() and addcslashes() can effectively safeguard user input against potential SQL injection attacks.

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