Formatting a PHP array for an SQL "IN" clause

Question

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 :)

try http://stackoverflow.com/questions/4311025/where-in-mysql-clause — Rakesh Sharma, Aug 06 '13 at 05:13

Daniel Williams · Answer 1 · 2013-08-06T05:54:46.037

7

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.

edited Aug 06 '13 at 05:54

answered Aug 06 '13 at 05:12

Daniel Williams

8,673
4
36
47

See comments below, for security against injection you need to use mysqli_escape_string(). – garrettmurray Aug 06 '13 at 05:33

cwallenpoole · Accepted Answer · 2013-08-08T02:35:11.630

7

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 ) . '\'';
}

edited Aug 08 '13 at 02:35

answered Aug 06 '13 at 05:27

cwallenpoole

79,954
26
128
166

Hi, thank you for your thorough answer! When I use that function, however, I receive the following error: "mysqli_escape_string() expects exactly 2 parameters" I'm using CodeIgniter, if it makes a difference. – Andrew Aug 06 '13 at 06:34
@Andrew Sorry about that. Forgot about the signature change. – cwallenpoole Aug 06 '13 at 14:33
Unfortunately CodeIgniter's `where_in` won't work for me since the query is rather complicated (sales reporting involving many cases based on user inputs). Would you know if the `connection` variable for the `mysqli_escape_string` function is already present somewhere in CodeIgniter? I would rather not redefine the database connection, since I know CodeIgniter already handles that (although it might not readily have it available as a variable). I hope that makes sense! Thank you again for your help, I really appreciate the great answer you provided! – Andrew Aug 08 '13 at 01:20
@Andrew CI also has a workaround for that. Use `$this->db->escape`. – cwallenpoole Aug 08 '13 at 02:35

score 2 · Answer 3 · answered Aug 06 '13 at 05:12

2

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

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

answered Aug 06 '13 at 05:12

sashkello

17,306
24
81
109

score 2 · Answer 4 · answered Aug 06 '13 at 05:13

2

Use this function

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

And this query

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

answered Aug 06 '13 at 05:13

Garry

595
4
19

This would wrap the entire where clause in one set of quotes, which won't work. – garrettmurray Aug 06 '13 at 05:22
@garrettmurray Check the implode function also if there are two items in $array=array(456456,4569845934); format array will return 456456','4569845934 And then $sql will be SELECT * FROM sales WHERE product_id IN ('456456','4569845934') – Garry Aug 06 '13 at 05:29
My fault, for some reason I missed that bit. =) – garrettmurray Aug 06 '13 at 05:30

score 1 · Answer 5 · answered Aug 06 '13 at 05:12

1

You need to put those in quotation markes like

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

answered Aug 06 '13 at 05:12

GautamD31

28,552
10
64
85

garrettmurray · Answer 6 · 2013-08-06T05:32:12.033

1

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.")";

edited Aug 06 '13 at 05:32

answered Aug 06 '13 at 05:14

garrettmurray

3,338
1
25
23

score 0 · Answer 7 · answered Aug 06 '13 at 05:13

0

Put the values under quote like this:

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

answered Aug 06 '13 at 05:13

Engineer

5,911
4
31
58

Formatting a PHP array for an SQL "IN" clause

7 Answers7

There is a better way

Linked