SQL Injection Prevention with Prisma

Prisma's query builder prevents most SQL injection by default. But raw queries, dynamic filters, and $queryRawUnsafe can reintroduce the same vulnerabilities you thought you left behind. This guide covers the safe patterns and the dangerous ones.

How Prisma Prevents SQL Injection

Prisma's standard query methods generate parameterized SQL:

// This is safe — Prisma parameterizes the input
const user = await prisma.user.findUnique({
  where: { email: userInput },
});

// Generated SQL: SELECT * FROM "User" WHERE "email" = $1
// The value is passed as a parameter, not concatenated

No matter what userInput contains — including '; DROP TABLE users; -- — it is treated as a value, not SQL.

Reference: Prisma — SQL Injection Prevention

The Danger: $queryRawUnsafe

Prisma provides $queryRawUnsafe for cases where you need fully dynamic SQL. It does no parameterization:

// DANGEROUS — direct string concatenation
const users = await prisma.$queryRawUnsafe(
  `SELECT * FROM "User" WHERE name = '${userInput}'`
);

If userInput is ' OR '1'='1, this returns all users. If it is '; DROP TABLE "User"; --, your table is gone.

Never use $queryRawUnsafe with user input. Use $queryRaw with tagged templates instead.

Safe Raw Queries with $queryRaw

$queryRaw uses tagged template literals. Values interpolated with ${} are automatically parameterized:

// SAFE — Prisma parameterizes the interpolated values
const users = await prisma.$queryRaw`
  SELECT * FROM "User"
  WHERE name = ${userInput}
  AND age > ${minAge}
`;

Critical: this only works with the tagged template syntax. Do not construct the string separately:

// BAD — this defeats the parameterization
const query = `SELECT * FROM "User" WHERE name = '${userInput}'`;
const users = await prisma.$queryRaw(Prisma.raw(query));

Reference: Prisma — Raw Queries

Dynamic Column Names and Table Names

You cannot parameterize column names, table names, or SQL keywords. These require allowlists:

// Column names cannot be parameterized
const ALLOWED_SORT_COLUMNS = ["name", "email", "createdAt"] as const;
type SortColumn = typeof ALLOWED_SORT_COLUMNS[number];

function buildOrderBy(column: string): SortColumn {
  if (!ALLOWED_SORT_COLUMNS.includes(column as SortColumn)) {
    throw new Error(`Invalid sort column: ${column}`);
  }
  return column as SortColumn;
}

// Use the validated column
const validColumn = buildOrderBy(userSortInput);
const users = await prisma.user.findMany({
  orderBy: { [validColumn]: "asc" },
});

For raw SQL:

const ALLOWED_COLUMNS = new Set(["name", "email", "created_at"]);

function getSortQuery(column: string, direction: string) {
  if (!ALLOWED_COLUMNS.has(column)) throw new Error("Invalid column");
  if (direction !== "asc" && direction !== "desc") throw new Error("Invalid direction");

  // Column name from allowlist, not user input
  return prisma.$queryRaw`
    SELECT * FROM "User"
    ORDER BY ${Prisma.raw(`"${column}" ${direction}`)}
  `;
}

Prisma.raw() inserts raw SQL. Only use it with validated values from an allowlist.

Reference: Prisma — Prisma.raw()

Dynamic WHERE Filters

Building dynamic filters from user input is common. Use Prisma's query builder, not string concatenation:

import { Prisma } from "@prisma/client";

function buildUserFilter(params: {
  name?: string;
  email?: string;
  role?: string;
  minAge?: number;
}): Prisma.UserWhereInput {
  const where: Prisma.UserWhereInput = {};

  if (params.name) {
    where.name = { contains: params.name, mode: "insensitive" };
  }
  if (params.email) {
    where.email = params.email;
  }
  if (params.role) {
    // Validate against known roles
    const VALID_ROLES = ["user", "admin", "moderator"];
    if (!VALID_ROLES.includes(params.role)) {
      throw new Error("Invalid role");
    }
    where.role = params.role;
  }
  if (params.minAge !== undefined) {
    where.age = { gte: params.minAge };
  }

  return where;
}

const users = await prisma.user.findMany({
  where: buildUserFilter(req.query),
});

This is safe because Prisma parameterizes all values in the generated SQL.

Dynamic IN Clauses

// SAFE — Prisma handles arrays
const users = await prisma.user.findMany({
  where: {
    id: { in: userProvidedIds }, // parameterized
  },
});

// SAFE with $queryRaw
const users = await prisma.$queryRaw`
  SELECT * FROM "User"
  WHERE id = ANY(${userProvidedIds})
`;

Pagination

Never let users control LIMIT and OFFSET without validation:

function safePagination(page?: number, pageSize?: number) {
  const safePage = Math.max(1, Math.floor(Number(page) || 1));
  const safePageSize = Math.min(100, Math.max(1, Math.floor(Number(pageSize) || 20)));

  return {
    skip: (safePage - 1) * safePageSize,
    take: safePageSize,
  };
}

const users = await prisma.user.findMany({
  ...safePagination(req.query.page, req.query.pageSize),
});

Prisma Middleware for Logging

Log all queries in development and audit sensitive queries in production:

prisma.$use(async (params, next) => {
  const start = Date.now();
  const result = await next(params);
  const duration = Date.now() - start;

  if (duration > 1000) {
    console.warn(`Slow query: ${params.model}.${params.action} took ${duration}ms`);
  }

  return result;
});

Checklist

• Standard Prisma queries used wherever possible (auto-parameterized)
• $queryRawUnsafe never used with user input
• $queryRaw used with tagged template literals only
• Column and table names validated against allowlists
• Prisma.raw() only used with pre-validated values
• Dynamic filters built using Prisma's query builder types
• Pagination values validated and capped
• Query logging enabled for performance monitoring

Related Guides

• How to Secure a Next.js Application
• API Key Security Best Practices