How to manage empty IN sql query?

Question

$ids = array(1,2,3);
$in = implode(',',$ids);

$query = "SELECT * FROM user where user_id IN ($in) ";

Query works no problem. But when $ids is empty array $ids = array();

I got sql query error rightly so because SELECT * FROM user where user_id IN () is not a valid query .

How can I avoid such situation without checking for empty array i.e making query run no matter what ?

Why not just check for empty array? MySQL just awaits some parameters after IN, so I guess there is no way — Konstantin Pereiaslov, May 07 '12 at 11:35
I hope `$ids` are not user-provided and if, you escape them properly, before inserting them into your query. — knittl, May 07 '12 at 11:38
I have a situation where I cannot check for empty array . I had to make query run without error . All I can do is change input parameter . Query runs return result zero is ok for me . — Mr Coder, May 07 '12 at 12:11
Possible duplicate of [Empty IN clause parameter list in MySQL](https://stackoverflow.com/questions/13210233/empty-in-clause-parameter-list-in-mysql) — Vadzim, Sep 24 '19 at 06:37

Faizan Ali · Answer 1 · 2012-05-07T12:16:24.677

8

you should not run the query when the $in is empty. I think what yoou need is something like this

$in = implode(',',$ids);
if($in) {
     $query = "SELECT * FROM user where user_id IN ($in) ";
 }
 else {
      //alternate scenario here
 }

Edit

$in = implode("','",$ids); // generate like 1','2
$query = "SELECT * FROM user where user_id IN ('$in') "; //  if has  1','2 surrond it with quote make it IN('1','2') and if empty than IN('')

edited May 07 '12 at 12:16

answered May 07 '12 at 11:35

Faizan Ali

509
12
35

1

How do you know that the query shouldn't be executed without a condition if `$ids` is empty? Also, SQL injection? – Jon May 07 '12 at 11:38
because if $in is empty the sql will throw an exception as explained in the question – Faizan Ali May 07 '12 at 11:41
1

I didn't ask why it *will not* be executed, but why it *should not* be executed. How do you know the OP doesn't want to select all users in that case? – Jon May 07 '12 at 11:42
I have a situation where I cannot check for empty array . I had to make query run without error . All I can do is change input parameter . Query runs return result zero is ok for me – Mr Coder May 07 '12 at 12:11
Falcon has posted the answer below which is the best solution for this situtation – Faizan Ali May 07 '12 at 12:14

score 7 · Accepted Answer · answered May 07 '12 at 11:40

7

Best way to manage this is:

$in = implode("','",$ids); // generate like 1','2
$query = "SELECT * FROM user where user_id IN ('$in') "; //  if has  1','2 surrond it with quote make it IN('1','2') and if empty than IN('')

This saves you from if/else structure and everything else

answered May 07 '12 at 11:40

Falcon

259
3
8

2

In some cases, the column could content an empty value, and this solution will return all empty values. This could happen in a foreign key column, for instance. – NLemay Mar 25 '15 at 18:03
2

If user_id is unique identifier, it throws Conversion failed error. – Gqqnbig Nov 06 '15 at 22:44
While this is obviously a clever solution, it's confusing and not really intuitive. You have to understand a lot to read this. The anser of @faizan-ali is better in that regard. – pi. Sep 26 '16 at 10:42

score 5 · Answer 3 · answered May 07 '12 at 11:34

5

you can add 0 ahead of $ids string. After all No ids will start from 0 in any database.

Or you can do like this..

$sql = "SELECT * FROM user where ".($ids != ''? "user_id IN ($ids)" : " 1=1");

answered May 07 '12 at 11:34

Rukmi Patel

2,619
9
29
41

score 3 · Answer 4 · answered Oct 21 '16 at 11:20

3

I usually always add an "invalid" value, so the list is never empty:

$in = implode(',', array_merge($ids, [-1])); // never empty!

No additional conditional statements are required, keeping the code relatively clean. The "invalid" value must be outside the range of allowed values, of course. (The example above assumes that valid $ids are always positive.)

answered Oct 21 '16 at 11:20

biberesser

39
2

1

Honestly, this is the best solution. – Davor Jan 04 '17 at 18:31

score 0 · Answer 5 · answered May 07 '12 at 11:40

0

if((count($in)>0)
{
  //execute sql query code
}

answered May 07 '12 at 11:40

Ovais Khatri

3,201
16
14

score -1 · Answer 6 · answered May 07 '12 at 11:35

-1

for the given query you should not run it at all.

if ($ids) {
  // run your code
} else {
  return false; //or whatever.
}

for the other cases it could be conditional concatenation as shown in the other answers

answered May 07 '12 at 11:35

Your Common Sense

156,878
40
214
345

score -1 · Answer 7 · answered May 07 '12 at 14:40

I do several different types of checking in different situations to make sure that the sql statement is valid. In the most basic type of checking, I make sure that the variable is not empty or false

$in = implode(',',$ids);
if(!$ids) $in="0";
$query = "SELECT * FROM user WHERE user_id IN ($ids)";

Typically, I use a whole series of db_* functions which validate the input I pass into queries so that I can handle more advanced uses check where the $ids array comes from

Here is some test code that works well for me in dozens of situations.

function db_number_list($lst)
{
    if(!$lst) 
        return "0"; // if there are no passed in values , return a 0 
    if (!is_array($lst)) 
        $lst = explode (",",$lst);  //if a lst was passed in,  create an array
    foreach ($lst as $k=>$val)
    {
        if(!is_numeric(trim($val)))
            unset($lst[$k]);//remove non-numeric values;
    }
    if(!count($lst)) 
        return "0"; //if nothing is in the array after removing non-numeric values,  return 0
    return implode (",",$lst);
} 

$ids=array();
$query = "SELECT * FROM user WHERE user_id IN (".db_number_list($ids).")";
echo "ids:'".print_r($ids,true)."'<br>$query<hr>";

$ids="1,2,45,6,";
$query = "SELECT * FROM user WHERE user_id IN (".db_number_list($ids).")";
echo "ids:'".print_r($ids,true)."'<br>$query<hr>";

$ids=array(3,6,1,"drop table x", 4);
$query = "SELECT * FROM user WHERE user_id IN (".db_number_list($ids).")";
echo "ids:'".print_r($ids,true)."'<br>$query<hr>";

Output:

ids:'Array ( ) '
SELECT * FROM user WHERE user_id IN (0)

ids:'1,2,45,6,'
SELECT * FROM user WHERE user_id IN (1,2,45,6)

ids:'Array ( [0] => 3 [1] => 6 [2] => 1 [3] => drop table x [4] => 4 ) '
SELECT * FROM user WHERE user_id IN (3,6,1,4)

score -2 · Answer 8 · answered May 07 '12 at 11:35

-2

You should set a conditional concatenation:

if (count($ids) > 0)
{
    $in = implode(',',$ids);
    $query = "SELECT * FROM user WHERE user_id IN ($in)";
    // ....
}

EDIT: fixed my misunderstanding.

answered May 07 '12 at 11:35

Nadir Sampaoli

5,437
4
22
32

How to manage empty IN sql query?

8 Answers8

Linked