Understand Prisma's built-in safety

Prisma Client methods are safe from SQL injection:

// SAFE - Prisma automatically parameterizes
const user = await prisma.user.findUnique({
  where: { email: userInput }  // userInput is safely escaped
});

const users = await prisma.user.findMany({
  where: {
    OR: [
      { name: { contains: searchTerm } },
      { email: { contains: searchTerm } }
    ]
  }
});

// Even complex queries are safe
const orders = await prisma.order.findMany({
  where: {
    userId: userId,
    status: { in: ['pending', 'processing'] },
    createdAt: { gte: new Date(startDate) }
  }
});

Handle raw queries safely

Raw queries are where vulnerabilities creep in:

// DANGEROUS - SQL injection vulnerability!
const result = await prisma.$queryRawUnsafe(
  `SELECT * FROM users WHERE email = '${userInput}'`
);

// SAFE - Use template literal with $queryRaw
const result = await prisma.$queryRaw`
  SELECT * FROM users WHERE email = ${userInput}
`;

// SAFE - For dynamic queries, use Prisma.sql
import { Prisma } from '@prisma/client';

const columns = ['id', 'name', 'email'];
const safeColumns = columns
  .filter(col => ['id', 'name', 'email'].includes(col))
  .map(col => Prisma.raw(col));

const result = await prisma.$queryRaw`
  SELECT ${Prisma.join(safeColumns)} FROM users
  WHERE id = ${userId}
`;

// For dynamic table names - use allowlist
const ALLOWED_TABLES = ['users', 'posts', 'comments'];
function getTable(tableName: string) {
  if (!ALLOWED_TABLES.includes(tableName)) {
    throw new Error('Invalid table name');
  }
  return Prisma.raw(tableName);
}

Validate inputs before queries

import { z } from 'zod';

// Define input schemas
const CreateUserSchema = z.object({
  email: z.string().email().max(255),
  name: z.string().min(1).max(100),
  role: z.enum(['user', 'admin']).default('user')
});

const UserIdSchema = z.string().uuid();

// Validate in your API handlers
async function createUser(input: unknown) {
  // Validate and sanitize input
  const data = CreateUserSchema.parse(input);

  // Now safe to use with Prisma
  return prisma.user.create({ data });
}

async function getUser(id: unknown) {
  const userId = UserIdSchema.parse(id);
  return prisma.user.findUnique({
    where: { id: userId }
  });
}

// Validate query parameters
const ListUsersSchema = z.object({
  page: z.coerce.number().int().positive().default(1),
  limit: z.coerce.number().int().min(1).max(100).default(20),
  sortBy: z.enum(['createdAt', 'name', 'email']).default('createdAt'),
  sortOrder: z.enum(['asc', 'desc']).default('desc')
});

Implement access control middleware

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

const prisma = new PrismaClient();

// Middleware to enforce access control
prisma.$use(async (params, next) => {
  const context = getRequestContext(); // Your auth context

  // Enforce user can only access their own data
  if (params.model === 'Order') {
    if (params.action === 'findMany' || params.action === 'findFirst') {
      params.args.where = {
        ...params.args.where,
        userId: context.userId  // Always filter by current user
      };
    }
    if (params.action === 'update' || params.action === 'delete') {
      params.args.where = {
        ...params.args.where,
        userId: context.userId
      };
    }
  }

  // Prevent deletion of audit logs
  if (params.model === 'AuditLog' && params.action === 'delete') {
    throw new Error('Audit logs cannot be deleted');
  }

  return next(params);
});

// Or use Prisma Client Extensions (newer approach)
const prismaWithAuth = prisma.$extends({
  query: {
    order: {
      async findMany({ args, query }) {
        const ctx = getRequestContext();
        args.where = { ...args.where, userId: ctx.userId };
        return query(args);
      }
    }
  }
});

Control field exposure

// DON'T return entire user objects to clients
async function getUser(id: string) {
  const user = await prisma.user.findUnique({
    where: { id }
  });
  return user; // Exposes password hash, internal fields, etc.
}

// DO select only needed fields
async function getUser(id: string) {
  return prisma.user.findUnique({
    where: { id },
    select: {
      id: true,
      name: true,
      email: true,
      createdAt: true
      // password, internal fields excluded
    }
  });
}

// Create reusable select objects
const publicUserSelect = {
  id: true,
  name: true,
  email: true,
  avatar: true,
  createdAt: true
} satisfies Prisma.UserSelect;

const privateUserSelect = {
  ...publicUserSelect,
  role: true,
  settings: true
} satisfies Prisma.UserSelect;

// Use in queries
const user = await prisma.user.findUnique({
  where: { id },
  select: isAdmin ? privateUserSelect : publicUserSelect
});

Secure connection string handling

// schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// NEVER do this:
// url = "postgresql://user:password@host:5432/db"

// For serverless with connection pooling
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")       // Pooled connection
  directUrl = env("DIRECT_DATABASE_URL") // For migrations
}

// .env (local development only)
DATABASE_URL="postgresql://..."

// Production: Set in your platform's environment variables
// Never commit .env files with real credentials

// Validate environment variables at startup
import { z } from 'zod';

const envSchema = z.object({
  DATABASE_URL: z.string().url(),
  NODE_ENV: z.enum(['development', 'production', 'test'])
});

envSchema.parse(process.env);

Implement query limits and pagination

// DANGEROUS - could return millions of rows
const allUsers = await prisma.user.findMany();

// SAFE - Always limit results
const users = await prisma.user.findMany({
  take: 100,  // Maximum records to return
  skip: 0     // Offset for pagination
});

// Implement cursor-based pagination (more efficient)
async function getUsers(cursor?: string, limit = 20) {
  const users = await prisma.user.findMany({
    take: limit + 1, // Fetch one extra to check if there's more
    ...(cursor && {
      cursor: { id: cursor },
      skip: 1 // Skip the cursor itself
    }),
    orderBy: { createdAt: 'desc' }
  });

  const hasMore = users.length > limit;
  const data = hasMore ? users.slice(0, -1) : users;
  const nextCursor = hasMore ? data[data.length - 1].id : null;

  return { data, nextCursor, hasMore };
}

// Enforce maximum limits
const MAX_LIMIT = 100;
function getLimit(requested?: number): number {
  if (!requested || requested < 1) return 20;
  return Math.min(requested, MAX_LIMIT);
}