Yii2: yii\db\Exception 42000对引号(‘)搜索

Question

嗨，我对Yii2搜索模型有问题。当用户试图使用引号(例如)进行搜索时，会引发以下情况：

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 's%' )' at line 1 
The SQL being executed was: 
SELECT COUNT(*) FROM `user` `t` WHERE id in (SELECT user_id from user_profile WHERE nickname like '%it's%' )

堆栈跟踪：

#0 \vendor\yiisoft\yii2\db\Command.php(900): PDOStatement->execute()
#1 \vendor\yiisoft\yii2\db\Command.php(388): yii\db\Command->queryInternal('fetchColumn', 0)
#2 \vendor\yiisoft\yii2\db\Query.php(393): yii\db\Command->queryScalar()
#3 \vendor\yiisoft\yii2\db\ActiveQuery.php(334): yii\db\Query->queryScalar('COUNT(*)', NULL)
#4 \vendor\yiisoft\yii2\db\Query.php(300): yii\db\ActiveQuery->queryScalar('COUNT(*)', NULL)
#5 \vendor\yiisoft\yii2\data\ActiveDataProvider.php(165): yii\db\Query->count('*', NULL)
#6 \vendor\yiisoft\yii2\data\BaseDataProvider.php(147): yii\data\ActiveDataProvider->prepareTotalCount()
#7 \vendor\yiisoft\yii2\base\Component.php(130): yii\data\BaseDataProvider->getTotalCount()
#8 \app\modules\post\views\default\search.php(47): yii\base\Component->__get('totalCount')
#9 \vendor\yiisoft\yii2\base\View.php(325): require('...')
#10 \vendor\yiisoft\yii2\base\View.php(247): yii\base\View->renderPhpFile('...', Array)
#11 \vendor\yiisoft\yii2\base\View.php(149): yii\base\View->renderFile('...', Array, Object(app\modules\post\controllers\DefaultController))
#12 \vendor\yiisoft\yii2\base\Controller.php(378): yii\base\View->render('search', Array, Object(app\modules\post\controllers\DefaultController))
#13 \app\modules\post\controllers\DefaultController.php(94): yii\base\Controller->render('search', Array)
#14 [internal function]: app\modules\post\controllers\DefaultController->actionSearch(''"', NULL, NULL)
#15 \vendor\yiisoft\yii2\base\InlineAction.php(55): call_user_func_array(Array, Array)
#16 \vendor\yiisoft\yii2\base\Controller.php(154): yii\base\InlineAction->runWithParams(Array)
#17 \vendor\yiisoft\yii2\base\Module.php(454): yii\base\Controller->runAction('search', Array)
#18 \vendor\yiisoft\yii2\web\Application.php(87): yii\base\Module->runAction('post/default/se...', Array)
#19 \vendor\yiisoft\yii2\base\Application.php(375): yii\web\Application->handleRequest(Object(yii\web\Request))
#20 \app\web\index.php(16): yii\base\Application->run()
#21 {main}

Yii2不能自动转义参数吗？我一定要这么做吗？

谢谢。

Answer 1

假设你在寻找它，你有一个错误的引号序列

尝试对内部引号使用转义

SELECT COUNT(*) FROM `user` `t` WHERE id in (SELECT user_id 
                                             from user_profile 
                                             WHERE nickname like '%it\'s%' )

或者使用类似的双引号

SELECT COUNT(*) FROM `user` `t` WHERE id in (SELECT user_id 
                                             from user_profile 
                                             WHERE nickname like ("%it's%" )