Yii2 Mssql Unknown PDO::PARAM_* constant given

Question

I am working on Yii2. I have added an MSSQL Database. For preventing from SQL injections please see my below is my query

$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();

When I execute it I am getting

{
"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}"
     ]
}

After checking this Answer I have done the following

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

Now I get

{
"name": "Database Exception",
"message": "SQLSTATE[HY093]: Invalid parameter number: Columns/Parameters are 1-based\nFailed to prepare SQL: select CustomerCode, CustomerNameFull\r\n,AreaCode,AreaNameFull,CityNameFull\r\n,ContactPerson,Address1,MobileNumber\r\nfrom Customers\r\nwhere 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}"
    ]
}
}

I must be missing something that I don't know. How to get this issue resolved? Any help would be highly appreciated.

Note: $area_ids is the array

Answer 1

The pattern I employ in these situations is this:

$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();

Please note: if your $area_ids array is empty, this will result in some_id IN () which is invalid syntax in nearly all SQL databases. You have to either remove that condition, or decide whether you need to call the query at all.

Answer 2

You can just let query builder take care of it for you.

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

The query builder uses param binding internally so you don't have to worry about SQL injection.

Side note: You don't need to load all AllowArea models just to get the list of area codes in cycle from them. You can do it like this:

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

The column() method returns first selected column as array.