Preparing MySQL statements that involve arrays and other parameters

Question

I'm updating several php files which contain vulnerable mysql queries. My task of course, to incorporate prepared statements and parameter binding. I'm having trouble finding the best way to convert the following type of query to a prepared statement however.

'UPDATE `client_media` SET folder ="' . $folder_name . '" WHERE id IN ('.$media_ids.') LIMIT ' . $assetCount;

This would be so much easier if it was only the array to be bound, but i'm having a tough time because of the limit condition which comes after the array, and causes an issue when applied after the unpacked parameter.

$stmt = $db->prepare($sql);
$types = 's' . str_repeat('i', count($media_ids)) . 'i';
$stmt->bind_param($types, $folder, ...$media_ids, $assetCount);
$stmt->execute();

Any hints or ideas?

Edit: @Progman Thanks but that question assumes that the only parameter is the array. I'm talking about preparing multiple parameters one (or many) before and after the array.

Does this answer your question? [A prepared statement, \`WHERE .. IN(..)\` query and sorting — with MySQL](https://stackoverflow.com/questions/3703180/a-prepared-statement-where-in-query-and-sorting-with-mysql) — Progman, Oct 23 '21 at 22:01
Don't assume that a query is vulnerable just because it's not using bound variables. Consider where the data is coming from. `$media_ids` looks like it's output from a different query, so consider whether a JOIN or a sub-query is appropriate here. Where does `$assetCount` come from? If there's a line in your code that sets it, you can trust the data and you could just concatenate it before you prepare the query. — Tangentially Perpendicular, Oct 24 '21 at 00:42
@TangentiallyPerpendicular Very good point, but unfortunately these values are provided over a request. Unfortunately I was only handed these files that contain the queries so I cannot see what's happening on say the calling side of things. For now I ended up just looping through the array of media_ids and querying each item and collecting errors. Probably not the best method. — FevtheDev, Oct 24 '21 at 11:56
@Progman Thanks but that question assumes that the only parameter is the array. I'm talking about preparing multiple parameters one (or many) before and after the array. — FevtheDev, Oct 24 '21 at 12:01

score 0 · Answer 1 · answered Oct 23 '21 at 22:29

You have two options.

Append the value to an array before bind_param():

$stmt = $db->prepare($sql);
$media_id[] = $assetCount;
$types = 's' . str_repeat('i', count($media_ids));
$stmt->bind_param($types, $folder, ...$media_ids);
$stmt->execute();

Create an array and then splat it again:

$stmt = $db->prepare($sql);
$types = 's' . str_repeat('i', count($media_ids)) . 'i';
$stmt->bind_param($types, ...[$folder, ...$media_ids, $assetCount]);
$stmt->execute();

Preparing MySQL statements that involve arrays and other parameters

1 Answers1