How to Safely Bind LIKE Parameters with Wildcards in PDO?

Binding LIKE Values with % Wildcards in PDO

PDO provides a secure way to execute SQL queries with bound parameters, preventing SQL injection vulnerabilities. When binding LIKE parameters that include wildcard characters like %, you might encounter confusion.

In the query below, we're trying to bind the variable $partial% using PDO:

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

It's critical to understand how PDO handles such bindings. The correct approach depends on your specific requirements.

Option 1: Bind with Partial Wildcard (%) at the End

Yes, this is a valid option:

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

where :partial is bound to $partial="somet".

Option 2: Bind with Partial Wildcard (%) Inside the Value

You can also use this approach:

select wrd from tablename WHERE wrd LIKE ':partial'

where :partial is bound to $partial="somet%".

Alternative: Use CONCAT Function

If you prefer, you can perform the string concatenation within the MySQL query itself:

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

Special Characters Handling

If your partial word contains special characters like %, _, or , you need to escape them manually before binding the parameter. The following code demonstrates this:

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

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