Proper escaping of LIKE queries with Doctrine querybuilder

Question

I'm trying to make a LIKE query with Doctrine QueryBuilder. I read on various other questions and articles that I have to proper escape this type of query, but I don't understand if Doctrine does it itself or not. Taking this data as an example:

my_column
ABC
ABCD
A%BCD

And the following input data

ABC
ABCD
A
A%

I expect these results:

SELECT * FROM my_table WHERE my_column LIKE "%ABC%" => ABC, ABCD
SELECT * FROM my_table WHERE my_column LIKE "%ABCD%" => ABCD
SELECT * FROM my_table WHERE my_column LIKE "%A%" => ABC, ABCD, A, A%
SELECT * FROM my_table WHERE my_column LIKE "%A%%" => A%

My question relates the latest query (and input data). How should I proper escape that query? Is '%' . addcslashes($input, '%_') . '%' enough?

I prepared this SQL Fiddle if it can help: http://sqlfiddle.com/#!9/35bc8/9

Answer 1

As found out with this gist by John Kary, Doctrine doesn't escape LIKE query statements. More specifically, it escapes the parameters using prepared statements (characters like backslashes or quotes are escaped properly) but characters like % or _ which are part of the syntax of a LIKE statement are not escaped and the input is not sanitized. The gist linked below provides a nice way to solve this issue, tested with Symfony 2.6 and Doctrine 2.5 it works perfectly. Just to be sure that the gist won't be deleted, I copy here the code:

<?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));
    }
}

And it's used like this example:

<?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();
    }
}