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.
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)