Binding an array in MySQLi prepared Insert statement PHP

Question

I tried multiple ways to create a function to bind dynamic array values into the MySQLi prepared statements. But I am getting error 'Uncaught mysqli_sql_exception: No data supplied for parameters in prepared statement'

Here is my code:

if (count($fields) == count($values)) {
    $fielddata = implode(", ", $fields);
    $questions = rtrim(str_repeat("?, ", count($values)), ", ");
    $typedata = implode("", $type);

    foreach ($values as $index => $current_val){ // build type string and parameters
        $value .= '$values['.$index.'],';
    }
    $value = rtrim($value,',');

    $statement = "INSERT INTO ".$table." (".$fielddata.") VALUES (".$questions.")";
    $stmt = $db->prepare($statement);
    $stmt->bind_param("sss", $value);

    $stmt->execute();
    $stmt->close();
    echo "inserted";
}

The same code works when I replace

$stmt->bind_param("sss", $value);

with

$stmt->bind_param("sss",$values[0],$values[1],$values[2]);

Bind the values inside of your loop instead of concatenating a variable. — GrumpyCrouton, Sep 30 '20 at 17:47
As this code is designed to be used to insert a variable number of columns with the same number of values, surely the `"sss"` part needs to be dynamically created as well — RiggsFolly, Sep 30 '20 at 17:49
@olibiaz This is `mysqli_*`, not `PDO`. I'm not super familiar with mysqli, but as far as I know, it doesn't support this — GrumpyCrouton, Sep 30 '20 at 17:52

GrumpyCrouton · Answer 1 · 2020-09-30T18:05:17.317

bind_param() doesn't take a string that is a comma separated list of values, which seems to be what you are trying to pass it.

Move your foreach loop below prepare() and bind the values inside the loop.

if (count($fields) == count($values)) {
    $fielddata = implode(", ", $fields);
    $questions = rtrim(str_repeat("?, ", count($values)), ", ");
    $typedata = implode("", $type);

    //NOTE: You should verify that `$table` contains a valid table name.
    $statement = "INSERT INTO {$table} ({$fielddata}) VALUES ({$questions})";
    $stmt = $db->prepare($statement);
    
    //bind parameters using variable unpacking (PHP 5.6+), assuming `$typedata` actually contains the proper types.
    $stmt->bind_param($typedata, ...$values);
    
    $stmt->execute();
    $stmt->close();
    echo "inserted";
}

Dharman · Accepted Answer · 2020-09-30T18:07:41.493

You seem to be binding a single string as a second argument in your bind_param(). This method takes a number of variables by reference and binds them to the placeholders in the query and since you bound a single string the number of bound parameters does not match.

You need to store the values in an array and then unpack them using the splat operator.

if (count($fields) == count($values)) {
    $fielddata = implode(", ", $fields);
    $questions = rtrim(str_repeat("?, ", count($values)), ", ");

    $statement = "INSERT INTO ".$table." (".$fielddata.") VALUES (".$questions.")";
    $stmt = $db->prepare($statement);
    $stmt->bind_param(str_repeat("s", count($values)), ...$values);
    $stmt->execute();
}

Also, the type should be a list of letters denoting the type of each variable being bound. The best case is to bind them all as strings, so just repeat s for each bound variable.

Take care of SQL injection. You need to make sure that the field names are properly whitelisted. If these can be arbitrary values you could be vulnerable to SQL injection.

@lock It's a Splat operator. It unpacks the variables so they are all passed to the function separately. — GrumpyCrouton, Sep 30 '20 at 18:07

Binding an array in MySQLi prepared Insert statement PHP

2 Answers2