Yii2 Mssql未知PDO::PARAM_*常量给定

Question

我正在研究Yii2。我加入了一个MSSQL Database。为了防止SQL注入，请参阅我的下面是我的查询

$area_ids = [];
$s = AllowArea::find()->where(['user_id' => Yii::$app->user->id])->all();
foreach ($s as $b) {
$area_ids[] = $b->area_code;
}
$ref = Yii::$app->sds->createCommand("select CustomerCode, CustomerNameFull
,AreaCode,AreaNameFull,CityNameFull
,ContactPerson,Address1,MobileNumber
from Customers
where AreaCode = :area_ids")->bindValues([':area_ids'=>$area_ids])->queryAll();

当我执行它时，我得到

{
"name": "PHP Fatal Error",
"message": "Unknown PDO::PARAM_* constant given.",
"code": 1,
"type": "yii\base\ErrorException",
"file": "G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\db\Command.php",
"line": 1290,
"stack-trace": [
"#0 [internal function]: yii\base\ErrorHandler->handleFatalError()",
"#1 {main}"
]
}

检查完这个答案后，我做了以下操作

->bindValues([':area_ids'=>$area_ids,PDO::PARAM_STR])

现在得到

{
"name": "Database Exception",
"message": "SQLSTATE[HY093]: Invalid parameter number: Columns/Parameters are 1-basednFailed to prepare SQL: select CustomerCode, CustomerNameFullrn,AreaCode,AreaNameFull,CityNameFullrn,ContactPerson,Address1,MobileNumberrnfrom Customersrnwhere AreaCode = :area_ids",
"code": 0,
"type": "yii\db\Exception",
"file": "G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\db\Command.php",
"line": 264,
"stack-trace": [
"#0 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\db\Command.php(1153): yii\db\Command->prepare(true)",
"#1 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\db\Command.php(399): yii\db\Command->queryInternal('fetchAll', NULL)",
"#2 G:\xampp\htdocs\wrfnan\api\modules\v1\controllers\RoutesController.php(400): yii\db\Command->queryAll()",
"#3 [internal function]: api\modules\v1\controllers\RoutesController->actionRefs()",
"#4 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array(Array, Array)",
"#5 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\base\Controller.php(157): yii\base\InlineAction->runWithParams(Array)",
"#6 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\base\Module.php(528): yii\base\Controller->runAction('refs', Array)",
"#7 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\web\Application.php(103): yii\base\Module->runAction('v1/routes/refs', Array)",
"#8 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\base\Application.php(386): yii\web\Application->handleRequest(Object(yii\web\Request))",
"#9 G:\xampp\htdocs\wrfnan\api\web\index.php(35): yii\base\Application->run()",
"#10 {main}"
],
"error-info": [
"HY093",
0
],
"previous": {
"name": "Exception",
"message": "SQLSTATE[HY093]: Invalid parameter number: Columns/Parameters are 1-based",
"code": "HY093",
"type": "PDOException",
"file": "G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\db\Command.php",
"line": 316,
"stack-trace": [
"#0 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\db\Command.php(316): PDOStatement->bindValue(0, 2, 1)",
"#1 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\db\Command.php(260): yii\db\Command->bindPendingParams()",
"#2 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\db\Command.php(1153): yii\db\Command->prepare(true)",
"#3 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\db\Command.php(399): yii\db\Command->queryInternal('fetchAll', NULL)",
"#4 G:\xampp\htdocs\wrfnan\api\modules\v1\controllers\RoutesController.php(400): yii\db\Command->queryAll()",
"#5 [internal function]: api\modules\v1\controllers\RoutesController->actionRefs()",
"#6 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array(Array, Array)",
"#7 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\base\Controller.php(157): yii\base\InlineAction->runWithParams(Array)",
"#8 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\base\Module.php(528): yii\base\Controller->runAction('refs', Array)",
"#9 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\web\Application.php(103): yii\base\Module->runAction('v1/routes/refs', Array)",
"#10 G:\xampp\htdocs\wrfnan\vendor\yiisoft\yii2\base\Application.php(386): yii\web\Application->handleRequest(Object(yii\web\Request))",
"#11 G:\xampp\htdocs\wrfnan\api\web\index.php(35): yii\base\Application->run()",
"#12 {main}"
]
}
}

我一定错过了一些我不知道的东西。如何解决这个问题?如有任何帮助，我将不胜感激。

注意:$area_ids为数组

Answer 1

您可以让查询生成器为您处理。

$ref = (new yiidbQuery())
->select([
'CustomerCode',
'CustomerNameFull',
'AreaCode',
'AreaNameFull',
'CityNameFull',
'ContactPerson',
'Address1',
'MobileNumber'
])->from('Customers')
->where(['AreaCode' => $area_$ids])
->all(Yii::$app->sds);

查询生成器在内部使用参数绑定，因此您不必担心SQL注入。

注:您不需要加载所有AllowArea模型来获取循环中的区号列表。你可以这样做:

$area_ids = AllowArea::find()
->select(['area_code'])
->where(['user_id' => Yii::$app->user->id])
->column();

column()方法返回第一个选定的列作为数组。

Answer 2

我在这些情况下使用的模式是:

$params = []; // positional only
// add any preceding parameters to it
$qmarks = [];
foreach ($area_ids as $id) {
$params[] = $id;
$qmarks[] = '?';
}
$queryStr = 'SELECT... WHERE some_id IN (' . implode(',', $qmarks) . ')';
$stmt = Yii::$app->sds->createCommand($queryStr); // though I use PDO directly, this is close enough
$indBase = 1; // base. if we have preceding parameters it must be higher
foreach ($params as $ind => $param) {
$stmt->bindValue($indBase + $ind, $param);
}
$ref = $stmt->queryAll();

请注意:如果你的$area_ids数组是空的，这将导致some_id IN ()，这是无效的语法在几乎所有的SQL数据库。您必须要么删除该条件，要么决定是否需要调用查询。