3

I'm implementing a bulk delete feature. The application uses PDO, but I haven't figured out a nice way to use prepared statements for this.

I have an array of ID's of rows to delete, any length:

array(3, 5, 8, [...])

With prepared statements I'd have to create a string of questions marks to use as a placeholder, and then bind the values, looking something like this:

$question_marks = array();
foreach($ids) $question_marks[] = '?';
$question_marks = join(', ', $question_marks);

$statement = $pdo->prepare('DELETE FROM `table` WHERE `id` IN ('.$question_marks.')');

for($i = 0; $i < count($ids); $i++) {
    $statement->bindValue($i + 1, $ids[$i]);
}

$statement->execute();

What I had in mind would be typecasting the ID's into integer, so that any SQL-injection would be removed, which is my question: would it?

$id_string = array();
foreach($ids as $id) $id_string[] = (int) $id;
$id_string = join(', ', $id_string);

$statement = $pdo->prepare('DELETE FROM `table` WHERE `id` IN ('.$id_string.')');

$statement->execute();

It's still a bit hacky, but in my opinion much nicer than the previous solution.

Is this safe? Are there any alternative solutions?

5
  • After casting to int it is safe, in any case. Second solution is best! :)
    – hek2mgl
    Mar 25, 2013 at 16:44
  • 3
    $idString = implode(',', array_map(intval, $ids)); will be enough here. ) But sometimes you want to capture these invalid params (to punish the ones who try to attack your site), and in this case you'll lose some info when doing only conversion without checking its result.
    – raina77ow
    Mar 25, 2013 at 16:47
  • 5
    I would also be concerned about non-sql-injection attacks, such as deleting a record that doesn't belong to the user. I would add a join to your sql query to limit the query to records that belong to the user.
    – Teddy
    Mar 25, 2013 at 16:48
  • Yes, it is totally safe. It's not hacky also. Just to inform you, the hacky way is $string[] = 0+$id. Happy coding
    – user2193789
    Mar 25, 2013 at 17:31
  • @Teddy Great point! In this application there are no users, however, so it's not an issue. Mar 25, 2013 at 17:47

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.