Skip to content

Instantly share code, notes, and snippets.

@johnkary
Last active April 22, 2022 08:09
Show Gist options
  • Save johnkary/9770413 to your computer and use it in GitHub Desktop.
Save johnkary/9770413 to your computer and use it in GitHub Desktop.
Proper DQL escaping for LIKE queries with Doctrine 2.
<?php
namespace Foo;
/**
* Methods for safe LIKE querying.
*/
trait LikeQueryHelpers
{
/**
* Format a value that can be used as a parameter for a DQL LIKE search.
*
* $qb->where("u.name LIKE (:name) ESCAPE '!'")
* ->setParameter('name', $this->makeLikeParam('john'))
*
* NOTE: You MUST manually specify the `ESCAPE '!'` in your DQL query, AND the
* ! character MUST be wrapped in single quotes, else the Doctrine DQL
* parser will throw an error:
*
* [Syntax Error] line 0, col 127: Error: Expected Doctrine\ORM\Query\Lexer::T_STRING, got '"'
*
* Using the $pattern argument you can change the LIKE pattern your query
* matches again. Default is "%search%". Remember that "%%" in a sprintf
* pattern is an escaped "%".
*
* Common usage:
*
* ->makeLikeParam('foo') == "%foo%"
* ->makeLikeParam('foo', '%s%%') == "foo%"
* ->makeLikeParam('foo', '%s_') == "foo_"
* ->makeLikeParam('foo', '%%%s') == "%foo"
* ->makeLikeParam('foo', '_%s') == "_foo"
*
* Escapes LIKE wildcards using '!' character:
*
* ->makeLikeParam('foo_bar') == "%foo!_bar%"
*
* @param string $search Text to search for LIKE
* @param string $pattern sprintf-compatible substitution pattern
* @return string
*/
protected function makeLikeParam($search, $pattern = '%%%s%%')
{
/**
* Function defined in-line so it doesn't show up for type-hinting on
* classes that implement this trait.
*
* Makes a string safe for use in an SQL LIKE search query by escaping all
* special characters with special meaning when used in a LIKE query.
*
* Uses ! character as default escape character because \ character in
* Doctrine/DQL had trouble accepting it as a single \ and instead kept
* trying to escape it as "\\". Resulted in DQL parse errors about "Escape
* character must be 1 character"
*
* % = match 0 or more characters
* _ = match 1 character
*
* Examples:
* gloves_pink becomes gloves!_pink
* gloves%pink becomes gloves!%pink
* glo_ves%pink becomes glo!_ves!%pink
*
* @param string $search
* @return string
*/
$sanitizeLikeValue = function ($search) {
$escapeChar = '!';
$escape = [
'\\' . $escapeChar, // Must escape the escape-character for regex
'\%',
'\_',
];
$pattern = sprintf('/([%s])/', implode('', $escape));
return preg_replace($pattern, $escapeChar . '$0', $search);
};
return sprintf($pattern, $sanitizeLikeValue($search));
}
}
<?php
namespace Foo\Entity;
use Doctrine\ORM\EntityRepository;
use Foo\LikeQueryHelpers;
class ProductRepository extends EntityRepository
{
use LikeQueryHelpers;
/**
* Find Product entities containing searched terms
*
* @param string $term
* @return Product[]
*/
public function findInSearchableFields($term)
{
return $this->createQueryBuilder('p')
->where("p.title LIKE :title ESCAPE '!'")
->setParameter('title', $this->makeLikeParam($term))
->getQuery()
->execute();
}
}
@johnkary
Copy link
Author

johnkary commented Aug 5, 2014

Special note: You MUST wrap the ! character in single quotes inside your ->where() query builder:

->where("p.title LIKE :title ESCAPE '!'")

Else you will get an error from the DQL parser that looks like this:

[Syntax Error] line 0, col 127: Error: Expected Doctrine\ORM\Query\Lexer::T_STRING, got '"'

@mantissefr
Copy link

Hello,
As indicated here, you can simply escape the ' char. Example :
->setParameter('title',preg_replace("/'/","''",$term))

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment