SQL injection and parameterised queries

SQL injectionSQL injection: A weakness in which text from a request is pasted into an SQL query, so the database parses some of its characters as SQL and the input can change what the query does. MITRE files it as CWE-89. is second in MITRE’s 2025 CWE Top 25, as CWE-89 with a score of 28.72, and the OWASP Top 10:2025 counts more than 14,000 CVEs under it. It happens when code pastes text from a request into an SQL query: the database then reads some of the text’s characters as SQL, so the input can change what the query does. A parameterised queryparameterised query: A query whose SQL text is fixed, with a placeholder such as ? where each value goes. The database compiles the text first and receives the values separately, so a value can never change the query's structure. prevents it. Its SQL text is fixed, with a placeholder where each value goes, and each value travels separately, so the database has compiled the query before it sees any value.

Here you watch an ordinary customer’s name break a pasted query, rewrite it with bound values in Python’s sqlite3 and Node’s node:sqlite, handle the four parts of a query that cannot be parameters, catch a stored value pasted into a later query, and add the narrow rights and quiet errors that catch whatever slips through.

A pasted value can change the query’s structure

A support tool finds customers by name. Its query worked for months, until the first customer with an apostrophe in her name. The program below writes the search two ways on four customers: the weak version pastes the name between two quotes, the fixed one sends it as a bound value:

// One customer search, written two ways, on a throwaway in-memory database (node:sqlite, Node.js 24).
// The test input is a real kind of name: one with an apostrophe in it.
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
  INSERT INTO customers (name, city) VALUES
    ('Asha Rao', 'Pune'), ('Ravi Kumar', 'Chennai'), ('Neha D''Souza', 'Goa'), ('Kiran Shah', 'Surat');
