MySQL and PHP: how to fetch multiple ID's in an array containing the ID's

Question

I have multiple ID's stored in a variable. This array can become long (+400 ID's). How do I fetch the data from these ID's in MySQL (PDO)?
I could do it via this:

"select * from info WHERE `id` IN ('1,2,3,4,5')"

But that's unsafe (SQL injection) and maybe not performant. Any suggestions to handle this problem?

Is your question how to prevent mysql injection or is there a problem with the code? — The Codesee, Apr 15 '16 at 15:34
See the [answer here](https://www.reddit.com/r/PHP/comments/4dlcu6/i_want_to_set_up_a_form_that_will_send_its_input/d1srxmm?context=3) using `implode()`. — halfer, Apr 15 '16 at 15:38

score 4 · Accepted Answer · edited May 23 '17 at 10:33

4

$in = join(',', array_fill(0, count($ids), '?'));
$select = <<<SQL
    SELECT *
    FROM galleries
    WHERE id IN ($in);
SQL;
$statement = $pdo->prepare($select);
$statement->execute($ids);

source

edited May 23 '17 at 10:33

Community

1
1

answered Apr 15 '16 at 15:39

Boy

1,182
2
11
28

score 1 · Answer 2 · answered Apr 15 '16 at 15:37

1

take out single quote from the content '1,2,3,4,5' this checks the complete value under ID.

so use it without single quotes as the ID column is numeric, 1,2,3,4,5 this will separate each value.

answered Apr 15 '16 at 15:37

ameenulla0007

2,663
1
12
15

score 1 · Answer 3 · answered Apr 15 '16 at 15:39

I assume that id field is INTEGER so please remove the apostrophes and new query will be like,

select * from info WHERE `id` IN (1,2,3,4,5)

the other hand at PHP side for instance,

$idList = explode(',', $_GET['id_list']);
$idList = implode(',', array_unique(array_map('intval', $idList)));

$query = "select * from info WHERE `id` IN ($idList)";

and please check max allowed selector in IN

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_allowed_packet

Arjun J Gowda · Answer 4 · 2016-04-16T07:02:11.067

0

Use

"select * from info WHERE `id` IN (1,2,3,4,5)"

Including single quotes forces whole value to be considered as single string.

If you have value in array then just write it as

$query = "select * from info WHERE `id` IN (implode(',', array_map('intval', json_decode('[' . $ids . ']', true))))" ;

edited Apr 16 '16 at 07:02

answered Apr 15 '16 at 15:38

Arjun J Gowda

720
10
21

2

This looks like it would be vulnerable to SQL injection issues. – halfer Apr 15 '16 at 15:40
We usually sanitise input before sending to model or executing query. So it's not vulnerable to sql injection. – Arjun J Gowda Apr 15 '16 at 15:42
That's not sufficient for an answer, in my view - if it doesn't use parameter binding or explicit untainting, then it is vulnerable. Your readers don't have the external (unstated) sanitisation code that you do. – halfer Apr 15 '16 at 15:46
1

@halfer i have updated :-) – Arjun J Gowda Apr 16 '16 at 07:02

MySQL and PHP: how to fetch multiple ID's in an array containing the ID's

4 Answers4