Prisma Raw Query Leads to SQL Injection? Yes and No

I recently talked with Yoni Goldberg and Avishai Ish-Shalom about the role of application security with ORMs, Query Builders, and Raw SQL.

We discussed the security implications of using abstractions such as Prisma, Sequelize and others and how they can help prevent SQL injection. Or wait, can they? The nuances matter and it is easy to get it wrong.

”Or 1=1” SQL Injection

SQL injection is a code injection technique that allows an attacker to craft and execute SQL statements. These statements can be used to manipulate the database, exfiltrate data, or even delete data, depending on permissions and the database schema.

The classic OR 1=1 SQL injection attack is a simple example of how an attacker can manipulate a SQL query to return all rows from a table, regardless of the WHERE clause. This is a textbook example of a SQL injection attack that can be used to bypass authentication mechanisms. Here’s an example of a vulnerable query:

SELECT * FROM users WHERE username = '${req.body.username}' AND password = '${req.body.password}';

To put the OR 1=1 attack into context, consider the following example which sets req.body.username to admin' OR 1=1 --, which results in the following SQL query statement:

SELECT * FROM users WHERE username = 'admin' OR 1=1 --' AND password = '${req.body.password}';

The database evaluates this query which results in the WHERE clause always being true, returning all rows from the users table.

FAQ: Raw SQL vs ORM

When it comes to SQL injection, the use of ORMs and query builders can help prevent SQL injection attacks by automatically escaping user input. This is because ORMs and query builders use parameterized queries, which separate the SQL query from the user input. However, ORMs are just an added abstraction layer and its own code can still be prone to SQL injection attacks, if unhandled correctly. Numerous examples exist of SQL injection vulnerabilities in Sequelize, TypeORMs and other third-party libraries.

Prisma Raw Query

Let’s start with Prisma to unfold the connection between ORMs and SQL injections.

Prisma is a modern database toolkit that makes it easy to work with databases in Node.js and TypeScript. It is a popular choice for developers who want to use a type-safe ORM for their Node.js applications.

Josh Grossman published an article and a poll to ask the community whether the following code is vulnerable to SQL injection or not:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

const users = await prisma.$queryRaw`
SELECT * FROM "User" WHERE email = ${untrustedInput};
`

At first glance, I could see exactly why both developers and security practitioners.

This code uses a specific JavaScript code pattern known as a tagged template literal. The ${untrustedInput} is a placeholder for the value of the untrustedInput variable. While this looks like a regular string interpolation, it is actually a special syntax that allows you to define a function (in this case $queryRaw is the function) that handles the data in the placeholder variable.

In the above code, the Prisma SDK will automatically escape the value of untrustedInput to prevent SQL injection attacks.

While seemingly a classic string concatenation example that is vulnerable to SQL injection, the Prisma SDK is smart enough to handle the escaping for you.

Another reason why this code is confusing is the use of the raw keyword in the function name. The queryRaw function would suggest that the query is executed as a raw SQL query, which is typically vulnerable to SQL injection attacks. This is however, not the case, as we just explained.

Prisma Raw Query queryRawUnsafe is an SQL injection waiting to happen

The Prisma SDK has yet another function, related to raw query handling, called queryRawUnsafe. This function allows you to execute raw SQL queries without any escaping (!!!).

The queryRawUnsafe function is explicitly designed for use cases where you need to execute raw SQL queries and you are confident that the input is safe and you are completely opting-out of Prisma’s query building and escaping mechanisms.

Let’s look at this one in practice:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

const users = await prisma.$queryRawUnsafe(`
SELECT * FROM "User" WHERE email = ${untrustedInput};
`)

That’s right there is an SQL injection vulnerability in the code above. The untrustedInput variable is not escaped and can be manipulated by an attacker to execute arbitrary SQL queries.

To make things complex, there’s another variation of it that expects parameters and is actually safe. How can you tell the difference? how can you ensure your newly onboarded team members don’t make this mistake? Good questions 🤷

How to prevent SQL injection

The best way to prevent SQL injection attacks is to use parameterized queries. Make sure your code or your abstraction’s code (the ORM or the library you use) is using the server-side prepared statements option and not a client-side escaping mechanism.

Parameterized queries tell the database engine to separate the SQL query from the user input so it knows to treat them in different contexts, thus preventing attackers from manipulating the query to execute arbitrary SQL statements.