Formatting a PHP array for an SQL "IN" clause [duplicate]

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 stackoverflow.com/questions/4311025/where-in-mysql-clause — Rakesh Sharma
– Rakesh Sharma, Commented Aug 6, 2013 at 5:13

cwallenpoole · Accepted Answer · 2013-08-08 02:35:11Z

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 8, 2013 at 2:35

answered Aug 6, 2013 at 5:27

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andrew Over a year ago

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.

cwallenpoole Over a year ago

@Andrew Sorry about that. Forgot about the signature change.

Andrew Over a year ago

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!

cwallenpoole Over a year ago

@Andrew CI also has a workaround for that. Use $this->db->escape.

Daniel Williams · Accepted Answer · 2013-08-06 05:54:46Z

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 6, 2013 at 5:54

answered Aug 6, 2013 at 5:12

Daniel Williams

8,9254 gold badges40 silver badges49 bronze badges

1 Comment

garrettmurray Over a year ago

See comments below, for security against injection you need to use mysqli_escape_string().

sashkello · Accepted Answer · 2013-08-06 05:12:33Z

2

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

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

answered Aug 6, 2013 at 5:12

sashkello

18k25 gold badges84 silver badges112 bronze badges

Comments

Garry · Accepted Answer · 2013-08-06 05:13:44Z

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 6, 2013 at 5:13

Garry

5974 silver badges20 bronze badges

3 Comments

garrettmurray Over a year ago

This would wrap the entire where clause in one set of quotes, which won't work.

Garry Over a year ago

@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')

garrettmurray Over a year ago

My fault, for some reason I missed that bit. =)

GautamD31 · Accepted Answer · 2013-08-06 05:12:22Z

1

You need to put those in quotation markes like

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

answered Aug 6, 2013 at 5:12

GautamD31

28.8k10 gold badges66 silver badges86 bronze badges

Comments

garrettmurray · Accepted Answer · 2013-08-06 05:32:12Z

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 6, 2013 at 5:32

answered Aug 6, 2013 at 5:14

garrettmurray

3,3681 gold badge28 silver badges24 bronze badges

Comments

William Buttlicker · Accepted Answer · 2013-08-06 05:13:01Z

0

Put the values under quote like this:

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

answered Aug 6, 2013 at 5:13

William Buttlicker

6,0005 gold badges36 silver badges59 bronze badges

Collectives™ on Stack Overflow

Formatting a PHP array for an SQL "IN" clause [duplicate]

7 Answers 7

There is a better way

4 Comments

1 Comment

Comments

3 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

There is a better way

4 Comments

1 Comment

Comments

3 Comments

Comments

Comments

Comments

Linked

Related