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`)))