03 - SQL Injection and Input Validation: Backend Security Guide

A search that concatenates the user's text into an SQL query lets that text into the query's syntax, and one apostrophe is enough to turn it from data into an instruction. The defence is a single one and has a well-known name, parameterised queries, but there are three places where it is not enough on its own: data that is stored and then reused, column names, and the fact that validating input does not repair injection. In this article you try them all on a test database and see the boundary between what parameters cover and what is left to you.

Injection in a few lines

According to OWASP's guide, an SQL injection happens when untrusted data, usually strings, becomes part of the syntax of a query and changes its logic; the source can be an HTTP request but also an email, a third-party service or the database itself. In the 2025 Top 10 it is inside A05, and the official page describes it as rare but of high impact. The most instructive example of its cost is MOVEit Transfer: CISA's advisory AA23-158A reports that from 27 May 2023 a ransomware group exploited an unknown SQL injection vulnerability (CVE-2023-34362) to install a web shell and steal data from the databases of exposed systems.

The script creates in memory, with the node:sqlite module of Node 24.11.0, a database with users, products and a table of fake secrets; then it writes each operation twice, the vulnerable version (concatenated string) and the correct one (separate values). The database lives in the process: there is no server, no network, and the only data is what the script inserts. The login also compares the password hash: the attack will show that this check can be skipped.

import { DatabaseSync } from 'node:sqlite';
import { createHash } from 'node:crypto';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, hash TEXT, role TEXT);
  CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
  CREATE TABLE secrets (id INTEGER PRIMARY KEY, note TEXT);
  INSERT INTO products VALUES (1, 'Notebook', 3.5), (2, 'Pen', 1.2);
  INSERT INTO secrets VALUES (1, 'test-key-123');
`);
const sha = (t) => createHash('sha256').update(t).digest('hex'); // for the demo only: see article 04
db.prepare('INSERT INTO users (email, hash, role) VALUES (?, ?, ?)')
  .run('admin@example.test', sha('a-password-i-do-not-know'), 'admin');

// VULNERABLE: the user's text becomes part of the SQL syntax
function vulnerableLogin(email, password) {
  const sql = `SELECT id, role FROM users WHERE email = '${email}' AND hash = '${sha(password)}'`;
  return db.prepare(sql).get();
}
function vulnerableSearch(term) {
  return db.prepare(`SELECT id, name FROM products WHERE name LIKE '%${term}%'`).all();
}

// CORRECT: the structure is fixed, the values travel separately
function login(email, password) {
  return db.prepare('SELECT id, role FROM users WHERE email = ? AND hash = ?').get(email, sha(password));
}
function search(term) {
  return db.prepare('SELECT id, name FROM products WHERE name LIKE ?').all(`%${term}%`);
}

const loginAttack = "admin@example.test' --";
const unionAttack = "x' UNION SELECT id, note FROM secrets --";
console.log('vulnerable login :', JSON.stringify(vulnerableLogin(loginAttack, 'anything')));
console.log('correct login    :', JSON.stringify(login(loginAttack, 'anything') ?? null));
console.log('vulnerable search:', JSON.stringify(vulnerableSearch(unionAttack)));
console.log('correct search   :', JSON.stringify(search(unionAttack)));
console.log('normal search    :', JSON.stringify(search('Pen')));

// Output:
// vulnerable login : {"id":1,"role":"admin"}
// correct login    : null
// vulnerable search: [{"id":1,"name":"test-key-123"}]
// correct search   : []
// normal search    : [{"id":2,"name":"Pen"}]

The first attack, admin@example.test' --, closes the email string and uses -- to turn the whole rest of the query into a comment, the hash check included: the vulnerable login returns the administrator without knowing the password. The second, x' UNION SELECT id, note FROM secrets --, pastes onto the search a second SELECT with the same number of columns and makes the content of another table appear in the results. In the correct versions both texts are just strings: the login finds nobody (null), the search finds no product, and a normal search (Pen) works as before.

Why parameters solve it

In the correct functions the script writes WHERE email = ? AND hash = ? and passes the values to get(email, sha(password)). The engine first compiles the query, with its fixed structure, and then binds the values to the placeholders: what gets bound is always a value, never syntax. SQLite's documentation puts it this way: in the query text, literals can be replaced by a parameter. The perimeter is already in the sentence: parameters replace literals, not table or column names. The OWASP guide puts this defence first, and explicitly discourages manual escaping, because it is easy to get wrong. With the perimeter clear, three cases remain that trouble anyone who believes that "I use parameters" settles the matter.

Case 1: data stored and then reused (second order)

In second-order injection the malicious value enters the database correctly, with a parameter, and explodes later in another function that trusts what it reads from the database. The script registers a customer named x' OR '1'='1, reads it back from the table and uses it in a sum of orders, first with concatenation and then with the parameter.

import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
  CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, total REAL);
  INSERT INTO orders VALUES (1, 'Anna', 10), (2, 'Bruno', 20), (3, 'Carla', 30);
`);

