1. The API response returned an 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 near ‘6’. See Figure 1.
![The API response returned an 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 near '6'.](https://media.shuijingwanwq.com/2021/05/1-2.png)
JSON
{
"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. After removing the request parameter filter[and][0][or][1][keyword][fis]=’, the response returned 200 with no further errors. See Figure 2.
![After removing the request parameter filter[and][0][or][1][keyword][fis]=', the response returned 200 with no further errors.](https://media.shuijingwanwq.com/2021/05/2-2.png)
3. Analysis revealed that the request parameter filter[and][0][or][1][keyword][fis]=’ generated the following SQL: (FIND_IN_SET(”’, `pa_plan`.`keyword`)). See Figure 3.
![Analysis revealed that the request parameter filter[and][0][or][1][keyword][fis]=' generated the following SQL: (FIND_IN_SET(''', `pa_plan`.`keyword`)).](https://media.shuijingwanwq.com/2021/05/3-2.png)
4. The request parameter filter[and][0][or][0][title][like]=’ generated the SQL (`pa_plan`.`title` LIKE ‘%\\’%’) without any errors. The difference lies in the \’, which was escaped. See Figure 4.
![The request parameter filter[and][0][or][0][title][like]=' generated the SQL (`pa_plan`.`title` LIKE '%\\'%') without any errors. The difference lies in the \', which was escaped.](https://media.shuijingwanwq.com/2021/05/4-2.png)
5. Copying and executing the SQL statement in Navicat for MySQL resulted in no errors. See Figure 4.
![The request parameter filter[and][0][or][0][title][like]=' generated the SQL (`pa_plan`.`title` LIKE '%\\'%') without any errors. The difference lies in the \', which was escaped.](https://media.shuijingwanwq.com/2021/05/4-3.png)
6. Copying and executing the SQL statement in Navicat for MySQL resulted in no errors. See Figure 5.
7. Reference URL: https://www.shuijingwanwq.com/2019/08/27/3458/ . In Yii 2.0, filter condition keywords are mapped to build methods. A specific condition builder (fis) was added to support the MySQL FIND_IN_SET() function.
8. Editing /common/components/data/ActiveDataFilter.php, the printed return value is: FIND_IN_SET(”’, {{%plan}}.[[keyword]]).
PHP
/**
* 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. Editing /common/components/data/ActiveDataFilter.php, the printed return value is: FIND_IN_SET(‘\”, {{%plan}}.[[keyword]]). The addslashes function is used to escape the parameter value.
PHP
/**
* 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. Editing /common/components/data/ActiveDataFilter.php, the response succeeded. The SQL is as follows. See Figure 6.
PHP
/**
* 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 . ")";
}
SQL
((`pa_plan`.`title` LIKE '%\'%') OR (FIND_IN_SET('\'', `pa_plan`.`keyword`)))
11. The mapping between special characters, frontend request parameters (request parameter values require URL encoding), and the final generated SQL is as follows:
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`)))