SQL 报错：SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘6’，FIND_IN_SET(”’, `pa_plan`.`keyword`)

1、接口响应 SQL 报错：SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘6’。如图1

图1

{
    "name": "Database Exception",
    "message": "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6')) GROUP BY `pa_plan`.`id`) `c`' at line 1\nThe SQL being executed was: SELECT COUNT(*) FROM (SELECT `pa_plan`.* FROM `pa_plan` LEFT JOIN `pa_config_column` ON `pa_plan`.`config_column_id` = `pa_config_column`.`id` LEFT JOIN `pa_config_column_user` `ccu_plan_create` ON `pa_plan`.`config_column_id` = `ccu_plan_create`.`config_column_id` AND `pa_plan`.`create_user_id` = `ccu_plan_create`.`user_id` LEFT JOIN `pa_plan_attended_user_relation` ON `pa_plan`.`id` = `pa_plan_attended_user_relation`.`plan_id` LEFT JOIN `pa_config_column_user` `ccu_plan_relation` ON `pa_plan_attended_user_relation`.`config_column_id` = `ccu_plan_relation`.`config_column_id` AND `pa_plan_attended_user_relation`.`relation_user_id` = `ccu_plan_relation`.`user_id` LEFT JOIN `pa_plan_group_relation` ON `pa_plan`.`id` = `pa_plan_group_relation`.`plan_id` LEFT JOIN `pa_config_column_user` `ccu_plan_accepted` ON `pa_plan_group_relation`.`config_column_id` = `ccu_plan_accepted`.`config_column_id` AND `pa_plan_group_relation`.`accepted_user_id` = `ccu_plan_accepted`.`user_id` WHERE (`pa_plan`.`is_deleted`=0) AND (((`pa_config_column`.`is_deleted`=0) AND (((((`pa_plan`.`is_send_down`=0) AND (`pa_plan`.`group_id`='f1709d2cea34db2ca5d8ce0daf956c3e')) OR ((`pa_plan`.`is_send_down`=1) AND (`pa_plan`.`send_down_accepted_group_id`='f1709d2cea34db2ca5d8ce0daf956c3e'))) AND (((`pa_plan`.`create_user_id`='1') AND (`ccu_plan_create`.`is_deleted`=0)) OR ((`pa_plan_attended_user_relation`.`relation_user_id`='1') AND (FIND_IN_SET('1', `pa_plan_attended_user_relation`.role)) AND (`pa_plan_attended_user_relation`.`is_deleted`=0) AND (`ccu_plan_relation`.`is_deleted`=0)) OR (`pa_plan`.`config_column_id`=1))) OR ((`pa_plan`.`is_not_isolated`=1) AND (`pa_plan_group_relation`.`relation_group_id`='f1709d2cea34db2ca5d8ce0daf956c3e') AND (`pa_plan_group_relation`.`is_inviter`=0) AND (`pa_plan_group_relation`.`is_deleted`=0) AND ((`pa_plan_group_relation`.`accepted_status` IN (0, 2)) OR ((`pa_plan_group_relation`.`accepted_status`=1) AND (`pa_plan_group_relation`.`accepted_user_id`='1') AND (`ccu_plan_accepted`.`is_deleted`=0)) OR ((`pa_plan_group_relation`.`accepted_status`=1) AND (`pa_plan`.`config_column_id`=1)))))) OR ((`pa_plan`.`is_send_down`=1) AND (`pa_plan`.`send_down_accepted_group_id`='f1709d2cea34db2ca5d8ce0daf956c3e') AND (`pa_plan`.`status`=8))) AND (((`pa_plan`.`created_at` >= '1618329600') AND (`pa_plan`.`created_at` <= '1620921599')) AND ((`pa_plan`.`title` LIKE '%\\'%') OR (FIND_IN_SET(''', `pa_plan`.`keyword`))) AND (`pa_plan`.`status` != '6')) GROUP BY `pa_plan`.`id`) `c`",
    "code": "42000",
    "type": "yii\\db\\Exception",
    "file": "/mcloud/www/pcs_api/vendor/yiisoft/yii2/db/Schema.php",
    "line": 678,
    "stack-trace": [
        "#0 /mcloud/www/pcs_api/vendor/yiisoft/yii2/db/Command.php(1304): yii\\db\\Schema->convertException(Object(PDOException), 'SELECT COUNT(*)...')",
        "#1 /mcloud/www/pcs_api/vendor/yiisoft/yii2/db/Command.php(1165): yii\\db\\Command->internalExecute('SELECT COUNT(*)...')",
        "#2 /mcloud/www/pcs_api/vendor/yiisoft/yii2/db/Command.php(433): yii\\db\\Command->queryInternal('fetchColumn', 0)",
        "#3 /mcloud/www/pcs_api/vendor/yiisoft/yii2/db/Query.php(505): yii\\db\\Command->queryScalar()",
        "#4 /mcloud/www/pcs_api/vendor/yiisoft/yii2/db/ActiveQuery.php(352): yii\\db\\Query->queryScalar('COUNT(*)', Object(yii\\db\\Connection))",
        "#5 /mcloud/www/pcs_api/vendor/yiisoft/yii2/db/Query.php(368): yii\\db\\ActiveQuery->queryScalar('COUNT(*)', Object(yii\\db\\Connection))",
        "#6 /mcloud/www/pcs_api/vendor/yiisoft/yii2/data/ActiveDataProvider.php(168): yii\\db\\Query->count('*', NULL)",
        "#7 /mcloud/www/pcs_api/vendor/yiisoft/yii2/data/BaseDataProvider.php(169): yii\\data\\ActiveDataProvider->prepareTotalCount()",
        "#8 /mcloud/www/pcs_api/vendor/yiisoft/yii2/data/ActiveDataProvider.php(105): yii\\data\\BaseDataProvider->getTotalCount()",
        "#9 /mcloud/www/pcs_api/vendor/yiisoft/yii2/data/BaseDataProvider.php(101): yii\\data\\ActiveDataProvider->prepareModels()",
        "#10 /mcloud/www/pcs_api/vendor/yiisoft/yii2/data/BaseDataProvider.php(114): yii\\data\\BaseDataProvider->prepare()",
        "#11 /mcloud/www/pcs_api/api/rests/plan/Serializer.php(47): yii\\data\\BaseDataProvider->getModels()",
        "#12 /mcloud/www/pcs_api/vendor/yiisoft/yii2/rest/Serializer.php(154): api\\rests\\plan\\Serializer->serializeDataProvider(Object(yii\\data\\ActiveDataProvider))",
        "#13 /mcloud/www/pcs_api/vendor/yiisoft/yii2/rest/Controller.php(99): yii\\rest\\Serializer->serialize(Object(yii\\data\\ActiveDataProvider))",
        "#14 /mcloud/www/pcs_api/vendor/yiisoft/yii2/rest/Controller.php(77): yii\\rest\\Controller->serializeData(Object(yii\\data\\ActiveDataProvider))",
        "#15 /mcloud/www/pcs_api/vendor/yiisoft/yii2/base/Controller.php(183): yii\\rest\\Controller->afterAction(Object(api\\rests\\plan\\HaveAction), Object(yii\\data\\ActiveDataProvider))",
        "#16 /mcloud/www/pcs_api/vendor/yiisoft/yii2/base/Module.php(534): yii\\base\\Controller->runAction('have', Array)",
        "#17 /mcloud/www/pcs_api/vendor/yiisoft/yii2/web/Application.php(104): yii\\base\\Module->runAction('v1/plan/have', Array)",
        "#18 /mcloud/www/pcs_api/vendor/yiisoft/yii2/base/Application.php(392): yii\\web\\Application->handleRequest(Object(yii\\web\\Request))",
        "#19 /mcloud/www/pcs_api/api/web/index.php(17): yii\\base\\Application->run()",
        "#20 {main}"
    ],
    "error-info": [
        "42000",
        1064,
        "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6')) GROUP BY `pa_plan`.`id`) `c`' at line 1"
    ],
    "previous": {
        "name": "Exception",
        "message": "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6')) GROUP BY `pa_plan`.`id`) `c`' at line 1",
        "code": "42000",
        "type": "PDOException",
        "file": "/mcloud/www/pcs_api/vendor/yiisoft/yii2/db/Command.php",
        "line": 1299,
        "stack-trace": [
            "#0 /mcloud/www/pcs_api/vendor/yiisoft/yii2/db/Command.php(1299): PDOStatement->execute()",
            "#1 /mcloud/www/pcs_api/vendor/yiisoft/yii2/db/Command.php(1165): yii\\db\\Command->internalExecute('SELECT COUNT(*)...')",
            "#2 /mcloud/www/pcs_api/vendor/yiisoft/yii2/db/Command.php(433): yii\\db\\Command->queryInternal('fetchColumn', 0)",
            "#3 /mcloud/www/pcs_api/vendor/yiisoft/yii2/db/Query.php(505): yii\\db\\Command->queryScalar()",
            "#4 /mcloud/www/pcs_api/vendor/yiisoft/yii2/db/ActiveQuery.php(352): yii\\db\\Query->queryScalar('COUNT(*)', Object(yii\\db\\Connection))",
            "#5 /mcloud/www/pcs_api/vendor/yiisoft/yii2/db/Query.php(368): yii\\db\\ActiveQuery->queryScalar('COUNT(*)', Object(yii\\db\\Connection))",
            "#6 /mcloud/www/pcs_api/vendor/yiisoft/yii2/data/ActiveDataProvider.php(168): yii\\db\\Query->count('*', NULL)",
            "#7 /mcloud/www/pcs_api/vendor/yiisoft/yii2/data/BaseDataProvider.php(169): yii\\data\\ActiveDataProvider->prepareTotalCount()",
            "#8 /mcloud/www/pcs_api/vendor/yiisoft/yii2/data/ActiveDataProvider.php(105): yii\\data\\BaseDataProvider->getTotalCount()",
            "#9 /mcloud/www/pcs_api/vendor/yiisoft/yii2/data/BaseDataProvider.php(101): yii\\data\\ActiveDataProvider->prepareModels()",
            "#10 /mcloud/www/pcs_api/vendor/yiisoft/yii2/data/BaseDataProvider.php(114): yii\\data\\BaseDataProvider->prepare()",
            "#11 /mcloud/www/pcs_api/api/rests/plan/Serializer.php(47): yii\\data\\BaseDataProvider->getModels()",
            "#12 /mcloud/www/pcs_api/vendor/yiisoft/yii2/rest/Serializer.php(154): api\\rests\\plan\\Serializer->serializeDataProvider(Object(yii\\data\\ActiveDataProvider))",
            "#13 /mcloud/www/pcs_api/vendor/yiisoft/yii2/rest/Controller.php(99): yii\\rest\\Serializer->serialize(Object(yii\\data\\ActiveDataProvider))",
            "#14 /mcloud/www/pcs_api/vendor/yiisoft/yii2/rest/Controller.php(77): yii\\rest\\Controller->serializeData(Object(yii\\data\\ActiveDataProvider))",
            "#15 /mcloud/www/pcs_api/vendor/yiisoft/yii2/base/Controller.php(183): yii\\rest\\Controller->afterAction(Object(api\\rests\\plan\\HaveAction), Object(yii\\data\\ActiveDataProvider))",
            "#16 /mcloud/www/pcs_api/vendor/yiisoft/yii2/base/Module.php(534): yii\\base\\Controller->runAction('have', Array)",
            "#17 /mcloud/www/pcs_api/vendor/yiisoft/yii2/web/Application.php(104): yii\\base\\Module->runAction('v1/plan/have', Array)",
            "#18 /mcloud/www/pcs_api/vendor/yiisoft/yii2/base/Application.php(392): yii\\web\\Application->handleRequest(Object(yii\\web\\Request))",
            "#19 /mcloud/www/pcs_api/api/web/index.php(17): yii\\base\\Application->run()",
            "#20 {main}"
        ]
    }
}

