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.

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. — Louis Loudog Trottier, Nov 22 '12 at 07:38
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. — MortenSickel, Nov 22 '12 at 07:58

score 10 · Accepted Answer · edited Jul 21 '15 at 10:14

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)

This did not work for me I had to change the $sql to $sql="insert into user($fieldlist) values(${qs}?)"; and it worked — Charbel Wakim, May 15 '14 at 20:24
**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: http://stackoverflow.com/a/4260168/956397 — PiTheNumber, Jun 02 '16 at 11:57
+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 — levi, Jan 28 '17 at 02:28

score 6 · Answer 2 · answered Nov 22 '12 at 08:06

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.

score 4 · Answer 3 · answered Jun 02 '16 at 12:11

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

score 3 · Answer 4 · answered May 21 '15 at 22:58

3

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

answered May 21 '15 at 22:58

Joseph Heininge

69
3

This is nice code, thank you. Faster approach than coding through an ORM for inserting data. – Acyra Sep 15 '17 at 15:53
Are you setting the values equal to the field name? – crafter Oct 18 '17 at 10:30

score 2 · Answer 5 · answered Nov 22 '12 at 08:45

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

score 2 · Answer 6 · edited Sep 17 '15 at 09:02

I know this question has be answered a long time ago, but I found it today and have a little contribution in addition to the answer of @MortenSickel.

The class below will allow you to insert or update an associative array to your database table. For more information about MySQL PDO please visit: http://php.net/manual/en/book.pdo.php

<?php

class dbConnection
{
  protected $dbConnection;

  function __construct($dbSettings) {
    $this->openDatabase($dbSettings);
  }

  function openDatabase($dbSettings) {  
    $dsn = 'mysql:host='.$dbSettings['host'].';dbname='.$dbSettings['name'];
    $this->dbConnection = new PDO($dsn, $dbSettings['username'], $dbSettings['password']);
    $this->dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  }

  function insertArray($table, $array) {
    $fields=array_keys($array);
    $values=array_values($array);
    $fieldlist=implode(',', $fields); 
    $qs=str_repeat("?,",count($fields)-1);

    $sql="INSERT INTO `".$table."` (".$fieldlist.") VALUES (${qs}?)";

    $q = $this->dbConnection->prepare($sql);
    return $q->execute($values);
  }

  function updateArray($table, $id, $array) {
    $fields=array_keys($array);
    $values=array_values($array);
    $fieldlist=implode(',', $fields); 
    $qs=str_repeat("?,",count($fields)-1);
    $firstfield = true;

    $sql = "UPDATE `".$table."` SET";
    for ($i = 0; $i < count($fields); $i++) {
        if(!$firstfield) {
        $sql .= ", ";   
        }
        $sql .= " ".$fields[$i]."=?";
        $firstfield = false;
    }
    $sql .= " WHERE `id` =?";

    $sth = $this->dbConnection->prepare($sql);
    $values[] = $id;
    return $sth->execute($values);
  }
}
?>

dbConnection class usage:

<?php
  $dbSettings['host'] = 'localhost';
  $dbSettings['name'] = 'databasename';
  $dbSettings['username'] = 'username';
  $dbSettings['password'] = 'password';
  $dbh = new dbConnection( $dbSettings );

  $a = array( 'phone' => 111111111, 'image' => "sadasdasd43eadasdad" );
  $dbh->insertArray('user', $a);

  // This will asume your table has a 'id' column, id: 1 will be updated in the example below:
  $dbh->updateArray('user', 1, $a);
?>

score 0 · Answer 7 · answered Jul 15 '21 at 19:48

0

public function insert($data = [] , $table = ''){
    $keys = array_keys($data);
    $fields = implode(',',$keys);
    $pre_fields = ':'.implode(', :',$keys);
    $query = parent::prepare("INSERT INTO $table($fields) VALUES($pre_fields) ");
    return $query->execute($data);
}

answered Jul 15 '21 at 19:48

saktan hamza

11
1

please explain your code it makes it a better answer. – DᴀʀᴛʜVᴀᴅᴇʀ Jul 15 '21 at 20:54

PDO PHP insert into DB from an associative array

7 Answers7

Linked