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
![接口响应 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'。](https://www.shuijingwanwq.com/wp-content/uploads/2021/05/1-2.png)
图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
![去掉请求参数：filter[and][0][or][1][keyword][fis]='，响应 200，未再报错。](https://www.shuijingwanwq.com/wp-content/uploads/2021/05/2-2.png)
图2
3、分析发现，请求参数：filter[and][0][or][1][keyword][fis]=’，所生成的 SQL：(FIND_IN_SET(”’, `pa_plan`.`keyword`))。如图3
![分析发现，请求参数：filter[and][0][or][1][keyword][fis]='，所生成的 SQL：(FIND_IN_SET(''', `pa_plan`.`keyword`))。](https://www.shuijingwanwq.com/wp-content/uploads/2021/05/3-2.png)
图3
4、请求参数：filter[and][0][or][0][title][like]=’，所生成的 SQL：(`pa_plan`.`title` LIKE ‘%\\’%’)，未报错。区别在于 \’。进行了转义。如图4
![请求参数：filter[and][0][or][0][title][like]='，所生成的 SQL：(`pa_plan`.`title` LIKE '%\\'%')，未报错。区别在于 \'。进行了转义。](https://www.shuijingwanwq.com/wp-content/uploads/2021/05/4-2.png)
图4
5、复制 SQL 语句在 Navicat for MySQL 中执行，未报错。如图4
![请求参数：filter[and][0][or][0][title][like]='，所生成的 SQL：(`pa_plan`.`title` LIKE '%\\'%')，未报错。区别在于 \'。进行了转义。](https://www.shuijingwanwq.com/wp-content/uploads/2021/05/4-3.png)
图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`)))
近期评论