SQL Injection: How It Works and How to Prevent It
SQL injection happens when user input becomes SQL code. Learn how it works and why parameterised queries are the definitive fix.
By Velloreth
What SQL injection is
SQL injection happens when user input is treated as part of a query rather than as data. The application builds a query by concatenating a string, and the attacker supplies input that contains SQL syntax, which changes the meaning of the query.
The classic example is a login check.
// Vulnerable
const query = `SELECT * FROM users WHERE email = '${email}' AND password = '${password}'`;
If the attacker supplies ' OR '1'='1 as the email, the query becomes:
SELECT * FROM users WHERE email = '' OR '1'='1' AND password = ''
The condition is always true, so the query returns the first user, and the attacker is logged in without a password.
Why it works
The input is concatenated into the query string. The database receives one string and parses it as SQL. It cannot tell which part came from the application and which part came from the user.
The database has no way to distinguish code from data once the two are concatenated. The parser sees a valid query, and it executes it.
The application’s privileges become the attacker’s privileges. If the connection can read every table, the attacker can read every table. If it can drop a table, the attacker can drop a table.
The definitive fix: parameterised queries
A parameterised query, also called a prepared statement, sends the query and the values separately. The database parses the query with placeholders, and the values are bound to those placeholders as data. The input is never parsed as SQL, so no input can change the query’s meaning.
// Safe
const query = 'SELECT * FROM users WHERE email = $1 AND password_hash = $2';
const result = await db.query(query, [email, passwordHash]);
This works regardless of the input. A value of ' OR '1'='1 is treated as a literal string, and the query looks for a user whose email is exactly that string. It finds none.
The same pattern applies in every language.
# Python with psycopg
cur.execute("SELECT * FROM users WHERE email = %s", (email,))
// Java with JDBC
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE email = ?");
stmt.setString(1, email);
// PHP with PDO
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $email]);
The cases parameters do not cover
A parameter can only stand in for a value, not for an identifier or a keyword. A dynamic column name, a table name, or an ORDER BY direction cannot be a parameter, and this is where injection still happens.
Use an allowlist. Map the user’s input to a known-safe value rather than interpolating it.
const SORTABLE = {
name: 'name',
created: 'created_at',
price: 'price',
};
const column = SORTABLE[req.query.sort] ?? 'created_at';
const direction = req.query.dir === 'asc' ? 'ASC' : 'DESC';
const query = `SELECT * FROM products ORDER BY ${column} ${direction}`;
The allowlist is the fix. The value that reaches the query is one of a fixed set, so no input can inject syntax. A regular expression that strips characters is a weaker version of the same idea and is easier to get wrong.
Defence in depth
Least privilege. The application’s database user should have only the permissions it needs. A web application rarely needs DROP TABLE or access to the users table from a reporting query. If an injection does occur, the damage is bounded by the privileges.
A separate user per service. A reporting service and a web application should not share a database user. A compromise of one does not grant the other’s access.
Error handling. A database error that is returned to the user leaks the schema, the table names and the query. Log the detail and return a generic message.
A WAF. A web application firewall can block known patterns, which raises the cost of an attack. It is a mitigation, not a fix, because a payload can be encoded to evade a pattern.
Monitoring. A query that returns an unusual number of rows, or a login that succeeds without a password check, is a signal. Logging the query shape and alerting on anomalies catches an attack that a static defence missed.
Why parameterisation works
The reason a parameterised query is immune to injection is worth understanding, because it explains why the alternatives are weaker. When a query is sent with placeholders, the database parses the statement first and produces a plan with the placeholders as slots. The values are then bound to those slots, and the binding is a data operation rather than a parsing one. The parser has already finished, so a value that contains SQL syntax is never seen by it. This is a structural property rather than a filter, which is why it holds for any input, including an input that no filter anticipated. Escaping, by contrast, tries to neutralise the dangerous characters before the parser sees them, and it depends on knowing every character that matters in every context. The structural approach does not depend on that knowledge, which is why it is the definitive fix rather than the best available filter.
Common mistakes
Concatenating input into a query. This is the vulnerability. Use parameters.
Escaping instead of parameterising. Escaping depends on the database, the character set and the connection settings, and a single missed case is a vulnerability. It is a fallback, not a primary defence.
Trusting an ORM. An ORM parameterises the queries it generates. A raw query you write yourself is your responsibility, and most ORMs let you write one.
A dynamic ORDER BY without an allowlist. This is the most common remaining injection, because a parameter cannot be used and the developer interpolates instead.
A database user with too many privileges. The injection is the bug; the privileges determine the blast radius.
Returning the database error. The error message tells the attacker the schema, which makes the next attempt easier.
A quotable summary
SQL injection happens when user input is concatenated into a query and parsed as SQL, which lets an attacker change the query’s meaning. The definitive fix is a parameterised query, which sends the query and the values separately so the input is never parsed as code. Parameters cannot stand in for an identifier, so a dynamic column or sort direction needs an allowlist. Least privilege, generic error messages and monitoring limit the damage if an injection does occur, and a WAF is a mitigation rather than a fix.
Related reading
For the input side, see input-validation-guide and for the output side, xss-prevention-guide. For the broader context, orm-vs-raw-sql and security-headers-guide.