SQL Injection Prevention with Prisma

Prisma's query builder prevents most SQL injection by default. But raw queries, dynamic filters, and $queryRawUnsafe can quietly reintroduce the vulnerabilities you thought you'd left behind. Below: 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