Revisit prisma.$queryRaw("...") vs. prisma.$queryRaw template literal

[matthewmueller]

Problem

Right now it's really easy to write a raw query that's susceptible to SQL injections

// Raw query. Susceptible to SQL injections! 
prisma.$queryRaw(`SELECT \* FROM User WHERE email = ${email}`);

// Prepared query. Not susceptible to SQL injections.
prisma.$queryRaw`SELECT \* FROM User WHERE email = ${email}`;

The difference is in the ( and ). This is far too subtle and there's no warning about which is which.

Suggested solution

• Investigate: Can we differentiate between prisma.$queryRaw("...") and the prisma.$queryRaw template literal?

If so,

• Rename prisma.$queryRaw("...") to something else. Perhaps $queryDangerouslyRaw.

  I think there are since there are places where you can't use a prepared statement (e.g. CREATE TABLE "${table}"). If so, I don't think we want to disallow the capability entirely.

• Consider writing a codemod for npx @prisma/codemods to rename function calls (not template literals) to the new method

If not,

• Consider other alternatives for clearly separating these two behaviors

Other Ideas

• Maybe it's possible for our VS Code extension to detect it and return a warning.
• Or maybe it's possible at runtime to detect that it's used the wrong way and throw an error?

[pantharshit00]

Related: #7095

[matthewmueller]

Internal Note: we consider this breaking change before the next major release.

[matthewmueller]

The design of this change is the following:

Narrowed $queryRaw (breaking change)

• $queryRaw now only supports a template literal.
• This is always a parameterized query and therefore not susceptible to SQL injections.
• The signature is prisma.$queryRaw`query`.
• For example, prisma.$queryRaw`select * from users where email = ${email}`

Added $queryRawUnsafe

• $queryRawUnsafe is a new function that can be a string or template string.
• The signature is prisma.$queryRawUnsafe(query, args...).
• This allows you to pass a raw template string in. For example, prisma.$queryRawUnsafe("create table ${table}").
• You could still have a parameterized query. For example, prisma.$queryRawUnsafe("select * from users where email = $1", "alice@prisma.io"). This is mostly to make the upgrade path easier.

$queryRaw to $queryRawUnsafe codemod

We plan to add a codemod to https://github.com/prisma/codemods that will turn all $queryRaw functions into $queryRawUnsafe functions. It will ignore $queryRaw tagged templates, since those work as is.

[Sytten]

Discussed it with @millsp on slack, but basically I got into the code of a pretty large customer that uses prisma and I found a lot of sql injections due to improper use of prisma raw queries. So I support that 100%.

Basically I want the following to fail:

let query = `SELECT * FROM table`
if (id) {
  query += `WHERE id = ${id}`
}
prisma.$queryRaw(query)

But this should still work:

let query = sql`SELECT * FROM table`
if (id) {
  query = sql`${query} WHERE id = ${id}`
}
prisma.$queryRaw(query)

[millsp]

Yes, the last call will work while the first one will fail.

[matthewmueller]

Closing since we've learned this will be possible.

Remaining tasks are tracked in: #8628