PDO PHP insert into DB from an associative array

Last time I checked, it was not possible to prepare a statement where the affected columns were unknown at preparation time - but that thing seems to work - maybe your database system is more forgiving than those I am using (mainly postgres)

What is clearly wrong is the implode() statement, as each variable should be handled by it self, you also need parenthesis around the field list in the insert statement.

To insert user defined fields, I think you have to do something like this (at least that how I do it);

$fields=array_keys($a); // here you have to trust your field names! $values=array_values($a);$fieldlist=implode(',',$fields); $qs=str_repeat("?,",count($fields)-1);$sql="insert into user($fieldlist) values(${qs}?)";$q=$DBH->prepare($sql);$q->execute($values);

If you cannot trust the field names in $a, you have to do something like

foreach($a as $f=>$v){   if(validfield($f)){      $fields[]=$f;      $values[]=$v;   }}

Where validfields is a function that you write that tests each fieldname and checks if it is valid (quick and dirty by making an associative array $valfields=array('name'=>1,'email'=>1, 'phone'=>1 ... and then checking for the value of $valfields[$f], or (as I would prefer) by fetching the field names from the server)

SQL query parameters can be used only where you would otherwise put a literal value.

So if you could see yourself putting a quoted string literal, date literal, or numeric literal in that position in the query, you can use a parameter.

You can't use a parameter for a column name, a table name, a lists of values, an SQL keyword, or any other expressions or syntax.

For those cases, you still have to interpolate content into the SQL string, so you have some risk of SQL injection. The way to protect against that is with whitelisting the column names, and rejecting any input that doesn't match the whitelist.

Because all other answers allow SQL injection. For user input you need to filter for allowed field names:

// change this$fields = array('email', 'name', 'whatever');$fieldlist = implode(',', $fields);$values = array_values(array_intersect_key($_POST, array_flip($fields)));$qs = str_repeat("?,",count($fields)-1) . '?';$q = $db->prepare("INSERT INTO events ($fieldlist) values($qs)");$q->execute($values);