Forums

SQL Question: How to use parameterized query with SQL "IN" operator?

Permalink
I like to protect myself from SQL injection by using parameterized queries, but when I try to do this:

return $db->GetAssoc('SELECT categoryID,category FROM btCategories WHERE categoryID IN (?)',array(join(',',$idArray)));


...I only get one row returned. I can get it to work if I do the following:

return $db->GetAssoc('SELECT categoryID,category FROM btCategories WHERE categoryID IN ('.join(',',$idArray).')');


...but this does not seem like a best practice approach. What's the right way to do this?

Thanks!

Alex
alexaalto
 
jelthure replied on at Permalink Reply
jelthure
It looks like you are trying to turn an array into a string and then back into an array. I'm assuming that you have a unknown number of items in your return $idArray so that you can't hard code in the correct amount of "?". I would do something like this: 

$str = '?';
$i = count($idArray);
while( $i > 1 ){ $str .= ',?'; $i--; }
$sql = "SELECT categoryID,category FROM btCategories WHERE categoryID IN ($str)";
$result  = $db->GetAssoc($sql,$idArray);
return  $result;


or something similar. (the while condition might need adjusting) I believe the issue is that you need the more then one "?" since you are passing in an array with more than one value. Sorry I don't know the proper names for things, but I hope you'll get the idea.