PDO Prepared Statement in PHP: Issues with MySQL LIKE Queries

PHP's PDO class with MySQL offers a mechanism for executing SQL statements with parameterized queries, improving security and performance. However, users may encounter difficulties when using LIKE queries.

Issue: PDO Prepared Statement not Returning Results with LIKE Query

When attempting to execute a query similar to the following using PDO:

SELECT *
FROM hs
WHERE hs_text LIKE "%searchTerm%"

Users may find that no results are returned.

Solution: Correct Parameterization

The issue lies in the incorrect parameterization of the search term. In PHP, prepared statements use named placeholders, which require different syntax. The correct parameterization for the LIKE query is:

$prep = $dbh->prepare($sql);
$ret = $prep->execute(array(':searchTerm' => '%'.$searchTerm.'%'));

Explanation:

Prepared statements separate the data from the query and use placeholders. Therefore, it is not necessary to wrap the search term in double quotes or perform string concatenation.

Other Common Mistakes:

WHERE hs_text LIKE :searchTerm
$ret = $prep->execute(array(':searchTerm' => '"%'.$searchTerm.'%"'));  // Incorrect

• This approach is incorrect as it adds unnecessary double quotes.

WHERE hs_text LIKE CONCAT(\'%\', ?, \'%\')
$ret = $prep->execute(array($searchTerm));  // Incorrect

• Using CONCAT to wrap the search term within the query is not necessary for parameterization.

By using the correct parameterization, you can successfully execute LIKE queries using PDO prepared statements in PHP.