Formatting a PHP array for an SQL “IN” clause

问题:

I'm attempting to query a database for records where the "product_id" is included in an array of products IDs.

The array is the post result of a multiple select input (<select>) and looks like:

$clients = 
  Array ( 
    [0] => 80000016-1302638679
    [1] => 8000003B-1329924004
  )

I would like to pass that array to the "IN" clause of an SQL statement such as:

$sql = "SELECT * FROM sales WHERE product_id IN (".$clients.")";

...but this doesn't work (Error: Message: Array to string conversion).

Several posts suggest using this function to format the array in a way suitable for SQL:

function format_array($array){
    return implode(', ', $array);
  }
}

Such as ...

$sql = "SELECT * FROM sales WHERE product_id IN (".format_array($clients).")";

That results in this query:

SELECT * FROM sales WHERE product_id IN (80000016-1302638679, 8000003B-132992400)

...and this error:

Unknown column '8000003B' in 'where clause'

What am I doing wrong? Any help is greatly appreciated! I can clarify the question if needed :)

回答1:

There is a better way

You mention in the comments that you are using CodeIgniter. Unless you are making something extraordinarily complicated, there is no practical reason you should be building your own home-baked queries when you have where_in built in.

And if that doesn't work, then there is good ol' fashioned escape.

---

OK, so, you have most people saying that you need to quote the items and are giving you this:

function createInClause($arr)
{
    return '\'' . implode( '\', \'', $arr ) . '\'';
}

but that really isn't sufficient if you have the possibility for questionable input (such as '); DROP TABLE STUDENTS; --. To protect against that, you need to make sure you check for SQL injection:

function createInClause($arr)
{
    $tmp = array();
    foreach($arr as $item)
    {
        // this line makes sure you don't risk a sql injection attack
        // $connection is your current connection
        $tmp[] = mysqli_escape_string($connection, $item);
    }
    return '\'' . implode( '\', \'', $tmp ) . '\'';
}

回答2:

Try this to put your ids in quotes:

function format_array($array){
    return "'" . implode("', '", $array) . "'";
  }
}

As cwallenpoole pointed out, you should escape the string if you haven't already. Not escaping a string is extremely dangerous, especially if you have a public application.

回答3:

You need to put 80000016-1302638679 in quotation marks. Otherwise it assumes you are doing subtraction.

return "'".implode("', '", $array)."'";

回答4:

Use this function

function format_array($array){
    return implode("','", $array);
  }
}

And this query

$sql = "SELECT * FROM sales WHERE product_id IN ('".$clients."')";

回答5:

You need to put those in quotation markes like

 SELECT * FROM sales WHERE product_id IN ('80000016-1302638679', '8000003B-132992400') 

回答6:

You need to wrap your items in quotes. Here's a quick fix:

$clientIds = "";
foreach ($clients as $client) {
  $clientIds .= ($clientIds == "" ? "" : ", ") . "'".mysqli_escape_string($client)."'";
}

$sql = "SELECT * FROM sales WHERE product_id IN (".$clientIds.")";

回答7:

Put the values under quote like this:

 SELECT * FROM sales WHERE product_id IN ('80000016-1302638679', '8000003B-132992400')