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

Question

I will try to execute the query below, but not sure if this will 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();

returns the expected results, but SQL injection may occur. My IN state looks likeIN (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();

compares only the first element in the array, because it looks like this: IN ('1, 2, 3')it consists of one line that does not check the second element in the array, only working on the first element.

I refer to the link below, but don’t know how to implement this condition.

Is it possible to bind an array to an IN () condition?

Please give a decision on how to use IN() Conditionin Onthe connection part (PDO / Yii2 / mysql).

Answer 1

:

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

Answer 2

Yii2 IN, i.e:

['post.status' => $status]

, Yii guide:

, where() , : ['post.author_id' => 'user.id'], post.author_id 'user.id'. , :

'post.author_id = user.id'

INNER JOIN, WHERE ON , INNER JOIN condition WHERE ON?. :

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