从数据库中选择给定数组中的id

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.

Answer 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.