Skip to content
Tutorials Center
My learning

Topics

Lesson 77 of 90 ยท Security and Administration

SQL Injection and Safe Queries in PostgreSQL

Prevent SQL injection in PostgreSQL with parameters, format() using %I and %L, safe SECURITY DEFINER functions with a fixed search_path, and least privilege.

You already know the core rule from the general SQL injection lesson: never build SQL by gluing input into the query text; send input as parameters. PostgreSQL follows the same rule, and it adds a few details of its own: a protocol that helps you, quoting functions for the cases where parameters can't be used, and one attack that belongs to PostgreSQL's schema search path.

This lesson covers the PostgreSQL side of safe queries: how parameters travel to the server, how to write dynamic SQL inside a function with format(), how to make SECURITY DEFINER functions safe, and how least privilege limits the damage when something slips through. The server-specific code is read-only; live examples use SQLite's equivalents (quote(), printf('%Q'), printf('%w')) to show the same quoting rules in action.

Keep code and data apart

Parameters Values travel separately from the SQL text.
Quoting format() with %I for names and %L for values.
Least privilege A hijacked query can only do what its role may do.

Parameters first. Quoting is for what parameters can't do.

1 / 2

Parameters: how PostgreSQL keeps data out of SQL

PostgreSQL's client protocol has two ways to run a query. The simple protocol sends one text string, which may even contain several statements separated by ;, and the server parses all of it. The extended protocol, which drivers use when you pass parameters, sends the statement with placeholders $1, $2, ... in one message and the values in another. The server parses the statement text first, so a value can never change its structure. A statement sent through the extended protocol also can only contain one command, which closes the stacked-queries door ('; DROP TABLE ...).

You can see placeholders directly in SQL with PREPARE, which is read-only here because it is a server feature:

SQL
PREPARE find_user (text) AS
  SELECT id, email FROM users WHERE email = $1;

EXECUTE find_user('x'' OR ''1''=''1');   -- finds nothing: the whole string is one value

The hostile text is compared as a plain string with the email column. No row has that email, so nothing is returned. Your driver does the same thing without PREPARE: in Python cur.execute("SELECT ... WHERE email = %s", (email,)) with psycopg 3, in Node client.query("SELECT ... WHERE email = $1", [email]), in Java PreparedStatement with ?. Connecting from code shows each driver.

Two driver traps are worth knowing. Some drivers emulate parameters by escaping them client-side and sending a single string (PDO with ATTR_EMULATE_PREPARES on, and psycopg2). That is usually safe when done correctly, but it is not the same guarantee as server-side binding, so prefer real parameters where your driver offers them. And placeholders stand for values only, never for table names, column names or keywords like ASC.

The next example is a live version of the idea. The hostile text is stored as data in a table and compared with =, exactly like a bound parameter. For contrast, the same text pasted into the query changes its meaning:

SQL Data stays data (like a bound parameter)

Press Run (or Ctrl + Enter) to see the result here.

The parameter version finds 0 rows; the pasted version finds all of them.

Dynamic SQL inside functions: format()

Inside PL/pgSQL you sometimes must build a statement as text, for example when the table name is a variable. EXECUTE runs such a string. Two tools keep it safe:

  • EXECUTE ... USING passes values as real parameters.
  • format() builds the text, with %I quoting an identifier and %L quoting a literal. The older quote_ident(), quote_literal() and quote_nullable() do the same jobs.
SQL
CREATE FUNCTION count_rows(tbl text, min_id bigint) RETURNS bigint
LANGUAGE plpgsql AS $$
DECLARE n bigint;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I WHERE id >= $1', tbl)
    INTO n
    USING min_id;
  RETURN n;
END $$;

