[mysql]正确的转义的像与学说 querybuilder 查询

标签: MySQL PHP
发布时间: 2017/4/9 23:09:54
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我试图与学说 QueryBuilder LIKE 查询。我读到关于其他的各项问题和文章都妥善逃脱这种类型的查询,但是我不明白学说是否它本身或不。以这个数据为例︰

my_column
ABC
ABCD
A%BCD

和下面的输入的数据

ABC
ABCD
A
A%

我希望这些结果︰

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%

我的问题涉及的最新查询 (和输入的数据)。如何应适当逃该查询?是 '%' . addcslashes($input, '%_') . '%' 足够吗?

我准备这个 SQL 提琴如果它能帮助︰ http://sqlfiddle.com/# ! 9/35bc8/9

解决方法 1:

如发现出与依据的约翰来,教义不逃避 LIKE 的查询语句。更具体地说,它逃脱使用预准备的语句 (如反斜杠或引号正确转义的字符) 的参数,但人物像 %_ 哪些部分的语法 LIKE 语句没有逃过和输入没有被清除掉。下面链接的要点提供了很好的方式来解决这一问题,Symfony 2.6 和它完美的学说 2.5 测试。要确保不会删除依据的是,我在这里复制的代码︰

<?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();
    }
}
官方微信
官方QQ群
31647020