Doctrine - how to use LIKE with dbal prepared statements

By on   1 comment 150 words

I'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();
    ->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!

Share this post on…

One thought on “Doctrine - how to use LIKE with dbal prepared statements

  1. @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.


What are your reckons?

All comments are moderated and may not be published immediately. Your email address will not be published.