SQL SQL Injection

SQL injection is a security vulnerability that lets an attacker manipulate a database query by sneaking their own SQL code into input that an application treats as plain data. It happens whenever untrusted input (a form field, URL parameter, or API payload) is concatenated directly into a SQL string instead of being kept separate from the query’s logic. It remains one of the most damaging and most common web application vulnerabilities, because a single unguarded query can expose, modify, or delete an entire database.

Overview: How SQL Injection Works

A database engine does not know the difference between “code the developer wrote” and “data the user supplied” — it only knows text. When an application builds a query by gluing together fixed SQL text and a variable, the variable’s contents become part of the SQL grammar the instant the whole string is sent to the database. If that variable contains characters with special meaning in SQL — a single quote ', a comment marker --, a semicolon ;, or keywords like OR and UNION — the parser will happily interpret them as part of the query structure rather than as inert data.

For example, a login check might be built in application code as a template like SELECT * FROM users WHERE username = '<input>' AND password = '<input>', where the angle-bracketed parts are substituted with whatever the user typed. If the developer expects only names and passwords to arrive, but never enforces that, an attacker can type SQL syntax instead of a name, and that syntax gets executed with full query privileges.

Syntax: Anatomy of an Injection

There is no special “injection syntax” — that is exactly the problem: injected text is just ordinary SQL, placed somewhere the developer didn’t expect it. The table below summarizes the most common injection categories you’ll encounter.

Type	How it works	Typical payload fragment
Tautology / auth bypass	Adds a condition that is always true so a `WHERE` clause matches every row	`' OR '1'='1`
Comment injection	Uses `--` or `/* */` to cut off the rest of the intended query	`admin'--`
UNION-based	Appends a second `SELECT` to pull data from another table into the visible output	`' UNION SELECT username, password FROM users--`
Stacked / batch queries	Uses a statement terminator to append entirely new statements (only works if the driver allows multi-statement execution)	`'; DROP TABLE users;--`
Blind / boolean	No data is returned directly; the attacker infers information from true/false page behavior	`' AND 1=1--` vs `' AND 1=2--`

Examples

The examples below run against a small users table so you can see the exact rows an injected query returns, not just describe it abstractly.

Example 1: A normal, safe-looking query

First, the query as intended — a user submits a matching username and password.

CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, password TEXT, is_admin INTEGER);
INSERT INTO users (id, username, password, is_admin) VALUES
  (1, 'alice', 'alicepass123', 0),
  (2, 'bob', 'bobpass456', 0),
  (3, 'admin', 'sup3rSecretAdminPW', 1);

SELECT id, username, is_admin FROM users
WHERE username = 'alice' AND password = 'alicepass123';

Result:

id	username	is_admin
1	alice	0

Exactly one row comes back — alice’s own record — because both conditions are satisfied only by her row.

Example 2: Tautology-based authentication bypass

Now imagine the username field is built by string concatenation and the attacker types ' OR '1'='1 followed by a comment marker instead of a real username. The query the database actually receives looks like this:

CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, password TEXT, is_admin INTEGER);
INSERT INTO users (id, username, password, is_admin) VALUES
  (1, 'alice', 'alicepass123', 0),
  (2, 'bob', 'bobpass456', 0),
  (3, 'admin', 'sup3rSecretAdminPW', 1);

SELECT id, username, is_admin FROM users
WHERE username = '' OR '1'='1' -- ' AND password = 'wrongpassword';

Result:

id	username	is_admin
1	alice	0
2	bob	0
3	admin	1

The -- comments out the password check entirely, and '1'='1' is always true, so the WHERE clause matches every row. The attacker is logged in as the very first user returned — often an administrator — without knowing any password.

Example 3: UNION-based data exfiltration

A product search box that builds WHERE product_name = '<input>' can be abused to pull data out of a completely unrelated table, as long as the attacker matches the column count and roughly matching types.

CREATE TABLE products (product_id INTEGER PRIMARY KEY, product_name TEXT, price REAL);
INSERT INTO products (product_id, product_name, price) VALUES
  (1, 'Keyboard', 49.99),
  (2, 'Mouse', 19.99),
  (3, 'Monitor', 199.99);

CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, password TEXT, is_admin INTEGER);
INSERT INTO users (id, username, password, is_admin) VALUES
  (1, 'alice', 'alicepass123', 0),
  (2, 'bob', 'bobpass456', 0),
  (3, 'admin', 'sup3rSecretAdminPW', 1);

SELECT product_name, price FROM products
WHERE product_name = ''
UNION SELECT username, password FROM users
ORDER BY 1;

Result:

product_name	price
admin	sup3rSecretAdminPW
alice	alicepass123
bob	bobpass456

The empty product_name = '' clause matches zero real products, so every visible row actually comes from the injected UNION SELECT against users — usernames and passwords are displayed disguised as “product names” and “prices” on what looks like an ordinary search results page.

How It Works Step by Step (Under the Hood)

Understanding why this works requires following the request from application code to the database engine:

1. String assembly: The application concatenates a fixed SQL template with untrusted input, producing one plain text string.
2. Transmission: That single string is handed to the database driver and sent to the database engine exactly as written — the driver has no idea which parts were “supposed” to be data.
3. Parsing: The engine’s parser tokenizes the text according to SQL grammar rules. It has no concept of trust; a quote character always ends a string literal, OR is always a logical operator, and UNION always starts a set operation, regardless of where those characters came from.
4. Query planning: The parsed statement is turned into an execution plan (deciding join order, index usage, and so on) just like any other valid query — there is no separate, more suspicious code path for an injected query.
5. Execution: The plan runs against the tables and returns whatever the resulting, attacker-modified WHERE/UNION/statement logic dictates, following the normal logical order of FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

