How to Escape MySQL Wildcards for Secure SQL Queries?

Escaping MySQL Wild Cards: A Comprehensive Guide

When dealing with untrustworthy user input in a MySQL environment, it's crucial to escape special characters to prevent SQL injection attacks. The PHP function mysql_real_escape_string is commonly used for this purpose. However, it does not escape MySQL wildcards "%" and "_".

To address this issue, some users resort to using addcslashes to manually escape these wildcards. However, this approach can lead to unexpected results, as seen in the example provided:

Input: test_test " '
Database: test\_test " '

Here, the underscore ("_") is escaped with a preceding backslash, unlike the single quote ("'") and double quote ("""). This behavior can be confusing since all these characters are escaped using the backslash character.

Understanding this phenomenon requires delving deeper into MySQL's wildcard escaping mechanisms. "_" and "%" are not treated as wildcards in general MySQL usage and should not be escaped when constructing normal string literals. mysql_real_escape_string correctly handles this situation.

However, "%_" become special characters only in the context of LIKE-matching. To prepare strings for literal use in LIKE statements, you must perform LIKE escaping even when using parameterized queries.

LIKE escaping involves two levels:

1. LIKE escaping: _ and % are special and must be escaped. The escape character () must also be escaped.
2. String literal escaping: After LIKE escaping, perform plain string literal escaping using mysql_real_escape_string (for MySQL) or the appropriate function for other databases.

The potential for double-backslash-escaping can be problematic, especially if you're using PHP's string literals, as demonstrated in the example.

To avoid these complexities and ensure portability, consider overriding the default (incorrect) LIKE escaping behavior and specifying a custom escape character using the LIKE ... ESCAPE ... construct:

// function that performs LIKE escaping
function like($s, $e) {
    return str_replace(array($e, '_', '%'), array($e.$e, $e.'_', $e.'%'), $s);
}

Then, use it as follows:

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

By following these recommendations, you can effectively escape MySQL wild cards and enhance the security of your database applications.

The above is the detailed content of How to Escape MySQL Wildcards for Secure SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!