SQL injection in Prisma – a practical guide to database hardening

SQL injection in Prisma is one of the most common vulnerabilities that can threaten data integrity and application confidentiality. In this article we present a brief threat model, Prisma’s operating mechanisms, a comparison of vulnerable and corrected code, a mitigation checklist, and references to recognized standards such as OWASP Top 10, CWE, and the least‑privilege principle.

Threat model – how SQL injection occurs

An attacker injects their own SQL code into input data that the application passes to the database without proper validation or parameterization. In the context of Prisma this most often happens using the queryRaw or executeRaw methods, which allow manual insertion of query fragments. If we pass unchecked strings to them, we open the door to query manipulation.

Basic scenario:

• The user provides a value in a form (e.g., product name).
• The application builds an SQL query, inserting that value without parameterization.
• The attacker adds an OR 1=1 or DROP TABLE statement, leading to unauthorized access or data destruction.

Protection mechanism in Prisma – parameterization and typing

Prisma ORM generates queries by default using prepared statements. This means values are sent as parameters rather than being embedded directly into the SQL text. In TypeScript we also benefit from types that limit the possibility of injecting unwanted code.

Key mechanisms:

• Query Builder – methods such as findMany, create, or update build queries safely.
• Parameterized RAW – prisma.$queryRaw`SELECT * FROM User WHERE email = ${email}` uses tagged interpolation, which automatically sanitizes the variable.
• Prisma Types – define data structures and enforce correct input formats.

Example: vulnerable code vs. corrected code

Below we compare two snippets – first the unsafe version, then the safe one.

// --- Vulnerable code ---
const userInput = req.body.username; // data from the form
const raw = `SELECT * FROM "User" WHERE username = '${userInput}'`;
const result = await prisma.$queryRaw(raw);

In this example userInput is inserted directly into the string, allowing arbitrary SQL injection.

// --- Corrected code ---
const userInput = req.body.username;
// using tagged interpolation ensures parameterization
const result = await prisma.$queryRaw`SELECT * FROM "User" WHERE username = ${userInput}`;

Tagged interpolation (prisma.$queryRaw`...`) turns ${userInput} into a bound parameter, eliminating injection risk.

Mitigation checklist – what to implement in a project

• Use only Prisma API methods (findUnique, create, etc.) instead of manual RAW queries.
• If RAW is necessary, apply tagged interpolation or pass parameters as a second argument (prisma.$queryRaw(sql, params)).
• Enable input‑schema validation (e.g., zod or class-validator) before sending data to the data‑access layer.
• Restrict the privileges of the DB account used by the application – follow the least‑privilege principle.
• Monitor SQL logs and detect unexpected patterns (e.g., sudden DROP usage).

References to standards – OWASP, CWE and least‑privilege

OWASP Top 10 lists A03:2021 – Injection as one of the most severe categories. The CWE catalog entry for this issue is CWE‑89: SQL Injection. By implementing the practices above, we satisfy the requirements of both standards.

The least‑privilege principle requires that the database account used by Prisma has only the necessary permissions (SELECT, INSERT, UPDATE, DELETE on required tables). Avoid granting the SUPERUSER or DBA role to a production application.

“Secure queries are not an add‑on, they are the foundation – code that cannot be injected does not need later fixes.”

Typical mistakes and trade‑offs in practice

Common pitfalls:

• Using prisma.$executeRaw without parameterization – easy to overlook, especially during migrations.
• Dynamically building table or column names – Prisma cannot parameterize identifiers, so they must be validated against a whitelist.
• Skipping input validation in favor of “trusting” the frontend – leads to unforeseen attack vectors.

Trade‑offs:

• In some cases it is necessary to use RAW for complex reports. In such situations, it is advisable to separate this layer into its own service and wrap it with an additional sanitization layer.
• If the application must handle ad‑hoc queries, consider using a whitelist of allowed fields and operators instead of freely inserting text.

Practical example – implementation checklist

// checklist.ts – sample implementation in TypeScript
import { PrismaClient } from '@prisma/client';
import { z } from 'zod';

const prisma = new PrismaClient();

// 1. Input validation schema
const userSchema = z.object({
  username: z.string().min(3).max(30),
  email: z.string().email(),
});

export async function createUser(payload: unknown) {
  // 2. Validation
  const data = userSchema.parse(payload);

  // 3. Safe use of Prisma API – not RAW
  return await prisma.user.create({
    data: {
      username: data.username,
      email: data.email,
    },
  });
}

// 4. RAW example with parameterization
export async function findUsersByDomain(domain: string) {
  const safeDomain = domain.replace(/[^a-zA-Z0-9.-]/g, ''); // additional sanitization
  return await prisma.$queryRaw`
    SELECT * FROM "User" WHERE email LIKE ${'%@' + safeDomain}`;
}

The above code demonstrates how to combine schema validation, safe API usage, and parameterized RAW queries in situations where they are required.

Conclusion and invitation to collaborate

By applying the described practices – parameterization, typing, validation, and privilege restriction – you eliminate the risk of SQL injection in projects built with Prisma and TypeScript, while also meeting OWASP Top 10 and CWE‑89 requirements. If you need a security audit, database hardening assistance, or help implementing best practices, get in touch with Coderia.it – our team of specialists will help you build a robust and secure backend architecture.