Parameterized queries (also called prepared statements) break this chain at step 1. The SQL template is sent to the database and parsed once, with placeholders (commonly ?, or driver-specific forms like %s or :name) marking where values go. The user’s data is sent separately, as a value to bind into an already-fixed, already-parsed plan. Because the data is never re-parsed as SQL text, it cannot introduce new clauses, comments, or statements no matter what characters it contains.

Common Mistakes

Mistake 1: Assuming numeric fields are safe

Developers often escape or quote string inputs but forget that numeric-looking fields need protection too, since no quotes are required to inject there.

CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, password TEXT, is_admin INTEGER);
INSERT INTO users (id, username, password, is_admin) VALUES
  (1, 'alice', 'alicepass123', 0),
  (2, 'bob', 'bobpass456', 0),
  (3, 'admin', 'sup3rSecretAdminPW', 1);

SELECT id, username, is_admin FROM users WHERE id = 1 OR 1=1;

Result: all three rows are returned (ids 1, 2, and 3), even though the page only meant to fetch the record for id = 1. A defense that only escapes single quotes does nothing here, because OR 1=1 needs no quotes at all. The real fix is to use a bound parameter for id, not to sanitize quote characters.

Mistake 2: Trusting a keyword blacklist

Filtering out the literal word DROP or UNION is easy to bypass with mixed case (UnIoN), inline comments (UNI/**/ON), or URL/character encoding, and it does nothing to stop tautology attacks like Example 2 that use no dangerous keywords at all. Blacklists react to specific attack strings instead of eliminating the underlying cause — string concatenation of untrusted data into SQL text.

Mistake 3: Allowing multiple statements per query

Some database APIs will happily execute several semicolon-separated statements submitted in a single call. If user input can reach that call unfiltered, an attacker isn’t limited to reading data — they can modify or destroy it.

CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, password TEXT, is_admin INTEGER);
INSERT INTO users (id, username, password, is_admin) VALUES
  (1, 'alice', 'alicepass123', 0),
  (2, 'bob', 'bobpass456', 0),
  (3, 'admin', 'sup3rSecretAdminPW', 1);

SELECT id, username FROM users WHERE username = 'alice';
DROP TABLE users;
SELECT 'users table dropped' AS result;

Result: the first statement matches alice’s row, but the second statement then drops the entire users table, and the third statement executes against a database that no longer has a users table at all. A single injected semicolon turned a read into irreversible data loss. Most modern drivers disable multi-statement execution by default for exactly this reason — never re-enable it to accept raw user input.

Best Practices

Use parameterized queries / prepared statements for every query that includes variable data — this is the primary defense, not an optional extra.
Prefer a well-supported ORM or query builder, which parameterizes queries for you by default, over hand-built SQL strings.
Never build identifiers (table names, column names, sort direction) from raw user input; instead validate them against a fixed whitelist of allowed values.
Apply the principle of least privilege to database accounts used by applications (e.g. no DROP/DELETE rights for a read-only reporting service) so a successful injection has limited blast radius.
Validate and constrain input types and formats (length, format, allowed characters) as defense-in-depth — but never as a substitute for parameterization.
Disable multi-statement (“stacked query”) execution in the database driver unless you have a specific, controlled reason to need it.
Avoid exposing raw database error messages to end users; detailed errors help attackers refine payloads.
Use a web application firewall and automated security scanning as an additional layer, not a replacement for secure query construction.

Practice Exercises

Exercise 1: Rewrite the vulnerable login check WHERE username = '<input>' AND password = '<input>' as a parameterized query template, naming the placeholders you would bind username and password to.
Exercise 2: A vulnerable search feature builds SELECT product_name, price FROM products WHERE category = '<input>'. Craft an injection payload for the category field that would use UNION to reveal every row of the users table shown in this lesson. (Hint: match the column count and order of product_name, price.)
Exercise 3: Explain, in your own words, why blacklisting the word DROP would fail to stop the auth-bypass payload from Example 2. What general property of the attack does a blacklist fail to address?

Summary

SQL injection happens when untrusted input is concatenated into SQL text and gets parsed as code instead of treated as data.
Common forms include tautologies (OR '1'='1'), comment injection, UNION-based data theft, stacked/batch statements, and blind boolean probing.
The database engine cannot distinguish “intended” SQL from “injected” SQL once they’re merged into one string — both are parsed and executed identically.
Parameterized queries fix this by sending the SQL template and the user data separately, so data can never be re-parsed as syntax.
Escaping quotes and keyword blacklists are incomplete defenses; numeric-context and encoding-based payloads routinely bypass them.
Defense-in-depth — least-privilege database accounts, input validation, and disabling multi-statement execution — limits damage even if one layer fails.

SQL SQL Injection

Overview: How SQL Injection Works

Syntax: Anatomy of an Injection

Examples

Example 1: A normal, safe-looking query

Example 2: Tautology-based authentication bypass

Example 3: UNION-based data exfiltration

How It Works Step by Step (Under the Hood)

Common Mistakes

Mistake 1: Assuming numeric fields are safe

Mistake 2: Trusting a keyword blacklist

Mistake 3: Allowing multiple statements per query

Best Practices

Practice Exercises

Summary

Table of Contents