`);

// Weak: the name is pasted into the SQL text, so its characters become part of the query.
const weakSql = (name) => `SELECT id, name FROM customers WHERE name = '${name}'`;
function findWeak(name) {
  return db.prepare(weakSql(name)).all();
}

// Fixed: the SQL text never changes; the name travels on its own, as a bound value.
const FIXED_SQL = 'SELECT id, name FROM customers WHERE name = ?';
const byName = db.prepare(FIXED_SQL);
function findFixed(name) {
  return byName.all(name);
}

function show(label, find, name) {
  try {
    const rows = find(name);
    console.log(`${label}  ${JSON.stringify(name).padEnd(15)} ${rows.length} row(s): ${rows.map((r) => r.name).join(', ') || '-'}`);
  } catch (error) {
    console.log(`${label}  ${JSON.stringify(name).padEnd(15)} error: ${error.message}`);
  }
}

for (const name of ['Asha Rao', "Neha D'Souza", 'Ravi']) {
  show('weak ', findWeak, name);
  show('fixed', findFixed, name);
}

console.log(`\nweak SQL for Neha:  ${weakSql("Neha D'Souza")}`);
console.log(`fixed SQL for Neha: ${FIXED_SQL}   with the value "Neha D'Souza"`);

// The regression check that goes with the fix: a name with an apostrophe is found like any other.
for (const [label, find] of [['weak ', findWeak], ['fixed', findFixed]]) {
  let found = 0;
  try {
    found = find("Neha D'Souza").length;
  } catch {
    found = 0;
  }
  console.log(`check ${label}: apostrophe name found ${found} time(s) -> ${found === 1 ? 'PASS' : 'FAIL'}`);
}

weak   "Asha Rao"      1 row(s): Asha Rao
fixed  "Asha Rao"      1 row(s): Asha Rao
weak   "Neha D'Souza"  error: near "Souza": syntax error
fixed  "Neha D'Souza"  1 row(s): Neha D'Souza
weak   "Ravi"          0 row(s): -
fixed  "Ravi"          0 row(s): -

weak SQL for Neha:  SELECT id, name FROM customers WHERE name = 'Neha D'Souza'
fixed SQL for Neha: SELECT id, name FROM customers WHERE name = ?   with the value "Neha D'Souza"
check weak : apostrophe name found 0 time(s) -> FAIL
check fixed: apostrophe name found 1 time(s) -> PASS

The weak search finds Asha and fails for Neha D’Souza with a syntax error. Look at the SQL text it built: three quotes, and nothing marks which of them belong to the name. SQLite reads from the left, takes Neha D as the value and tries to read Souza' as more SQL. The name was never treated as one value; its characters became part of the query. That is SQL injection in its plainest form. A value whose characters can end the value early can also add conditions or clauses of its own, and the database has no way to tell your SQL from the pasted text. The OWASP Top 10 describes the whole category this way: untrusted input sent to an interpreter, which runs part of it as commands.

Step through the same search as the database sees it, first pasted and then bound:

The fixed search sends WHERE name = ? first, and the name afterwards. Its last two lines are the regression testregression test: A test kept after a fix so that the weakness cannot come back unnoticed. In a security lab it fails on the weak version and passes on the fixed one, and it runs again on every later change. More in “Start here: learn web security safely and legally” that belongs with the fix: a name with an apostrophe, a canary inputcanary input: A harmless test value holding characters that matter to an interpreter, such as an apostrophe for SQL or quotes and angle brackets for HTML. A test fails if the value changes the query's or the page's structure. for this query, must be found exactly once. It fails on the weak version and passes on the fixed one, so it keeps the bug from coming back. SQLite in your browser shows the same thing with three queries. SQL typed on its own has no placeholders, so the second query reads the name from a one-row table, where it stays a value just as a bound one does. Press Run: the hand-doubled quote and the value from the table both find Neha, and the last query, the name pasted as typed, stops with an error:

-- The same four customers, in the SQLite engine that runs in your browser.
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO customers (name) VALUES ('Asha Rao'), ('Ravi Kumar'), ('Neha D''Souza'), ('Kiran Shah');

-- SQL writes a quote inside a text value as two quotes. Code that pastes names into SQL must get this right
-- for every value, every time.
SELECT 'quote doubled by hand' AS query, id, name FROM customers WHERE name = 'Neha D''Souza';

-- A bound value needs no quoting at all. SQL on its own has no placeholders, so this query reads the name from
-- a one-row table, which keeps it a value in the same way a bound parameter does.
CREATE TABLE request (name TEXT NOT NULL);
INSERT INTO request (name) VALUES ('Neha D''Souza');
SELECT 'value from a table' AS query, id, name FROM customers WHERE name = (SELECT name FROM request);

-- What pasting the name as it was typed produces: the second quote ends the value early.
SELECT 'pasted as typed' AS query, id, name FROM customers WHERE name = 'Neha D'Souza';

┌───────────────────────┬────┬──────────────┐
│         query         │ id │     name     │
├───────────────────────┼────┼──────────────┤
│ quote doubled by hand │ 3  │ Neha D'Souza │
└───────────────────────┴────┴──────────────┘
┌────────────────────┬────┬──────────────┐
│       query        │ id │     name     │
├────────────────────┼────┼──────────────┤
│ value from a table │ 3  │ Neha D'Souza │
└────────────────────┴────┴──────────────┘

Parse error near line 16: near "Souza": syntax error

Both safe queries return the same row, id 3, and only the name pasted as typed was ever read as SQL.

Bound parameters keep every value out of the SQL text

A parameterised query reaches the database in two steps. First the SQL text, with a placeholder such as ? where each value goes: the database compiles it into a statement whose structure is settled from then on. Then the values, bound to the placeholders one by one. Python’s documentation is blunt about the alternative: building queries with Python’s string operations is vulnerable to SQL injection, and placeholders should bind every value instead. ASVS 5.0.0 requirement 1.2.4 asks the same of every database query, SQL or not, with parameterised queries, an ORM or an equivalent protection.

Both drivers below insert three customers with one prepared statement, then look one up with each placeholder style the driver knows. Neither needs anything special for the apostrophe:

# Bound values in Python's sqlite3, on a throwaway in-memory database.
import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL)")

# executemany() binds each tuple in turn; the quote in D'Souza needs no special care.
new_customers = [("Asha Rao", "Pune"), ("Neha D'Souza", "Goa"), ("Kiran Shah", "Surat")]
with con:  # commits when the block ends without an error, rolls back when it raises
    con.executemany("INSERT INTO customers (name, city) VALUES (?, ?)", new_customers)

# qmark style: ? takes a sequence, in order.
print("qmark:", con.execute("SELECT id, name FROM customers WHERE city = ?", ("Goa",)).fetchall())

# named style: :city takes a dict.
print("named:", con.execute("SELECT id, name FROM customers WHERE city = :city", {"city": "Goa"}).fetchall())

# The value is compared as one string, whatever characters it holds.
print("exact:", con.execute("SELECT id FROM customers WHERE name = ?", ("Neha D'Souza",)).fetchall())

# A wrong number of values is refused before anything runs.
try:
    con.execute("SELECT id FROM customers WHERE name = ? AND city = ?", ("Asha Rao",))
except sqlite3.ProgrammingError as error:
    print("refused:", error)

qmark: [(2, "Neha D'Souza")]
named: [(2, "Neha D'Souza")]
exact: [(2,)]
refused: Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied.

// Bound values in Node's node:sqlite, on a throwaway in-memory database.
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL)');

// One prepared statement, run once per customer; the quote in D'Souza needs no special care.
const insert = db.prepare('INSERT INTO customers (name, city) VALUES (?, ?)');
let added = 0;
for (const [name, city] of [['Asha Rao', 'Pune'], ["Neha D'Souza", 'Goa'], ['Kiran Shah', 'Surat']]) {
  added += insert.run(name, city).changes;
}
console.log('inserted:', added);

const show = (rows) => JSON.stringify(rows.map((r) => [r.id, r.name]));

// Anonymous parameters: ? takes the values in order.
console.log('anonymous:', show(db.prepare('SELECT id, name FROM customers WHERE city = ?').all('Goa')));

// Named parameters: $city takes an object.
console.log('named:', show(db.prepare('SELECT id, name FROM customers WHERE city = $city').all({ city: 'Goa' })));

// The value is compared as one string, whatever characters it holds.
console.log('exact:', show(db.prepare('SELECT id, name FROM customers WHERE name = ?').all("Neha D'Souza")));

// A named value the statement does not have is refused before anything runs.
try {
  db.prepare('SELECT id FROM customers WHERE city = $city').all({ town: 'Goa' });
} catch (error) {
  console.log('refused:', error.code, error.message);
}

inserted: 3
anonymous: [[2,"Neha D'Souza"]]
named: [[2,"Neha D'Souza"]]
exact: [[2,"Neha D'Souza"]]
refused: ERR_INVALID_STATE Unknown named parameter 'town'

Each driver also refuses a mismatch before anything runs: Python’s sqlite3 raises ProgrammingError when two placeholders get one value, and node:sqlite refuses a named value the statement does not have. The styles differ by driver, so read your driver’s documentation once and use one style throughout a project:

The last column matters as much as the placeholders. Python’s execute() refuses a string that holds more than one statement, while executescript() runs every statement it is given; Node’s exec() also runs one or more statements. Those two calls are for scripts you wrote, such as a schema file, and must never see text that came from a request.

Names, keywords and patterns cannot be parameters

A placeholder stands for a value, and only for a value. It cannot be a table or a column name, a keyword such as DESC, or an operator. ASVS notes that table and column names, including those after ORDER BY, cannot be escaped either, and the Top 10 calls user-chosen structure names a common issue in report-writing software. Four parts of a query come up again and again, and the program below gives each its recipe on a catalogue of eight products:

// What a parameter cannot do, and the recipe for each case: sort columns, LIKE patterns, IN lists and page sizes.
// A throwaway in-memory catalogue (node:sqlite, Node.js 24).
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price INTEGER NOT NULL);
  INSERT INTO products (name, price) VALUES
    ('Steel bottle', 450), ('Cotton shirt, 100% cotton', 899), ('Desk lamp', 1299), ('Notebook_A5', 120),
    ('Tea, 50% off', 199), ('Pen set', 150), ('Wool socks', 299), ('Backpack', 1999);
`);
const names = (rows) => rows.map((r) => r.name).join(' | ');

