CODEWITHSUNDEEP
sqlsymfonydoctrine-orm
user1581663
user1581663

Reputation:

Doctrine 2 Query with LIKE

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?

Upvotes: 65

Views: 86901

Answers (2)

robert
robert

Reputation: 3615

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

Upvotes: 36

Elnur Abdurrakhimov
Elnur Abdurrakhimov

Reputation: 44831

You forgot the % signs around the word:

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

Upvotes: 125

Related Questions