CODEWITHSUNDEEP
phpmysqlyii2sql-injection
Hiren Bhut
Hiren Bhut

Reputation: 1226

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

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).

Upvotes: 4

Views: 3903

Answers (2)

sdlins
sdlins

Reputation: 2295

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

Upvotes: 5

topher
topher

Reputation: 14860

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

Upvotes: 0

Related Questions