CODEWITHSUNDEEP
phpmysqlarrays
Jose Calderon
Jose Calderon

Reputation: 571

Selecting from database where id in given array

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.

Upvotes: 1

Views: 1202

Answers (2)

TML
TML

Reputation: 12966

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.

Upvotes: 1

Damon Skelhorn
Damon Skelhorn

Reputation: 1511

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)

Upvotes: 0

Related Questions