PostgreSQL PDO插入到数组类型

Question

我正在尝试使用PHP PDO将值插入到PostgreSQL数据库中，但遇到以下错误消息：

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: array value must start with "{" or dimension information

该字段类型是PostgreSQL数组。

以下是我的代码（简化版）：

try {
  $sql='INSERT INTO table (fieldName, foo) VALUES (?, ?)';
  $fieldName=array('1','2');
  $data=array($fieldName, 'bar'); # fieldName is array type in PostgreSQL
  $STH = $conn->prepare($sql);          
  $STH->execute($data);
catch(PDOException $e) {
  # Handle exception
}

谢谢！

- Matt

---

为什么要使用$data=array(array('1','2'));而不是$data=array('1','2');？ - George Cummins

我更新了问题。fieldname变量在PostgreSQL中存储为数组。 - Matt

Answer 1

以防万一，如果有人遇到这种情况，解决方法是将数组implode后添加{}。

$fieldName='{'.implode(",",$fieldName).'}';

- Matt

---

用户可以提供数据，这些数据可能会导致您的数据库生成错误或被处理为与数据库客户端不同的数组：在php中，['hello \','world']将变成'{hello world}'在PostgreSQL中，而实际上应该是'{hello \,world}'。当然，我从未使用过PgSQL，所以我可能是错的，但根据我在文档中阅读到的内容，这似乎是正确的。https://www.postgresql.org/docs/current/static/arrays.html - Chinoto Vokro

我给这个答案投了反对票，因为它没有转义值。如果$fieldName包含逗号和其他特殊字符，它将无法按预期解释！ - Demurgos

Answer 2

我最近有类似的需求。我需要使用json_encode将PHP数组转换为json，并绑定参数或值，以便它可以转义所有值，从而消除SQL注入的可能性。

$st->bindValue(':arr',json_encode(["jam sandwich","ham sandwich","cheese sandwich"]),\PDO::PARAM_STR);

然后在PDO语句中使用数组聚合函数从json函数构建数组。

例如。

SELECT array_agg(ar) FROM 
jsonb_array_elements_text('["jam sandwich","ham sandwich","cheese sandwich"]') ar;

你可以在CTE（公共表达式）中使用它来执行一次单一查询的插入、更新或其他操作，同时使其安全，因为所有值都已转义。

- whitelined

Answer 3

我知道这是一个旧的帖子，但它是“php Postgres PDO array”在Google上的首个搜索结果，所以我会添加我的答案。 Matt的答案存在问题，因为implode没有固有的净化。因此，如果$fieldname包含引号、逗号、胡须括号等，则会创建无效值。请参见下面的示例，了解如何使用正确的PDO处理数组数据：

// Connect to PostgreSQL database and create test table /////
$dsn = "pgsql:host=$host;dbname=$db;user=$user;password=$pwd";
$psql = new PDO($dsn);

if($psql == null){throw new Exception("Error, db connection returned null.");}
// Set errormode to exceptions
$psql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql_table = "
    CREATE TABLE IF NOT EXISTS testarray (
        id         SERIAL PRIMARY KEY,
        something  TEXT,
        test1      TEXT[]
    )";
$q_table = $psql->prepare($sql_table);
$q_table->execute();
/////////////////////////////////////////////////////////////

// Create a new row and get it's ID
$sql_new = "INSERT INTO testarray (something) VALUES ('stuff') RETURNING id";
$q_new = $psql->prepare($sql_new);
$q_new->execute();
$r_new = $q_new->fetchAll(PDO::FETCH_ASSOC);
$id = $r_new[0]['id'];

$myarray = array("Test1", "Test2", "test3", "testing4", "TEST5");

// Use a PDO for efficiency and ease of use
// Use the ARRAY_APPEND SQL function to use PDO to add to an array
$sql_up = "UPDATE testarray SET test1 = ARRAY_APPEND(test1, :arr) WHERE id = :id";
$q_up = $psql->prepare($sql_up);
$q_up->bindParam(":id", $id);

// Loop through each element, binding it to the PDO and executing again
foreach($myarray as $elem){
    $q_up->bindParam(":arr", $elem);
    $q_up->execute();
}

现在在您的数据库中：

testing=# SELECT * FROM testarray;
 id | something |               test1                
----+-----------+------------------------------------
  1 | stuff     | {Test1,Test2,test3,testing4,TEST5}
(1 row)

testing=# 

- browner87

---

看起来有点不专业，但至少它似乎可以可靠地按预期放入数据，而没有出现错误的机会。 - Chinoto Vokro