44

I have a string of ids like 1,2,3,4,5 and I want to be able to list all rows in mysql where the ID is contained in that list.

I assumed the easiest way would be to turn the string into an array and then match in ($array) but it doesn't work for me - no errors etc but it returns no rows:

$string="1,2,3,4,5";
$array=array_map('intval', explode(',', $string));
$query=mysqli_query($conn, "SELECT name FROM users WHERE id IN ('".$array."')");

If I do a var_dump of $array I get:

array(5) { 
    [0]=> int(1) 
    [1]=> int(2) 
    [2]=> int(3) 
    [3]=> int(4) 
    [4]=> int(5) 
}

Any idea where I am screwing up?

bhttoan
  • 2,641
  • 5
  • 42
  • 71

3 Answers3

98
$string="1,2,3,4,5";
$array=array_map('intval', explode(',', $string));
$array = implode("','",$array);
$query=mysqli_query($conn, "SELECT name FROM users WHERE id IN ('".$array."')");

NB: the syntax is:

SELECT * FROM table WHERE column IN('value1','value2','value3')

Ploppy
  • 14,810
  • 6
  • 41
  • 58
CrayonViolent
  • 32,111
  • 5
  • 56
  • 79
  • 1
    @bhttoan it might have worked great, but it is subject to vulnerabilities... – qwertynl Nov 25 '13 at 21:17
  • 1
    How so? I showed a VERY simplified and stripped down view of the code (including the actual query itself) to avoid putting too much unnecessary code but I am happy that the array content is sanitised and safe – bhttoan Nov 25 '13 at 21:23
  • @bhttoan You are not parameterizing anything. Especially if the string is coming from the client there could be issues. – qwertynl Nov 25 '13 at 21:25
  • 2
    @qwertynl parameterizing is a solid way to protect against sql injection but that doesn't mean all other ways are wrong. If I expect a value of "10" and I do `$string==10` this is not any weaker than parameterizing.. – CrayonViolent Nov 25 '13 at 21:36
  • how would you parameterize this statement? –  Apr 06 '20 at 22:38
  • @qwertynl That array went through intval(), there is no room for SQL injection. I use intval a lot myself when I only work with numbers. – Daniel Wu Apr 26 '20 at 15:46
10

Your query translates to:

SELECT name FROM users WHERE id IN ('Array');

Or something to that affect.

Try using prepared queries instead, something like:

$numbers = explode(',', $string);
$prepare = array_map(function(){ return '?'; }, $numbers);
$statement = mysqli_prepare($link , "SELECT name FROM users WHERE id IN ('".implode(',', $prepare)."')");
if($statement) {
   $ints = array_map(function(){ return 'i'; }, $numbers);
   call_user_func_array("mysqli_stmt_bind_param", array_merge(
      array($statement, implode('', $ints)), $numbers
   ));
   $results = mysqli_stmt_execute($statement);
   // do something with results 
   // ...
}
qwertynl
  • 3,912
  • 1
  • 21
  • 43
4

Change

$array=array_map('intval', explode(',', $string));

To:

$array= implode(',', array_map('intval', explode(',', $string)));

array_map returns an array, not a string. You need to convert the array to a comma separated string in order to use in the WHERE clause.

Darius
  • 612
  • 2
  • 11
  • 23
  • Then that just turns it back into the original string... What is the point? – qwertynl Nov 25 '13 at 21:01
  • I believe that's what he requested. – Darius Nov 25 '13 at 21:22
  • This looks like the same end result as my chosen answer but in one line rather than two so not sure why the downvote? FYI @Darius I did not downvote this – bhttoan Nov 25 '13 at 21:28
  • @bhttoan this answer is the same as doing: `$query=mysqli_query($conn, "SELECT name FROM users WHERE id IN ('".$string."')"); ` It does not change the original string at all. – qwertynl Nov 25 '13 at 21:38
  • @qwertynl Well the OP wanted to run the intval() function on each value in the array, then spit back a comma-separated string. I accomplished what was asked by the OP, nothing more, whether or not array_map() was required. – Darius Nov 25 '13 at 21:58