PHP PDO: SELECT array within IN()

Question

I am a little late to the game and am trying to transition to PDO from mysql_* while trying to tackle a current challenge. I have an interface where I capture box number values within an array and that array is stored in another array by line item (for clarity purposes these are nested arrays).

My main purpose is to take the box numbers for a particular line item and run a mysql select query to return the number of units in that given set of boxes. If the qty in the boxes is not the quantity the user thinks there are I want it to throw an error.

Currently my challenge is I'm getting an empty result set. I believe this to be due to my array of box numbers not being properly passed to the PDO select statement. Any thoughts or guidance would be much appreciated.

Here is what I have so far:

$Boxes = $_POST['Boxes']; //this includes box numbers within an array for each line item of a form

$e = 0;

while($e<$num1){
$units = 0;
$r = 0;
$SO_Line_Item=mysql_result($result1,$e,"SO_Line_Item");

    foreach ($Boxes[$e] as $a => $b)  // the purpose of this loop is to take the values from Boxes and store it in $zzz which I hope to use in my Select statement below.
    {
    $zzz[] = $Boxes[$e][$r];
    $r++; 
    }
   //end inner foreach

$BNs= implode(',', $zzz);

$db = new PDO('mysql:host=XXXXXX ;dbname=XXXXXX', $dbuser,$dbpass);
$stmt = $db->prepare("SELECT Box_Num,Timestamp,SN,Assy_Status FROM Current_Box WHERE Box_Num IN(' . $BNs . ')");
$stmt->execute($zzz);   

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($results); // this shows up as an empty array

}  
$e++;
}

You're vulnerable to SQL injection attacks by directly using $_POST data in your query. You **MUST** escape the individual items you're fetching from $_POST before inserting them into the query string. — Marc B, Aug 23 '12 at 21:47
just using pdo doesn't make you safe, just as driving a car with a seat belt doesn't make you safe unless you actually put on the seatbelt. PDO allows for prepared statements/placeholders, but you're not actually using them, so your PDO is actually just as "unsafe" as the old mysql_*() functions. That being said, placeholders fall on their face when using arbitrary `IN(...)` clauses,and you do in fact haev to do some dynamic stuff to convince PDO to work with this. — Marc B, Aug 23 '12 at 22:09
I see what you are saying... aside from the escaping if I changed $BNs to $BNs= implode(',', array_fill(0,count($zzz), '?')); would this not create my placeholders in my SELECT IN(...) statement? Then could I loop through each instance of $zzz to get the desired result? Gave it a go and appear to be missing something. I would think this should move more in the prepared statement direction, creating some safety while providing the desired result. — user1459766, Aug 23 '12 at 23:43
`count() - 1`, actually, if you're 0-basing. but yeah, that's the basic idea. placeholders have to have a 1:1 match with their associated values, so you generate a `?,?,?,...` series and bind each value independently. it's still a 'home brew' query, but all you're inserting are `?` and `,`, so the injection potential is 0. — Marc B, Aug 24 '12 at 13:56

score 0 · Accepted Answer · answered Aug 28 '12 at 00:24

This got it done. Thanks to Marc B for his thoughts:

$e = 0;

while($e<$num1){
$units = 0;
$r = 0;
$SO_Line_Item=mysql_result($result1,$e,"SO_Line_Item");

    foreach ($Boxes[$e] as $a => $b)  
    {

        $zzz[] = $Boxes[$e][$r];

$ce = count($Boxes[$e]);        

    $r++; 
    }
//end inner foreach

$products = implode(',', array_fill(0,$ce, '?'));

$db = new PDO('mysql:host=192.168.1.197 ;dbname=Tracking', $dbuser,$dbpass);
$stmt = $db->prepare("SELECT Box_Num,Timestamp,E3_SN,Assy_Status FROM Current_Box WHERE Box_Num IN( $products )");
$stmt->execute($zzz);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
unset($zzz);
$e++;
}

PHP PDO: SELECT array within IN()

1 Answers1

Linked