The Wildcard Trap in PDO LIKE Queries
A common stumbling block when transitioning from raw SQL to prepared statements involves the handling of wildcard characters. In standard SQL, a LIKE clause often looks like this:
SELECT * FROM users WHERE name LIKE '%john%'
When you attempt to translate this directly into a PDO prepared statement, it is tempting to think that the placeholder syntax simply replaces the variable values within the pattern string. However, there is a fundamental conflict between how SQL wildcards work and how PDO parameter binding functions.
The issue arises because ? or :name in a prepared statement represents a literal value to be inserted into the query. If you pass a string containing % to the driver, it treats that percent sign as a literal character, not as a wildcard operator. Consequently, your query effectively becomes:
SELECT * FROM tbl WHERE address LIKE '%%var1%%' OR address LIKE '%%var2%%'
Unless your database column actually contains double percent signs surrounding the variable values, this will return no results. The PDO driver is protecting you from SQL injection by forcing you to bind values as parameters, but doing so strips away the dynamic nature of wildcards if they are not handled explicitly in the query string itself.
To fix this, you must construct the pattern string directly in your PHP code before binding it to the statement. Since the % character is specific to SQL syntax and not a special character for PDO's parameter binding system, you can safely concatenate wildcards around your user input.
Here is the corrected approach:
$pattern1 = '%' . $var1 . '%';
$pattern2 = '%' . $var2 . '%';
$query = "SELECT * FROM tbl WHERE address LIKE :pattern1 OR address LIKE :pattern2";
$params = [
':pattern1' => $pattern1,
':pattern2' => $pattern2
];
$stmt = $handle->prepare($query);
$stmt->execute($params);
By concatenating the % signs in PHP, you ensure that the SQL engine receives a string like '%search term%', while the parameter binding still handles the core search text safely. This method maintains the security benefits of prepared statementsâpreventing SQL injection attacks on the variable contentâwhile allowing the database engine to interpret the concatenated result as a valid wildcard pattern.
There is a trade-off here regarding performance and query structure. If you are searching for multiple terms, creating a separate LIKE condition for each one can lead to complex query plans. In some databases, executing a single OR chain with multiple LIKE clauses might prevent the use of standard indexes on the column, forcing a full table scan.
If performance becomes a bottleneck with large datasets, consider using REGEXP (Regular Expressions) if your database supports it and allows for more flexible pattern matching without the overhead of multiple wildcard checks. Alternatively, you might normalize your data or use full-text search capabilities provided by modern databases like PostgreSQL or MySQL's MATCH ... AGAINST syntax, which are optimized specifically for this type of retrieval task.
For developers working within the Laravel ecosystem, this same logic applies when using Eloquent query builders. While Eloquent abstracts much of the SQL generation, raw queries passed to DB::table() still require explicit handling of wildcards in the string construction phase before binding parameters.
Ultimately, the solution relies on understanding that parameter binding substitutes values, not syntax. Wildcards must be part of the literal pattern string sent to the database engine.