Node.js - PostgreSQL - 无法确定参数 $1 的数据类型错误

Question

我正在尝试使用node.js中的pg npm包创建PostgreSQL预处理语句。然而，我一直收到错误提示：

无法确定参数$1的数据类型。

 function promiseQuery(sql, values) {
    return new Promise(function(resolve, reject) {
        pool.query('select $1 from workers', ['name'], function(err, result) {
            if (err) {console.log(err); reject(err)}
            else resolve(result.rows);   
        })
    });
}

在数据库中，name字段被设置为text not null类型。我也尝试过pg-promise，但也没有成功。

- wizard

---

在这个例子中，brianc 是一个文本值，而不是列名 - 这是你的情况吗？ - Vao Tsun

不，我想创建动态字段列表。 - wizard

然后将 name 作为参数传递给你的 promiseQuery，并将其连接起来，如：'select ' + param['name'] + ' from workers'。 - VladNeacsu

如果您可以像那样执行动态SQL，则无法防止SQL注入。 - Łukasz Kamiński

我认为准备语句的整个重点在于保护您免受这种能力的影响。使用plpgsql，您可以动态构建execute format('select %I from workers','name')。 - Vao Tsun

Answer 1

在查询语句 select name from workers 中，从SQL语法的角度来看，name是一个标识符，而标识符不能作为$N参数传递，它们必须在命令中一字不差地出现。否则，查询语句无法被准备。 $N参数只能出现在查询语句中字面量（常量）所在的位置。

如果在任何客户端库之外尝试类似于PREPARE SQL命令的操作，您将遇到同样的错误。

PREPARE p as SELECT $1 FROM pg_class;
ERROR:  could not determine data type of parameter $1

解决办法是使用字符串替换技术在javascript中构建查询语句，用于列名或表名，在将其提交到数据库之前。 pg-promise支持使用特定语法将标识符注入到查询中。来自文档：

db.query('SELECT $1:name FROM $2:name', ['*', 'table']);
//=> SELECT * FROM "table"

- Daniel Vérité

---

字符串替换技术这样做不会重新打开 SQL 注入查询吗？ - Vincent Buscarello

@VincentBuscarello：如果适当引用，它是安全的。看起来pg-promise支持标识符的引用/转义（不确定3年前是否支持）。我已经在答案中添加了一点内容。 - Daniel Vérité

Answer 2

延续Daniel Vérité的答案...

你不能将预处理语句与动态列名结合使用，你必须在客户端生成查询。

使用pg-promise语法进行SQL名称，你可以像这样正确地转义查询:

db.any('SELECT $1~ FROM table', [colName])
// OR:
db.any('SELECT $1:name FROM table', [colName])
// OR:
db.any('SELECT ${colName~} FROM table', {colName})
// OR:
db.any('SELECT ${colName:name} FROM table', {colName})
// Etc, other variable syntax, like $[], $//, $<>, $()

如果您想对一列列表执行此操作，则最简单的方法如下：

const colNames = ['one', 'two', 'three'];

db.any('SELECT $1~ FROM table', [colNames])
// etc, the same variations as above, all will generate:
// SELECT "one","two","three" FROM table

或者从所有对象属性中获取：

const data = {
    one: 123,
    two: true,
    three: 'text'
};
db.any('SELECT $1~ FROM table', [data])
// etc, the same variations as above, all will generate:
// SELECT "one","two","three" FROM table

所有这些方法都会适当地转义查询，确保不可能发生SQL注入。

- vitaly-t