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

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.