Prisma queryRaw throws error when using template string variable in table name

Get Help and Help Others
1

Below are what I have tried,

  let result = await db.$queryRaw`SELECT * from "Person"` // This works fine
  console.log(result)

  const model = 'Person'
  result = await db.$queryRaw`SELECT * from "${model}"` // This throws the error

The second $queryRaw throws the following error.

Invalid `prisma.queryRaw()` invocation:

    Raw query failed. Code: `42P01`. Message: `relation "$1" does not exist`

Below are Prisma logs from two queryRaw runs.

{"level":20,"time":1649915257507,"pid":6135,"hostname":"MBP","prisma":{"clientVersion":"3.11.1"},"timestamp":"2022-04-14T05:47:37.507Z","query":"SELECT * from \"Person\"","params":"[]","duration":2,"target":"quaint::connector::metrics","msg":"Query performed in 2 msec"}
  console.log
    [
      {
        id: 29,
        name: 'Rob',
        email: 'rob@example.com',
        age: 30,
        roles: null
      }
    ]

{"level":20,"time":1649915257513,"pid":6135,"hostname":"Chens-MBP","prisma":{"clientVersion":"3.11.1"},"timestamp":"2022-04-14T05:47:37.513Z","query":"SELECT * from \"$1\"","params":"[\"Person\"]","duration":1,"target":"quaint::connector::metrics","msg":"Query performed in 1 msec"}

As we can see, the $1 parameter is set correctly in the second queryRaw run. But for some unknown reasons, it throws the relation "$1" does not exist error.

1 Like
2

This issue is also reported to Prisma, Prisma queryRaw throws error when using template string variable in table name · Discussion #12817 · prisma/prisma · GitHub

1 Like
3

Hi, @chenliu9. Unfortunately, that’s not a valid “Tagged template” replacement - they work when replacing items like parameters in where clauses.

Please see: Using variables in the Prisma docs Raw queries

Note : You cannot pass a table or column name into a tagged template placeholder. For example, you cannot SELECT ? and pass in * or id, name based on some condition.

Perhaps you could share what you are trying to be dynamic and there may be alternatives.

4

Hi, @dthyresson thanks for the reply!

I’m trying to write a service that takes the table name as a parameter, and the service itself calls queryRaw to run a heavily optimized query.

I’m able to do plain string concatenation, but wondering how I could pass the plain string to queryRaw.

5

As I noted, don’t think you can do that with Prisma tagged templated replacements because they use GitHub - blakeembrey/sql-template-tag: ES2015 tagged template string for preparing SQL statements, works with `pg`, `mysql`, and `sqlite` underneath – and the intent here is to query the query safe from SQL injection and other issues. So, you are limited in how and where you can replace.

That said – and I don’t advise this due to its “unsafe” nature and potential for SQL injection – is that Prisma does have

The $queryRawUnsafe method allows you to pass a raw string (or template string) to the database.

But!

By using this method with user inputs (i.e. SELECT * FROM table WHERE columnx = ${userInput}), you open up the possibility for SQL injection attacks. SQL injection attacks can expose your data, be it confidential or otherwise sensitive, to being modified, or even destroyed.

We strongly advise that you use the $queryRaw query instead. For more information on SQL injection attacks, see the OWASP SQL Injection guide.

So, you use that at your own discretion and please make 100% sure that whatever that param is … is safe to use in a query.

6

@dthyresson thanks for the detailed reply. It really helped!

I will see if I can find a way to not use queryRaw.

7

It’s not a big deal to use queryRaw if you can statically know the table value doesn’t depend on user input to an extent that creates a vulnerability

If you have user input in the query you can have multiple functions that inline the table

You can also use a separate injection checker with queryRaw, or just write code that proves it’s safe in context, like if the user input is supposed to be a single int that isn’t valid SQL, if it’s a continuous alphanumeric string you also can’t do anything with that in an injection