Today’s Lesson
Security for Legal SaaS — Episode 8: SQL Injection and ORM Safety
The Attack That Won’t Die
SQL injection was first documented in 1998. Twenty-eight years later, it remains OWASP’s A03 (Injection) and continues to cause catastrophic breaches. The MOVEit breach of 2023 — CVE-2023-34362, CVSS 9.8 — was a SQL injection that compromised 2,773 organisations. A single injection point. Ninety-five million individuals’ data exposed.
For legal SaaS, SQL injection is existential. Your database contains privileged communications, litigation strategy, client identity, and billing records. An attacker who achieves SQL injection doesn’t just read data — they can modify records, escalate privileges, and in some configurations execute operating system commands.
Key stat: HackerOne's 2023 report found SQL injection in their top 10 most impactful vulnerability categories, with median bounty payouts reflecting its severity.
How SQL Injection Works
The Vulnerable Pattern: String Concatenation
SQL injection exploits the mixing of code and data in database queries. When user input is concatenated directly into a SQL string, the database cannot distinguish between the developer’s intended query structure and attacker-supplied SQL.
# VULNERABLE — never do this
query = f"SELECT * FROM matters WHERE citation = '{user_input}'"If user_input is ' OR '1'='1:
SELECT * FROM matters WHERE citation = '' OR '1'='1'The query now returns every row in the table. The attacker has bypassed all access control.
Escalation: From Data Theft to Full Compromise
| Injection Type | Technique | Impact |
|---|---|---|
| Union-based | UNION SELECT to extract data from other tables |
Read any table: users, credentials, privileged documents |
| Error-based | Force database errors that leak information | Enumerate table names, column types, database version |
| Blind (boolean) | Ask true/false questions, observe response differences | Extract data character by character |
| Blind (time-based) | Use SLEEP() or WAITFOR to infer data |
Works even when no output difference is visible |
| Out-of-band | DNS or HTTP requests from the database server | Exfiltrate data through side channels |
| Stacked queries | Terminate query and start a new one | DROP TABLE, INSERT, UPDATE — full write access |
PortSwigger’s SQL injection tutorial demonstrates that even blind injection — where the attacker never sees query output directly — can extract entire databases given enough requests.
Legal SaaS scenario: An attacker finds a blind SQL injection in your case search endpoint. They can't see results directly, but they ask: "Is the first character of the admin's password hash greater than 'M'?" The response time differs by 5 seconds (time-based blind). In 2,000 requests — completed in minutes by automated tools — they have the full admin password hash. With admin access, they read every privileged document in the system.
Parameterised Queries: The Primary Defence
OWASP’s SQL Injection Prevention Cheat Sheet identifies parameterised queries (prepared statements) as the primary defence. They work by separating SQL structure from data at the protocol level:
# SAFE — parameterised query
cursor.execute(
"SELECT * FROM matters WHERE citation = %s AND tenant_id = %s",
(user_input, current_tenant_id)
)The database engine receives the query structure and the parameters separately. The parameter is always treated as data — never as SQL code — regardless of its content. This defence is complete against first-order SQL injection.
What You Cannot Parameterise
Parameters work for values in WHERE clauses, INSERT data, and UPDATE sets. They do not work for:
- Table names
- Column names
- ORDER BY / GROUP BY directions
- SQL keywords and operators
For these, use strict allowlists:
ALLOWED_SORT_COLUMNS = {'filed_date', 'citation', 'status', 'created_at'}
ALLOWED_DIRECTIONS = {'ASC', 'DESC'}
if sort_column not in ALLOWED_SORT_COLUMNS:
raise ValueError("Invalid sort column")
if direction not in ALLOWED_DIRECTIONS:
raise ValueError("Invalid sort direction")
query = f"SELECT * FROM matters ORDER BY {sort_column} {direction}"ORM Safety and Its Limits
Modern ORMs — Prisma, SQLAlchemy, TypeORM, Django ORM — use parameterised queries by default. When you use the ORM’s query builder, you’re generally safe:
// Prisma — safe by default
const matters = await prisma.matter.findMany({
where: { courtCode: userInput, tenantId: currentTenant }
});# SQLAlchemy — safe via query builder
matters = session.query(Matter).filter(
Matter.citation == user_input,
Matter.tenant_id == current_tenant_id
).all()The Raw Query Escape Hatch
Every ORM provides a way to execute raw SQL. This is where injection returns:
// Prisma — UNSAFE raw query with string interpolation
const result = await prisma.$queryRawUnsafe(
`SELECT * FROM matters WHERE citation = '${userInput}'`
);// Prisma — SAFE raw query with parameters
const result = await prisma.$queryRaw`
SELECT * FROM matters WHERE citation = ${userInput}
`;Prisma’s documentation explicitly warns that $queryRawUnsafe accepts arbitrary strings and must never include unsanitised user input. The tagged template literal version ($queryRaw) automatically parameterises.
ORM Safety Rules
- Use the query builder for all standard operations
- If you must use raw queries, use the parameterised variant
- Never interpolate user input into raw SQL strings
- Audit raw query usage in code reviews — search for
$queryRawUnsafe,text(),.raw() - Lint rules: ESLint custom rules can flag unsafe raw query patterns
Second-Order SQL Injection
First-order injection: malicious input is immediately used in a query. Second-order injection: malicious input is stored safely, then later used unsafely in a different query.
Scenario:
1. A user registers with username: admin'--
2. The registration query uses parameterised statements — safe, value stored as-is
3. Later, an admin panel builds a query: "SELECT * FROM audit_log WHERE username = '" + stored_username + "'"
4. The stored value, retrieved from the database, is now injected into a vulnerable query
OWASP documents second-order injection as particularly insidious because the initial input point appears safe — parameterised queries protect it. The vulnerability exists in a completely different code path that reads the stored value and uses it unsafely.
Defence: Treat all data as untrusted, even data from your own database. Every query that includes any variable data — regardless of its source — must use parameterised statements.
SQL Injection Testing
Manual Testing
PortSwigger’s methodology starts with:
1. Submit a single quote ' and observe error messages
2. Submit boolean conditions (OR 1=1, OR 1=2) and compare responses
3. Submit time-delay payloads ('; WAITFOR DELAY '0:0:5'--) and observe timing
4. Submit OWASP’s SQL injection test strings from the Testing Guide
Automated Testing
| Tool | Purpose | Note |
|---|---|---|
| sqlmap | Automated SQL injection detection and exploitation | Gold standard for SQLi testing |
| Burp Suite | Proxy-based web security scanner | Comprehensive scanning including SQLi |
| OWASP ZAP | Open-source web application scanner | Free alternative to Burp |
Code-Level Prevention
Static analysis tools can identify vulnerable patterns before deployment:
- Semgrep rules for raw SQL detection
- SonarQube SQL injection detection
- CodeQL queries for taint analysis (tracking user input to SQL queries)
Database-Level Hardening
Even with perfect parameterisation, defence in depth requires database-level controls:
| Control | Purpose |
|---|---|
| Principle of least privilege | Application DB user has only SELECT/INSERT/UPDATE on required tables — never DROP, CREATE, or sys admin |
| Separate read/write credentials | Read-only endpoints use read-only DB credentials |
| Stored procedures | Encapsulate complex queries; restrict direct table access |
| Row-level security | PostgreSQL RLS enforces tenant isolation at the database level |
| Query logging | Log all queries for anomaly detection |
For multi-tenant legal SaaS, PostgreSQL Row-Level Security provides a particularly strong defence. Even if an injection occurs, the database itself enforces that queries can only return rows belonging to the current tenant — a last-resort containment.
Conclusion
SQL injection is a solved problem at the code level — parameterised queries eliminate it entirely for value-position injection. Yet it persists because developers use raw queries for “just this one complex case,” because ORMs offer unsafe escape hatches, and because second-order injection hides in code paths nobody thought to check. Use the ORM. Parameterise the exceptions. Treat stored data as untrusted. Test regularly.
Next episode: Cross-Site Scripting (XSS) — when the attacker’s code runs in your users’ browsers, with access to their sessions, their documents, and their privileged data.