// 1. Sort column. A parameter binds a value, so ORDER BY ? sorts by the same text for every row: no sort at all.
console.log('ORDER BY ? with "price":', names(db.prepare('SELECT name FROM products ORDER BY ? LIMIT 3').all('price')));
// The recipe: map the public names the API accepts to fixed SQL fragments, and refuse the rest.
const SORTS = { price: 'price', name: 'name', newest: 'id DESC' };
function sortedBy(choice) {
  if (!Object.hasOwn(SORTS, choice)) return `refused: unknown sort "${choice}"`;
  return names(db.prepare(`SELECT name FROM products ORDER BY ${SORTS[choice]} LIMIT 3`).all());
}
console.log('allowlist "price":     ', sortedBy('price'));
console.log('allowlist "colour":    ', sortedBy('colour'));

// 2. LIKE pattern. The value is bound, but % and _ inside it still mean "anything" to LIKE.
const likeWeak = db.prepare('SELECT name FROM products WHERE name LIKE ?');
const likeFixed = db.prepare("SELECT name FROM products WHERE name LIKE ? ESCAPE '\\'");
const escapeLike = (text) => text.replace(/[\\%_]/g, (c) => `\\${c}`);
for (const term of ['%', '_']) {
  console.log(`search ${JSON.stringify(term)} weak:  ${likeWeak.all(`%${term}%`).length} of 8 rows`);
  console.log(`search ${JSON.stringify(term)} fixed: ${names(likeFixed.all(`%${escapeLike(term)}%`))}`);
}

