Doctrine 2 Query with LIKE

Question

I have this code for query:

$repository = $em->getRepository('AcmeCrawlerBundle:Trainings');
       $query = $repository->createQueryBuilder('p')
               ->where('p.title LIKE :word')
               ->orWhere('p.discription LIKE :word')
               ->setParameter('word', $word)
               ->getQuery();
$trainings = $query->getResult();

The problem is: even if matches exist, they not found by this query. I used this code to see full sql:

print_r(array(
        'sql'        => $query->getSQL(),
        'parameters' => $query->getParameters(),
        ));

And what I've got:

FROM Trainings t0_ WHERE t0_.title LIKE ? OR t0_.discription LIKE ? [parameters] => Array ( [word] => Spoken )

(last part of query) Tell me please what to change?

Elnur Abdurrakhimov · Accepted Answer · 2014-03-07T16:12:22.060

122

You forgot the % signs around the word:

->setParameter('word', '%'.$word.'%')

edited Mar 07 '14 at 16:12

answered Oct 03 '12 at 10:15

Elnur Abdurrakhimov

44,533
10
148
133

1

why is doctrine doing this automatically when I pass a parameter to the like function ? – Miles M. Apr 11 '14 at 22:21
1

It's secure do it? By appending directly the string? – baquiax Nov 19 '15 at 19:29
10

@baquiax it's secure because parameter is escaped while preparing statement. – cnkt Dec 14 '15 at 15:59

score 35 · Answer 2 · edited Dec 18 '19 at 10:02

35

Below are some additional steps you can take to further sanitise input data.

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

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

The percentage sign '%' and the symbol underscore '_' are interpreted as wildcards by LIKE. If they're not escaped properly, an attacker might construct arbitrarily 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

edited Dec 18 '19 at 10:02

barell

1,470
13
19

answered Dec 31 '17 at 10:49

robert

3,484
3
29
38

1

@ElnurAbdurrakhimov You can improve your answer, then I'll delete mine. – robert Aug 17 '18 at 08:50
1

See https://www.doctrine-project.org/projects/doctrine-dbal/en/2.8/reference/security.html Even the examples in doctrine documentation does NOT escape the values. Hmm why? – Evren Yurtesen Oct 13 '18 at 16:09
4

The way in which the accepted answer uses the query builder causes the generated SQL to leverage *prepared statements* which means that the answer is neither 'wrong' nor 'insecure'. I'd go as far as saying that _this_ answer is wrong. – Chris Harrison Nov 08 '18 at 14:52
Arguably, this answer is wrong. Especially when using this answer's definition of "wrong". You should avoid 'rolling your own' security - as this answer encourages. The accepted answer very properly uses Doctrine's built-in security mechanisms. – Chris Harrison Nov 08 '18 at 14:59
15

Come on, guys. Look at the stackoverflow post I've linked in my answer. In short: The setParameter magic protects against SQL injection, but not against wildcard injection. I would like to point out that I have actually tested this myself, and all of it is true. We're not making this up. Without escaping % and _ manually, they will be interpreted as wildcards. Again: setParameter protects against SQL injection, but not against this type of wildcard injection. – robert Nov 08 '18 at 18:14
1

I can confirm, passing `%` into the like expression (between other `%` signs) still returns every record in the database in Doctrine 2.8.1. As for why Doctrine doesn't guard against this could be simply because there may be wild cards put there intentionally. – Peter Mghendi Feb 14 '21 at 12:53

Doctrine 2 Query with LIKE

2 Answers2

Linked