2、去掉请求参数：filter[and][0][or][1][keyword][fis]=’，响应 200，未再报错。如图2

图2

3、分析发现，请求参数：filter[and][0][or][1][keyword][fis]=’，所生成的 SQL：(FIND_IN_SET(”’, `pa_plan`.`keyword`))。如图3

图3

4、请求参数：filter[and][0][or][0][title][like]=’，所生成的 SQL：(`pa_plan`.`title` LIKE ‘%\\’%’)，未报错。区别在于 \’。进行了转义。如图4

图4

5、复制 SQL 语句在 Navicat for MySQL 中执行，未报错。如图4

图4

6、复制 SQL 语句在 Navicat for MySQL 中执行，未报错。如图5

图5

7、参考网址：https://www.shuijingwanwq.com/2019/08/27/3458/ 。在 Yii 2.0 中，基于映射过滤条件关键字以构建方法。新增特定条件构建器(fis)的实现，以支持 MySQL FIND_IN_SET() 函数

8、编辑 /common/components/data/ActiveDataFilter.php，打印返回值：FIND_IN_SET(”’, {{%plan}}.[[keyword]])。

    /**
     * Builds an findInSet condition.
     * @param string $operator operator keyword.
     * @param mixed $condition attribute condition.
     * @param string $attribute attribute name.
     * @return string actual condition.
     */
    protected function buildFindInSetCondition($operator, $condition, $attribute)
    {
        if (isset($this->queryOperatorMap[$operator])) {
            $operator = $this->queryOperatorMap[$operator];
        }
        print_r("" . $operator . "('" . $this->filterAttributeValue($attribute, $condition) . "', " . $attribute . ")");
        exit;
        return "" . $operator . "('" . $this->filterAttributeValue($attribute, $condition) . "', " . $attribute . ")";
    }

