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
– Marc B, Commented Aug 23, 2012 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
– Marc B, Commented Aug 23, 2012 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
– user1459766, Commented Aug 23, 2012 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
– Marc B, Commented Aug 24, 2012 at 13:56

user1459766 · Accepted Answer · 2012-08-28 00:24:02Z

0

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++;
}

answered Aug 28, 2012 at 0:24

user1459766

1182 gold badges3 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

PHP PDO: SELECT array within IN()

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related