PDO Prepared Statements and Like With (%_) Characters

How to escape SQL wildcard characters in PDO prepared statements.

2041 views
d

By. Jacob

Edited: 2020-08-09 16:13

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.

Tell us what you think:

    More in: PDO