DELETE IN with explode()

Question

I have a form that submits an array of transaction IDs to $_POST['transid'] so those transaction records can be deleted.

I typically use mysqli_real_escape_string to help prevent attacks, but I am not sure how to go about it with an array. The following is my query:

$query = 'DELETE FROM TRANSACTIONS WHERE (transid) IN ("'.implode('","',$_POST[transid]).'")'

...which gives me something like this:

$query = 'DELETE FROM TRANSACTIONS WHERE (transid) IN ("123","124","138","145")'

This seems to be asking for trouble. How can I protect myself from disaster (malicious or otherwise)? Is there an efficient way to sanitize the array? Or should I go about this another way?

Any thoughts or guidance would be appreciated.

@Mihai: It's a little tricky using prepared statements with a variable number of values. Also, MySQLi also supports prepared statements. — gen_Eric, Sep 13 '13 at 17:29
@RocketHazmat One does not exclude the other..But I`m curious,how would you use variable number of parameters for prepared statements? — Mihai, Sep 13 '13 at 17:33
If you don't want to use prepared statements, you could also filter not integer values by using `array_filter($_POST['transid'], 'ctype_digit')` — Jan.J, Sep 13 '13 at 17:37
Other way you can take a look at this answer http://stackoverflow.com/a/10722827/937367 — Jan.J, Sep 13 '13 at 17:38
@Mihai: It was your suggestion. Anyway, you'd have to count the number of elements and add that many `?`s, then probably use `call_user_func_array()` to call the `bind_param` function. — gen_Eric, Sep 13 '13 at 17:58

score 3 · Accepted Answer · answered Sep 13 '13 at 17:43

3

You're probably better off sanitizing the $_POST before you use it to implode and for that you'll have to traverse it. @user870018 beat me to the punch on the structure but here's what I'd do anyway:

function sanitize($n)
{
    return your_escape_function_here($n);
}

$values = implode(",", array_map("sanitize", $_POST[transid]));
$query = 'DELETE FROM TRANSACTIONS WHERE (transid) IN ('.$values.')';

answered Sep 13 '13 at 17:43

Stephen O'Flynn

2,309
23
34

Thank you for your feedback. This is pretty much the direction that I was thinking I'd need to take this, but validation from other people always helps, as well as an example to work off of in my testing. – mrphuzz Sep 14 '13 at 00:24

score 2 · Answer 2 · answered Sep 13 '13 at 17:33

Use a foreach loop before building your query;

foreach ($_POST[transid] as &$x) $x = your_escape_function_here($x);

Or (if you use arrays in this manner regularly) build it into a function to keep the overall program a bit cleaner;

function sqlEscapeArray($arr){
foreach ($arr as &$x)
$x = your_escape_function_here($x);
return $arr;
}

Then use it like so;

$query = 'DELETE FROM TRANSACTIONS WHERE (transid) IN ("'.implode('","',sqlEscapeArray($_POST[transid])).'")';

Of course, replace your_escape_function with, well.... Your escape function.

DELETE IN with explode()

2 Answers2