// 3. IN list. One placeholder per value; an empty list is refused before any SQL is built.
function byIds(ids) {
  if (!Array.isArray(ids) || ids.length === 0 || ids.length > 50 || !ids.every(Number.isSafeInteger)) return 'refused: 1 to 50 whole-number ids';
  const marks = ids.map(() => '?').join(', ');
  return `IN (${marks}) -> ${names(db.prepare(`SELECT name FROM products WHERE id IN (${marks})`).all(...ids))}`;
}
console.log('ids [2, 5, 7]:', byIds([2, 5, 7]));
console.log('ids []:       ', byIds([]));

// 4. Page size. SQLite reads a negative LIMIT as "no limit", so a bound -1 returns every row.
const pageWeak = db.prepare('SELECT name FROM products LIMIT ?');
console.log('limit "-1" weak: ', pageWeak.all(Number('-1')).length, 'rows');
function pageSize(text) {
  if (!/^\d{1,3}$/.test(text)) return null;
  const n = Number(text);
  return n >= 1 && n <= 5 ? n : null;
}
for (const text of ['3', '-1', 'abc']) {
  const n = pageSize(text);
  console.log(`limit ${JSON.stringify(text)} fixed:`, n === null ? 'refused: 1 to 5' : `${db.prepare('SELECT name FROM products LIMIT ?').all(n).length} rows`);
}

ORDER BY ? with "price": Steel bottle | Cotton shirt, 100% cotton | Desk lamp
allowlist "price":      Notebook_A5 | Pen set | Tea, 50% off
allowlist "colour":     refused: unknown sort "colour"
search "%" weak:  8 of 8 rows
search "%" fixed: Cotton shirt, 100% cotton | Tea, 50% off
search "_" weak:  8 of 8 rows
search "_" fixed: Notebook_A5
ids [2, 5, 7]: IN (?, ?, ?) -> Cotton shirt, 100% cotton | Tea, 50% off | Wool socks
ids []:        refused: 1 to 50 whole-number ids
limit "-1" weak:  8 rows
limit "3" fixed: 3 rows
limit "-1" fixed: refused: 1 to 5
limit "abc" fixed: refused: 1 to 5

The first line is a quiet bug rather than an error. ORDER BY ? binds the text price as a value, so every row sorts by the same string, and the products come back unsorted. The allowlist that follows maps the three names the API accepts to fixed SQL fragments and refuses colour; the user’s text never enters the query.

The empty list deserves a word. SQLite accepts IN () and simply matches nothing, but its documentation notes that most other engines and the SQL92 standard require at least one value, so code that relies on it breaks on another database. Refusing an empty list in your own code behaves the same everywhere.

A stored value is untrusted again when it is used

Binding values at the form is not the end of it. A value that was stored safely comes back later, often in another part of the application, and the same name can break a query that was written months after the form. This is second-order injectionsecond-order injection: Injection through a value that was stored safely and later pasted into another query or command, often by a different part of the application. The fix is a bound value at every use.. MITRE’s entry for CWE-89 warns of it: once data is in the database, later processes may neglect to handle its special characters before they use it. Step through Neha’s sign-up and the report job that reads her name afterwards:

