Yii2： yii\db\异常 42000 在 quote(') 搜索

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 vendoryiisoftyii2dbCommand.php(900): PDOStatement->execute()
#1 vendoryiisoftyii2dbCommand.php(388): yiidbCommand->queryInternal('fetchColumn', 0)
#2 vendoryiisoftyii2dbQuery.php(393): yiidbCommand->queryScalar()
#3 vendoryiisoftyii2dbActiveQuery.php(334): yiidbQuery->queryScalar('COUNT(*)', NULL)
#4 vendoryiisoftyii2dbQuery.php(300): yiidbActiveQuery->queryScalar('COUNT(*)', NULL)
#5 vendoryiisoftyii2dataActiveDataProvider.php(165): yiidbQuery->count('*', NULL)
#6 vendoryiisoftyii2dataBaseDataProvider.php(147): yiidataActiveDataProvider->prepareTotalCount()
#7 vendoryiisoftyii2baseComponent.php(130): yiidataBaseDataProvider->getTotalCount()
#8 appmodulespostviewsdefaultsearch.php(47): yiibaseComponent->__get('totalCount')
#9 vendoryiisoftyii2baseView.php(325): require('...')
#10 vendoryiisoftyii2baseView.php(247): yiibaseView->renderPhpFile('...', Array)
#11 vendoryiisoftyii2baseView.php(149): yiibaseView->renderFile('...', Array, Object(appmodulespostcontrollersDefaultController))
#12 vendoryiisoftyii2baseController.php(378): yiibaseView->render('search', Array, Object(appmodulespostcontrollersDefaultController))
#13 appmodulespostcontrollersDefaultController.php(94): yiibaseController->render('search', Array)
#14 [internal function]: appmodulespostcontrollersDefaultController->actionSearch(''"', NULL, NULL)
#15 vendoryiisoftyii2baseInlineAction.php(55): call_user_func_array(Array, Array)
#16 vendoryiisoftyii2baseController.php(154): yiibaseInlineAction->runWithParams(Array)
#17 vendoryiisoftyii2baseModule.php(454): yiibaseController->runAction('search', Array)
#18 vendoryiisoftyii2webApplication.php(87): yiibaseModule->runAction('post/default/se...', Array)
#19 vendoryiisoftyii2baseApplication.php(375): yiiwebApplication->handleRequest(Object(yiiwebRequest))
#20 appwebindex.php(16): yiibaseApplication->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%" )