如何使用Prisma和PostgreSQL处理有条件的预处理语句？

Question

我有一个搜索查询，它的参数根据客户端输入而变化。

await prisma.$queryRaw(`SELECT column FROM table ${condition ? `WHERE column = '${condition}'`  :' ' } `) 

如何使用预处理语句编写此查询并避免重复查询。我想到的唯一解决方案是以下内容：

const result = condition ? await prisma.$queryRaw(`SELECT column FROM table WHERE column = $1`,condition) : await prisma.$queryRaw(`SELECT column FROM table`)

这样做的目的是为了避免第一个查询时发生SQL注入。

编辑在尝试@Ryan建议的解决方案后，我得到了以下错误：

Raw query failed. Code: `22P03`. Message: `db error: ERROR: incorrect binary data format in bind parameter 1`

这是我的实现：

    const where = Prisma.sql`WHERE ${searchConditions.join(' AND ')}`;
    const fetchCount = await prisma.$queryRaw`
    SELECT 
      COUNT(id)
    FROM
      table
    ${searchConditions.length > 0 ? where : Prisma.empty}
  `;

这将在Prisma日志中翻译为以下内容：

Query: 
    SELECT 
      COUNT(id)
    FROM
      table
    WHERE $1
   ["column = something"]

解决方案 我必须进行大量的重新工作才能实现我想要的结果。以下是其背后的思路:

对于每个搜索条件，您需要执行以下操作:

let queryCondition = Prisma.empty;
    if (searchFilter) {
      const searchFilterCondition = Prisma.sql`column = ${searchFilter}`;

      queryCondition.sql.length > 0
        ? (queryCondition = Prisma.sql`${queryCondition} AND ${streamingUnitCondition}`)
        : (queryCondition = searchFilterCondition);
    }

最终查询后，您可以执行以下类似操作:

SELECT COUNT(*) FROM table ${queryCondition.sql.length > 0 ? Prisma.sql`WHERE ${queryCondition}` : Prisma.empty}

- user 007

Answer 1

您可以这样做：

import { Prisma } from '@prisma/client'

const where = Prisma.sql`where column = ${condition}`

const result = await prisma.$queryRaw`SELECT column FROM table ${condition ? where : Prisma.empty}`

- Ryan

---

谢谢您的输入。当没有传递任何条件时，这个方法可以正常工作，但是一旦至少传递一个参数，就会抛出错误。我已经根据您的建议更新了我的问题。您能否看一下？谢谢。 - user 007

在这种情况下，您需要执行类似于这样的操作。 - Ryan

你的第一个建议引导我找到了正确的答案。我还按照文档 https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access 进行了操作，所以我会将你的回复标记为正确答案。 - user 007

Answer 2

这是我的工作版本，使用 Prima.join：

import { Prisma } from '@prisma/client'

const searchConditions: Prisma.Sql[] = []
if (q) {
  searchConditions.push(Prisma.sql`column = ${q}`)
}
const where = searchConditions.length ? 
  Prisma.sql`where ${Prisma.join(searchConditions, ' and ')}` : 
  Prisma.empty

await prisma.$queryRaw(
  Prisma.sql`
    select *
    from table
    ${where}
    `
)

- Olivier Lecrivain