PDO bindParam breaking mySQL query

Question

I have a mySQL statement that goes like this:

SELECT *
FROM `events_table`
WHERE `event_category` NOT IN (:category_ids)

I use bindParam to bind the $category_ids to :category_ids.

category_id is a series of comma delineated numbers, e.g. 1, 2, 8

However, once executed the statement becomes

SELECT * 
FROM `table` 
WHERE `event_category` NOT IN ('1,2,8')

These quotes around the numbers break the NOT IN function.

Is there a way to use bindParam without these quotes being inserted? Is it secure to simply write

SELECT * 
FROM `table` 
WHERE `event_category` NOT IN ($category_ids)

I didn't think to look for IN (), I was so fixed on NOT IN(). Thanks. — ColonelPanik, Jan 30 '14 at 21:15

score 1 · Answer 1 · answered Jan 30 '14 at 20:59

1

you might use this

   $category_ids     = array(1, 2, 8);
   $inQuery = implode(',', array_fill(0, count($category_ids), '?'));

   $db = new PDO(...);
   $stmt = $db->prepare(
    'SELECT *
     FROM table
      WHERE `event_category` IN (' . $inQuery . ')'
   );

   // bindvalue is 1-indexed, so $k+1
   foreach ($category_ids as $k => $id)
    $stmt->bindValue(($k+1), $id);

   $stmt->execute();

answered Jan 30 '14 at 20:59

echo_Me

37,078
5
58
78

you got this from http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition? I was hoping I was missing a proper pdo way of doing this. – ColonelPanik Jan 30 '14 at 21:14

score 0 · Answer 2 · answered Jan 30 '14 at 20:52

0

try,

$sth->bindParam(':category_ids', $category_ids, PDO::PARAM_INT);

answered Jan 30 '14 at 20:52

Ant

118
3

As soon as there is a , in the list, bindParam seems to put quotes around it. For example, if there's just one number in the list, it translates as NOT IN (8). – ColonelPanik Jan 30 '14 at 21:01

PDO bindParam breaking mySQL query

2 Answers2