4

I am trying to pass an array to $stmt->bind_param for as an IN variable. How can I do this?

$values = array('a','b','c','d');
$values = '"' . implode('","', $values) . '"';

$stmt->prepare('SELECT value1, value2 FROM table1 WHERE value3 IN (?)');
$stmt->bind_param('s', $values);

I can't get it to work for the life of me. Any thoughts? The above code is just a sample.

ATLChris
  • 3,198
  • 7
  • 39
  • 65

2 Answers2

2

It can but SHOULD NOT be done, as it is code that is hard to maintain (read, understand, debug, change).

A way to do it would use dynamic variables to provide auto-referencable variables and using call_user_func_array to supply a dynamic amount of arguments to the callback/method $stmt->bind_param().

<?php
$values = array('a','b','c','d');

$s = substr( str_repeat( ' , ?' , count( $values ) ) , 2 );
$stmt->prepare('SELECT value1, value2 FROM table1 WHERE value3 IN (' . $s . ')');
# OR array_map in case of different datatypes
$typeDefintions = str_repeat( 's' , count( $values ) );
$params = array( $typeDefinitions );
foreach ( $values as $k => $v ) {
    ${ 'varvar' . $k } = $v;
    $params[] = &${ 'varvar' . $k };# provide references
}
call_user_func_array( array( $stmt , 'bind_param' ) , $params );
imme
  • 598
  • 1
  • 9
  • 22
-1

This is a scenario where doing it this way is inappropriate. You're constructing actual SQL (that's what the commas and quotes are), and passing it in as a parameter. It's basically evaluating to value3 IN ('...') where ... is the entirety of $values.

Also that's a good call about the quotes. MySQL uses single quotes.

You'll need to either build the SQL using string concatenation alone, or use more than one parameter.

EDIT

As an example:

$values = array('a','b','c','d');
$values = "'" . implode("','", $values) . "'";
$stmt->prepare('SELECT value1, value2 FROM table1 WHERE value3 IN (' . $values . ')');
Chris Eberle
  • 47,994
  • 12
  • 82
  • 119
  • Could you maybe provide a little code sample or a link so I can learn more? – ATLChris May 19 '11 at 03:05
  • Thanks, do I need to be worried about SQL injection with this method? The `$values` in my real work is user generated. – ATLChris May 19 '11 at 11:10
  • @ATLChris, in that case, yes. You should pre-escape the values yourself with `mysqli->real_escape_string()` before inserting it directly into your query. – Jason McCreary May 19 '11 at 13:37
  • Please delete this answer. It still contains an SQL injection, which is only mentioned in the comments. This is too dangerous, and it has been like that for almost 9 years. – Roland Illig Mar 31 '20 at 18:06
  • @RolandIllig it's not "injection" if none of the data comes from a user. If the coder trusts the inputs, this solution works fine. If not, the comments above yours already address the proper handling. Stop concern-trolling please. – Chris Eberle Mar 31 '20 at 20:32