1

How can I properly bind an array for a raw query's NOT IN / IN?

The following throws an array to string conversion error:

    $user_list_to_exclude = [1, 2, 3];
    
    $data = DB::select("SELECT * FROM users WHERE user_id NOT IN :user_list_to_exclude",
                ['user_list_to_exclude' => $user_list_to_exclude]);
pileup
  • 1
  • 2
  • 18
  • 45
  • 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) – OMi Shah Jul 19 '23 at 08:49
  • also: https://stackoverflow.com/questions/41913973/binding-parameter-to-dbraw-laravel-query – OMi Shah Jul 19 '23 at 08:50
  • My bindings are working but not for an array. The other posts use a single value bindings (Which work for me) – pileup Jul 19 '23 at 08:55

1 Answers1

1

you can use php implode to help you do that:

  $placeholders = implode(',', array_fill(0, count($user_list_to_exclude), '?'));

    $data = DB::table('users')->select(['*'])
    ->whereRaw("users.id NOT IN ($placeholders)", $user_list_to_exclude)->get();
OMR
  • 11,736
  • 5
  • 20
  • 35
  • But then it's not using bind parameters, I think it leaves a SQL injection vulnerability? (In case it's not just a hard-coded array but a user input) – pileup Jul 19 '23 at 10:16
  • 1
    $user_list_to_exclude is a array, why you think it's not bind parameters? – OMR Jul 19 '23 at 10:44
  • see: https://laravel.com/api/10.x/Illuminate/Database/Query/Builder.html#method_selectRaw – OMR Jul 19 '23 at 10:45
  • Oh yes, I did not notice the `, $user_list_to_exclude` part. `$placeholders` is just the `?` symbols, sorry. But, in reality I am using named parameters. I am not sure I can mix between the two? i.e. `['param1' => $param1, ..]` – pileup Jul 19 '23 at 10:49
  • no, in named parameters , you should set place holder manually – OMR Jul 19 '23 at 11:05