In Clause in mysql with sql injection

Question

I have written an mysql update query in PHP with preventing sql injections as:

UPDATE table1 status = 1 WHERE id IN ( ? ) and active = ?, array(implode(',' $ids), 1)

where id field with integer data type.

I am getting problem in executing this problem as implode generate a comma separated string and ids are not assigned with IN clause as it becomes:

array(implode(',' $ids) == array(1,2,3, 1)

first three are id's and fourth value in array is active field value but statement consider first two values from array (i.e. 1,2) to replace '?' in query

Note: there is no problem in the query as if same query without using preventing sql injection as

UPDATE table1 SET status = 1 WHERE id IN ( 1,2,3 ) and active = 1

is working fine. Only issue with the PHP implode function.

Answer 1

You can prevent the sql injection when specifically cast all values in the array to int. Then it will be safe.

$intCastedIds = array_map('intval', $ids);

In case you have injecting code in your $ids array the intval will cast all values to int. So it's not possible to inject in this particular case.

So instead of:

UPDATE table1 status = 1 WHERE id IN ( ? ) and active = ?, array(implode(',' $ids), 1)

do something like:

'UPDATE table1 status = 1 WHERE id IN (' . implode(',', $intCastedIds) . ') and active = ?', 1)

Answer 2

Use Prepared Statements!

Check out this article that details how you can still use prepared statements with a dynamic amount of variables..

http://www.pontikis.net/blog/dynamically-bind_param-array-mysqli