Escaping MySQL wild cards

by

_ and % are not wildcards in MySQL in general, and should not be escaped for the purposes of putting them into normal string literals. mysql_real_escape_string is correct and sufficient for this purpose. addcslashes should not be used.

_ and % are special solely in the context of LIKE-matching. When you want to prepare strings for literal use in a LIKE statement, so that 100% matches one-hundred-percent and not just any string starting with a hundred, you have two levels of escaping to worry about.

The first is LIKE escaping. LIKE handling takes place entirely inside SQL, and if you want to turn a literal string into an literal LIKE expression you must perform this step even if you are using parameterised queries!

In this scheme, _ and % are special and must be escaped. The escape character must also be escaped. According to ANSI SQL, characters other than these must not be escaped: \' would be wrong. (Though MySQL will typically let you get away with it.)

Having done this, you proceed to the second level of escaping, which is plain old string literal escaping. This takes place outside of SQL, creating SQL, so must be done after the LIKE escaping step. For MySQL, this is mysql_real_escape_string as before; for other databases there will be a different function, of you can just use parameterised queries to avoid having to do it.

The problem that leads to confusion here is that in MySQL uses a backslash as an escape character for both of the nested escaping steps! So if you wanted to match a string against a literal percent sign you would have to double-backslash-escape and say LIKE 'something\\%'. Or, if that’s in a PHP " literal which also uses backslash escaping, "LIKE 'something\\\\%'". Argh!

This is incorrect according to ANSI SQL, which says that: in string literals backslashes mean literal backslashes and the way to escape a single quote is ''; in LIKE expressions there is no escape character at all by default.

So if you want to LIKE-escape in a portable way, you should override the default (wrong) behaviour and specify your own escape character, using the LIKE ... ESCAPE ... construct. For sanity, we’ll choose something other than the damn backslash!

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 (eg. in PDO):

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

(If you want more portability party time, you can also have fun trying to account for MS SQL Server and Sybase, where the [ character is also, incorrectly, special in a LIKE statement and has to be escaped. argh.)

More Related Contents:

  1. What does it mean to escape a string?
  2. Is mysql_real_escape_string() broken?
  3. Escaping single quote in PHP when inserting into MySQL [duplicate]
  4. How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?
  5. PHP DateTime supported formats
  6. Can I call a PHP function in a JavaScript click handler?
  7. How to create a chat program using php and mysqli for chatting [closed]
  8. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE `id` [closed]
  9. Can I store images in MySQL [duplicate]
  10. MySQL – This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery
  11. PHP PDOException: “SQLSTATE[HY093]: Invalid parameter number”
  12. Multiple mysql INSERT statements in one query php [duplicate]
  13. how safe are PDO prepared statements
  14. How do I escape only single quotes?
  15. How to join three table by laravel eloquent model
  16. Secure files for download
  17. Hiding true database object ID in url’s
  18. Mysql No connection could be made because the target machine actively refused it
  19. How to connect to MySQL database in PHP using mysqli extension?
  20. Laravel 5.4 field doesn’t have a default value
  21. org.json.JSONException: Value
  22. PHP Check for NULL
  23. How to store .pdf files into MySQL as BLOBs using PHP?
  24. Fatal error: Uncaught exception ‘mysqli_sql_exception’ with message ‘No index used in query/prepared statement’
  25. Outputting all PHP errors to database not error_log
  26. Laravel nested relationships
  27. getting the value of an extra pivot table column laravel
  28. php sample script for pagination [closed]
  29. How to select rows where column value IS NOT NULL using CodeIgniter’s ActiveRecord?
  30. Mysqli multiple row insert, simple multi insert query [duplicate]

Leave a Comment