pg_query_params因参数过多而失败 - pg_query_params fails with too many arguments

我不知道为什么会失败，我有两个例子，它们是同一个函数，但一个有效，另一个无效。我真的希望那个不起作用的能起作用，因为它可以让我把很多潜在的重复代码放在一个函数中。

这将调用setItemWorks或setItemDoesNotWork。

public function setitemName($itemId, $name){
    /* Add the name of the item to the database */
    $fieldName = 'name';
    $tableName = "items";
    $idName = "id";
    $result = $this->setItemWorks($itemId, $name);
    // Use comments to enable below function and disable function call above.
    //$result = $this->setItemDoesNotWork($tableName, $idName, $fieldName, $itemId, $name);
}

setItemDoesNotWork：

private function setItemDoesNotWork($table, $id, $field, $itemId, $fieldValue){
    $_1 = $itemId;
    $_2 = $fieldValue;
    $_3 = $field;
    $_4 = $table;
    $_5 = $id;
    $parameters = array($_1, $_2, $_3, $_4, $_5);
    // If the ID already exists, then update the name!
    $sql = 'update $4 set $3 = $2 where $5 = $1;';
    pg_query_params($this->database, $sql, $parameters);
    // Add ID and Name into table.
    $sql = 'insert into $4($5, $3) select $1, $2 where not exists(select 1 from $4 where $5=$1)';
    $result = pg_query_params($this->database, $sql, $parameters);
    return $result;
}

setItemWorks:

private function setItemWorks($table, $id, $field, $itemId, $fieldValue){
    $_1 = $itemId;
    $_2 = $fieldValue;
    $parameters = array($_1, $_2);
    // If the ID already exists, then update the name!
    $sql = 'update items set $name = $2 where id = $1;';
    pg_query_params($this->database, $sql, $parameters);
    // Add ID and Name into table.
    $sql = 'insert into items(id, name) select $1, $2 where not exists(select 1 from items where id=$1)';
    $result = pg_query_params($this->database, $sql, $parameters);
    return $result;
}

当我尝试将5个变量放入pg_query_params中时，它似乎不起作用。

这是我得到的错误：

错误：

[20-Mar-2015 00:17:19 UTC] PHP Warning:  pg_query_params(): Query failed: ERROR:  syntax error at or near "$4"
LINE 1: update $4 set $3 = $2 where $5 = $1;
               ^ in /home/ubuntu/workspace/lib/ItemDatabase.php on line 139

编辑：我目前给出的答案是不安全的，有什么想法可以在不能使用pg_query_params的情况下确保安全吗？

您正在同时打开两个蠕虫罐头：

动态SQL同时避免SQL注入
UPSERT同时使用并发事务解决竞争条件

两者都很棘手，最佳解决方案取决于用例的细节。

PL/pgSQL函数

我建议使用一个服务器端plpgsql函数来封装这两个问题的干净解决方案：

SQL注入是不可能的
并发问题得到解决

CREATE OR REPLACE FUNCTION f_dynamic_upsert
         (_tbl text, _id text, _fld text, _item_id int, _field_val text)
  RETURNS void AS
$func$
DECLARE
   _found int;
BEGIN
LOOP
   EXECUTE format('UPDATE %I SET %I = $1 WHERE %I = $2', _tbl, _fld, _id)
   USING   _field_val, _item_id;
   GET DIAGNOSTICS _found = ROW_COUNT;
   IF _found > 0 THEN RETURN; END IF;
   BEGIN
      EXECUTE format('INSERT INTO %I(%I, %I) SELECT $1, $2', _tbl, _fld, _id)
      USING   _field_val, _item_id;
      RETURN;
   EXCEPTION WHEN unique_violation THEN
      -- rarely happens, keep trying
   END;
END LOOP;
END
$func$  LANGUAGE plpgsql;

呼叫：

SELECT f_dynamic_upsert('foo', 'foo_id', 'foo_val', 3, 'new foo');

或者使用命名参数语法：

SELECT f_dynamic_upsert(_tbl := 'foo'
                      , _id := 'foo_id'
                      , _fld := 'foo_val'
                      , _item_id := 3
                      , _field_val :='new foo');

SQL Fiddle演示它的工作原理。

关于动态SQL和SQL注入的更多信息：

表名作为PostgreSQL函数参数
将表名和列名定义为plpgsql函数中的参数
在触发器函数中使用动态表名的INSERT

Postgres UPSERT的实现工作正在进行中：INSERT .. ON CONFLICT UPDATE。如果运气好的话，这将出现在下一个9.5版本中。Postgres Wiki中的详细信息
关于UPSERT:的更多信息

插入，在PostgreSQL中重复更新
如何在PostgreSQL中升级（MERGE，INSERT…ON DUPLICATE UPDATE）
UPSERT函数中的复合类型问题

PHP函数

可能看起来像这样：

private function setItem($table, $id, $field, $itemId, $fieldValue) {
   $parameters = array($table, $id, $field, $itemId, $fieldValue)
   $sql = 'SELECT f_dynamic_upsert($1, $2, $3, $4, $5)';
   $result = pg_query_params($this->database, $sql, $parameters);
   return $result;
}

参数的数量不会导致pg_query_params()出现任何问题（无论如何，不是只有5个参数），问题的根源是您的查询。

正如错误消息所说，查询解析器在该位置不需要参数，而是需要表名。

只能使用查询参数，而不能使用文字值（字符串、数字、NULL等）。数据库、表和字段名称、SQL关键字、函数、运算符和其他语法元素不能用参数代替。

您必须将表名和字段放入查询中。这就是您的方法setItemWorks()运行而setItemDoesNotWork()失败的原因。

pg_query_params()（以及其他DBMS的类似函数）只接受参数而不接受文字值有一个很好的原因：使用参数的查询由服务器解析，并且必须是正确的SQL。

准备好的查询的主要目的是，当使用不同的文字值多次执行同一查询时，只进行一次SQL解析。准备好的查询的另一个优点是可以防止SQL注入。因为参数的值没有直接插入（引用或不引用）到查询中，所以SQL注入被排除在外。这些参数的值被发送到服务器，而不带标签；没有理由对它们进行转义，因为它们不再成为SQL查询的一部分。

根据wildplasser's的注释，我已经按照如下方式组装了函数，现在它可以正常工作：

private function setItemDoesNotWork($table, $id, $field, $itemId, $fieldValue){
    $_1 = $itemId;
    $_2 = $fieldValue;
    $_3 = $field;
    $_4 = $table;
    $_5 = $id;
    $parameters = array($_1, $_2);
    // If the ID already exists, then update the name!
    $sql = 'update ' . $_4 . ' set ' .$_3 . ' = $2 where ' . $_5 . ' = $1;';
    /*$result = */pg_query_params($this->database, $sql, $parameters);
    // Add ID and Name into table.
    $sql = 'insert into ' . $_4 . '(' . $_5 . ',' . $_3 . ') select $1, $2 where not exists(select 1 from ' . $_4 . ' where ' . $_5 . '=$1)';
    $result = pg_query_params($this->database, $sql, $parameters);
    return $result;
}

编辑：

所以这是不安全的，有没有一种简单的方法可以使这个函数免受SQL注入的影响？