PHP PDO is inserted into the database from an associative array

Question

I have an array like this

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

When I do var-dump, I get this →

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

Now I'm trying to add this to the database 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 facing is that implode returns a string. But the “telephone” column is an integer in the database, and the array also stores it as an integer. Therefore, I get an SQL error since my last query looks like this:

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

What a wrong request. Is there any way around this.

My column names are dynamically based on what the user wants to insert. Therefore, I cannot use placeholders such as : phone and : image , since I cannot always get values ​​for these two columns. Please let me know if there is a way around this. otherwise, I will have to define several functions for each type of update.

Thanks.

Answer 1

The last time I checked, it was impossible to prepare an instruction in which the affected columns were unknown at the time of preparation - but this thing seems to work - perhaps your database system is more forgiving than the ones I use (mostly postgres)

What is obviously wrong is the implode () operator, since each variable must be processed by it independently, you also need a bracket around the list of fields in the insert statement.

To insert custom fields, I think you need to do something like this (at least the way 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 need to do something like

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

If validfields is a function you write that checks every field name and checks if it is valid (fast and dirty by creating an associative array $ valfields = array ('name' => 1, 'email' => 1, phone ' => 1 ... and then check the value of $ valfields [$ f] or (as I would prefer) by extracting the field names from the server)

Answer 2

SQL query parameters can only be used where otherwise you could enter a literal value.

So, if you could see that you are inserting a quoted string literal, date literal, or numeric literal into the query, you can use the parameter.

You cannot use a parameter for column name, table name, value lists, SQL keyword, or any other expressions or syntax.

In these cases, you still have to interpolate the contents into an SQL string, so you have some risk of SQL injection. The way to protect against this is by using a whitelist of column names and rejecting any input that does not match the whitelisting.

Answer 3

Since all other answers allow SQL injection . To enter a user, you need to filter the 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); 

Answer 4

In fact, you can have :phone and :image fields associated with null values ​​in advance. The table structure has been fixed anyway, and you should probably get this way.

---

But the answer to your question may 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)); 

Answer 5

I know this question has long been answered, but I found it today and made a small contribution in addition to @ MortenSickel's answer.

The following class allows you to insert or update an associative array into a 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); } } ?> 

Using the dbConnection class:

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

Answer 6

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

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