doctrine2 dql, use setParameter with % wildcard when doing a like comparison

Question

I want to use the parameter place holder - e.g. ?1 - with the % wild cards. that is, something like: "u.name LIKE %?1%" (though this throws an error). The docs have the following two examples: 1.

// Example - $qb->expr()->like('u.firstname', $qb->expr()->literal('Gui%'))
public function like($x, $y); // Returns Expr\Comparison instance

I do not like this as there is no protection against code injection.

2.

// $qb instanceof QueryBuilder

// example8: QueryBuilder port of: "SELECT u FROM User u WHERE u.id = ?1 OR u.nickname LIKE ?2 ORDER BY u.surname DESC" using QueryBuilder helper methods
$qb->select(array('u')) // string 'u' is converted to array internally
   ->from('User', 'u')
   ->where($qb->expr()->orx(
       $qb->expr()->eq('u.id', '?1'),
       $qb->expr()->like('u.nickname', '?2')
   ))
   ->orderBy('u.surname', 'ASC'));

I do not like this because I need to search for terms within the object's properties - that is, I need the wild cards on either side.

score 76 · Accepted Answer · edited Dec 25 '22 at 00:26

76

When binding parameters to queries, DQL pretty much works exactly like PDO (which is what Doctrine2 uses under the hood).

So when using the LIKE statement, 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.

$qb->expr()->like('u.nickname', '?2')
$qb->getQuery()->setParameter(2, '%' . $value . '%');

See this comment in the PHP manual.

edited Dec 25 '22 at 00:26

starball

20,030
7
43
238

answered Sep 21 '10 at 15:42

Bryan M.

17,142
8
46
60

1

what happen if `$value = '\\'`? – seyed Sep 18 '13 at 10:57
1

Is it safe? Should $value be escaped somehow before? – MacDada Nov 16 '16 at 11:45
2

@MacDada No, it is not secure. See my answer. – robert Dec 31 '17 at 10:55

score 15 · Answer 2 · answered Dec 31 '17 at 10:54

The selected answer is wrong. It works, but it is not secure.

You should escape the term that you insert between the percentage signs:

->setParameter(2, '%'.addcslashes($value, '%_').'%')

The percentage sign '%' and the symbol underscore '_' are interpreted as wildcards by LIKE. If they're not escaped properly, an attacker might construct arbirtarily complex queries that can cause a denial of service attack. Also, it might be possible for the attacker to get search results he is not supposed to get. A more detailed description of attack scenarios can be found here: https://stackoverflow.com/a/7893670/623685

doctrine2 dql, use setParameter with % wildcard when doing a like comparison

2 Answers2

Linked