Using wildcards with Doctrine's createQuery method

Question

Can anyone enlighten me as to why this query isn't working please? I tried alternating between single and double quotes as well.

$em = $this->getDoctrine()->getManager();

$query = $em->createQuery('SELECT t FROM AcmeBlogBundle:BlogTag t WHERE t.title LIKE \'%:title%\'')
->setParameter('title', $keyword);

Doctrine simply returns Invalid parameter number: number of bound variables does not match number of tokens.

Also, is it better to perform such a query using the createQuery method or createQueryBuilder?

I think you want to select all the table, for that you have to use SELECT * ... and not SELECT t. Was that it? — eLRuLL, Jan 20 '13 at 02:44

score 32 · Accepted Answer · edited May 23 '17 at 11:54

PDO treats both the keyword and the % wildcards as a single token. You cannot add the wildcards next to the placeholder. You must append them to the string when you bind the params.

Also see this comment on the php docs.

Therefore you will need to do the following:

$qb = $em->createQueryBuilder();

$qb
    ->select('tag')
    ->from('AcmeBlogBundle:BlogTag', 'tag')
    ->where($qb->expr()->like('tag.title', ':title'))
    ->setParameter('title', '%' . $keyword . '%')
;

or

$query = $em->createQuery('SELECT t FROM AcmeBlogBundle:BlogTag t WHERE t.title LIKE :title');
$query->setParameter('title', '%' . $keyword . '%');

I prefer to use the query builder because it is nicer for structuring and making your statement more maintainable

What are chances of SQL injection by directly appending $keyword, I think there is, is there any other safer way to do it. — Black Panther, May 12 '23 at 12:09

Using wildcards with Doctrine's createQuery method

1 Answers1