SQL Injection Prevention with ORMs: Why Prisma and TypeORM Still Need Your Help

SQL injection was first documented in 1998 and ranked #3 in OWASP Top 10 2021. Twenty-seven years later, it remains one of the most exploited vulnerabilities. The reason is not ignorance — most developers know what SQL injection is. The reason is complacency: 'I use an ORM, so I am safe.' That is partially true and dangerously false. ORMs like Prisma, TypeORM, and Sequelize prevent injection in standard query methods, but they all provide raw query escape hatches that bring the vulnerability back if used incorrectly. I test for SQL injection in every ERP system I build, including reviewing all raw query usage in the codebase.

How ORMs Protect You (and Where They Do Not)

Prisma's query methods (findMany, findUnique, create, update, delete) use parameterized queries internally — user input goes into parameter slots, never into the SQL string itself. This means `prisma.user.findMany({ where: { email: userInput } })` is safe even if userInput contains SQL characters. Where you lose protection: Prisma's $queryRaw and $executeRaw template literal tags, TypeORM's QueryBuilder with concatenated strings, Sequelize's raw queries, and any time you build a WHERE clause string from user input and pass it to a raw query function.

The Dangerous Patterns

The injection patterns I find in code reviews: (1) String interpolation in $queryRaw — `prisma.$queryRaw('SELECT * FROM users WHERE email = ' + email)` is injectable. The safe version uses tagged template literals: `prisma.$queryRaw\`SELECT * FROM users WHERE email = ${email}\`` which Prisma parameterizes automatically. (2) Dynamic ORDER BY — ORMs typically cannot parameterize column names. `ORDER BY ${userInput}` is injectable. Validate column names against a whitelist of allowed sort columns. (3) Dynamic table names — never let user input specify a table name. Validate against an explicit allowlist.

// DANGEROUS — string concatenation in raw query
const email = req.body.email // attacker sends: ' OR '1'='1
await prisma.$queryRaw('SELECT * FROM users WHERE email = ' + email)
// → dumps entire users table

// SAFE — tagged template literal (auto-parameterized by Prisma)
await prisma.$queryRaw`SELECT * FROM users WHERE email = ${email}`

// DANGEROUS — dynamic ORDER BY with user input
const sortColumn = req.query.sort // attacker sends: id; DROP TABLE users--
await prisma.$queryRaw`SELECT * FROM invoices ORDER BY ${sortColumn}`

// SAFE — allowlist validation for column names
const ALLOWED_SORT_COLUMNS = ['createdAt', 'amount', 'invoiceNumber', 'status'] as const
type SortColumn = typeof ALLOWED_SORT_COLUMNS[number]

function validateSortColumn(col: string): SortColumn {
  if (!ALLOWED_SORT_COLUMNS.includes(col as SortColumn)) {
    throw new BadRequestException(`Invalid sort column: ${col}`)
  }
  return col as SortColumn
}

const safeSort = validateSortColumn(req.query.sort as string)
// Use Prisma's safe orderBy instead of raw SQL
const invoices = await prisma.invoice.findMany({
  orderBy: { [safeSort]: req.query.order === 'desc' ? 'desc' : 'asc' },
  where: { userId: req.user.id }
})

// Testing with sqlmap
// sqlmap -u 'https://staging.api.com/invoices?sort=createdAt' \
//   --cookie='Authorization=Bearer <token>' \
//   --level=3 --risk=2 --batch

Safe Raw Query Patterns in Prisma

When you genuinely need raw SQL (complex window functions, database-specific features, performance-critical queries), use Prisma's tagged template literal syntax which automatically parameterizes all interpolated values. For dynamic column names and table names that cannot be parameterized, implement strict allowlist validation — never use blocklist validation for security-critical code. The allowlist approach means only explicitly permitted values pass through, regardless of what clever encoding an attacker uses.

Testing for SQL Injection

I run sqlmap against every staging API endpoint that accepts user input before release. The basic scan: `sqlmap -u 'https://staging.example.com/api/users?id=1' --cookie='auth=<token>' --level=3 --risk=2`. For POST endpoints: `sqlmap -u 'https://staging.example.com/api/search' --data='query=test' --method=POST`. sqlmap automatically tests dozens of injection techniques and payloads. If it finds anything, the endpoint is not deploying. This takes 5-10 minutes per endpoint and has caught injection vulnerabilities in raw query code that passed code review.

Second-Order SQL Injection

First-order injection (input directly into a query) is what most developers test for. Second-order injection (input stored safely, then retrieved and used unsafely later) is less well understood. Example: a user registers with username `admin'--`. The registration stores this safely via parameterized query. Later, an admin panel feature retrieves the username and uses it in a raw query string for a report. The injection fires on the second use, not the first. Defense: parameterize ALL queries, not just those that take direct user input. Treat all data from the database as untrusted when building queries.

Beyond Injection: Database Security Fundamentals

SQL injection prevention is one layer of database security. Also implement: principle of least privilege for database users (the API user should not have DROP TABLE or CREATE USER permissions), separate database users per service (not one superuser for all services), connection pooling with limits to prevent connection exhaustion DoS, query timeout limits (kill queries running over 30 seconds), and audit logging for sensitive tables (enable PostgreSQL's pgaudit extension for compliance logging on financial tables).

-- PostgreSQL security configuration
-- 1. Dedicated app user with minimal privileges
CREATE USER app_user WITH PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE erp_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- Note: no DDL permissions (no CREATE, DROP, ALTER)

-- 2. Enable audit logging for sensitive tables
CREATE EXTENSION IF NOT EXISTS pgaudit;
-- postgresql.conf:
-- pgaudit.log = 'write, ddl'
-- pgaudit.log_relation = on

-- 3. Row-level security for multi-tenant data
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::UUID);

-- 4. Set statement timeout
ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

My Database Security Checklist

What I verify on every deployment: all user inputs go through ORM parameterized queries or validated against allowlists before raw query use, database user has SELECT/INSERT/UPDATE/DELETE only (no DDL), connection string in environment variable (not in code), pg_hba.conf limits database access to application server IP only, pgaudit enabled on financial tables, query timeout set to 30 seconds, connection pool max set to avoid overwhelming the database, regular automated backups tested with restore verification. Running sqlmap against staging before every release is the final verification step.

Related Articles

• OWASP Top 10 Developer Checklist
• API Security in NestJS Production