// Step 1: the registration is done properly, with parameters. The malicious value is stored as it is.
db.prepare('INSERT INTO customers (name) VALUES (?)').run("x' OR '1'='1");
const stored = db.prepare('SELECT name FROM customers WHERE id = 1').get().name;
console.log('name stored in the database:', stored);

// Step 2: another function trusts the database and concatenates.
const vulnerableTotal = (name) =>
  db.prepare(`SELECT SUM(total) AS t FROM orders WHERE customer = '${name}'`).get().t;
const total = (name) =>
  db.prepare('SELECT SUM(total) AS t FROM orders WHERE customer = ?').get(name).t;

console.log('vulnerable sum:', vulnerableTotal(stored));
console.log('correct sum   :', total(stored));
console.log("Anna's sum    :", total('Anna'));

// Output:
// name stored in the database: x' OR '1'='1
// vulnerable sum: 60
// correct sum   : null
// Anna's sum    : 10

The name is saved identical, because the first step is safe; the vulnerable sum returns 60, that is, the sum of all orders, while the correct one returns null (no customer with that name) and Anna's returns 10. The rule that follows is that there is no such thing as "trusted data" in general: the database is a source of untrusted data just like the HTTP request, and the OWASP guide lists it among the sources. You parameterise every query, regardless of where the value comes from.

Case 2: column names, sort orders and LIKE

If the sort column is chosen by the user (?sort=price), a parameter is no use: the engine treats it as a constant and does not sort at all. The script shows it, then shows what happens when you concatenate the name (a CASE expression that decides the order based on a secret table: the result changes as a function of data the user should not see) and finally the solution OWASP provides for the structural parts: an allowlist of fixed values, owned by the application. The same block shows a different limit: with LIKE the parameter prevents injection, but % and _ remain special characters of the pattern.

import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
  CREATE TABLE secrets (id INTEGER PRIMARY KEY, note TEXT);
  INSERT INTO products VALUES (1, 'Notebook', 3.5), (2, 'Pen', 1.2), (3, 'Backpack', 25);
  INSERT INTO secrets VALUES (1, 'confidential');
