When using prepared statements when communicating with your database from your PHP application, you may have noticed that trying to execute a select statement that also contains a like clause can result in unexpected results when the input contains certain characters.
When you execute a database query containing a like clause, you will need to manually escape percent sign % and underscore _. If you do not escape them manually, then they will have special meaning in your query, and you might end up with some unexpected results.
So, the way I solved this was to add backslashes \ in front of all percent signs and underscores within the string. In PHP we can use the addcslashes function to do this:
$search_query = 'part of some title'; $escaped_title = addcslashes($search_query, '%_'); $stmt = $pdo->prepare("SELECT title FROM articles WHERE title LIKE :title ORDER BY datetime LIMIT 50"); $stmt->execute([":title" => "$escaped_title%"]);
If you want to do this without the named parameters, you can do like below instead:
$stmt = $pdo->prepare("SELECT title FROM articles WHERE title LIKE ? ORDER BY datetime LIMIT 50"); $stmt->execute(["$escaped_title%"]);
Wildcard characters in MySQL
The percentage % character is used to perform queries with simple pattern matching. If a query has a percentage sign in a like clause, it either means that the column field must begin with, or that it must end with — depending on the position of the character — the following will return all queries beginning with "Carl":
$escaped_name = addcslashes('Carl', '%_'); $stmt = $pdo->prepare("SELECT people FROM WHERE name LIKE ? ORDER BY datetime LIMIT 50"); $stmt->execute([$escaped_name . '%']);
If we instead wanted to search by last name, we could do like this:
$stmt->execute(['%' . $escaped_name]);
A literal percent % or underscore _ character must be escaped when used inside the string we are searching for, and to do that we may use the PHP addcslashes function.