通过 Knex.js 执行 POSTGRES LIKE 时出现语法错误

Question

这给了我一个语法错误：

if (searchCode) {
  customerProducts = await customerProducts.andWhere(
    db.sequelize.knex.raw('customer.code LIKE '
      + `%${searchCode}%`)
    );
  }
}

错误如下所示：

{"message":"UnknownErrorMiddleware error: select "CustomerProduct"."id" as "_id", "CustomerProduct"."last_delivered" as "_lastDelivered", "CustomerProduct"."margin" as "_margin", "CustomerProduct"."outlier" as "_outlier", "CustomerProduct"."growth" as "_growth", "CustomerProduct"."period" as "_period", "CustomerProduct"."price" as "_price", "CustomerProduct"."active" as "_active", "CustomerProduct"."customer_id" as "_customerId", "CustomerProduct"."product_id" as "_productId", "CustomerProduct"."modified" as "_modified", "CustomerProduct"."month_value" as "_monthValue", "customer"."id" as "_customer_id", "customer"."title" as "_customer_title", "customer"."code" as "_customer_code" from "customer_products" as "CustomerProduct" inner join "customers" as "customer" on "CustomerProduct"."customer_id" = "customer"."id" where "product_id" = $1 and customer.code LIKE %ZOO1% - syntax error at or near "%"","level":"info"}

我认为问题是%ZOO1%周围没有''，但我不知道我是如何添加的。它是如何完成的，如果这不是问题，那是什么？

Answer 1

您可以

像这样添加它们'%${searchCode}%'。但是searchCode变量很容易进行sql注入。

但是，您应该使用原始参数绑定功能

db.sequelize.knex.raw('customer.code LIKE ?', [`%${searchCode}%`])

https://knexjs.org/#Raw-Bindings