Skip to content

SQL injection with ORMs and raw queries

Values travel apart from the SQL; names and keywords come from your own list.

Predict first

Before you read: predict

ExamHall's admin candidate search runs this, with Prisma Client:

The candidates table holds Zhou, O'Brien, Adams and Okafor. A proctor looks for Aoife O'Brien with ?q=O'Brien&sort=last_name.

What does the search return?

Code for this question
const rows = await prisma.$queryRawUnsafe(  "SELECT id, last_name FROM candidates WHERE last_name ILIKE '" + req.query.q + "%' ORDER BY " + req.query.sort);
Pick one

The 30-second version

A database parses SQL text into commands, names and values before it runs anything. Paste input into that text and its characters join the parse: a real name's apostrophe ends a value. Parameterised queries send the text with placeholders and the values apart. Placeholders hold values only, so a column or sort direction comes from an allow-list.

Values travel apart from the SQL; names and keywords come from your own list.

How it works

Step 1 of 11

A query is a program. PostgreSQL parses the SQL text into keywords, names and values before running any of it.
In the kitchen
  • the machine reads each order line as a command: STITCH 'text' THREAD navy → PostgreSQL parsing the SQL text (step 1)

Analogy: A monogramming shop's order form and its embroidery machine (machine embroidery)

A customer's words are stitched; only the shop decides how.

At the monogramming shop In the database
the machine reads each order line as a command: STITCH 'text' THREAD navy PostgreSQL parsing the SQL text (step 1)
a clerk types the customer's words into that line between quote marks input concatenated into the query (step 2)
Mrs O'Neill's apostrophe ends the quoted words early; the machine rejects the line an apostrophe ending the value; 42601 (step 3)
the new form has a sealed box for the words; the machine stitches the box's contents, marks and all a placeholder and a value sent apart (step 5)
"navy" written in the words box gets the word navy stitched, not navy thread a placeholder where a name belongs is a value (step 9)
thread is ticked on the shop's printed colour chart; an unlisted colour is refused at the counter the sort allow-list (step 10)

In real terms: the customer supplies the words; the shop supplies the command and every choice in it.

Breaks down when: a machine operator can spot a mangled line and ask; PostgreSQL parses mechanically and runs whatever parses (steps 2–3).

Examples, run for real

4.1 ExamHall EXH-3780 → MR !4080: the candidate search, before and after

ExamHall's admin candidate search pasted q and sort into the string it passed to $queryRawUnsafe. A proctor's search for a real candidate failed, and so did a mistyped sort key. MR !4080 moves q into a tagged $queryRaw and takes the sort column from a fixed map:

4.1 before (concatenated into $queryRawUnsafe), a search for O'Brien: 500 (SQLSTATE 42601) · queries sent 1
4.1 after MR !4080 (tagged $queryRaw, sort from a fixed map), a search for O'Brien: 200 · 1 row (O'Brien) · queries sent 1
4.1 before (concatenated into $queryRawUnsafe), a mistyped sort key: 500 (SQLSTATE 42703) · queries sent 1
4.1 after MR !4080 (tagged $queryRaw, sort from a fixed map), a mistyped sort key: 400 · queries sent 0

The proctors' searches did not change; only how they reach PostgreSQL did. The name now travels as a value, so its apostrophe is a character like any other (steps 2, 5–6). The sort key never reaches the SQL: the map supplies the column, and an unlisted key is refused before any query runs (steps 9–10).

4.2 Payments ledger: one statement, one list of accounts

A payments ledger's statement query takes a list of account ids from the request. The first version joined them into the text; the second passes them through Prisma.join inside the tagged template:

4.2 joined into the text: ...IN ('acc-7', 'acc-9', 'acc-12')... · values none · rows acc-7 3, acc-9 1
4.2 through Prisma.join: ...IN ($1,$2,$3)... · values ["acc-7","acc-9","acc-12"] · rows acc-7 3, acc-9 1

Prisma.join writes one placeholder per id and sends the ids apart, so the three accounts reach PostgreSQL as values (step 6). The joined text held the ids as SQL, which works only while every id is what the code expects (step 2).

