在yii2中运行原始sql时获取sql查询语法错误或访问冲突

8e2ybdfx  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(328)

我试图创建一个搜索功能,能够搜索产品,也过滤的相关性,但我得到了查询后语法错误的结果。下面是我犯的错误

sql error
    SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mens', 'winter', 'jacket'%',6,0) + if (title LIKE '%'mens'%',5,0) + if (title LI' at line 5
The SQL being executed was: SELECT p.product_id,p.title,p.price,p.unit_sold,
p.profile_img,p.store_name,p.item_number,
(
(-- Title score
if (title LIKE '%'mens', 'winter', 'jacket'%',6,0) + if (title LIKE '%'mens'%',5,0) + if (title LIKE '%'winter'%',5,0) + if (title LIKE '%'jacket'%',5,0)
)+
(-- description
if (description LIKE '%'mens', 'winter', 'jacket'%',5,0) + if (description LIKE '%'mens'%',4,0) + if (description LIKE '%'winter'%',4,0) + if (description LIKE '%'jacket'%',4,0)
)

) as relevance
FROM products p
WHERE p.is_active = '1'
HAVING relevance > 0
ORDER BY relevance DESC,p.unit_sold DESC
LIMIT 10

以及搜索功能

function search($q){
            if (mb_strlen(trim($q))===0){
                // no need for empty search
                return false; 
            }
            $query = $this->limitChars(trim($q));

            // Weighing scores
            $scoreFullTitle = 6;
            $scoreTitleKeyword = 5;
            $scoreFullDescription = 5;
            $scoreDescriptionKeyword = 4;

            $keywords = $this->filterSearchKeys($query);
            $escQuery = $this->escape($keywords); 
            $titleSQL = array();
            $descSQL = array();

            /**Matching full occurences**/
            if (count($keywords) > 1){
                $titleSQL[] = "if (title LIKE '%".$escQuery."%',{$scoreFullTitle},0)";
                $descSQL[] = "if (description LIKE '%".$escQuery."%',{$scoreFullDescription},0)";

            /**Matching Keywords**/
            foreach($keywords as $key){
                $titleSQL[] = "if (title LIKE '%".Yii::$app->db->quoteValue($key)."%',{$scoreTitleKeyword},0)";
                $descSQL[] = "if (description LIKE '%".Yii::$app->db->quoteValue($key)."%',{$scoreDescriptionKeyword},0)";
            }

            //add 0 is query string is empty to avoid error
            if (empty($titleSQL)){
                $titleSQL[] = 0;
            }
            if (empty($descSQL)){
                $descSQL[] = 0;
            }
            $sql = "SELECT p.product_id,p.title,p.price,p.unit_sold,
                    p.profile_img,p.store_name,p.item_number,
                    (
                        (-- Title score
                        ".implode(" + ", $titleSQL)."
                        )+
                        (-- description
                        ".implode(" + ", $descSQL)." 
                        )

                    ) as relevance
                    FROM products p
                    WHERE p.is_active = '1'
                    HAVING relevance > 0
                    ORDER BY relevance DESC,p.unit_sold DESC
                    LIMIT 10";
            $results = Yii::$app->db->createCommand($sql)->queryAll();
            if (!$results){
                return false;
            }
            return $results;
        }

我还使用escape()方法对查询字符串进行转义,以避免sql注入,但我不太相信这是最佳做法,因为转义方法在字符串周围添加单引号,这反过来甚至不会返回表中的任何匹配项,我也尝试使用mysqli\u escape\u string(),但也无法使其工作,所以我想知道yii2中的最佳实践是什么来转义查询字符串和避免sql注入攻击。

function escape($values)
        {
                $values = (array)$values;
                $escaped = array();
                foreach($values as $value) {
                        if(!is_scalar($value)) {
                                throw new CException('One of the values passed to values() is not a scalar.');
                        }
                        $escaped[] = Yii::$app->db->quoteValue($value);
                }
                return implode(', ', $escaped);
        }
8aqjt8rx

8aqjt8rx1#

你应该避开整个表情 LIKE ,包括 % 通配符:

$value = Yii::$app->db->quoteValue('%' . implode(', ', (array) $keywords) . '%');
$titleSQL[] = "if (title LIKE $value,$scoreFullTitle,0)";

这将产生如下结果:

if (title LIKE '%mens, winter, jacket%',6,0)

相关问题