Doctrine2 query builder does not quote the string

Question

Below is the code excerpt I have

$column_name = "ipAddress";
$qb = EntityManagerContainer::get()->createQueryBuilder();
$qb->select('u')
    ->from(BlacklistedIps::class, 'u');

if($search_term)
{   
    $clause = $qb->expr()->like("u.".$column_name, "'%$search_term%'");
    $qb->where($clause);
}

$query = $qb->getQuery();

$result = $query->getResult();

It works absolutely fine (although it's open to SQL injection but that's another story).

My problem with this is the need to have "'%$search_term%'". Without this extra set of single quotes the query fails

Uncaught exception 'Doctrine\ORM\Query\QueryException' with message 'SELECT u FROM Orm\Entity\BlacklistedIps u WHERE u.ipAddress LIKE %123% ORDER BY u.reason desc' in ***

I am not entirely sure I am doing it the right way. Because if I do, then there is a bug (mssing feature?) in Doctrine2. When I do

$qb->expr()->like("u.".$column_name, "%$search_term%");

then I am ABSOLUTELY sure that I am dealing with a string. When integers or booleans or floats, etc are compared to each other different operators are used, but definitely not LIKE. LIKE is used ONLY when dealing with strings, so quoting the string in DQL is exactly the only possible ->like method use case.

Please tell me I am doing something wrong. I've been using Doctrine2 for couple of days only and feel fascinated by it. But don't like strings not being quoted automatically for me.

Answer 1

it looks like a problem of how you use querybuilder. You should do something like that :

$qb ->where($qb->expr()->orX($qb->expr()->like('u.'.$column_name, $qb->expr()->literal("%$searchTerm%"))))  

or

$qb->where($qb->expr()->like("u.".$column_name, array("%$searchTerm%")));  

also to avoid sql injection, a good practice is to not pass user input in any querybuilder methods, use setParameter with ? or : instead.

$qb->where('u.'.$column_name.' LIKE :searchTerm')
$qb->setParameter('searchTerm', '%'.$searchTerm.'%')  

or something like :

$qb->expr()->like('u.'.$column_name, '?1')
$qb->getQuery()->setParameter(1, '%' . $searchTerm . '%');