Multi Parameter binding prevent SQL Injection in laravel with DB:raw in WHERE IN clause-CodePudding

How can I bind multiple parameters from different arrays to prevent SQL injection?

$userTypeID = [1,2,3];
$userDeptID = [1,4,6];
$arr = join(",",$arr);
DB::select("select * from users WHERE userTypeID IN (?,?,?) AND userDeptID IN(?,?,?)");

I'm not able to use eloquent because I have a complex query for which I'm using raw queries. So to prevent it from SQL Injection I've to bind the parameters but this isn't working.

CodePudding user response：

Use array_merge to merge two arrays to single. And pass it to DB::select() as second parameter.

$userTypeID = [1,2,3];
$userDeptID = [1,4,6];
$arr = array_merge($userTypeID, $userDeptID);

DB::select("SELECT * FROM users WHERE userTypeID IN (?,?,?) AND userDeptID IN(?,?,?)", $arr);

EDIT: OR alternatively, you can also use OPENJSON in SQL-Server to convert json object to row. This can then be used in IN operator to extract required data.

$userDeptID = (array) json_encode([1,4,6]);
DB::select("SELECT * FROM users WHERE userTypeID IN (SELECT value FROM OPENJSON(?))", $userDeptID);

CodePudding user response：

There are options other than Eloquent relations and just raw SQL. Try doing this with query builder instead:

$userTypeID = [1,2,3];
$userDeptID = [1,4,6];

DB::table("users")
    ->select("*")
    ->whereIn("userTypeID", $userTypeID)
    ->whereIn("userDeptID", $userDeptID);