MySQL prepared statement with bulk insert

Question

I have a module which allows admins to add some users (maybe multiple) to a group.

PHP

$user_id  = $_POST["user_id"]; //this can be an array
$group_id = $_POST["group_id"];

$sql = "INSERT IGNORE INTO users_groups (user_id, group_id) 
        VALUES ($user_id[0],$group_id)";

for ( $i = 1; $i < count($user_id); $i++) 
{
    $sql .= ", ($user_id[$i],$group_id)"
}

As you can see the sql-query depends on how much users the admin has selected.

How can I use this code with prepared statements because the post-variables come from a user (SQL injections...)

UPDATE

I have two selectboxes:

• one for the user selection (multiple selection is possible -> array): $_POST["user_id"]
• one for the group selection (only one selection is possible): $_POST["group_id"]

And now I want a prepared SQL statement for inserting user_id and group_id to the many-to-many-table (users_groups). The problem is that the number of values which have to be inserted can change (depending how much users the admin has selected in the selectbox).

I want to change the prepared query depending on how much users the admin has selected.

For example:

• the admin selected two users -> sql: INSERT IGNORE INTO users_groups (user_id, group_id) VALUES ($user_id[0],$group_id), ($user_id[1],$group_id)
• the admin selected four users -> sql: INSERT IGNORE INTO users_groups (user_id, group_id) VALUES ($user_id[0],$group_id), ($user_id[1],$group_id), ($user_id[2],$group_id), ($user_id[3],$group_id)

My question: How can I do this automaticaly and with prepared sql statements because I dont want to have like 10 times if(count($user_id) == number) {...?

UPDATE 2 If I would do this manually the code would look like this:

$sql = $db->prepare("INSERT IGNORE INTO users_groups (user_id, group_id) VALUES (?, ?)"); 
$sql->bind_param('ii', $user_id[0], $group_id);

UPDATE 3 To check whether there are only integers in the post variables:

$user_id = filter_input_array(INPUT_POST, 'user_id', FILTER_SANITIZE_NUMBER_INT);
$user_id = abs($user_id);

$group_id = filter_input(INPUT_POST, 'group_id', FILTER_SANITIZE_NUMBER_INT);
$group_id = abs($group_id);

Answer 1

To create the SQL statement, you can use this to generate as many placeholders as you will need for all of your user_id values.

$user_ids  = $_POST["user_id"]; //this can be an array
$group_id = $_POST["group_id"];
$placeholders = implode(', ', array_fill(0, count($user_ids), "(?, ?)"));    
$sql = "INSERT IGNORE INTO users_groups (user_id, group_id) VALUES $placeholders";

Then to bind the values, if you are using pdo, something like this should work:

$stmt = $pdo->prepare($sql);
$i = 1;
foreach ($user_ids as $user_id) {
    $stmt->bindValue($i++, $user_id);
    $stmt->bindValue($i++, $group_id);
}

Or if you are using mysqli, you could try this approach using reflection (taken from a user note in the php docs here) the note does state that this needs PHP 5.3+, which hopefully you do have.

$stmt = $mysqli->prepare($sql);
$values[] = str_repeat('ii', count($user_ids));
foreach ($user_ids as $user_id) {
    $values[] = $user_id;
    $values[] = $group_id;
}
$ref = new ReflectionClass('mysqli_stmt');
$method = $ref->getMethod("bind_param");
$method->invokeArgs($stmt, $values);
$stmt->execute(); 

Or, because I'm stubborn and the thing from the php docs user note doesn't seem to work, which after reading more I don't see how it could work, considering the php doc for ReflectionMethod::invokeArgs specifically says

Note: If the function has arguments that need to be references, then they must be references in the passed argument list.

then using call_user_func_array could possibly work.

$stmt = $mysqli->prepare($sql);
$type = str_repeat('ii', count($user_ids));
foreach ($user_ids as $user_id) {
    $values[] = $user_id;
    $values[] = $group_id;
}
foreach ($values as $key => $value) {
    $value_references[$key] = &$values[$key];
}
call_user_func_array('mysqli_stmt_bind_param', 
    array_merge(array($stmt, $type), $value_references));

But what a pain. I really like pdo.