PDO PHP从关联数组插入到DB中

I have an array like this

我有这样的数组

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

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

当我进行var-dump时，我得到了这个 - >

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

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

现在我尝试使用IN语句将其添加到数据库 -

 $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 --

我遇到的问题是内爆返回一个字符串。但'phone'列是数据库中的整数，并且数组也将其存储为整数。因此我得到SQL错误，因为我的最终查询看起来像这样 -

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.

我的列名是动态的，基于用户想要插入的内容。所以我不能使用像：phone和：image这样的占位符，因为我可能并不总是得到这两列的值。如果有办法，请告诉我。否则我将不得不为每种类型的更新定义多个功能。

Thanks.

谢谢。

6 个解决方案

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

上次我检查时，不可能准备一份声明，其中受影响的列在准备时是未知的 - 但这似乎有用 - 也许你的数据库系统比我使用的更宽容（主要是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.

显而易见的是implode（）语句，因为每个变量都应由它自己处理，你还需要在insert语句中的字段列表周围加括号。

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

如果你不相信$ a中的字段名称，你必须做类似的事情

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)

其中validfields是你编写的函数，它测试每个fieldname并检查它是否有效（通过建立一个关联数组来快速和脏化$ valfields = array（'name'=> 1，'email'=> 1，'phone'= > 1 ...然后通过从服务器获取字段名称来检查$ valfields [$ f]的值，或者（我希望如此）

#2

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

SQL查询参数只能用于放置文字值的位置。

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.

您不能将参数用于列名，表名，值列表，SQL关键字或任何其他表达式或语法。

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.

对于这些情况，您仍然需要将内容插入到SQL字符串中，因此您有一些SQL注入的风险。防止这种情况的方法是将列名列入白名单，并拒绝任何与白名单不匹配的输入。

#3

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

因为所有其他答案都允许SQL注入。对于用户输入，您需要筛选允许的字段名称：

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

#4

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.

实际上，您可以预先使用：phone和：image字段以空值绑定。无论如何，表的结构是固定的，你可能应该这样。

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

#5

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

我很欣赏MortenSickel的回答，但我想使用命名参数来保证安全：

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

#6

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.

我知道很久以前就已经回答过这个问题了，但我今天发现它并且除了@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

下面的类将允许您将关联数组插入或更新到数据库表。有关MySQL PDO的更多信息，请访问：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:

dbConnection类用法：

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

#1