在使用pg Postgres NodeJS作为参数时，如何使用LIMIT / ORDER BY？

Question

我有一个模型，它使用SORT/PAGE/PER PAGE来调用产品。当参数为数字时，它能够良好地工作，但是如果参数是字符串，则无法正常工作。

以下是可以正常工作的示例：

params.limit = 12
client.query('SELECT * FROM products LIMIT $1', [params.limit], function(err, result)

然而，这并不意味着......

params.sort = 'product_id'
params.direction = 'DESC'
client.query('SELECT * FROM products ORDER BY $1 $2', [params.sort, params.direction], function(err, result)`

我猜是因为它将单词DESC包装成'DESC'，但我不知道如何在不直接将其注入到字符串中的情况下实现这一点。

此外，使用整数传递LIMIT总是可以，但传递ALL可能出现同样的问题。

任何帮助都将非常有用！

- Matt The Ninja

---

你做不到，当涉及到查询格式化时，node-postgres相当有限。然而，pg-promise在查询格式化方面要好得多。在你的例子中，这是一个典型的原始查询参数注入。 - vitaly-t

谢谢，我一定会去看看这个！ - Matt The Ninja

Answer 1

我也遇到了同样的情况，似乎没有太多解决方案。

将order和orderby放入查询参数列表中似乎会将它们作为字符串文字放置，例如order by "name" "desc"，这会导致它们不再是描述符，从而回归到order by id asc。@gordon-linoff的答案看起来很好，但我想对于$1仍然会陷入同样的陷阱。

以下解决方案首先使用字符串文字构建“order”查询字符串，然后通过参数添加“where”、“limit”和“offset”。但是，它要求开发人员知道哪些表/列可用，以及哪些表/列将被排序（对某些列进行排序是没有意义的）。

let params = { page: 3, limit: 10, orderby: "name", order: "ASC" };

let orderby = ["id", "name", "family", "age"].includes(params.orderby)
  ? params.orderby
  : "id";

let order = ["ASC", "DESC"].includes(params.order.toUpperCase())
  ? params.order
  : "ASC";

client.query(
  `SELECT name, family, age \
   FROM customers \
   WHERE id=$1 \
   ORDER BY ${orderby} ${order} \
   LIMIT $2 \
   OFFSET $3`,
  [id, params.limit, params.page]
);

编辑： "字符串字面量" 是 ES6+ 的特性。对于旧的 JS 版本，您可以使用加法符号进行字符串连接：w="world"; str="hello" + w

PS：在生成最终字符串之前，您可以获取列名，但需要额外连接到数据库。不过，您可以将此步骤放在初始化步骤中。查看此答案以了解快速操作方法：https://dba.stackexchange.com/a/22368/150156

- Yılmaz Durmaz

Answer 2

我认为您可能需要进行显式比较：

order by (case when $2 = 'ASC' then $1 end) ASC,
         (case when $2 = 'DESC' then $1 end) DESC

- Gordon Linoff

---

它有特定的失败方式吗？ - Gordon Linoff

我也遇到了一个使用ORDER BY作为参数的情况，但是在准备好的语句中（这不会转义引号！）。让我试一下这种方式。我认为connection.escape不起作用，因为它是一个准备好的语句。 - KTM