Passing array of values from form to sql query WHERE IN clause

Question

I have a form with a number of checkboxes, which are generated from unique values in a MySQL table:

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<?php
    $query5="SELECT distinct from_user from tracks WHERE uploaded_page='$this_path_short' ORDER BY from_user";
    $result5=mysql_query($query5) or die(mysql_error());
    while ($row = mysql_fetch_array($result5)) {
        $from_user = $row['from_user'];
        echo "<input type=\"checkbox\" name=\"from_user[]\" value=\"AND ".$from_user."\" checked=\"checked\">".$from_user."<br>";
    }
?>
<input type="submit" name="submit" value="filter"><br>

I would then like to pass the array of 'from_user' values to another MySQL query on the page. I can get the values like this:

$names=implode(" ", $_POST['from_user']);

But I am not sure how to include this array in the following MySQL query:

$query1="SELECT * FROM tracks WHERE from_user IN **array goes here**)";

possible duplicate of [Use the IN directive to search with a prepared statement](http://stackoverflow.com/questions/9006066/use-the-in-directive-to-search-with-a-prepared-statement) — Gordon, Aug 11 '12 at 11:28
possible duplicate of [PDO with where in queries](http://stackoverflow.com/questions/2373562/pdo-with-where-in-queries) — Gordon, Aug 11 '12 at 11:28
possible duplicate of [MySQL Prepared statements with a variable size variable list](http://stackoverflow.com/questions/327274/mysql-prepared-statements-with-a-variable-size-variable-list) — Gordon, Aug 11 '12 at 11:29
*(related)* [MySql vs MySqlI vs PDO](http://stackoverflow.com/questions/548986/mysql-vs-mysqli-in-php/8891836#8891836) — Gordon, Aug 11 '12 at 11:31

score 0 · Answer 1 · answered Aug 11 '12 at 11:29

0

$query1='SELECT * FROM tracks WHERE from_user IN ('.implode(',',$_POST['from_user']).')';

answered Aug 11 '12 at 11:29

3

-1 because the code is wide open to SQL Injection. Never ever use user input directly in a SQL statement. Sanitize the input and use prepared statements. – Gordon Aug 11 '12 at 11:31
point taken, but - with out knowing what was done to post before this point, you cant say it hasn't been sanitised. – Aug 12 '12 at 02:32
maybe it was, maybe it wasnt. If you don't know it, you shouldn't assume it. And there clearly is nothing hinting at it in the OP's code. – Gordon Aug 12 '12 at 09:34
and like wise why assume it wasn't? – Aug 12 '12 at 19:28
+1 because of two reasons: 1. I don't like the fact that veteran users here "educate" by "slapping other users' hand". and 2. when you downvote you state that this answer was not useful - which is not true. TMHO, @Gordon's remark is good as a comment, no need to downvote. – Nir Alfasi Aug 14 '12 at 02:25

Besnik · Answer 2 · 2012-08-11T11:40:45.107

0

Remove the AND so the checkbox value looks like this:

echo "<input type=\"checkbox\" name=\"from_user[]\" value=\"".$from_user."\" checked=\"checked\">".$from_user."<br>";
    }

IN expects comma separated values:

$names=implode(",", $_POST['from_user']);    
$query1="SELECT * FROM tracks WHERE from_user IN (".$names."))";

!!!! BUT: Please, please, please use prepared statements because your code is wide open to SQL Injection: http://php.net/manual/de/mysqli.prepare.php

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
}

$result = null;
$names = implode(",", $_POST['from_user']);  

/* create a prepared statement */
if ($stmt = $mysqli->prepare("SELECT * FROM tracks WHERE from_user IN (?)")) {

  /* bind parameters for markers */
  $stmt->bind_param("s", $names);

  /* execute query */
  $stmt->execute();

  /* bind result variables */
  $stmt->bind_result($result);

  /* fetch value */
  $stmt->fetch();

  print_r($result);

  /* close statement */
  $stmt->close();
}

/* close connection */
$mysqli->close();

edited Aug 11 '12 at 11:40

answered Aug 11 '12 at 11:35

Besnik

6,469
1
31
33

Thanks, and thanks for info on prepared statements. I have added your code (i.e. removing the AND, adding the comma on the implode, and changing the query, but I am getting the following error for the implode: `[11-Aug-2012 11:44:22] PHP Warning: implode() [function.implode]: Invalid arguments passed in **** on line 136`. – Nick Aug 11 '12 at 11:48
It would seem that, inspite of this warning, $names is being set OK, but for some reason the page is breaking (i.e. I get a white screen) when I submit the form that runs the query with $names in – Nick Aug 11 '12 at 12:06
I have got this working now using `$names = "\"".implode('","', $_POST['from_user'])."\"";` and `$query1="SELECT * FROM tracks WHERE from_user IN ($names)";`. It would seem that I need to wrap each $_POST['from_user'] value in quotes because the items to be checked in my MySQL table are strings. I am still getting the PHP warning on the implode line - I am not sure why this is so, but I have it working. Should I mark your answer as the answer as it is, or do you want to make an adjustment based on the information in this comment? – Nick Aug 11 '12 at 13:53

Passing array of values from form to sql query WHERE IN clause

2 Answers2