Try it

Answer first, then check. Nothing is saved.

Question 1 of 6

A payments ledger's MR changes three files, each through Prisma Client. Every account and name below exists, O'Brien Catering among them.

Which change should the review block?

Pick one

Question 2 of 6

A payments ledger's export lets the caller choose a sort from a fixed map:

A caller asks for ?sort=amount. The table holds four entries with different amounts.

What does the export return?

Code for this question
const SORTS = { amount: 'amount_cents', payee: 'payee' };const rows = await prisma.$queryRaw`SELECT id, payee, amount_cents FROM ledger_entries ORDER BY ${SORTS[req.query.sort]}`;
Pick one

Question 3 of 6

ExamHall's proctor console lists submissions newest first with prisma.$queryRaw`SELECT id FROM submissions ORDER BY submitted_at ${dir}`, where the handler sets dir to 'DESC'. Three submissions exist.

What does the list query return?

Pick one

Question 4 of 6

A payments ledger builds a query object before running it:

payee is "O'Brien Catering".

Before anything reaches PostgreSQL, what do q.text and q.values hold?

Code for this question
const q = Prisma.sql`SELECT id FROM ledger_entries WHERE payee = ${payee}`;
Pick one

Question 5 of 6

A reviewer lets ExamHall's candidate search keep $queryRawUnsafe, but with the name passed as an argument: prisma.$queryRawUnsafe('SELECT id, last_name FROM candidates WHERE last_name = $1', req.query.q). The table holds Zhou, O'Brien, Adams and Okafor. A proctor sends ?q=O'Brien.

What does the search return?

Pick one

Question 6 of 6

A payments ledger's report lets the caller pick one column: prisma.$queryRaw`SELECT ${col} AS picked FROM ledger_entries`, with col set to 'payee'. The table holds four entries.

What does the report return?

Pick one

Go deeper

Pro tip
  1. In review, read every $queryRawUnsafe, $executeRawUnsafe and Prisma.raw call and ask where each piece of its text comes from; anything that came from a request must be a parameter or a lookup in your own map. When: an MR touching raw SQL (mr-security-lens's pass over sinks). Status: SELF (this lesson's practice target; not run).
Source

Quotes are checked verbatim by quote-check.mjs. Valid as of 2026-10-13.

  • OWASP, SQL Injection Prevention Cheat Sheet (the row's first source), https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html (owasp-sqli-prevention.txt): "Stop writing dynamic queries with string concatenation." (step 2); "parameterized queries force the developer to define all SQL code first and pass in each parameter to the query later." and "the database will always distinguish between code and data, regardless of what user input is supplied." (step 5); "If you are faced with parts of SQL queries that can't use bind variables, such as table names, column names, or sort order indicators (ASC or DESC), input validation or query redesign is the most appropriate defense." (step 9); "developers should map the parameter values to the legal/expected table or column names to make sure unvalidated user input doesn't end up in the query." (step 10); "It is very database specific in its implementation. This methodology is fragile compared to other defenses" (step 4); "some kind of Object Relational Mapper (ORM) that builds your queries for you." (step 6).
  • Prisma, Raw queries (Prisma ORM v7; the row's second source), https://www.prisma.io/docs/orm/prisma-client/using-raw-sql/raw-queries, served from https://www.prisma.io/docs/orm/v7/prisma-client/using-raw-sql/raw-queries (prisma-raw-queries.txt): "The method is implemented as a tagged template, which allows you to pass a template literal where you can insert your variables. In turn, Prisma Client creates prepared statements that are safe from SQL injections" (step 6); "are a lot more flexible but are at significant risk of making your code vulnerable to SQL injection." (said of the methods with Unsafe in their names; step 7); "You can also run a parameterized query." (step 8); "Template variables can only be used for data values (such as email in the example above). Variables cannot be used for identifiers such as column names, table names or database names, or for SQL keywords." (step 9); "The ideal way to avoid SQL injection in Prisma Client is to use the ORM models to perform queries wherever possible." (step 6). As of 2026-10-13.

Checked against the tools and docs as of 2026-10-13.