SQL 125 🛢️ Dynamic SQL and SQL Injection Hazards in Procedural Code
Dynamic SQL is the construction of a SQL statement as a string at runtime, then executing it. It exists because some parts of a query cannot be parameterized: table names, column names, sort directions, and identifiers that the query planner needs to see as structure rather than data. The hazard is that the same mechanism that allows a procedure to select from a variable table name also allows an attacker to inject a UNION SELECT or a DROP TABLE if the variable is built from unvalidated input. The distinction that matters is between values and identifiers. Values can and must be parameterized. Identifiers cannot be parameterized, so they must be validated against a fixed set of permitted names .
Key point: A stored procedure does not make a query safe. A procedure that concatenates its parameter into a string and executes it is exactly as vulnerable as an application that does the same thing . The protection comes from parameterization and identifier allowlisting, not from the location of the code.
Why dynamic SQL exists in procedural code
The identifier problem. A query that filters on a column value can use a bind parameter: WHERE id = $1. A query that groups by a column name cannot. The column name is part of the query structure, and the database must know it before the query is planned. A reporting procedure that accepts a column name for ORDER BY or a table name for a count cannot bind those as parameters. It must construct the SQL string with the identifier embedded .
The administrative task problem. Maintenance procedures often need to operate on a variable set of tables. A procedure that refreshes all materialized views whose names match a pattern must construct a REFRESH MATERIALIZED VIEW statement for each one. The table name comes from the catalog, not from user input, but it is still a string that must be inserted into a statement and executed. PostgreSQL’s format() function with %I exists for this case .
The injection hazard. The hazard is not dynamic SQL itself. It is the combination of dynamic SQL and untrusted input. The canonical vulnerable pattern is a procedure that accepts a name and concatenates it into a query with || or string formatting. The input '; DROP TABLE users; -- closes the string literal, terminates the statement, and executes the injected command . The database cannot distinguish the developer’s SQL from the attacker’s SQL because they arrived as the same string.
a. The Safe Pattern: format() with %I and USING
PostgreSQL provides two mechanisms for safe dynamic SQL, and they solve different problems. The format() function with the %I specifier quotes an identifier, and the USING clause binds values as parameters.
-- VULNERABLE: identifier and value are both concatenated
CREATE OR REPLACE FUNCTION bad_count(tabname text, checked_user text)
RETURNS bigint AS $$
DECLARE c bigint;
BEGIN
EXECUTE 'SELECT count(*) FROM ' || tabname ||
' WHERE inserted_by = ''' || checked_user || ''''
INTO c;
RETURN c;
END;
$$ LANGUAGE plpgsql;-- SAFE: %I quotes the identifier, USING binds the value
CREATE OR REPLACE FUNCTION safe_count(tabname text, checked_user text)
RETURNS bigint AS $$
DECLARE c bigint;
BEGIN
EXECUTE format('SELECT count(*) FROM %I WHERE inserted_by = $1', tabname)
INTO c
USING checked_user;
RETURN c;
END;
$$ LANGUAGE plpgsql;The %I specifier is equivalent to quote_ident(). It wraps the identifier in double quotes if necessary and doubles any embedded double quotes. The result is a valid identifier, not a SQL fragment that can be terminated and extended . The $1 placeholder in the format string is bound by the USING clause, which passes the value separately from the query text. The value is never parsed as SQL, so a string containing a single quote is treated as a string containing a single quote, not as the end of a literal .
The format() function also supports %L for literals, which is equivalent to quote_nullable(). For values that must be embedded as literals rather than bound, %L produces a properly quoted string. But the USING clause is preferred because it avoids converting the value to text and back, and because it handles type conversion natively .
b. The Allowlist Pattern for Identifiers
Quoting an identifier prevents it from breaking out of its context, but it does not prevent a malicious identifier from being a valid identifier that references a sensitive table. format('%I', 'pg_shadow') produces a valid, quoted reference to the system table that contains password hashes. Quoting prevents injection but does not prevent access to objects the caller should not reach .
The defense is an allowlist. The identifier is checked against a fixed set of permitted names before it is used. If the input does not match exactly, the function rejects it.
-- SAFE: allowlist validation before format()
CREATE OR REPLACE FUNCTION safe_report(sort_column text)
RETURNS TABLE(id bigint, created_at timestamptz, total numeric) AS $$
BEGIN
IF sort_column NOT IN ('created_at', 'total', 'id') THEN
RAISE EXCEPTION 'Invalid sort column: %', sort_column;
END IF;
RETURN QUERY EXECUTE format(
'SELECT id, created_at, total FROM orders ORDER BY %I',
sort_column
);
END;
$$ LANGUAGE plpgsql;The allowlist approach is the only safe way to handle identifiers that come from user input. Escaping is not sufficient because a valid, escaped identifier can still reference an object the user should not access. The allowlist defines what is permitted, and everything else is rejected .
c. The Stored Procedure Illusion
A common misconception is that moving a query into a stored procedure protects it from injection. The PostgreSQL mailing list addressed this directly in a 2004 thread: using a stored procedure is just as unsafe as using a direct SQL statement unless the procedure itself uses parameterized queries . The location of the code does not matter. The construction of the query matters.
The same misconception applies to SECURITY DEFINER functions. A function that runs with the owner’s privileges and constructs a query from unvalidated input is a privilege escalation vector, not a security control. The SECURITY DEFINER attribute grants the function access to objects the caller cannot reach directly. If the function’s dynamic SQL is injectable, the attacker inherits that access .
-- VULNERABLE: SECURITY DEFINER with concatenated input
CREATE OR REPLACE FUNCTION bad_admin_query(username text)
RETURNS SETOF users AS $$
BEGIN
RETURN QUERY EXECUTE
'SELECT * FROM users WHERE name = ''' || username || '''';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Input: ' OR '1'='1
-- Result: SELECT * FROM users WHERE name = '' OR '1'='1'
-- Returns every row, with the definer's privilegesThe safe version uses format('%L') or, better, a parameterized query. The SECURITY DEFINER attribute is not the problem; the string construction is.
Complete Example Session
-- ============================================
-- PART 1: VULNERABLE FUNCTION
-- ============================================
-- Concatenation of identifier and value
CREATE OR REPLACE FUNCTION vuln_search(tabname text, name_filter text)
RETURNS SETOF record AS $$
BEGIN
RETURN QUERY EXECUTE
'SELECT * FROM ' || tabname ||
' WHERE name = ''' || name_filter || '''';
END;
$$ LANGUAGE plpgsql;
-- ============================================
-- PART 2: INJECTION ATTEMPT
-- ============================================
-- Calling with a malicious name_filter
SELECT * FROM vuln_search('users', ''' OR ''1''=''1');
-- The constructed query is:
-- SELECT * FROM users WHERE name = '' OR ''1''=''1
-- Returns every row
-- ============================================
-- PART 3: SAFE FUNCTION WITH %I AND USING
-- ============================================
CREATE OR REPLACE FUNCTION safe_search(tabname text, name_filter text)
RETURNS SETOF record AS $$
BEGIN
RETURN QUERY EXECUTE
format('SELECT * FROM %I WHERE name = $1', tabname)
USING name_filter;
END;
$$ LANGUAGE plpgsql;
-- ============================================
-- PART 4: SAFE FUNCTION REJECTS INJECTION
-- ============================================
SELECT * FROM safe_search('users', ''' OR ''1''=''1');
-- The value is bound as a parameter, not parsed as SQL
-- Returns zero rows because no user has that literal name
-- ============================================
-- PART 5: ALLOWLIST FOR IDENTIFIERS
-- ============================================
CREATE OR REPLACE FUNCTION safe_sort(sort_col text)
RETURNS TABLE(id bigint, total numeric) AS $$
BEGIN
IF sort_col NOT IN ('id', 'total') THEN
RAISE EXCEPTION 'Invalid sort column';
END IF;
RETURN QUERY EXECUTE
format('SELECT id, total FROM orders ORDER BY %I', sort_col);
END;
$$ LANGUAGE plpgsql;
-- ============================================
-- PART 6: SECURITY DEFINER WITH SAFE DYNAMIC SQL
-- ============================================
CREATE OR REPLACE FUNCTION admin_user_count()
RETURNS bigint AS $$
DECLARE c bigint;
BEGIN
-- No user input; identifier is a literal
EXECUTE 'SELECT count(*) FROM admin.users' INTO c;
RETURN c;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- ============================================
-- PART 7: SECURITY DEFINER WITH UNSAFE DYNAMIC SQL
-- ============================================
-- Do not do this
CREATE OR REPLACE FUNCTION admin_bad_query(tbl text)
RETURNS SETOF record AS $$
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM ' || tbl;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- ============================================
-- PART 8: FORMAT %L FOR LITERALS
-- ============================================
-- %L quotes a literal value safely
EXECUTE format('UPDATE users SET name = %L WHERE id = %L', new_name, user_id);
-- Equivalent to quote_nullable()
-- ============================================
-- PART 9: LEAST PRIVILEGE FOR DYNAMIC SQL FUNCTIONS
-- ============================================
-- Revoke public execute; grant only to specific roles
REVOKE ALL ON FUNCTION safe_search(text, text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION safe_search(text, text) TO app_user;
-- ============================================
-- PART 10: VERIFICATION
-- ============================================
-- Test with a malicious input and confirm it is treated as data
SELECT * FROM safe_search('users', '''; DROP TABLE users; --');
-- Returns zero rows; users table remainsThe ten parts trace the vulnerable pattern, the injection, the safe pattern, the allowlist, and the SECURITY DEFINER hazard. Part 4 is the verification: the same input that returned every row from the vulnerable function returns zero rows from the safe one because it is treated as a literal string, not as SQL.
Quick Reference
Safe Dynamic SQL Mechanisms
| Mechanism | Purpose | Equivalent |
|---|---|---|
format('%I', identifier) | Quote an identifier | quote_ident() |
format('%L', value) | Quote a literal | quote_nullable() |
USING value | Bind a parameter | $1, $2 placeholders |
| Allowlist check | Restrict identifiers | IF x NOT IN (...) |
Vulnerability Patterns
| Pattern | Risk |
|---|---|
'...' || user_input || '...' | Direct injection |
format('%s', user_input) in identifier position | Injection via identifier |
SECURITY DEFINER + concatenation | Privilege escalation |
| Stored procedure + concatenation | Same risk as application code |
Security Checklist
| Check | Required |
|---|---|
| No string concatenation of values | Yes |
%I for all dynamic identifiers | Yes |
| Allowlist for user-supplied identifiers | Yes |
USING for all dynamic values | Yes |
Least privilege on SECURITY DEFINER functions | Yes |
Best Practices
✅ Do This:
-- Use format(%I) for identifiers and USING for values
EXECUTE format('SELECT * FROM %I WHERE id = $1', tabname) USING id; -- ✅-- Validate identifiers against an allowlist
IF sort_col NOT IN ('created_at', 'total') THEN RAISE EXCEPTION; -- ✅-- Revoke public execute on SECURITY DEFINER functions
REVOKE ALL ON FUNCTION fn() FROM PUBLIC; -- ✅❌ Don’t Do This:
-- Concatenate values into the query string
EXECUTE 'SELECT * FROM t WHERE name = ''' || name || ''''; -- ❌-- Use format(%s) for an identifier
EXECUTE format('SELECT * FROM %s', tabname); -- %s does not quote -- ❌-- Assume stored procedures are safe by default
-- They are as vulnerable as their construction -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Injection in procedure | Values concatenated into query | Use USING |
| Identifier references sensitive table | %I quotes but does not restrict | Allowlist check |
SECURITY DEFINER escalates privileges | Function runs as owner | Revoke public, validate input |
format('%s') does not quote | %s is raw substitution | Use %I for identifiers |
| Procedure appears safe but calls unsafe function | Nested unsafe code | Audit all functions in call chain |
Real-World Examples
1. Vulnerable Concatenation
EXECUTE 'SELECT * FROM users WHERE name = ''' || p_name || '''';2. Safe Identifier Quoting
EXECUTE format('SELECT * FROM %I', tabname);3. Safe Value Binding
EXECUTE format('SELECT * FROM %I WHERE id = $1', tabname) USING id;4. Allowlist Check
IF col NOT IN ('a', 'b') THEN RAISE EXCEPTION 'Invalid'; END IF;5. Literal Quoting
EXECUTE format('UPDATE t SET name = %L WHERE id = %L', name, id);6. SECURITY DEFINER with Revoke
REVOKE ALL ON FUNCTION fn() FROM PUBLIC;7. Injection Test
SELECT * FROM safe_search('users', '''; DROP TABLE users; --');8. Format %I vs %s
format('%I', 'my table') -- "my table"
format('%s', 'my table') -- my table (not quoted)9. Quote Ident Directly
EXECUTE 'SELECT * FROM ' || quote_ident(tabname);10. Check Function Privileges
\df+ function_nameVisual
The Injection Mechanism
┌──────────────────────────────────────────────────────────────┐
│ DEVELOPER'S INTENT │
│ SELECT * FROM users WHERE name = 'Alice' │
│ │
│ INPUT: ' OR '1'='1 │
│ │
│ CONCATENATED QUERY │
│ SELECT * FROM users WHERE name = '' OR '1'='1' │
│ │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ The database sees one SQL string. ││
│ │ It cannot distinguish developer SQL from attacker SQL. ││
│ │ The WHERE clause is always true. ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ Parameterization sends the SQL and the value separately. │
│ The value is never parsed as SQL. │
│ │
└──────────────────────────────────────────────────────────────┘format() and USING
┌──────────────────────────────────────────────────────────────┐
│ EXECUTE format('SELECT * FROM %I WHERE id = $1', tabname) │
│ USING id; │
│ │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ %I │ │ $1 │ │
│ │ │ │ │ │
│ │ Quoted │ │ Bound by │ │
│ │ identifier │ │ USING clause │ │
│ │ │ │ │ │
│ │ tabname → │ │ id → value │ │
│ │ "users" │ │ (data, not SQL) │ │
│ └─────────────────┘ └─────────────────┘ │
│ │
│ The query text is fixed except for the quoted identifier. │
│ The value never becomes part of the query text. │
│ │
└──────────────────────────────────────────────────────────────┘Allowlist Decision
┌──────────────────────────────────────────────────────────────┐
│ USER-SUPPLIED IDENTIFIER: "pg_shadow" │
│ │
│ ┌─────────────────┐ │
│ │ format('%I', │────► Valid identifier │
│ │ 'pg_shadow') │ "pg_shadow" │
│ └────────┬────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Query executes │ Reads password hashes │
│ │ successfully │ If caller has privilege │
│ └─────────────────┘ │
│ │
│ QUOTING PREVENTS INJECTION. │
│ IT DOES NOT PREVENT ACCESS. │
│ │
│ ┌─────────────────┐ │
│ │ Allowlist check │────► Rejected │
│ │ IF NOT IN (...) │ "pg_shadow" is not permitted │
│ └─────────────────┘ │
│ │
│ The allowlist defines what is permitted. │
│ Everything else is rejected. │
│ │
└──────────────────────────────────────────────────────────────┘SECURITY DEFINER Hazard
┌──────────────────────────────────────────────────────────────┐
│ SECURITY DEFINER FUNCTION │
│ Runs with owner's privileges (e.g., admin) │
│ │
│ ┌─────────────────┐ │
│ │ Input: ' OR │ │
│ │ '1'='1 │ │
│ └────────┬────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Concatenated │ │
│ │ into query │ │
│ └────────┬────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Query runs as │ │
│ │ admin, returns │ │
│ │ every row │ │
│ └─────────────────┘ │
│ │
│ The definer's privileges are inherited by the injection. │
│ Use format(%I) and USING, and revoke public execute. │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Dynamic SQL hazard | String construction with untrusted input |
| Safe identifier quoting | format('%I', name) |
| Safe value binding | USING value with $1 placeholder |
| Allowlist | Reject identifiers not in a fixed set |
SECURITY DEFINER risk | Escalates privileges if injectable |
| Stored procedure safety | Depends on construction, not location |
format('%s') | Raw substitution, not quoting |
%L | Quoted literal, equivalent to quote_nullable() |
Key takeaways:
- The location of the code does not determine its safety. A stored procedure that concatenates input is as vulnerable as an application that concatenates input . The protection comes from parameterization and identifier allowlisting, not from moving the query into the database.
format('%I')quotes identifiers;USINGbinds values. These are the two mechanisms that make dynamic SQL safe in PostgreSQL. The%Ispecifier produces a valid quoted identifier, and theUSINGclause sends values separately from the query text so they are never parsed as SQL .- Quoting is not the same as access control.
format('%I', 'pg_shadow')produces a valid, quoted reference to a sensitive system table. Quoting prevents injection but does not prevent access. The allowlist is what restricts which identifiers are permitted . SECURITY DEFINERamplifies the consequences of injection. A function that runs with the owner’s privileges and constructs a query from unvalidated input is a privilege escalation vector. The fix is the same—format('%I')andUSING—plus revoking public execute and validating input .- The
format('%s')specifier is not a sanitizer. It substitutes the string as-is, without quoting. It is safe only for values that have already been validated or for literal SQL fragments that the developer controls. For identifiers,%Iis required; for values,USINGis preferred.
Remember: Dynamic SQL is a tool for the cases where parameterization cannot reach—identifiers, table names, sort directions. The hazard is not the tool; it is the string construction that treats untrusted data as query structure. The defense has two layers: format('%I') ensures that an identifier is syntactically valid and cannot break out of its context, and an allowlist ensures that a valid identifier is one the caller is permitted to use. Values never need dynamic SQL; they belong in USING. When a procedure uses format('%I') for identifiers and USING for values, it is safe regardless of where it runs. When it concatenates either, it is vulnerable regardless of whether it is called a stored procedure.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!