Insert large amount of variables into table using PDO

Question

I have a large form with about 25 input fields.

Im trying to insert them into my table and the only way i know how is using the following...

$count = $dbh->exec("INSERT INTO directory(field1, field2) VALUES (':value1', ':value2')");

As I have so many post variables, is there a better way to do this than type each and everyone into my query?

Answer 1

Dynamic prepared queries

You can build your query dynamically from $_POST array:

But, NEVER trust user input, which means you cannot trust that data in $_POST will contain valid column names.

1. Sanitize the post data

You can define an array of whitelisted column names $whitelist = array('field1', 'field2', ...), and then use:

$data = array_intersect_key($_POST, array_flip($whitelist));

to find the intersection between the whitelisted columns and your $_POST array. (Thanks @BillKarwin)

2. Build the query

private function buildInsertSql($data, $table) {
    $columns = "";  
    $holders = "";  
    foreach ($data as $column => $value) {  
       $columns .= ($columns == "") ? "" : ", ";  
       $columns .= $column;  
       $holders .= ($holders == "") ? "" : ", ";  
       $holders .= ":$column";  
    }  
    $sql = "INSERT INTO $table ($columns) VALUES ($holders)";  
    return $sql; 
}

This will give you a SQL statement of the form:

$sql = INSERT INTO directory (field1, field2) VALUES (:field1, :field2)

and prepare the statement:

$stmt = $dbh->prepare($sql);

3. Bind parameters

You can then dynamically bind parameters to the placeholders:

foreach ($data as $placeholder => $value) {
    $stmt->bindValue(":$placeholder", $value);
 }

and execute it:

$stmt->execute();

---

A little more advanced...

• Take a look at this link Binding to the same placeholder For information about how to make your dynamic prepared statement more robust.
• Take a look at this link: Bind Params Inside Loop For a caveat regarding binding paramaters vs values in a loop.

Answer 2

You can build INSERT statements dynamically, but you should be careful about restricting the POST fields. Don't trust the request contains only valid columns.

Also delimit table and column identifiers. MySQL uses the back-tick as the identifier delimiter by default.

function execInsert($pdo, $table, $_POST) {
    // get a list of columns in $table, either by hard-coding them per table, 
    // or by querying DESC or INFORMATION_SCHEMA
    $real_columns = array('col1', 'col2', 'col3');

    $fields = array_intersect_key($_POST, array_flip($real_columns));
    if (!$fields) {
        // no POST fields match the real columns
        return false;
    }

    $columns = array_map(function($col) { return "`".$col."`"; }, array_keys($fields));
    $holders = array_map(function($col) { return ":".$col; }, array_keys($fields));
    $values = $fields;

    $sql = "INSERT INTO `$table` (" . join(",", $columns) . 
        " VALUES (" . join(",", $holders) . ")";  

    if (($stmt = $pdo->prepare($sql)) === false) {
        die(print_r($pdo->errorInfo(), true));
    }
    if (($retval = $stmt->execute($values)) === false) {
        die (print_r($stmt->errorInfo(), true));
    }
    return $retval;
}