How do you run a MySQLi prepared statement with a dynamic array?

Question

What I want to be able to do is create a prepared statement that would insert values based on an inputted array, the array would be dynamic. What I mean by dynamic is the ability to just change the array $table by including an extra line such as the commented out one below and it properly send and insert the values into the table regardless how many are present. Because of my new use of prepared statements I cannot just use my previous method of:

INSERT INTO table ($fields) VALUES ($data)

Because that is insecure and is open for SQL Injection.

$table = array();
$table['Favorite_Chocolate']     = 'Milk';
$table['Favorite_Cake']          = 'Vanilla';
$table['Favorite_IceCream']      = 'Neapolitan';
//$table['Favorite_Candy']      = 'Taffy';

function magic_insert ($table, $dbc) {

    if ($stmt = mysqli_prepare($dbc, "INSERT INTO mytable (?, ?, ?) VALUES (?, ?, ?)")) {
            mysqli_stmt_bind_param($stmt, "ssssss", $1, $2, $3, $4, $5, $6);
            $1 = array_keys($table)[0];
            $2 = array_keys($table)[1];
            $3 = array_keys($table)[2];
            $4 = array_values($table)[0];
            $5 = array_values($table)[1];
            $6 = array_values($table)[2];

            mysqli_stmt_execute($stmt);
            mysqli_stmt_close($stmt);
        }
    }

fyi - i dont know of ANY database which lets you bind column names. mysql certainly wont. — goat, Feb 23 '14 at 01:10
@rambocoder It is just a quick example script. I would probably input it directly because the $table keys are set by the code and not through user-input — Darkaaja, Feb 23 '14 at 01:28
You can't bind column names, so for your query you can just bind the required values. If you really need to change the column names you can secure the column names & values by using `mysqli_real_escape_string()` and constructing a query by concatenation. No doubt the pedants will complain about this, but done properly this is safe. — , Feb 23 '14 at 01:32
@MikeW Putting column names through `mysqli_real_escape_string()` doesn't secure anything. If you need to secure them, a white-list is the only option. — jeroen, Feb 23 '14 at 01:50

score 1 · Answer 1 · edited Aug 02 '23 at 17:40

I think this is what he wanted. Note that function must filter out the column names against a predefined list of allowed columns names.

function magic_insert ($table, $dbc) {
    $columns = array_keys($table);
    $allowed = ['Favorite_Chocolate', 'Favorite_Cake', 'Favorite_IceCream'];
    $diff = array_diff($allowed, $columns);
    if ($diff) {
        throw new \InvalidArgumentException("Unknown columns(s): ". implode(",", $diff));
    }
    $values = array_values($table);
    $col_count = count($columns);
    $value_str = substr(str_repeat("?,", $col_count), 0,-1);  // e.g. ?, ?, ?
    $value_types_str = str_repeat("s", $col_count);           // sssss
    $insert_sql_cols = implode($columns, ",");
    $sql = "INSERT INTO test_table ($insert_sql_cols) VALUES ($value_str)";
    $stmt = mysqli_prepare($dbc, $sql);
    mysqli_stmt_bind_param($stmt, $value_types_str, ...$values);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
}

How do you run a MySQLi prepared statement with a dynamic array?

1 Answers1