queryRaw doesn't support dynamic tables names

[BlakeBrown]

Bug description

const values = await prisma.$queryRaw`SELECT type FROM ${collection};`; fails with the error

Message: `db error: ERROR: syntax error at or near "$1"

However,
const values = await prisma.$queryRawUnsafe(`SELECT type FROM ${collection};`); works fine.

Does anyone know why this occurs? I need to be able to be use the safe version to prevent SQL injection in my app :/

How to reproduce

Raw query a postgres database with a dynamic table name.

Expected behavior

No response

Prisma information

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model spacebudz {
  type              String?
}

Environment & setup

• OS: Mac OS
• Database: PostgreSQL
• Node v16.10.10

Prisma Version

3.2.1

[MercurialWeb]

Same problem here with Prisma Version 3.3.0

This query working well on Prisma Version 2.23.0:

(interpolation operator deleted for git editor)

const periodSelect = ${splitType < 1 ? HOUR(${dateField}) cHour, : ''} ${splitType < 2 ? DAY(${dateField}) cDay, : ''} ${splitType < 3 ? MONTH(${dateField}) cMonth, : ''} YEAR(${dateField}) cYear,;

const transactionsQuery = SELECT ${periodSelect} SUM(CASE WHEN T.astatus = 'A' THEN 1 ELSE 0 END) AS sales, SUM(CASE WHEN T.astatus = 'P' THEN 1 ELSE 0 END) AS pendingSales, SUM(CASE WHEN T.astatus = 'D' OR T.astatus = 'P' THEN 1 ELSE 0 END) AS totalSales, SUM(CASE WHEN T.astatus = 'D' THEN 1 ELSE 0 END) AS rejectedSales, SUM(CASE WHEN T.astatus <> 'D' THEN IFNULL(T.commission_amount,0) ELSE 0 END) AS commissions FROM transactions T;
`

On 3.3.0 returns:
Raw query failed. Code: '1064'. Message: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1'

After some tests I discovered that the problem is the empty placeholders, does it seem to write '?' when they are null.

[pantharshit00]

Hello,

You will need to use the following if you want to do dynamic table names and manually write the prepared statement variables:

const values = await prisma.$queryRaw(`SELECT type FROM ${collection} WHERE id = $1;`, "my-id");

Please notice that I am calling $queryRaw as a function above instead of a tagged template variable. When using the tagged template variable version of this API any variables that you will provide in the template literal body will be converted into prepared statement variables like $1 or $2. SQL doesn't support naming tables as prepared variables so that throws the error. So in order to take control of these yourself, you will need to use the function version here.

Also, please avoid using $queryRawUnsafe, it doesn't escape anything and it is almost guaranteed that the resulting API would be vulnerable to SQL injection there if you don't manually escape variables.

[pantharshit00]

@MercurialWeb Please open a new issue and fill out the template so that I can assist you better.

[em-ebitlabs]

Hello,

You will need to use the following if you want to do dynamic table names and manually write the prepared statement variables:

const values = await prisma.$queryRaw(`SELECT type FROM ${collection} WHERE id = $1;`, "my-id");

Please notice that I am calling $queryRaw as a function above instead of a tagged template variable. When using the tagged template variable version of this API any variables that you will provide in the template literal body will be converted into prepared statement variables like $1 or $2. SQL doesn't support naming tables as prepared variables so that throws the error. So in order to take control of these yourself, you will need to use the function version here.

Also, please avoid using $queryRawUnsafe, it doesn't escape anything and it is almost guaranteed that the resulting API would be vulnerable to SQL injection there if you don't manually escape variables.

Your solution doesn't work.
I get this error when I try it: Argument of type 'string' is not assignable to parameter of type 'TemplateStringsArray | Sql'.

[em-ebitlabs]

Is it possible to use a variable for the order direction?

I'm trying to do something like this:

const order = 'DESC';
await prismaClient.$queryRaw`SELECT user.name FROM user ORDER BY user.name ${order}`;