`);
const names = (rows) => rows.map((r) => r.name).join(',');

// 1. A parameter cannot be a column name: it is treated as a constant.
const withParameter = db.prepare('SELECT name FROM products ORDER BY ?').all('price');
console.log('ORDER BY ? with "price":', names(withParameter), '(insertion order: no sorting)');

// 2. Concatenating the column name reopens the injection: here an expression chooses the order
//    based on data from another table ("blind" technique, no data comes out directly).
const sort = "(CASE WHEN (SELECT count(*) FROM secrets) > 0 THEN price ELSE name END)";
console.log('concatenated ORDER BY   :', names(db.prepare(`SELECT name FROM products ORDER BY ${sort}`).all()));

// 3. Solution: the user picks a key, the application owns the SQL text.
const COLUMNS = new Map([['name', 'name'], ['price', 'price']]);
const DIRECTIONS = new Map([['asc', 'ASC'], ['desc', 'DESC']]);
function list(column, direction) {
  const c = COLUMNS.get(column);
  const d = DIRECTIONS.get(direction);
  if (!c || !d) throw new RangeError('sort order not allowed');
  return names(db.prepare(`SELECT name FROM products ORDER BY ${c} ${d}`).all());
}
console.log('allowlist price desc    :', list('price', 'desc'));
try { list(sort, 'asc'); } catch (e) { console.log('allowlist with attack   :', e.name + ': ' + e.message); }

// 4. LIKE: the parameter prevents the injection, but % and _ remain special characters of the pattern.
const literal = (t) => t.replace(/[\\%_]/g, (c) => '\\' + c);
console.log('LIKE with "%"           :', names(db.prepare('SELECT name FROM products WHERE name LIKE ?').all('%')));
console.log('LIKE with escaped "%"   :', names(db.prepare("SELECT name FROM products WHERE name LIKE ? ESCAPE '\\'").all(literal('%'))) || '(none)');

// Output:
// ORDER BY ? with "price": Notebook,Pen,Backpack (insertion order: no sorting)
// concatenated ORDER BY   : Pen,Notebook,Backpack
// allowlist price desc    : Backpack,Notebook,Pen
// allowlist with attack   : RangeError: sort order not allowed
// LIKE with "%"           : Notebook,Pen,Backpack
// LIKE with escaped "%"   : (none)

The allowlist is a Map from a public key to SQL text: the user chooses price and desc, but the only text that enters the query is the one in the map; the attack gets a RangeError before touching the database. For LIKE, searching for % returns everything; with the literal function and the ESCAPE clause the same search finds nothing, because it now really looks for the percent character. It is not a security vulnerability in the strict sense, but a correctness error: the search returns more than the user wanted.

Case 3: validating is not defending

Input validation reduces what reaches the query and blocks many abuses, but it cannot be the defence against injection. The reason is simple: O'Brien is a valid surname and contains an apostrophe, and if you reject apostrophes you reject real customers. A filter that removes "dangerous" characters is fragile and gets it wrong in both directions. What validation does well is enforce types and limits. The script uses Fastify's JSON schema (validated with Ajv) on a POST /orders route, run with inject() without opening ports, and saves with a parameter.

import Fastify from 'fastify';
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, quantity INTEGER)');
const insert = db.prepare('INSERT INTO orders (customer, quantity) VALUES (?, ?)');

const app = Fastify();
app.post('/orders', {
  schema: {
    body: {
      type: 'object',
      required: ['customer', 'quantity'],
      additionalProperties: false,
      properties: {
        customer: { type: 'string', minLength: 1, maxLength: 60 },
        quantity: { type: 'integer', minimum: 1, maximum: 100 },
      },
    },
  },
}, async (req, reply) => {
  const { customer, quantity } = req.body;
  insert.run(customer, quantity);
  return reply.code(201).send({ customer, quantity });
});

const tests = [
  ['valid', { customer: "O'Brien", quantity: 3 }],
  ['NoSQL operator instead of text', { customer: { $ne: null }, quantity: 3 }],
  ['quantity out of range', { customer: 'Anna', quantity: 5000 }],
  ['coercible text quantity', { customer: 'Anna', quantity: '7' }],
  ['non-numeric quantity', { customer: 'Anna', quantity: 'many' }],
  ['extra field', { customer: 'Anna', quantity: 1, role: 'admin' }],
];
for (const [name, payload] of tests) {
  const r = await app.inject({ method: 'POST', url: '/orders', payload });
  console.log(`${name}: ${r.statusCode} ${r.statusCode === 400 ? r.json().message : r.body}`);
}
console.log('rows stored:', JSON.stringify(db.prepare('SELECT customer, quantity, typeof(quantity) AS type FROM orders').all()));

// Output:
// valid: 201 {"customer":"O'Brien","quantity":3}
// NoSQL operator instead of text: 400 body/customer must be string
// quantity out of range: 400 body/quantity must be <= 100
// coercible text quantity: 201 {"customer":"Anna","quantity":7}
// non-numeric quantity: 400 body/quantity must be integer
// extra field: 201 {"customer":"Anna","quantity":1}
// rows stored: [{"customer":"O'Brien","quantity":3,"type":"integer"},{"customer":"Anna","quantity":7,"type":"integer"},{"customer":"Anna","quantity":1,"type":"integer"}]

The surname with the apostrophe passes and is saved without problems, because it is a parameter. An object instead of text, like {"$ne": null}, is rejected with 400: it is the operator injection that PortSwigger describes for MongoDB, where a client sends a query operator instead of a value. Here the database is SQLite and there is nothing MongoDB about it: the test only shows that the schema stops the wrong type before it reaches any query. Two behaviours of Fastify are worth knowing because they surprise. The first is coercion: quantity: "7" is accepted and saved as the integer 7 (in the database the type is integer), because Ajv's default configuration converts types; the documentation warns you to evaluate it and shows how to turn it off on the body. The second: with additionalProperties: false the extra field role is not rejected but silently removed, because the removeAdditional option is true. This protects against mass assignment, but the error disappears; if you want to reject the request, change the configuration.

The layers that complete the picture

If an injection succeeds, the damage depends on the privileges of the database account. For this reason OWASP recommends least privilege: start from what the application needs instead of removing what is left over, give read-only access to accounts that only read, never administrator privileges, and use views to expose only part of a table. ORMs do not change the picture: every library that accepts raw SQL also accepts concatenation, and the flaw lies not in the tool but in the place where you build the string. Before a review you can use this check:

• no query built with template strings or concatenation that contains input values, from any source;
• every column name, table name or direction chosen by the user goes through an allowlist;
• validation declares types, lengths and ranges, and does not try to filter "dangerous characters";
• the account the application uses has no more rights than the code uses.

The four items have no fixed order of importance, but the first is the only one that closes classic injections: the others limit the perimeter and the damage. The comparison with the previous layer of the series is useful: where article 02 put the data in a document, here the data ends up in a different interpreter, and the principle of keeping it separate from the syntax is the same.

Frequently asked questions

Does using an ORM keep me safe from SQL injection?

Only as long as you do not build raw queries by concatenating values. The queries generated by the ORM are parameterised, but every interface that accepts SQL text must be treated as a hand-written query. Column names chosen by the user remain a problem even with an ORM.

Can I defend myself by filtering apostrophes and words like UNION?

No. A filter of this kind blocks real customers (O'Brien) and lets through the variants it did not foresee. OWASP indicates parameterised queries as the main defence and manual escaping as a choice to avoid, because it is easy to get wrong.

Is schema validation still useful, then?

Yes, for another purpose: it enforces types, lengths and ranges, and stops an object where you expect text. It reduces the surface and makes errors clear, but it does not replace parameters, which are what separates data from syntax.

What you can do now

You can reproduce a login bypass and a UNION on a test database, and fix them with parameters. You can explain why a parameter is not a column name and how to choose a sort order with an allowlist, why data read from the database must be treated as untrusted, and how to configure Fastify knowing that it coerces types and silently removes extra fields. The next step is authentication: what here was a fake login becomes a session system that holds.

Sources