Prisma and PostgreSQL vulnerable to NoSQL injection? A surprising security risk explained

Introduction

Imagine you’re building a blogging web app using Prisma. You write a simple query to authenticate users based on their provided email and password:

1const user = await prisma.user.findFirst({
2  where: { email, password },
3});

Looks harmless, right? But what if an attacker sends password = { "not": "" }? Instead of returning the User object only when email and password match, the query always returns the User when only the provided email matches.

This vulnerability is known as operator injection, but it’s more commonly referred to as NoSQL injection. What many developers don’t realize is that despite strict model schemas some ORMs are vulnerable to operator injection even when they’re used with a relational database such as PostgreSQL, making it a more widespread risk than expected.

In this post, we’ll explore how operator injection works, demonstrate exploits in Prisma ORM, and discuss how to prevent them.

Understanding Operator Injection

To understand operator injection in ORMs, it’s interesting to first look at NoSQL injection. MongoDB introduced developers to an API for querying data using operators such as $eq, $lt and $ne. When user input is passed blindly to MongoDB's query functions, there exists a risk of NoSQL injection.

Popular ORM libraries for JavaScript started offering a similar API for querying data and now almost all major ORMs support some variation of query operators, even when they don’t support MongoDB. Prisma, Sequelize and TypeORM have all implemented support for query operators for relational databases such as PostgreSQL.

Exploiting Operator Injection in Prisma

Prisma query functions that operate on more than one record typically support query operators and are vulnerable to injection. Example functions include findFirst, findMany, updateMany and deleteMany. While Prisma does validate the model fields referenced in the query at runtime, operators are a valid input for these functions and therefor aren’t rejected by validation.

One reason why operator injection is easy to exploit in Prisma, is the string-based operators that are offered by the Prisma API. Some ORM libraries have removed support for string-based query operators because they are so easily overlooked by developers and easy to exploit. Instead, they force developers to reference custom objects for operators. As these objects cannot be readily de-serialized from user input, the risk of operation injection is greatly reduced in these libraries.

Not all query functions in Prisma are vulnerable to operator injection. Functions that select or mutate a single database record typically do not support operators and throw a runtime error when an Object is provided. Apart from findUnique, the Prisma update, delete and upsert functions also do not accept operators in their where filter.

1  // This query throws a runtime error:
2  // Argument `email`: Invalid value provided. Expected String, provided Object.
3  const user = await prisma.user.findUnique({
4    where: { email: { not: "" } },
5  });

‍

Best Practices to Prevent Operator Injection

1. Cast User Input to Primitive Data Types

Typically casting input to primitive data types such as strings or numbers suffices to prevent attackers from injecting objects. In the original example, casting would look as follows:

1  const user = await prisma.user.findFirst({
2    where: { email: email.toString(), password: password.toString() },
3  });

2. Validate User Input

While casting is effective, you might want to validate the user input, to ensure that the input meets your business logic requirements.

There are many libraries for server-side validation of user input, such as class-validator, zod and joi. If you’re developing for a web application framework such as NestJS or NextJS, they likely recommend specific methods for user input validation in the controller.

In the original example, zod validation might look as follows:

1import { z } from "zod";
2
3const authInputSchema = z.object({
4  email: z.string().email(),
5  password: z.string().min(8)
6});
7
8const { email, password } = authInputSchema.parse({email: req.params.email, password: req.params.password});
9
10const user = await prisma.user.findFirst({
11  where: { email, password },
12});

3. Keep your ORM updated

Stay updated to benefit from security improvements and fixes. For example, Sequelize disabled string aliases for query operators starting from version 4.12, which significantly reduces susceptibility to operator injection.

Conclusion

Operator injection is a real threat for applications using modern ORMs. The vulnerability stems from the ORM API design and isn’t related to the database type in use. Indeed, even Prisma combined with PostgreSQL may be vulnerable to operator injection. While Prisma offers some built-in protection against operator injection, developers must still practice input validation and sanitization to ensure application security.

Appendix: Prisma schema for User model

1// This is your Prisma schema file,
2// learn more about it in the docs: https://pris.ly/d/prisma-schema
3
4generator client {
5  provider = "prisma-client-js"
6}
7
8datasource db {
9  provider = "postgresql"
10  url      = env("DATABASE_URL")
11}
12
13// ...
14
15model User {
16  id       Int      @id @default(autoincrement())
17  email    String   @unique
18  password String
19  name     String?
20  posts    Post[]
21  profile  Profile?
22}