[MilesConn]

Hello,
You will need to use the following if you want to do dynamic table names and manually write the prepared statement variables:

const values = await prisma.$queryRaw(`SELECT type FROM ${collection} WHERE id = $1;`, "my-id");

Please notice that I am calling $queryRaw as a function above instead of a tagged template variable. When using the tagged template variable version of this API any variables that you will provide in the template literal body will be converted into prepared statement variables like $1 or $2. SQL doesn't support naming tables as prepared variables so that throws the error. So in order to take control of these yourself, you will need to use the function version here.
Also, please avoid using $queryRawUnsafe, it doesn't escape anything and it is almost guaranteed that the resulting API would be vulnerable to SQL injection there if you don't manually escape variables.

Your solution doesn't work. I get this error when I try it: Argument of type 'string' is not assignable to parameter of type 'TemplateStringsArray | Sql'.

If you remove the parens from the function call the type error shouldn't show up anymore. That said, I still got a syntax error at $1

[MilesConn]

I actually found a solution to this that's slightly safer imo than $queryRawUnsafe which is you can wrap the table name with const tableArg = Prisma.raw(tableName) and then use tableArg. This makes things slightly safer as its easier to validate the tableName than the entire string.

[BlakeBrown]

@pantharshit00's solution did not work me unfortunately.

I found the solution to my original question. If you first save the query in a variable,

const query = `SELECT type FROM ${collection};`

Then evaluating this directly will work, as Prisma won't make any attempt to validate the arguments

const values = await prisma.$queryRawUnsafe(query);

Easy way to get SQL injected, but hey at least you can get your queries to work :)

[dydent]

@MilesConn

I actually found a solution to this that's slightly safer imo than $queryRawUnsafe which is you can wrap the table name with const tableArg = Prisma.raw(tableName) and then use tableArg. This makes things slightly safer as its easier to validate the tableName than the entire string.

This is a great and quick fix that actually worked for me:

Just wrap your inserted variable with Prisma.raw() and it should work.

In this context:
const values = await prisma.$queryRaw SELECT type FROM ${Prisma.raw(collection)}; should do the job!

[DevTotti]

${Prisma.raw(collection)}

Property 'raw' does not exist on type 'PrismaClient<PrismaClientOptions, never, RejectOnNotFound | RejectPerOperation>'.ts(2339)

[mohammadalathamena]

@BlakeBrown there is some rules in queryRow that protect the database so you can use queryRawUnsafe, it will work fine

[LeoAso]

What queryRaw does is to avoid SQL injection by preventing you from just inserting arbitrary strings into your query. If you are 100% sure without a doubt that the string is a safe, valid SQL segment, you can use the Prisma.sql helper. Since it's a template string function, you will need to use it like this Prisma.sql([yourValidStringVariable]).

Here is a completely type-safe way to dynamically set table names and column names in your query, assuming that all your table names and columns names are exactly the same both in the schema and in the database (no %%maps).

import { Prisma } from '@prisma/client';

type ModelName = Prisma.ModelName;

type FieldName<T> = string & keyof T;

export const modelName = (model: ModelName) => Prisma.sql([`"${model}"`]);

export const fieldName = <T>(name: FieldName<T>) => Prisma.sql([`"${name}"`]);

Then you can do things like this.

const User = modelName(Prisma.ModelName.User);
const Post = modelName(Prisma.ModelName.Post);

const Id = columnName<User | Post>("id");
const UserId = columnName<Post>("userId");
const Name = columnName<User>("name");
const Message = columnName<Post>("message");

const userName = "johndoe";

type YourType = {
  id: string;
  message: string;
  author: string;
}

prisma.queryRaw<YourType[]>`
SELECT
  ${Post}.${Id},
  ${Post}.${Message},
  ${User}.${Name} AS "author",
FROM ${Post}
JOIN ${User} ON ${Post}.${UserId} = ${User}.${Id}
WHERE ${User}.${Name} = ${userName};
`