SQL Injection and Parameterised Queries

Concept

SQL injection happens when user input is concatenated into a SQL string, so the input can stop being data and start being code. It remains one of the most damaging and most preventable vulnerabilities in software.

The vulnerable pattern

-- Application code (any language) builds this string:
--   "SELECT * FROM users WHERE email = '" + input + "' AND active = 1"

-- Input: asha@example.com     -> harmless
SELECT * FROM users WHERE email = 'asha@example.com' AND active = 1;

-- Input: ' OR '1'='1        -> the filter is gone
SELECT * FROM users WHERE email = '' OR '1'='1' AND active = 1;

-- Input: '; DROP TABLE users; --   -> a second statement, if the driver allows it
SELECT * FROM users WHERE email = ''; DROP TABLE users; -- ' AND active = 1;

The fix: parameterised queries

-- The query is sent with placeholders and planned BEFORE any data arrives
PREPARE stmt FROM 'SELECT * FROM users WHERE email = ? AND active = ?';
SET @email = 'asha@example.com', @active = 1;
EXECUTE stmt USING @email, @active;
DEALLOCATE PREPARE stmt;

In application code this is the driver's prepared statement API - PDO or mysqli in PHP, PreparedStatement in Java, parameterised queries in .NET, %s placeholders passed as a tuple in Python's DB-API. This site's own repository layer does exactly that:

-- The shape every data access layer should use:
--   db_all('SELECT * FROM notes WHERE category_id = ? AND status = ?',
--          [$categoryId, 'published']);
--
-- The SQL string is a constant. The values travel separately.

The protection is structural, not textual. The statement is parsed and planned first, so its shape is already fixed when the value arrives. There is no parsing step left for the input to influence.

What parameters cannot do

-- Placeholders bind VALUES only. These are not values:
SELECT * FROM ? WHERE id = 1;              -- table name: not bindable
SELECT * FROM orders ORDER BY ?;           -- column name: not bindable
SELECT * FROM orders ORDER BY id ?;        -- ASC/DESC: not bindable

For dynamic table names, column names and sort directions, use an allowlist - never the raw input:

-- Application pseudocode
-- allowed = { 'latest': 'publish_at DESC',
--             'title':  'title ASC',
--             'views':  'views_count DESC' }
-- orderBy = allowed[input] ?? allowed['latest']
-- sql = "SELECT ... ORDER BY " + orderBy   // safe: only our own strings

This is exactly what this application's safe_sort() helper and NoteRepo::orderClause() do - the user picks a key, and the code maps it to a fixed fragment it wrote itself.

Defence in depth

Important rules

• Escaping functions are a weaker fallback, not the solution. Character set edge cases have defeated them historically.
• Stored procedures are not automatically safe - a procedure that concatenates its parameters into dynamic SQL is just as vulnerable.
• ORMs are safe when you use their query builders, and unsafe the moment you drop to raw SQL with interpolation.
• Injection is not limited to SELECT: INSERT, UPDATE, DELETE and ORDER BY are all reachable.
• Numeric fields are injectable too - without quotes, 1 OR 1=1 needs no quote to escape.

Common mistakes

• Parameterising most queries and hand building one "just this once".
• Interpolating a sort column because it "cannot be parameterised anyway".
• Trusting that a value came from a dropdown, so it must be valid.
• Relying on client side validation.
• Running the application as the database root user.
• Building dynamic SQL inside a stored procedure and assuming the procedure boundary protects you.

Best practices

• Parameterise every query with user input. Treat any exception as a bug.
• Allowlist identifiers and sort directions; map keys to fragments you wrote.
• Give the application account the minimum privileges it needs - see the privileges note.
• Validate and cast types at the application boundary.
• Log the failure, show the user a generic message.
• Add a lint or grep rule that fails CI on SQL built by concatenation.

Practice

1. Rewrite "SELECT * FROM notes WHERE slug = '" + slug + "'" as a parameterised query.
2. Design a safe sort parameter for a listing page that offers newest, oldest, title and views.
3. Explain why WHERE id = " + id is injectable even with no quotes involved.