How to Bind LIKE Values with the % Character in PDO?

Binding LIKE Values with % Character in PDO

In PDO-driven SQL queries, it's common to encounter the need to bind LIKE values that include the % wildcard character. Binding such values ensures SQL injection prevention. However, the syntax for doing so can be confusing.

Query with LIKE '%': Binding Considerations

Consider the query:

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

Here, you want to bind the variable $partial using PDO. The dilemma arises when deciding how to bind the % character.

Options for Binding

Option 1:

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

Bind :partial to $partial="somet".

Option 2:

select wrd from tablename WHERE wrd LIKE ':partial'

Bind :partial to $partial="somet%".

Option 3 (Alternative):

SELECT wrd FROM tablename WHERE wrd LIKE CONCAT(:partial, '%')

Perform string concatenation using the MySQL CONCAT function.

Special Case Considerations

If the partial word being searched contains a % or underscore character, special handling is required. The solution involves using the ESCAPE clause in the PDO statement:

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

This technique ensures that the special characters are interpreted correctly within the LIKE expression.

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