-1

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.

miken32
  • 42,008
  • 16
  • 111
  • 154
Syed Arsalan Ahmed
  • 115
  • 6
  • Does this answer your question? [How to bind parameters to a raw DB query in Laravel that's used on a model?](https://stackoverflow.com/questions/20864872/how-to-bind-parameters-to-a-raw-db-query-in-laravel-thats-used-on-a-model) – Waseem Alhabash Dec 31 '21 at 12:57
  • What is complex about your query that is preventing you using Eloquent? – Peppermintology Dec 31 '21 at 13:09
  • `DB::select("select * from users WHERE userTypeID IN (?,?,?) AND userDeptID IN(?,?,?)", $arr);` How about this? You tried this? Also, can you explain what your problem is? – sonam81 Dec 31 '21 at 13:24
  • Yes, sonam81 i tried that but parameters count issue occurred for that.. – Syed Arsalan Ahmed Dec 31 '21 at 13:51
  • Peppermintology, yes I actually the graph is complex because the result is populated into graph data and other reporting sections. so that's why if we go for eloquent right I would be a little bit expensive and challenging as well... – Syed Arsalan Ahmed Dec 31 '21 at 13:53
  • the bindings would be in an array, not a single string – lagbox Dec 31 '21 at 14:00
  • yes, that is the when i'm going to convert array to string then query become SELECT * FROM table WHERE ID IN('1,2,3,4') AND DeptID IN('1,4,6') and got array to string conversion array. is there any that I convert array into string like this: [1,2,3] => "1","2","3" OR 1,2,3 when I'm doing this by using implode(',',$arr) then result occur in this format '1,2,3'.... – Syed Arsalan Ahmed Dec 31 '21 at 14:13
  • no it needs to be multiple values, that is why it would be an array ... making it a string would be 1 single value, you have 6 parameters there, not 1 – lagbox Dec 31 '21 at 14:14
  • yes, lagbox so how can i do that brother any solution because I'm totally stuck at this stage. try all the solution. – Syed Arsalan Ahmed Dec 31 '21 at 14:17
  • by passing an array of all the values you want to bind to those parameters; an array with 6 elements ... not sure what part is holding you up about that – lagbox Dec 31 '21 at 14:24
  • yes, actually in my application i've have 6 multiple selectors which accept the value in array then in my function I'm using laravel DB:raw query to the data from database on the behalf of that filter and populate that data in different reporting section. To prevent from SQL Injection I have to bind the parameters as well all the parameters values are in array and my SQL query I use that parameter with "WHERE IN"clause. – Syed Arsalan Ahmed Dec 31 '21 at 14:29

1 Answers1

1

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);
sonam81
  • 157
  • 2
  • 9