The program behind the scene stores three customers with bound inserts, then runs a monthly report written the weak way and the fixed way:

# A value stored safely, then pasted into a second query later: second-order injection, with real names.
import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
    CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
    CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total INTEGER NOT NULL);
""")


def sign_up(name):
    """The sign-up form binds the name, so storing it is safe."""
    with con:
        con.execute("INSERT INTO customers (name) VALUES (?)", (name,))


for name in ["Asha Rao", "Neha D'Souza", "Kiran Shah"]:
    sign_up(name)
with con:
    con.executemany("INSERT INTO orders (customer, total) VALUES (?, ?)",
                    [("Asha Rao", 450), ("Neha D'Souza", 899), ("Neha D'Souza", 199), ("Kiran Shah", 150)])
print("stored:", [row[0] for row in con.execute("SELECT name FROM customers ORDER BY id")])


def report_weak():
    """Weeks later, a report job reads the stored names and pastes each one into new SQL."""
    for (name,) in con.execute("SELECT name FROM customers ORDER BY id").fetchall():
        sql = f"SELECT count(*), sum(total) FROM orders WHERE customer = '{name}'"
        print("  weak ", name.ljust(13), con.execute(sql).fetchone())


def report_fixed():
    """The fix is at the second use too: the stored name is bound like any other value."""
    for (name,) in con.execute("SELECT name FROM customers ORDER BY id").fetchall():
        sql = "SELECT count(*), sum(total) FROM orders WHERE customer = ?"
        print("  fixed", name.ljust(13), con.execute(sql, (name,)).fetchone())


try:
    report_weak()
except sqlite3.OperationalError as error:
    print("  weak report stopped:", error)
report_fixed()

stored: ['Asha Rao', "Neha D'Souza", 'Kiran Shah']
  weak  Asha Rao      (1, 450)
  weak report stopped: near "Souza": syntax error
  fixed Asha Rao      (1, 450)
  fixed Neha D'Souza  (2, 1098)
  fixed Kiran Shah    (1, 150)

The weak report handles Asha, then stops at Neha’s row with the same syntax error as the search, so the third customer is never reported. Validating names at sign-up would not have helped: an apostrophe belongs in Neha’s name, and the input was stored correctly. The fix is at every use, not at the door: wherever a value enters SQL, it is bound.

The same applies to the raw interfaces of ORMs, which take SQL text you write yourself. Django’s documentation calls for care with raw queries, extra() and RawSQL, and Prisma’s documentation warns that $queryRawUnsafe puts code at significant risk of SQL injection when it receives input. An ORM protects the queries it builds; a string you hand it is yours. The next lessons of this module take ORMs and query builders further.

Narrow rights, quiet errors and a test catch what slips through

Every query bound, and still a team plans for the one that was not. Three controls limit the damage of a mistake. The application’s own database account can read and write rows but not change the schema, and a reporting account can only read: OWASP’s cheat sheet says never to give an application account administrator rights, which is least privilegeleast privilege: Giving every program, account and person only the rights its task needs, so that a bug or a stolen credential can reach as little as possible. More in “Security principles every builder applies” applied to a database. Errors go to the server’s log, and the user sees a short, generic message, as ASVS 5.0.0 requirement 16.5.1 asks, with a reference that leads the team to the logged error. And every fix keeps the regression test that proved it.

SQLite has no user accounts, so the program below narrows each connection instead: an authorizer callback refuses schema changes on the app’s connection, and PRAGMA query_only makes the report’s connection read-only:

# Defence in depth around the queries: narrow rights per connection, and errors that stay in the log.
import sqlite3

URI = "file:shop?mode=memory&cache=shared"  # one in-memory database, shared by the connections below

owner = sqlite3.connect(URI, uri=True)  # the migration account: it may change the schema
owner.executescript("""
    CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
    INSERT INTO customers (name, city) VALUES ('Asha Rao', 'Pune'), ('Neha D''Souza', 'Goa');
