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);