Preventing SQL Injection

SQL Injection Prevention in Prisma 6

Overview

SQL injection is one of the most critical security vulnerabilities in database applications. In Prisma 6, raw SQL queries must be written using $queryRaw tagged templates for automatic parameterization. NEVER use $queryRawUnsafe with user input.

Critical Rules

1. ALWAYS Use $queryRaw Tagged Templates

const email = userInput;

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

Prisma automatically parameterizes ${email} to prevent SQL injection.

2. NEVER Use $queryRawUnsafe with User Input

const email = userInput;

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

VULNERABLE TO SQL INJECTION - attacker can inject: ' OR '1'='1

3. Use Prisma.sql for Dynamic Queries

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

const conditions: Prisma.Sql[] = [];

if (email) {
  conditions.push(Prisma.sql`email = ${email}`);
}

if (status) {
  conditions.push(Prisma.sql`status = ${status}`);
}

const where = conditions.length > 0
  ? Prisma.sql`WHERE ${Prisma.join(conditions, ' AND ')}`
  : Prisma.empty;

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

Attack Vectors and Prevention

Vector 1: String Concatenation in WHERE Clause

VULNERABLE:

const searchTerm = req.query.search;

const results = await prisma.$queryRawUnsafe(
  `SELECT * FROM "Product" WHERE name LIKE '%${searchTerm}%'`
);

Attack: '; DELETE FROM "Product"; --

SAFE:

const searchTerm = req.query.search;

const results = await prisma.$queryRaw`
  SELECT * FROM "Product" WHERE name LIKE ${'%' + searchTerm + '%'}
`;

Vector 2: Dynamic Column Names

VULNERABLE:

const sortColumn = req.query.sortBy;

const users = await prisma.$queryRawUnsafe(
  `SELECT * FROM "User" ORDER BY ${sortColumn}`
);

Attack: email; DROP TABLE "User"; --

SAFE:

const sortColumn = req.query.sortBy;
const allowedColumns = ['email', 'name', 'createdAt'];

if (!allowedColumns.includes(sortColumn)) {
  throw new Error('Invalid sort column');
}

const users = await prisma.$queryRawUnsafe(
  `SELECT * FROM "User" ORDER BY ${sortColumn}`
);

Note: Column names cannot be parameterized, so use allowlist validation.

Vector 3: Dynamic Table Names

VULNERABLE:

const tableName = req.params.table;

const data = await prisma.$queryRawUnsafe(
  `SELECT * FROM "${tableName}"`
);

Attack: User" WHERE 1=1; DROP TABLE "Session"; --

SAFE:

const tableName = req.params.table;
const allowedTables = ['User', 'Product', 'Order'];

if (!allowedTables.includes(tableName)) {
  throw new Error('Invalid table name');
}

const data = await prisma.$queryRawUnsafe(
  `SELECT * FROM "${tableName}"`
);

Vector 4: IN Clause with Arrays

VULNERABLE:

const ids = req.body.ids.join(',');

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

Attack: 1) OR 1=1; --

SAFE:

const ids = req.body.ids;

const users = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE id IN (${Prisma.join(ids)})
`;

Vector 5: LIMIT and OFFSET Injection

VULNERABLE:

const limit = req.query.limit;
const offset = req.query.offset;

const users = await prisma.$queryRawUnsafe(
  `SELECT * FROM "User" LIMIT ${limit} OFFSET ${offset}`
);

Attack: 10; DELETE FROM "User"; --

SAFE:

const limit = parseInt(req.query.limit, 10);
const offset = parseInt(req.query.offset, 10);

if (isNaN(limit) || isNaN(offset)) {
  throw new Error('Invalid pagination parameters');
}

const users = await prisma.$queryRaw`
  SELECT * FROM "User" LIMIT ${limit} OFFSET ${offset}
