Security: SQL injection, privileges, prepared statements

Reproduce an SQL injection, neutralize it with prepared statements, and apply least privilege.

11 min · 3 questions

An SQL injection occurs the moment untrusted data is concatenated into the text of a query. The attacker supplies a value that closes the string and hijacks the logic. Let's reproduce the attack on a users table: directly concatenating the input ada' OR '1'='1 turns a precise filter into an always-true condition.

SQL

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  is_admin INTEGER NOT NULL DEFAULT 0
);

INSERT INTO users (id, name, is_admin)
VALUES (1, 'ada', 0), (2, 'root', 1);

-- Dangerous concatenation (NEVER do this)
-- input = ada' OR '1'='1
SELECT * FROM users
WHERE name = 'ada' OR '1'='1';

The injected query returns ALL the rows.

The payload works because the quote prematurely closes the literal 'ada', then OR '1'='1' adds a universally true condition. A variant appends -- to comment out the rest of the original query. The lesson: as long as the data and the code share the same string, no manual escaping is truly reliable.

SQL

-- Positional placeholder
SELECT * FROM users WHERE name = ?;

-- Named placeholder
SELECT * FROM users WHERE name = :name;

Two forms of bound parameters in SQLite.

The remedy is the prepared statement: the driver first compiles the structure of the query, then binds the values separately. The bound data is never parsed as SQL, only compared. As a bonus, the compiled structure is reusable: binding several values in succession avoids recompiling the plan on every call.

SQL

-- The bound value stays data, not SQL
SELECT * FROM users
WHERE name = :name;
-- :name = ada' OR '1'='1
-- -> 0 rows: no user
--    is literally named that

The same payload, now harmless.

Apply least privilege. SQLite has no GRANT system: restrict via the file permissions, open read-only with PRAGMA query_only = ON, or filter actions with an authorizer. On PostgreSQL or MySQL, create a dedicated application user and GRANT only the necessary rights (often SELECT, INSERT, UPDATE), never DROP.

Knowledge check

Make sure you remember the key points of this lesson.

Why does a prepared statement with parameters prevent SQL injection?
- Because it encrypts the transmitted values
- Because the bound value is treated as data, never parsed as SQL
- Because it automatically escapes the quotes in the string
- Because it limits the length of user input
The payload ' OR '1'='1 succeeds at injection because it...
- saturates the memory of the database server
- closes the string literal then adds an always-true condition
- drops the table targeted by the query
- changes the privileges of the current user
Can you bind a table name via a ? parameter?
- Yes, placeholders accept any kind of identifier
- No: placeholders only bind values; an identifier must be validated against a whitelist
- Yes, but only with the named form :name
- Yes, provided the table already exists