How to Safely Bind LIKE Values with PDO in SQL Queries?

Binding LIKE Values with PDO

When attempting to bind LIKE values using the PDO extension, it's important to consider how the % wildcard character is handled.

In the provided example:

select wrd from tablename WHERE wrd LIKE '$partial%'

There are several ways to approach the binding:

• select wrd from tablename WHERE wrd LIKE ':partial%':
  This would bind :partial to $partial="somet", and the query would search for words ending with "somet."
• select wrd from tablename WHERE wrd LIKE ':partial':
  This would bind :partial to $partial="somet%", including the % in the bound value. The query would not find any results since the partial string does not end with %.
• SELECT wrd FROM tablename WHERE wrd LIKE CONCAT(:partial, '%'):
  This option joins the bound value with % at the MySQL end, effectively handling the wildcard character.

In complex cases where the partial string may contain special characters (% or _), additional escaping may be necessary:

$stmt= $db->prepare("SELECT wrd FROM tablename WHERE wrd LIKE :term ESCAPE '+'");
$escaped= str_replace(array('+', '%', '_'), array('++', '+%', '+_'), $var);
$stmt->bindParam(':term', $escaped);

This approach replaces occurrences of , %, and _ in the bound value with escaped versions.

The above is the detailed content of How to Safely Bind LIKE Values with PDO in SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!