How to Safely Bind LIKE Values with PDO in PHP?

Binding LIKE Values with PDO

In a query where you want to perform a partial string match using the LIKE operator, binding LIKE values using the PDO extension can be confusing. Let's explore how to handle this correctly.

Partial String Match with LIKE

When using the LIKE operator, you append % to the partial string to search for matching records. For example:

SELECT wrd FROM tablename WHERE wrd LIKE '$partial%'

Here, $partial represents the string that the wrd column should match.

Binding with PDO

To bind the $partial value using PDO, you have several options:

1. Bind with Wildcards in the Query: You can include the % wildcard directly in the query, like:

$stmt = $db->prepare("SELECT wrd FROM tablename WHERE wrd LIKE :partial");
$stmt->bindParam(':partial', $partial);

Here, $partial is bound to the :partial placeholder without any modifications.

2. Bind Without Wildcards in the Query: This method removes the % wildcard from the query, so you need to manually append it when binding:

$stmt = $db->prepare("SELECT wrd FROM tablename WHERE wrd LIKE CONCAT(:partial, '%')");
$stmt->bindParam(':partial', $partial);

3. Handling Special Characters: If the partial string contains characters that have special meaning in LIKE queries (e.g., %, _, ), you need to escape these characters before binding:

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

By following these guidelines, you can effectively bind LIKE values using PDO and perform partial string matches in your database queries.

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