Selecting from database where id in given array

Question

I'm trying to select elements from a table in a mysql database where the id of a row is in the given array.

This returns values:

<?php
     $ids = '1,2,3,4';
     $DBH = ....
     $getID = $DBH->prepare("SELECT * FROM t1 WHERE id IN ($ids)");
     $getID->execute();
?>

This returns nothing:

<?php
     $ids = '1,2,3,4';
     $DBH = ....
     $getID = $DBH->prepare("SELECT * FROM t1 WHERE id IN (:ids)");
     $getID->execute(array(':ids'=>$ids));
?>

I can't understand what is wrong with that code.

what is :ids?? in your second query........will it give 1,2,3,4 output when you are executing your query — Venkata Krishna
– Venkata Krishna, Commented Feb 13, 2013 at 14:06
try changing $ids from string to array like this $ids = array('1','2','3','4') — Ateszki
– Ateszki, Commented Feb 13, 2013 at 14:08
Your first code should be working. To ask an obvious question, does table t1 contain a column canned 'id'? Also, not that it affects it, but $ids is actually a string not an array. — KevInSol
– KevInSol, Commented Feb 13, 2013 at 14:11
but it works as a string in the first example. Whats the diff between the two? — Jose Calderon
– Jose Calderon, Commented Feb 13, 2013 at 14:12

TML · Accepted Answer · 2013-02-13 14:21:40Z

1

In the first one, you're using PHP to do string interpolation before talking to the database; in effect, using PHP variables to generate SQL code. This is where SQL injection comes from - the database doesn't know the difference between data and code, so it can't protect you from "data" leaking into the "code" space. In the second, you are using bound parameters, telling the database "Please deal with :ids as a SINGLE VALUE, whose contents I will tell you later." An easy way to solve the disconnect is something like:

$sql = 'SELECT * from t1 where id in (' . str_repeat('?', count($ids)) . ')';
$stmt = $pdo->prepare($sql);
$stmt->execute($ids);

Check out this tutorial for more on these points.

answered Feb 13, 2013 at 14:21

TML

13k3 gold badges42 silver badges47 bronze badges

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

Comments

Damon Skelhorn · Accepted Answer · 2013-02-13 14:14:29Z

0

Assuming you are using PDO, try the following.

<?php
     $ids = '1,2,3,4';
     $DBH = ....
     $getID = $DBH->prepare("SELECT * FROM t1 WHERE id IN (:ids)");
     $getID->bindParam(":ids", $ids, PDO::PARAM_INT);
     $getID->execute();
?>

When your original query is executed, PDO will escape your input so your query will look like

SELECT * FROM t1 WHERE id IN ("1,2,3,4")

When you tell PDO to bind as a integer, it will execute

SELECT * FROM t1 WHERE id IN (1,2,3,4)

answered Feb 13, 2013 at 14:14

Damon Skelhorn

1,51112 silver badges18 bronze badges

2 Comments

Jose Calderon Over a year ago

Is there any way to print the PDO object with the params bound?

TML Over a year ago

One of the points of prepared statements in this use-case is that there's never an SQL that contains the values in-line. 'select * from foo where bar = :baz" is what goes to the database engine; this is allocated on the database server as a statement; later, that statement is recalled, and the value provided at that point. Since the database doesn't know what the "data" is when the "code" is being sent, it prevents the latter from somehow being treated as the former (a problem commonly known as "SQL injection").

Collectives™ on Stack Overflow

Selecting from database where id in given array

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related