PDO PHP insert into DB from an associative array

Question

I have an array like this

  $a = array( 'phone' => 111111111, 'image' => "sadasdasd43eadasdad" );

When I do a var-dump I get this ->

 { ["phone"]=> int(111111111) ["image"]=> string(19) "sadasdasd43eadasdad" }

Now I am trying to add this to the DB using the IN statement -

 $q = $DBH->prepare("INSERT INTO user :column_string VALUES :value_string");
 $q->bindParam(':column_string',implode(',',array_keys($a)));
 $q->bindParam(':value_string',implode(',',array_values($a)));
 $q->execute();

The problem I am having is that implode return a string. But the 'phone' column is an integer in the database and also the array is storing it as an integer. Hence I am getting the SQL error as my final query look like this --

INSERT INTO user 'phone,image' values '111111111,sadasdasd43eadasdad';

Which is a wrong query. Is there any way around it.

My column names are dynamic based what the user wants to insert. So I cannot use the placeholders like :phone and :image as I may not always get a values for those two columns. Please let me know if there is a way around this. otherwise I will have to define multiple functions each type of update.

Thanks.

Solution 1

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)

Solution 2

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.

Solution 3

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);

Solution 4

I appreciated MortenSickel's answer, but I wanted to use named parameters to be on the safe side:

    $keys = array_keys($a);
    $sql = "INSERT INTO user (".implode(", ",$keys).") \n";
    $sql .= "VALUES ( :".implode(", :",$keys).")";        
    $q = $this->dbConnection->prepare($sql);
    return $q->execute($a);

Solution 5

You actually can have the :phone and :image fields bound with null values in advance. The structure of the table is fixed anyway and you probably should got that way.

---

But the answer to your question might look like this:

$keys = ':' . implode(', :', array_keys($array)); 
$values = str_repeat('?, ', count($array)-1) . '?';

$i = 1;
$q = $DBH->prepare("INSERT INTO user ($keys) VALUES ($values)");

foreach($array as $value)
    $q->bindParam($i++, $value, PDO::PARAM_STR, mb_strlen($value));

View more solutions

Comments

[Louis Loudog Trottier]

how long is the length of your hone field? it return nine (9) 1's but your phone number have 10d igits. Sql sould return a warning as he truncated the value but just check.

[MortenSickel]

Agree Louis, although a phone number can be seen as an integer, you do not expect to do any math with it and you may run out of size, it is usually better treated and stored as a text string.

[Charbel Wakim]

This did not work for me I had to change the $sql to $sql="insert into user($fieldlist) values(${qs}?)"; and it worked

[PiTheNumber]

SQL INJECTION This is not secure if $a is filled by your form. It is in the text but I feel like this needs to be more visible. If you need to user input use a filter: stackoverflow.com/a/4260168/956397

[kray]

+1 not for the end result, but I recongized the way you created an array with a foreach loop which was exactly what I was trying to do for a queried assoc array, and needing to add additional key=>value's. Thanks :). @MortenSickel

[Acyra]

This is nice code, thank you. Faster approach than coding through an ORM for inserting data.

[crafter]

Are you setting the values equal to the field name?

[DᴀʀᴛʜVᴀᴅᴇʀ]

please explain your code it makes it a better answer.