9、编辑 /common/components/data/ActiveDataFilter.php，打印返回值：FIND_IN_SET(‘\”, {{%plan}}.[[keyword]])。使用函数 addslashes 对参数值进行转义。

    /**
     * Builds an findInSet condition.
     * @param string $operator operator keyword.
     * @param mixed $condition attribute condition.
     * @param string $attribute attribute name.
     * @return string actual condition.
     */
    protected function buildFindInSetCondition($operator, $condition, $attribute)
    {
        if (isset($this->queryOperatorMap[$operator])) {
            $operator = $this->queryOperatorMap[$operator];
        }
        print_r("" . $operator . "('" . addslashes($this->filterAttributeValue($attribute, $condition)) . "', " . $attribute . ")");
        exit;
        return "" . $operator . "('" . $this->filterAttributeValue($attribute, $condition) . "', " . $attribute . ")";
    }

10、编辑 /common/components/data/ActiveDataFilter.php，响应成功。SQL如下。如图6

图6

    /**
     * Builds an findInSet condition.
     * @param string $operator operator keyword.
     * @param mixed $condition attribute condition.
     * @param string $attribute attribute name.
     * @return string actual condition.
     */
    protected function buildFindInSetCondition($operator, $condition, $attribute)
    {
        if (isset($this->queryOperatorMap[$operator])) {
            $operator = $this->queryOperatorMap[$operator];
        }
        return "" . $operator . "('" . addslashes($this->filterAttributeValue($attribute, $condition)) . "', " . $attribute . ")";
    }

