SQL / SECURITY, ROUTINES, AND DIALECTS
Parameterised queries and the boundary they enforce
Bind user input with placeholders so the database parses your SQL first and treats values as data, and know which parts of a query can never be parameterised.
What you will learn
- Write queries with placeholders and let EXECUTE or the driver supply every user value
- Explain why identifiers and keywords can never be parameterised, only values
- Pass a list as one array parameter matched with = ANY($1), not a comma string
- Escape % and _ in bound LIKE patterns; parameters stop code, not wildcards
Understanding Parameterised queries and the boundary they enforce
A parameterised query reaches the server in two pieces: the SQL text, which contains placeholders, and the values, which travel separately. The server parses and plans the text on its own, so by the time a value arrives the statement's shape is already fixed as a parse tree in which $1 is a leaf marked 'one value of this type goes here'. Binding fills that leaf; nothing re-reads the value as characters, so a quote, a semicolon or a -- inside it has no syntactic role left to play.
That is the whole boundary, and it also explains its limits. A placeholder can stand only where a single value can stand, so it cannot be a table name, a column name, an operator, or the DESC in an ORDER BY: those decide the statement's structure, and the structure is settled before the value exists. Escaping works the other way round, splicing input into the text and then trying to defuse the dangerous characters, which is why it must be done perfectly every time for each dialect and string type, while a placeholder never puts them in the text at all.
In practice this means you never write quotes around a placeholder and never pre-escape a bound value; quoting is a wire-format problem the driver owns. The declared type matters as much as the position: bound as int, the input 007 is compared as the number 7, while binding text against an integer column raises an operator error instead of silently coercing. Plan reuse comes free because parsing and planning belong to the text, though PostgreSQL may switch a statement executed several times to one generic plan that suits some parameter values better than others.
CREATE TABLE members (id int, username text);
INSERT INTO members VALUES (1, 'alice'), (2, 'bob');
PREPARE find_member (text) AS
SELECT id, username FROM members WHERE username = $1;
EXECUTE find_member('bob');
EXECUTE find_member('bob'' OR 1=1 --');A placeholder is a hole exactly one value wide in a statement that was already parsed, which is why bound input can never become SQL syntax and never supply an identifier.
Worked examples
One placeholder, one value: lists need an array
Shows how a variable-length IN list is parameterised as a single array value rather than as text with commas.
CREATE TABLE items (id int, name text);
INSERT INTO items VALUES (1, 'nut'), (2, 'bolt'), (3, 'nail');
PREPARE pick (int[]) AS
SELECT id, name FROM items WHERE id = ANY($1) ORDER BY id;
EXECUTE pick('{1,3}');Example explained
Line 1PREPARE pick (int[]) declares the hole as one array-valued parameter, not three separate numbers.
Line 2= ANY($1) tests each row's id against the elements of that single value, which is what an IN list means once it is data.
Line 3'{1,3}' is the literal form of an int[]; binding the text '1,3' would fail because a string is not an array of integers.
Line 4Row 2 is missing purely because 2 is not an element of the bound array, not because of anything in the SQL text.
Wildcards ride inside the bound value
Demonstrates that concatenating % onto a parameter is safe but leaves LIKE metacharacters under the caller's control.
CREATE TABLE people (name text);
INSERT INTO people VALUES ('Anna'), ('O''Hara'), ('Bob');
PREPARE search (text) AS
SELECT name FROM people WHERE name LIKE '%' || $1 || '%' ORDER BY name;
EXECUTE search('''');
EXECUTE search('%');Example explained
Line 1The || happens at run time on values, so the statement still has exactly one parsed hole and stays safe.
Line 2EXECUTE search('''') passes a single apostrophe, which matches O'Hara as ordinary data instead of closing a string.
Line 3EXECUTE search('%') returns every row: % is meaningful to LIKE itself, so escape % and _ when the search must be literal.
Important notes
PREPARE and EXECUTE at the console are the visible form of what a driver does over the wire; the placeholder spelling differs ($1, ?, :name) but the value-versus-structure boundary is the same, and a PostgreSQL prepared statement lives only for the current session.
Parameters protect the parser, not your rules: a perfectly bound value can still be another customer's id, so authorisation belongs in the predicate.
Common mistakes
Quoting the placeholder, as in WHERE username = '$1': the text now holds no parameter, so PREPARE builds a zero-argument statement that compares every row with the two-character string $1, the query returns nothing, and the fix often becomes concatenation again.
Trying to parameterise an identifier, as in SELECT * FROM $1: parsing fails before any value exists, and the usual reaction is to build the whole query by concatenation, values included.
Binding '1,2,3' to a single placeholder for an IN list: it is one value, so you get a type error or zero matches rather than three rows.
Try it yourself
Change, predict, then run
Create notes(id int, body text) with three rows, PREPARE find(text) AS SELECT * FROM notes WHERE body LIKE '%' || $1 || '%', then EXECUTE it with the value a and with the value % and explain why the second returns every row.
Open the SQL workspaceCheck your understanding
An application sends SELECT id, total FROM orders WHERE customer = ? ORDER BY ? and binds 'acme' and 'total DESC'. The rows come back not sorted by total. What does that tell you about placeholders?
- A placeholder supplies one value, and a value in ORDER BY is the same sort key for every row, so the sort does nothing
- The driver escaped the space in 'total DESC', so the column name no longer resolved
- ORDER BY is evaluated before parameters are bound, so a placeholder there is discarded
- The plan cached on the first execution fixed the sort order for all later executions
Show answer
The statement was parsed before the value arrived, so the second placeholder is a value expression, not a sort specification; every row gets the identical key 'total DESC' and the ordering is arbitrary. The escaping answer is tempting but describes the wrong mechanism: a bound value is never spliced into the SQL text, so there is nothing to escape and no way for the string to become an identifier, which is why sort columns must come from an allow-list in code.