current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

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++;
}
share|improve this question
    
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
    
wasn't that part of the benefit of using PDO? – user1459766 Aug 23 '12 at 21:59
    
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
1  
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
up vote 0 down vote accepted

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++;
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.