((`pa_plan`.`title` LIKE '%\'%') OR (FIND_IN_SET('\'', `pa_plan`.`keyword`)))

11、特殊字符与前端的请求参数(请求参数值需要URL编码)、最终生成的SQL的对应关系如下：

'
filter[and][0][or][0][title][like]=%27&filter[and][0][or][1][keyword][fis]=%27
((`pa_plan`.`title` LIKE '%\'%') OR (FIND_IN_SET('\'', `pa_plan`.`keyword`)))

#
filter[and][0][or][0][title][like]=%23&filter[and][0][or][1][keyword][fis]=%23
((`pa_plan`.`title` LIKE '%#%') OR (FIND_IN_SET('#', `pa_plan`.`keyword`)))

&
filter[and][0][or][0][title][like]=%26&filter[and][0][or][1][keyword][fis]=%26
((`pa_plan`.`title` LIKE '%&%') OR (FIND_IN_SET('&', `pa_plan`.`keyword`)))

\
filter[and][0][or][0][title][like]=%5C&filter[and][0][or][1][keyword][fis]=%5C
((`pa_plan`.`title` LIKE '%\\\\%') OR (FIND_IN_SET('\\', `pa_plan`.`keyword`)))

+
filter[and][0][or][0][title][like]=%2B&filter[and][0][or][1][keyword][fis]=%2B
((`pa_plan`.`title` LIKE '%+%') OR (FIND_IN_SET('+', `pa_plan`.`keyword`)))