Doctrine - how to use LIKE with dbal prepared statements
By @edent
on
1 comment 150 wordsI'm just getting started with Symfony, so I'm blogging some of the weird things I'm finding.
I want to use Doctrine dbal to search a database for a partial match. For example searching for "smith" should find "blacksmith" and "smithy".
I have a prepared statement like this:
$queryBuilder = $conn->createQueryBuilder();
$queryBuilder
->select("whatever")
->from("table")
->where("name LIKE '%?%'")
->setParameter(0, $query);
But I get the error:
The number of variables must match the number of parameters in the prepared statement.
The solution is annoyingly simple. The escaping has to be in the parameter itself. Like this:
...
->where("name LIKE ?")
->setParameter(0, "%{$query}%");
I hope that's helpful to someone - even if that someone is me in a few years' time!
@Edent To help provide a sense of intuition for why it works this way: In the first code snippet, you're putting data directly into your query, which is what DBALs are designed to prevent. You always want your data strictly segregated from your query, to avoid SQL injection vulnerabilities.