prisma: Raw query failed. Code: `42601`. Message: `db error: ERROR: syntax error at or near "$1"` Postgresql INTERVAL

For some reason, Prisma does not accept the use of variables for table names and field names. One way I found to solve this problem was to use an auxiliary variable to assemble the query and then use this variable with queryRaw.

See an example below:

const query = `
  SELECT * FROM "${options.table}"
  WHERE "${options.column}" = '${value}'
`;

const row: any[] = await prisma.$queryRaw`${Prisma.raw(query)}`;

Important:

• If the name of the table or field contains double quotes, they must be placed in the query.
• It is necessary to use Prisma.raw() for the query to work.

@janpio I’ll try to put together something soon to cover the suggestions.

On further thought I’m not sure whether it’d be wise to try and do a Prisma.identifier concept since it’ll be so frequently inapplicable - it’d be trying to solve for a user education issue but would probably just open its own can of worms. I think there might already be an existing issue for queryRaw error logging so I’ll hunt around for that before creating a new one.

For some reason, Prisma does not accept the use of variables for table names and field names. One way I found to solve this problem was to use an auxiliary variable to assemble the query and then use this variable with queryRaw.

See an example below:

const query = `
  SELECT * FROM "${options.table}"
  WHERE "${options.column}" = '${value}'
`;

const row: any[] = await prisma.$queryRaw`${Prisma.raw(query)}`;

Important:

• If the name of the table or field contains double quotes, they must be placed in the query.
• It is necessary to use Prisma.raw() for the query to work.

@lucasjribeiro I just wanted to call out an additional major issue with this approach since it looks like several users have upvoted your comment. Your query is entirely subject to SQL injection attacks; it looks like you are parameterizing the query, but in reality you’ve just created a normal template string with options.table, options.column, and value interleaved and then told Prisma to run it. This might occasionally be fine if each of those variables are entirely trustworthy, but I’d be surprised.

For instance, if you’re using this options table to decide which are available to a user, we could just inject with something like this to get every option from that table: const value = '\' OR 1=1 OR \'\' = \''. You want to be extremely careful when using Prisma.raw - whenever you apply that, you’re just issuing a string and need to sanitize carefully for SQL injection yourself.

Suppose your code has string parameters. then try this. it worked for me

//example 1
let userTable = 'User'
let result = await prisma.$queryRawUnsafe(`SELECT * FROM ${userTable}`)

//my code
const lastMonday = `'${lastMonday}'`;
const thisMonday = `'${thisMonday}'`;

const userInterest =
    await prisma.$queryRawUnsafe<InterestUser>(`Select user_id, SUM(interest_amount) as weekly_amount  from "Interest"
        where
        created_date between ${lastMonday} AND ${thisMonday}
         GROUP BY user_id
         HAVING SUM(interest_amount) > 1`);

I am unfortunately not able to do that in next few days. Anyhow it would not be the query I would have to share, but the generation using template strings. Queries are generated, by a function. Table names are dynamically added and some conditions based on functions params will be added.

Though, it is now working with prisma.$rawQuery and actually that is totally fine. My queries are for calculating statistics and filling other tables. None of the params are changeable by any user.

I can reproduce in 4.0.0 and in 4.3.1. My query is way more complex and it was working with 3.x

Same trouble

+1 facing the same issue