Doctrine 2 Query with LIKE

php

sql-injection

doctrine-orm

performance-tips

byAlex Kataev·Nov 17, 2024

Here's a nut-cracker for LIKE search in Doctrine 2:

$qb = $entityManager->createQueryBuilder();
$result = $qb->select('e')
    ->from('YourBundle:Entity', 'e')
    ->where($qb->expr()->like('LOWER(e.field)', $qb->expr()->literal('%' . strtolower($search) . '%')))
    ->getQuery()
    ->getResult();

Heroic elements:

Build your SQL castle using QueryBuilder ($qb).
expr()->like() is your magic spell for the LIKE maneuver.
Engage LOWER() for an all-out assault on case-sensitive search.
literal() shields your search term, very knightly!
Unleash % for a not-so-secret weapon: partial match.
Grab your victory flag with getResult().

Passing Parameters: More than Just a Message in a Bottle

While using setParameter(), marriage with LIKE warrants a special courtship strategy – the inclusion of wildcards in the parameter itself:

// A secret post-it note for the SQL elves
$queryBuilder->where('entity.field LIKE :param')
             ->setParameter('param', '%'. $search .'%');

The : before param is the secret handshake with named placeholders. The % is a wildcard, matching any string of characters. Always beware of strangers bearing gifts - sanitize and escape values to roll out the red carpet for SQL injection attacks.

Charming Wildcards: Escaping the Jailbreak

To ensure wildcards don't turn into wild horses, it's crucial to escape such characters when working with user input:

// Putting the magic beans to sleep
$search = addcslashes($search, '%_');
$queryBuilder->where('entity.field LIKE :param')
             ->setParameter('param', '%'. $search .'%');

The maiden in distress here is addcslashes(), a PHP function that by escaping such characters ensures they don't toss and turn into wildcards in a LIKE expression.

Behind the Scenes: Pulling the Curtain on SQL

Sometimes the pot of gold isn't quite at the end of the rainbow. To make sure your map is accurate, take a peak at the SQL cooked by Doctrine:

// A peek behind the magic curtain
echo $queryBuilder->getDQL();

And then counter-check it with your own:

// Comparing maps to treasures
echo $queryBuilder->getQuery()->getSQL();

You'd be surprised to find some uncanny bloopers between parameter bindings and how Doctrine whips DQL into SQL.

Safeguarding the Realm

While setParameter() is coded to prevent dragons, err... I mean SQL injection, developers need the watchful eyes of a hawk to not expose raw SQL errors in production environments. Acquaint yourself with common attack scenarios and armor up with security practices specific to the Doctrine ORM.