""")

# The app's connection reads and writes rows, and nothing else: schema changes are refused.
SCHEMA_CHANGES = {sqlite3.SQLITE_CREATE_TABLE, sqlite3.SQLITE_DROP_TABLE, sqlite3.SQLITE_ALTER_TABLE,
                  sqlite3.SQLITE_CREATE_INDEX, sqlite3.SQLITE_DROP_INDEX}
app = sqlite3.connect(URI, uri=True, autocommit=True)
app.set_authorizer(lambda action, *rest: sqlite3.SQLITE_DENY if action in SCHEMA_CHANGES else sqlite3.SQLITE_OK)

# The report's connection may only read.
report = sqlite3.connect(URI, uri=True, autocommit=True)
report.execute("PRAGMA query_only = ON")

LOG = []


def for_the_user(connection, sql, params=()):
    """Runs one query; on an error the details go to the log and the user gets a reference only."""
    try:
        cursor = connection.execute(sql, params)
        return cursor.fetchall() if cursor.description else f"{cursor.rowcount} row(s) changed"
    except sqlite3.Error as error:
        ref = f"E{1001 + len(LOG)}"
        LOG.append(f"{ref} {type(error).__name__}: {error} | sql={sql!r}")
        return f"Something went wrong. Quote reference {ref} if you contact us."


print("app reads:      ", for_the_user(app, "SELECT name FROM customers WHERE city = ?", ("Goa",)))
print("app writes:     ", for_the_user(app, "UPDATE customers SET city = ? WHERE id = ?", ("Mumbai", 1)))
print("app alters:     ", for_the_user(app, "ALTER TABLE customers ADD COLUMN notes TEXT"))
print("report reads:   ", for_the_user(report, "SELECT count(*) FROM customers"))
print("report writes:  ", for_the_user(report, "UPDATE customers SET city = ? WHERE id = ?", ("Delhi", 2)))
print("typo in a query:", for_the_user(app, "SELECT nmae FROM customers"))
print("server log:")
for line in LOG:
    print(" ", line)

app reads:       [("Neha D'Souza",)]
app writes:      1 row(s) changed
app alters:      Something went wrong. Quote reference E1001 if you contact us.
report reads:    [(2,)]
report writes:   Something went wrong. Quote reference E1002 if you contact us.
typo in a query: Something went wrong. Quote reference E1003 if you contact us.
server log:
  E1001 DatabaseError: not authorized | sql='ALTER TABLE customers ADD COLUMN notes TEXT'
  E1002 OperationalError: attempt to write a readonly database | sql='UPDATE customers SET city = ? WHERE id = ?'
  E1003 OperationalError: no such column: nmae | sql='SELECT nmae FROM customers'

The app reads and updates rows, but its ALTER TABLE is refused; the report reads, but its UPDATE is refused. Each refusal and the typo nmae reach the user as the same sentence with a reference, while the log keeps the error and the SQL for the team. In PostgreSQL or MySQL the same separation is a set of GRANT statements per account.

None of the three replaces the bound value. They are defence in depthdefence in depth: Stacking independent controls, such as a role check in the router and a tenant check in the data layer, so that one failing control does not leave the asset unprotected. More in “Security principles every builder applies”: each one reduces what a missed placeholder can cost.

Practise: six query builders

Six functions of a product catalogue build their queries by pasting the input. Rewrite them so that the SQL text is fixed and every value is bound, with the recipes of this lesson for the sort, the search pattern, the id list and the page. The sample tests run in your browser, in JavaScript or Python:

// Each function returns [sql, values]. Starter: every input is pasted into the SQL text. Fix all six.

export function findCustomer(name) {
  return [`SELECT id, name FROM customers WHERE name = '${name}'`, []];
}

export function searchProducts(term) {
  return [`SELECT id, name FROM products WHERE name LIKE '%${term}%'`, []];
}

export function productsByIds(ids) {
  return [`SELECT id, name FROM products WHERE id IN (${ids.join(', ')})`, []];
}

export function sortedProducts(sort) {
  return [`SELECT id, name FROM products ORDER BY ${sort}`, []];
}

export function productPage(limit, offset) {
  return [`SELECT id, name FROM products ORDER BY id LIMIT ${limit} OFFSET ${offset}`, []];
}

export function ordersFor(storedName) {
  return [`SELECT count(*) FROM orders WHERE customer = '${storedName}'`, []];
}

import { test, assert } from 'mysmartcopilot:test';
import { findCustomer, ordersFor, productPage, productsByIds, searchProducts, sortedProducts } from './queries.mjs';

const refuses = (fn, why) => assert.throws(fn, undefined, why);

test('names are bound, never pasted', () => {
  assert.deepEqual(findCustomer("Neha D'Souza"), ['SELECT id, name FROM customers WHERE name = ?', ["Neha D'Souza"]]);
  assert.deepEqual(ordersFor("Neha D'Souza"), ['SELECT count(*) FROM orders WHERE customer = ?', ["Neha D'Souza"]], 'a stored name is bound too');
});

test('a search term is bound, with its wildcards escaped', () => {
  const sql = "SELECT id, name FROM products WHERE name LIKE ? ESCAPE '\\'";
  assert.deepEqual(searchProducts('lamp'), [sql, ['%lamp%']]);
  assert.deepEqual(searchProducts('100%'), [sql, ['%100\\%%']], 'a % in the term matches a % only');
  assert.deepEqual(searchProducts('A5_\\'), [sql, ['%A5\\_\\\\%']], 'so do _ and the escape character');
});

test('an IN list gets one placeholder per id', () => {
  assert.deepEqual(productsByIds([2, 5, 7]), ['SELECT id, name FROM products WHERE id IN (?, ?, ?)', [2, 5, 7]]);
  assert.deepEqual(productsByIds([9]), ['SELECT id, name FROM products WHERE id IN (?)', [9]]);
  const fifty = Array.from({ length: 50 }, (_, i) => i + 1);
  assert.equal(productsByIds(fifty)[1].length, 50, '50 ids are allowed');
  refuses(() => productsByIds([]), 'an empty list');
  refuses(() => productsByIds([...fifty, 51]), '51 ids');
  refuses(() => productsByIds([2, '5']), 'an id that is text');
  refuses(() => productsByIds([2.5]), 'an id that is not whole');
});

test('a sort comes from the table of three, or not at all', () => {
  assert.deepEqual(sortedProducts('price'), ['SELECT id, name FROM products ORDER BY price', []]);
  assert.deepEqual(sortedProducts('newest'), ['SELECT id, name FROM products ORDER BY id DESC', []]);
  refuses(() => sortedProducts('colour'), 'an unknown sort');
  refuses(() => sortedProducts('price DESC'), 'a sort that is almost a known one');
});

test('a page is two checked numbers, bound', () => {
  const sql = 'SELECT id, name FROM products ORDER BY id LIMIT ? OFFSET ?';
  assert.deepEqual(productPage('20', '40'), [sql, [20, 40]]);
  assert.deepEqual(productPage('1', '0'), [sql, [1, 0]]);
  assert.deepEqual(productPage('50', '10000'), [sql, [50, 10000]], 'both upper limits are allowed');
  refuses(() => productPage('0', '0'), 'a limit of 0');
  refuses(() => productPage('51', '0'), 'a limit above 50');
  refuses(() => productPage('-1', '0'), 'a negative limit, which SQLite reads as no limit');
  refuses(() => productPage('20', '10001'), 'an offset above 10000');
  refuses(() => productPage('2e1', '0'), 'a limit that is not digits');
  refuses(() => productPage('+5', '0'), 'a limit with a sign, which Number() would accept');
});

# Each function returns (sql, values). Starter: every input is pasted into the SQL text. Fix all six.


def find_customer(name):
    return f"SELECT id, name FROM customers WHERE name = '{name}'", []


def search_products(term):
    return f"SELECT id, name FROM products WHERE name LIKE '%{term}%'", []


def products_by_ids(ids):
    return f"SELECT id, name FROM products WHERE id IN ({', '.join(str(i) for i in ids)})", []


def sorted_products(sort):
    return f"SELECT id, name FROM products ORDER BY {sort}", []


def product_page(limit, offset):
    return f"SELECT id, name FROM products ORDER BY id LIMIT {limit} OFFSET {offset}", []


def orders_for(stored_name):
    return f"SELECT count(*) FROM orders WHERE customer = '{stored_name}'", []

from queries import find_customer, orders_for, product_page, products_by_ids, search_products, sorted_products


def refuses(fn, *args):
    """True when fn(*args) raises ValueError, as the prompt asks for refused input."""
    try:
        fn(*args)
    except ValueError:
        return True
    return False


def test_names_are_bound_never_pasted():
    assert find_customer("Neha D'Souza") == ("SELECT id, name FROM customers WHERE name = ?", ["Neha D'Souza"])
    assert orders_for("Neha D'Souza") == ("SELECT count(*) FROM orders WHERE customer = ?", ["Neha D'Souza"]), "a stored name is bound too"


def test_a_search_term_is_bound_with_its_wildcards_escaped():
    sql = "SELECT id, name FROM products WHERE name LIKE ? ESCAPE '\\'"
    assert search_products("lamp") == (sql, ["%lamp%"])
    assert search_products("100%") == (sql, ["%100\\%%"]), "a % in the term matches a % only"
    assert search_products("A5_\\") == (sql, ["%A5\\_\\\\%"]), "so do _ and the escape character"


def test_an_in_list_gets_one_placeholder_per_id():
    assert products_by_ids([2, 5, 7]) == ("SELECT id, name FROM products WHERE id IN (?, ?, ?)", [2, 5, 7])
    assert products_by_ids([9]) == ("SELECT id, name FROM products WHERE id IN (?)", [9])
    fifty = list(range(1, 51))
    assert len(products_by_ids(fifty)[1]) == 50, "50 ids are allowed"
    assert refuses(products_by_ids, []), "an empty list"
    assert refuses(products_by_ids, fifty + [51]), "51 ids"
    assert refuses(products_by_ids, [2, "5"]), "an id that is text"
    assert refuses(products_by_ids, [2.5]), "an id that is not whole"
    assert refuses(products_by_ids, [True]), "a boolean is not an id"


def test_a_sort_comes_from_the_table_of_three_or_not_at_all():
    assert sorted_products("price") == ("SELECT id, name FROM products ORDER BY price", [])
    assert sorted_products("newest") == ("SELECT id, name FROM products ORDER BY id DESC", [])
    assert refuses(sorted_products, "colour"), "an unknown sort"
    assert refuses(sorted_products, "price DESC"), "a sort that is almost a known one"


def test_a_page_is_two_checked_numbers_bound():
    sql = "SELECT id, name FROM products ORDER BY id LIMIT ? OFFSET ?"
    assert product_page("20", "40") == (sql, [20, 40])
    assert product_page("1", "0") == (sql, [1, 0])
    assert product_page("50", "10000") == (sql, [50, 10000]), "both upper limits are allowed"
    assert refuses(product_page, "0", "0"), "a limit of 0"
    assert refuses(product_page, "51", "0"), "a limit above 50"
    assert refuses(product_page, "-1", "0"), "a negative limit, which SQLite reads as no limit"
    assert refuses(product_page, "20", "10001"), "an offset above 10000"
    assert refuses(product_page, "2e1", "0"), "a limit that is not digits"
    assert refuses(product_page, "+5", "0"), "a limit with a sign, which int() would accept"

Finish with the adaptive questions: they start where your answers so far are weakest, and every choice comes with its reason:

Interview questions

Warm-up: what is SQL injection, and why does a parameterised query prevent it?

SQL injection happens when an application pastes text it received into an SQL query, so the database parses some of that text as SQL and the input can change what the query does. A real name such as Neha D’Souza shows it: the apostrophe ends the value early, and the query fails or means something else. A parameterised query prevents it by sending the SQL text and the values separately. The database compiles the text, with a placeholder where each value goes, before any value arrives, so a bound value is never parsed as SQL, whatever characters it holds. Placeholders cover values only, so table names, sort columns and keywords come from an allowlist in the code.

Key takeaways

• A value pasted into SQL text is parsed with it, so its characters can change the query; that is SQL injection, and a name with an apostrophe is enough to show it.
• A parameterised query sends fixed SQL text with placeholders and the values separately, so a bound value can never change the query’s structure.
• Placeholders bind values only: sort columns come from an allowlist of fragments, LIKE patterns are escaped, IN lists get one placeholder per value, and page sizes are checked numbers.
• A value stored safely is still pasted text when a later query builds SQL from it, so every use binds it again.
• Narrow account rights, generic errors with detailed logs and a regression test per fix limit what a missed placeholder can cost; none of them replaces it.