Binding a PHP array for SQL insert

Question

Trying to bind an array (first array binding) to prevent SQL injection

This is valid code:

;

<code>if (isset($_POST['checkbox_selected']))
{
    $valuesArr = array();
    foreach ($_POST['checkbox_selected'] as $key => $value) {
        //Retrieve the Array ID to find the row number of the CSVOption column
        $findrow = array_search_partial($attributeid, $value);
        //When the form is submitted, the attribute value is assigned to the attribute id
        $attribute = $value;
        $csv = $csvcolumn[$findrow];
        $valuesArr[] = "('$userid', '$feed_id', '$attribute', '$csv')";
    }
        
    $sql = "INSERT INTO map (user_id, feed_id, attribute_id, csvcolumn) values ​​";
    $sql .= implode(',', $valuesArr);
    mysqli_query($conn,$sql);
}
</code>

I can't bind the array, tried:

$sql = "INSERT INTO map (user_id, feed_id, attribute_id, csvcolumn) VALUES (?, ?, ? ,?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('iiii', implode(',', $valuesArr));
$stmt->execute();

echo implode(',', $valuesArr)
//('1', '1', '13', '9') //This is the array inserted into SQL
//(user_id, feed_id, attribute_id, csvcolumn) //Here is the value assigned in the first statement

Answer 1

You have two problems:

You are not using the correct bind syntax.

You are trying to insert multiple rows in a prepared statement

if (isset($_POST['checkbox_selected']))
{
    $sql = "INSERT INTO map (user_id, feed_id, attribute_id, csvcolumn) VALUES (?, ?, ?, ?);";
    // prepare only has to happen once
    $stmt = mysqli_prepare($conn,$sql);

    mysqli_begin_transaction($conn);
    try {
        foreach ($_POST['checkbox_selected'] as $key => $value) {
            $findrow = array_search_partial($attributeid, $value);
            $attribute = $value;            
            $csv = $csvcolumn[$findrow];
            
            $stmt->bindParam('iiii', $userid, $feed_id, $attribute, $csv);
            $stmt->execute();
        }
        mysqli_commit($conn);
    } catch(mysqli_sql_exception $e) {
        mysqli_rollback($conn); // immediately roll back changes
        throw $e; // re-throw exception
    }
}

The only slight benefit is that when you try to wrap multiple VALUES() in a query, it will be wrapped by an implicit transaction. But other aspects of this approach are disadvantageous. Explicitly opening the transaction that wraps the binding/execution loop, you can get the same benefits [error rollback, IO batching], while also taking advantage of the benefits of prepared statements [single simple query parsing, parameterization, etc.]