Answer a question

I have a search query that its parameters changes depending on the client input.

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

how can I write this query using prepared statement and avoiding duplicate queries. The only solution I came up with is the following:

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

The goal from this is to avoid sql injections from the first query.

EDIT after trying the solution suggested by @Ryan I got the following error:

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

here's my implementation:

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

that will translate to the following in the prisma logs:

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

SOLUTION I had to do a lot of rework to achieve what I want. Here's the idea behind it:

for every search condition you need to do the following:

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

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

afterwards in the final search query you can do something of this sort:

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

Answers

You can do it like this:

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}`