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 MySQL Server version for the right syntax to use near6, find_in_set(, `PA_plan`.`keyword`)
1. Interface response to 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 MySQL Server version for the right syntax to use near6. as shown in Figure 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)
Figure 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. Remove the request parameter: filter[and][0][or][1][keyword][fis]=, the response is 200, and no error is reported. as shown in Figure 2
![去掉请求参数：filter[and][0][or][1][keyword][fis]='，响应 200，未再报错。](https://www.shuijingwanwq.com/wp-content/uploads/2021/05/2-2.png)
Figure 2
3, the analysis found, the request parameter: filter[and][0][or][1][keyword][fis]=, the generated sql: (find_in_set(, `pa_plan`.`keyword`)). as shown in Figure 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)
Figure 3
4. Request parameters: filter[and][0][or][0][title][like]=, the generated sql:(`pa_plan`.`title` like%\\%), no error is reported. The difference is \. escaped. as shown in Figure 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)
Figure 4
5. Copy the SQL statement to execute in Navicat for MySQL, no error is reported. as shown in Figure 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)
Figure 4
6. Copy the SQL statement to execute in Navicat for MySQL, no error is reported. as shown in Figure 5
Figure 5
7. Reference URL:https://www.shuijingwanwq.com/2019/08/27/3458/. In Yii 2.0, the method is constructed based on the mapping filter condition keyword. Added implementation of specific condition builder (FIS) to support the mysql find_in_set() function
8. Edit /common/components/data/activedatafilter.php, and print the return value: 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. Edit /common/components/data/activedatafilter.php, and print the return value: find_in_set(\, {{%plan}}.[[keyword]]). Use the function addslashes to escape the parameter value.
/**
* 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. Edit /common/components/data/activedatafilter.php, and the response is successful. SQL is as follows. as shown in Figure 6
Figure 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. The correspondence between the special characters and the front-end request parameters (the request parameter value requires URL encoding), and the final generated SQL is as follows:
'
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`)))
