0

I am building a dynamic news application for a site. The news will be split up into categories which may then contain subcategories(which may contain subcategories) and news articles. I have created a recursive function to return all of the category IDs for all subcategories of a certain category. I would like to run a single query to grab all of the news posts whose category is in the set that was returned by the recursive function.

When I try to use this (I'M USING PEAR DB)

//$myReturnedIDs is a comma delimited list of ids. 5,6,7,8,10,12

$oPrep = $oConn->prepare("SELECT NewsID FROM SiteNews WHERE NewsCategory IN (?)");

$oRes = $oConn->execute($oPrep, array($myReturnedIDs));

The resulting query looks like this:

SELECT NewsID FROM SiteNews WHERE NewsCategory IN('5,6,7,8,10,12')

while I need it to look like:

SELECT NewsID FROM SiteNews WHERE NewsCategory IN(5,6,7,8,10,12)

Now, I realize I could populate that portion of the query in the prepare statement, but I feel that leads to some possible hole for an attack. Am I being over cautious, since the data is not user generated? Is there some way to make this work? My other thought was to add a ? for each ID, amount would be determined before the prepare statement (after getting all the IDs), but that feels clunky to me. Any suggestions?

Michael Wheeler
  • 670
  • 7
  • 11
  • possible duplicate of [PHP PDO: Can I bind an array to an IN() condition?](http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition) –  Aug 30 '12 at 00:17

1 Answers1

0

I'm going to guess that $myReturnedIDs is a comma-delimited string. So this:

array($myReturnedIDs)

yields a one-element array:

["5,6,7,8,10,12"]

If that's the case, then you want to use explode to make the array you want:

$oRes = $oConn->execute($oPrep, explode(",", $myReturnedIDs));

Hope that helps!

Xavier Holt
  • 14,471
  • 4
  • 43
  • 56
  • With PEAR execute, the number of parameters in the prepared statement must equal the count of the argument array. This results in a mismatch error. – Michael Wheeler Aug 30 '12 at 00:22
  • @MichaelWheeler - I think I misinterpreted `execute`'s arguments. Try using `array(explode(",", $myReturnedIDs))` instead. If that's works, I'll fix it in my answer, too. Cheers! – Xavier Holt Aug 30 '12 at 00:27
  • I still get the mismatch error. Im looking through http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition as it seems my problem is a duplicate of that. – Michael Wheeler Aug 30 '12 at 00:32
  • Dang. Would have been convenient... One other thought - if you trust the source of `$myReturnedIDs`, you can use `!` instead of `?` in [prepare](http://pear.php.net/manual/en/package.database.db.intro-execute.php) to substitute an argument it in without escaping it. But that's all I got. Good luck! – Xavier Holt Aug 30 '12 at 00:36
  • Thanks for the thoughts! Realistically, there should be no way of an ID coming up that would mess with any queries (All ID's are automatically generated when an admin creates a subcategory), so the data should be sanitary! – Michael Wheeler Aug 30 '12 at 02:25