Here the table name goes through %I (it is an identifier, so it can't be a parameter) and the number goes through USING (it is a value, so it should be one). If someone passes tbl = 'users"; DROP TABLE users; --', %I wraps it in double quotes and doubles the embedded quote, so it becomes one very strange table name that does not exist, instead of a second command.

Avoid %s for anything that came from outside: it inserts the text as it is, with no quoting at all. Use %L only when a parameter really isn't possible (for example in a DEFAULT clause in DDL), and remember that %L turns NULL into the bare word NULL, which is what you want, while quote_literal(NULL) returns NULL and would silently wipe out the whole string.

SQLite offers the same two quoting rules through printf: %Q is the literal quote (it wraps in single quotes, doubles embedded quotes and turns NULL into NULL), and %w doubles double quotes for identifiers. This example shows what each approach produces for tricky inputs:

SQL Naive concatenation vs quoting (printf %Q and %w)

Press Run (or Ctrl + Enter) to see the result here.

Look at naive_concat for the third row: the quote in the input ends the string and the rest becomes a second statement. In quoted_literal the quote is doubled, so the whole input stays inside one string. The NULL input is the easy-to-miss case: concatenation turns the whole expression into NULL, while %Q produces a correct NULL literal.

Identifiers need an allowlist as well as quoting. Quoting makes a hostile name harmless, but you usually want to accept only names you chose. Map the user's choice to a known value:

SQL Allowlist for sort columns, quoting for names

Press Run (or Ctrl + Enter) to see the result here.

Unknown or hostile requests fall back to a safe default, and the identifier is quoted on the way out. In PL/pgSQL the same shape is CASE p_sort WHEN 'price' THEN 'unit_price' ... ELSE 'created_at' END followed by format('ORDER BY %I', col).

search_path and SECURITY DEFINER

A SECURITY DEFINER function runs with the privileges of the role that created it, much like sudo. PostgreSQL looks up unqualified names (tables, functions, operators) through the search_path, and the caller's session controls that setting. That opens a classic attack: if a definer function calls lower(email) or reads accounts without a schema, an attacker who can create objects in a schema earlier on the path (or in the temporary schema, which is searched first for tables) can put a look-alike function or table there, and the definer function runs it with its owner's rights.

The defence is to pin the path inside the function and to qualify names yourself:

SQL
CREATE FUNCTION public.promote_user(p_id bigint) RETURNS void
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
  UPDATE public.users SET role = 'member' WHERE id = p_id;
$$;

REVOKE ALL ON FUNCTION public.promote_user(bigint) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.promote_user(bigint) TO app_user;

Three details matter. SET search_path on the function overrides the caller's value while it runs. Putting pg_catalog first stops look-alike replacements of built-in functions. And REVOKE ... FROM PUBLIC is needed because new functions are executable by everyone by default. Since PostgreSQL 15, ordinary users no longer have CREATE on the public schema by default, which closes much of the old hole, but pinning the path is still the right habit. For tables that attackers could shadow with temporary tables, list pg_temp last: SET search_path = pg_catalog, public, pg_temp.

Least privilege: limiting the blast radius

Assume a bug will eventually let a query through. What can it do then depends on the role the application uses:

  • Connect as a role that does not own the tables and isn't a superuser. It should have SELECT, INSERT, UPDATE and DELETE on the tables it needs and nothing else (see roles and privileges).
  • Run migrations with a different, more powerful role than the web application uses.
  • Use row-level security so a hijacked query for one tenant still can't read another tenant's rows.
  • Set statement_timeout for the application role so an injected expensive query can't run forever, and never show raw database errors to users.
  • Remember LIKE wildcards: % and _ in user input are still wildcards even when bound as a parameter. Escape them (\%) when users search for literal text.

None of this replaces parameters. It makes the day when a mistake happens survivable.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why can a statement sent with bound parameters not be turned into two commands by the input?

    With the extended protocol the structure of the statement is fixed before any value arrives, and values are never parsed as SQL.
  2. 2. In format('SELECT * FROM %I WHERE id = %L', tbl, id), what do the two specifiers do?

    %I produces a safely quoted identifier and %L a safely quoted string literal; %s would insert the text with no quoting.
  3. 3. Which setting protects a SECURITY DEFINER function from look-alike objects planted in the caller's search path?

    A function-level SET search_path replaces the caller's value while the function runs, so unqualified names resolve where you intend.
  4. 4. A user sort choice has to become part of ORDER BY. What is the safest approach?

    Placeholders only stand for values, not identifiers. An allowlist ensures only known names reach the query, and %I quotes them correctly.

Summary

  • Pass input as parameters ($1 in SQL, %s/? in drivers); values never become SQL, and the extended protocol runs one command per statement.
  • Placeholders can't stand for identifiers: use an allowlist plus format('%I'), and use %L or EXECUTE ... USING for values; avoid %s for untrusted text.
  • Pin search_path on every SECURITY DEFINER function, qualify names, and revoke EXECUTE from PUBLIC.
  • Give the application a non-owner role with only the privileges it needs, add row-level security and timeouts, and hide database errors from users.
  • Escape LIKE wildcards separately; parameters don't do that for you.

Next: Monitoring and System Catalogs.