Yii2 : Can I bind an array to an IN() condition in join?

Question

I will try below query, but not sure is prevent sql injection?

        $status = [1, 2, 3];
        $param = implode(', ', $status);

        $rows = (new \yii\db\Query())
            ->select('*')
            ->from('user')
            ->leftJoin('post', "post.user_id = user.id AND post.some_column = $value AND post.status IN ($param)");
            ->all();

return expected results but may be occur sql injection. My IN condition look like is IN (1, 2, 3)

        $rows = (new \yii\db\Query())
            ->select('*')
            ->from('user')
            ->leftJoin('post', "post.user_id = user.id AND post.some_column = :sid AND post.status IN (:param)", [':param' => $param, ':sid' => $value]);
            ->all();

only compare first element in array because is look like this IN ('1, 2, 3') its consist single string not check second element in array only work on first element.

I refer below link but no idea for how to implement this condition.

Can I bind an array to an IN() condition?

Please give the solution for how to use IN() Condition in On part of join(PDO/Yii2/mysql).

Ok, I deleted my answer because `where` conditions are diff of `on` conditions and you need `on` conditions. By the way, I open this issue, may you are interested: https://github.com/yiisoft/yii2/issues/11827 — sdlins, Jun 26 '16 at 20:59

score 5 · Accepted Answer · answered Jun 27 '16 at 20:28

5

Based on this issue:

        $rows = (new \yii\db\Query())
        ->select('*')
        ->from('user')
        ->leftJoin('post', ['post.user_id' => new \yii\db\Expression('user.id'), 'post.some_column' => $sid, 'post.status' => $statuesArray]);
        ->all();

answered Jun 27 '16 at 20:28

sdlins

2,195
1
23
31

I am using `MSSQL` . How can I pass my connection name in `(new \yii\db\Query())` – Moeez Jul 14 '22 at 11:48
@Moeez, if it is different of `db`, you generally will pass it as a param of `all()`, `one()`and similar methods of your query. You can see the docs: https://www.yiiframework.com/doc/api/2.0/yii-db-query#all()-detail – sdlins Jul 16 '22 at 05:05

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

Yii2 can create a parametrized IN condition by passing the condition as an array i.e:

['post.status' => $status]

However, converting your join condition to the array format will not work as explained in the Yii guide:

Note that the array format of where() is designed to match columns to values instead of columns to columns, so the following would not work as expected: ['post.author_id' => 'user.id'], it would match the post.author_id column value against the string 'user.id'. It is recommended to use the string syntax here which is more suited for a join:

'post.author_id = user.id'

Since you are using an INNER JOIN the result of putting the join condition in WHERE instead of in ON will be syntactically equal as explained in INNER JOIN condition in WHERE clause or ON clause?. For readability and ease of maintenance, you can leave the comparison for the tables columns in the join condition:

$rows = (new \yii\db\Query())
        ->select('*')
        ->from('user')
        ->innerJoin('post', 'post.user_id = user.id')
        ->where(['post.some_column' => $value, 'post.status' => $status])
        ->all();

sorry, I have used `leftJoin` in my project. I have write query in my question is just demo. I used this same situation in my project. how to used `IN()` on `OnCondition`? — Hiren Bhut, Jun 24 '16 at 04:37

Yii2 : Can I bind an array to an IN() condition in join?

2 Answers2