`;

Dynamic Query Building Patterns

Pattern 1: Optional Filters

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

interface SearchFilters {
  email?: string;
  status?: string;
  minAge?: number;
}

async function searchUsers(filters: SearchFilters) {
  const conditions: Prisma.Sql[] = [];

  if (filters.email) {
    conditions.push(Prisma.sql`email LIKE ${'%' + filters.email + '%'}`);
  }

  if (filters.status) {
    conditions.push(Prisma.sql`status = ${filters.status}`);
  }

  if (filters.minAge !== undefined) {
    conditions.push(Prisma.sql`age >= ${filters.minAge}`);
  }

  const where = conditions.length > 0
    ? Prisma.sql`WHERE ${Prisma.join(conditions, ' AND ')}`
    : Prisma.empty;

  return prisma.$queryRaw`
    SELECT * FROM "User" ${where}
  `;
}

Pattern 2: Dynamic Sorting

type SortColumn = 'email' | 'name' | 'createdAt';
type SortOrder = 'ASC' | 'DESC';

async function getUsers(sortBy: SortColumn, order: SortOrder) {
  const allowedColumns: SortColumn[] = ['email', 'name', 'createdAt'];
  const allowedOrders: SortOrder[] = ['ASC', 'DESC'];

  if (!allowedColumns.includes(sortBy) || !allowedOrders.includes(order)) {
    throw new Error('Invalid sort parameters');
  }

  return prisma.$queryRawUnsafe(
    `SELECT * FROM "User" ORDER BY ${sortBy} ${order}`
  );
}

Pattern 3: Complex JOIN with Dynamic Conditions

async function searchOrdersWithProducts(
  userId?: number,
  productName?: string,
  minTotal?: number
) {
  const conditions: Prisma.Sql[] = [];

  if (userId !== undefined) {
    conditions.push(Prisma.sql`o."userId" = ${userId}`);
  }

  if (productName) {
    conditions.push(Prisma.sql`p.name LIKE ${'%' + productName + '%'}`);
  }

  if (minTotal !== undefined) {
    conditions.push(Prisma.sql`o.total >= ${minTotal}`);
  }

  const where = conditions.length > 0
    ? Prisma.sql`WHERE ${Prisma.join(conditions, ' AND ')}`
    : Prisma.empty;

  return prisma.$queryRaw`
    SELECT o.*, p.name as "productName"
    FROM "Order" o
    INNER JOIN "Product" p ON o."productId" = p.id
    ${where}
    ORDER BY o."createdAt" DESC
  `;
}

Pattern 4: Batch Operations with Safe Arrays

async function updateUserStatuses(
  userIds: number[],
  newStatus: string
) {
  if (userIds.length === 0) {
    return [];
  }

  return prisma.$queryRaw`
    UPDATE "User"
    SET status = ${newStatus}, "updatedAt" = NOW()
    WHERE id IN (${Prisma.join(userIds)})
    RETURNING *
  `;
}

When $queryRawUnsafe is Acceptable

$queryRawUnsafe is ONLY acceptable when:

1. No user input involved (static queries only)
2. Identifiers from allowlist (column/table names validated)
3. Generated by type-safe builder (internal tools, not user data)

async function getTableSchema(tableName: 'User' | 'Product' | 'Order') {
  return prisma.$queryRawUnsafe(`
    SELECT column_name, data_type
    FROM information_schema.columns
    WHERE table_name = '${tableName}'
  `);
}

Still requires: TypeScript literal type or runtime validation against allowlist.

Migration from $queryRawUnsafe

Before:

const status = req.query.status;
const minAge = req.query.minAge;

const users = await prisma.$queryRawUnsafe(
  `SELECT * FROM "User" WHERE status = '${status}' AND age >= ${minAge}`
);

After:

const status = req.query.status;
const minAge = parseInt(req.query.minAge, 10);

const users = await prisma.$queryRaw`
  SELECT * FROM "User"
  WHERE status = ${status} AND age >= ${minAge}
`;

Testing for SQL Injection

Test Case 1: Authentication Bypass

const maliciousEmail = "' OR '1'='1";

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

Expected: Returns empty array (no match for literal string)

Test Case 2: Comment Injection

const maliciousInput = "test'; --";

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

Expected: Searches for exact string test'; --, doesn't comment out rest of query

Test Case 3: Union-Based Attack

const maliciousId = "1 UNION SELECT password FROM Admin";

const user = await prisma.$queryRaw`
  SELECT * FROM "User" WHERE id = ${maliciousId}
`;

Expected: Type error or no results (string cannot match integer id column)

Detection and Remediation

Detection Patterns

Use grep to find vulnerable code:

grep -r "\$queryRawUnsafe" --include="*.ts"
grep -r "queryRawUnsafe.*\${" --include="*.ts"
grep -r "queryRawUnsafe.*req\." --include="*.ts"

Automated Detection

import { ESLint } from 'eslint';

const dangerousPatterns = [
  /\$queryRawUnsafe\s*\([^)]*\$\{/,
  /queryRawUnsafe\s*\([^)]*req\./,
  /queryRawUnsafe\s*\([^)]*params\./,
  /queryRawUnsafe\s*\([^)]*query\./,
  /queryRawUnsafe\s*\([^)]*body\./,
];

Remediation Checklist

• Replace all $queryRawUnsafe with $queryRaw where user input exists
• Use Prisma.sql for dynamic query building
• Validate column/table names against allowlists
• Parameterize all user inputs
• Parse numeric inputs before use
• Use Prisma.join() for array parameters
• Add SQL injection test cases
• Run security audit tools

Related Skills

Security Best Practices:

• If sanitizing user inputs before database operations, use the sanitizing-user-inputs skill from typescript for input sanitization patterns

Resources

• Prisma Raw Database Access Docs
• OWASP SQL Injection Prevention Cheat Sheet
• Prisma Security Best Practices

Summary

• ALWAYS use $queryRaw tagged templates for user input
• NEVER use $queryRawUnsafe with untrusted data
• USE Prisma.sql and Prisma.join() for dynamic queries
• VALIDATE column/table names against allowlists
• TEST for common SQL injection attack vectors
• AUDIT codebase regularly for $queryRawUnsafe usage