0

I am just wondering, is there any way to select data that is NOT IN array using PDO bindValue. My main purpose is to prevent SQL injection.

My query goes something like this:

$array_name = array('George', 'Bob', 'Stephanie', 'Erica');

$query = $PDO -> prepare("SELECT id FROM table WHERE name NOT IN (:array_name)")
$query->bindValue(':array_name', implode(",",$array_name), PDO::PARAM_STR);

I've tried the code above but it is not working.

Adrian Cid Almaguer
  • 7,815
  • 13
  • 41
  • 63
Bono
  • 513
  • 2
  • 7
  • 10
  • can you write error message or log ? – Anri Mar 28 '14 at 16:37
  • It is not showing any error. But it is not returning any data either. – Bono Mar 28 '14 at 16:38
  • My fault, it was returning something, It seems the query that was returned was correct. However, if It try something like this SELECT id FROM table WHERE name NOT IN (".implode(",",$array_name).") it will return the correct data that IS NOT IN ARRAY. But whenever I try the code above it is returning data that IS IN ARRAY. – Bono Mar 28 '14 at 16:43
  • check this topic: http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition – S.Visser Mar 28 '14 at 16:57

1 Answers1

1

PDO doesn't support binding an array like that, but what you can do is dynamically create the placeholders in the string:

$placeholderStr = str_repeat('?, ', count($array_name)-1) . '?';
$query = $PDO -> prepare("SELECT id FROM table WHERE name NOT IN ($placeholderStr)");
$query->execute($array_name);

Alternatively you could use a library that has this functionality built in, such as Doctrine2 DBAL.

MrCode
  • 63,975
  • 10
  • 90
  • 112
  • IS this safe for SQL Injection? – Bono Mar 28 '14 at 16:45
  • Yes it is, you get all the benefits of a prepared statement, just like if you hard coded the placeholders. The content of the array is not going into the SQL itself, only the placeholders. – MrCode Mar 28 '14 at 16:46
  • I prefer to use `join(',', array_fill(1, count($array_name), '?'))` because there's less chance of off-by-one errors, and you don't have to handle the last element differently. – Amal Murali Mar 28 '14 at 17:13