0

Can someone help me get the SELECT query (2). below to work?

This string used for both SELECT statements:

$seller_items = ('6','9','12','13','14','15','16','17','18','19','20','22','23','24','25','26','28','27','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','53','54','55','57','58','59','60','62','63','64','65','61','67','56','69','70','74','73','75','78','80','76','72','95','94','101','102','71','103','2','104','4','81','21','10','11','3','79','5','8','7','97','93','96','98');

(1). This SELECT query is working fine:

if ($stmt = $mysqli->prepare("SELECT info FROM items WHERE item_id IN $seller_items AND active = ?")){
$stmt->bind_param("s",$active);

(2). This SELECT query is not working:

if ($stmt = $mysqli->prepare("SELECT info FROM items WHERE item_id IN ? AND active = ?")){
$stmt->bind_param("ss",$seller_items,$active);

I think placing the variable in the SELECT query itself may defeat the purpose of a prepared statement.

I can get the IN predicate to work just fine with a non-prepared statement. It's the prepared statement with which I am having the problem.

Thank you in advance.

Harvey
  • 65
  • 6
  • 4
    You can't parameterize the `IN` operator in SQL. – Dai Jun 21 '18 at 22:04
  • In MSSQL, the way to parameterize an array of values is to pass it as a table-valued-parameter and do a JOIN with it. I assume MySQL has something similar. Failing that, create and insert into a temporary table. – Dai Jun 21 '18 at 22:05
  • Possible duplicate of [Use one bind\_param() with variable number of input vars](https://stackoverflow.com/questions/793471/use-one-bind-param-with-variable-number-of-input-vars) – Mike Jun 21 '18 at 22:14
  • @Dai - thanks for the information. I appreciate it. :) – Harvey Jun 22 '18 at 17:19
  • @Mike - thanks for the information and the link. I appreciate it. :) – Harvey Jun 22 '18 at 17:21

1 Answers1

1

As @Dai mentioned the IN cannot be parameterized with just one variable. Sure it can be done with a series of parameters but the number of them is fixed. The idea with prepare statements is that the insertion of values are expected the same position, the same number of parameters and the same kind.

If the amount of parameter inside of the IN is fixed, something like this works:

$a=[1,2,3];
$s=$mysqli->prepare("SELECT id FROM users WHERE role_id IN (?,?,?)");
$s->bind_param('iii',$a[0],$a[1],$a[2]);
$s->execute();
$s->bind_result($id);
$c=[];
while($s->fetch()){
  $c[]=$id;
}
var_dump($c);

Maybe this is not the answer that you are looking for, but if the amount of variables is not know better insert the imploded array string inside the original SQL command.

$a=[1,2,3];
$b="('".implode("','",$a)."')";
$s=$mysqli->prepare("SELECT id FROM users WHERE role_id IN {$b}");
$s->execute();
$s->bind_result($id);
$c=[];
while($s->fetch()){
  $c[]=$id;
}
var_dump($c);
CarlosH.
  • 711
  • 7
  • 12
  • An option is to have a large predefined number of placeholders and put `NULL` into the placeholders that aren't being used. I assume the MySQL query preprocessor will optimize that away, e.g. `WHERE foo IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,)` -> `WHERE foo IN (1,2,3,4,NULL,NULL,NULL,NULL,NULL,NULL,etc)` – Dai Jun 22 '18 at 02:41
  • Yes that could works. However the `bind_param` also become tricky to fulfill, it will need a bunch of dummy variables. – CarlosH. Jun 22 '18 at 03:27
  • @Carlos H. - you saved me a lot of headaches. thanks. :) would another question be ok? the {$b} works great. does that method impede the security of the SELECT query? – Harvey Jun 22 '18 at 17:29
  • @Harvey it all depends how those values are captured and sanitized to avoid that includes single quotes. If they do (e.g. a Irish last name), they need be escaped. If they are for sure numeric should not be a problem. In reality it is a developer due diligence cleaning users inputs wherever goes to prepare statements or not. – CarlosH. Jun 24 '18 at 17:20