SQL Injection, in depth: dynamic SQL in the database, privilege containment and detection

Stored routines can reopen injection

Injection happens whenever untrusted text is handed to a SQL parser as code. A bind parameter guarantees the value arrives at the server as data. But if a routine then splices that value into a new SQL string and executes it, the value meets a second parser, and the first protection is irrelevant. The phrase "we use stored procedures, so we are safe" is only true for procedures with static SQL.

Here is the vulnerable shape in PL/pgSQL. The application calls search_orders($1, $2) with properly bound arguments, and the function undoes that care:

-- VULNERABLE: arguments are concatenated into a new statement
CREATE FUNCTION search_orders(p_sort text, p_status text)
RETURNS SETOF orders LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT * FROM orders WHERE status = ''' || p_status ||
    ''' ORDER BY ' || p_sort;
END $$;

A status value containing a quote ends the string literal early, and whatever follows is parsed as SQL; an illustrative value such as x' OR '1'='1 widens the filter to every row. The sort argument is worse, since it is not even inside quotes. The fix separates the two kinds of input. Values travel through USING as real parameters, and identifiers are validated against an allowlist and quoted with format('%I'):

-- FIXED: values bound with USING, identifier allowlisted and quoted
CREATE FUNCTION search_orders(p_sort text, p_status text)
RETURNS SETOF orders LANGUAGE plpgsql AS $$
BEGIN
  IF p_sort NOT IN ('created_at', 'total', 'status') THEN
    RAISE EXCEPTION 'unsupported sort column %', p_sort;
  END IF;
  RETURN QUERY EXECUTE
    format('SELECT * FROM orders WHERE status = $1 ORDER BY %I', p_sort)
    USING p_status;
END $$;

The allowlist is the security control; %I (identifier quoting, like quote_ident) guarantees correct syntax if an allowed name ever needs quoting. Use %L or quote_literal only when a value genuinely cannot be passed through USING, which is rare.

The same fix in SQL Server, Oracle and MySQL

Every major engine has the same two tools, a way to bind values into dynamic SQL and a way to validate or quote identifiers. The names differ:

-- SQL Server: sp_executesql binds values; QUOTENAME quotes identifiers
DECLARE @sql nvarchar(max) =
  N'SELECT * FROM dbo.orders WHERE status = @status ORDER BY ' + QUOTENAME(@sort);
EXEC sp_executesql @sql, N'@status nvarchar(20)', @status = @status;

-- Oracle: EXECUTE IMMEDIATE ... USING binds; DBMS_ASSERT validates names
EXECUTE IMMEDIATE
  'SELECT COUNT(*) FROM ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_table) ||
  ' WHERE owner_id = :1'
  INTO v_count USING p_owner;

-- MySQL: server-side prepared statement inside a procedure
SET @q = CONCAT('SELECT * FROM orders WHERE status = ? ORDER BY ', v_sort_col);
PREPARE stmt FROM @q;      -- v_sort_col must come from an allowlist check
EXECUTE stmt USING p_status;
DEALLOCATE PREPARE stmt;

Note what each quoting function does and does not do. QUOTENAME brackets a name and escapes closing brackets, but it accepts any name, so it stops syntax breakout without stopping a caller from choosing a different, valid column. DBMS_ASSERT.SIMPLE_SQL_NAME rejects anything that is not a syntactically simple name but, likewise, does not check that the object is one you meant to expose. MySQL has no built-in identifier quoting function for this, so an explicit allowlist is mandatory. In all engines, the allowlist decides what is permitted; quoting only decides how it is spelled.

The most common mistake in older T-SQL code is EXEC(@sql) with concatenated values. It is the exact equivalent of string-built SQL in the application and should be replaced by sp_executesql with a parameter list wherever it appears.

Definer rights and search_path

Routines can run with the privileges of their owner rather than their caller: SECURITY DEFINER in PostgreSQL and MySQL, EXECUTE AS OWNER in SQL Server, definer rights (the default) in Oracle. That is a legitimate way to let a low-privilege role perform one narrow privileged action. It also means an injectable definer routine is a privilege escalation: the injected SQL runs as the owner.

PostgreSQL adds a subtler trap. A definer function resolves unqualified names through the caller's search_path, so a caller who can create objects in a schema on that path can shadow a table or function the routine uses and get their own code run with the owner's rights. The PostgreSQL documentation's guidance is to pin the path on the function itself and to restrict who can call it:

CREATE FUNCTION admin.reset_user_quota(p_user bigint)
RETURNS void LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = admin, pg_temp          -- pg_temp last, trusted schema only
AS $$
BEGIN
  UPDATE admin.quotas SET used = 0 WHERE user_id = p_user;
END $$;

REVOKE ALL ON FUNCTION admin.reset_user_quota(bigint) FROM PUBLIC;  -- PUBLIC can execute by default
GRANT EXECUTE ON FUNCTION admin.reset_user_quota(bigint) TO support_role;

Keep definer routines small, static where possible, schema-qualified throughout, and audited as a separate class of code.

Containment: roles, grants and row-level security

Assume injection will eventually happen somewhere and ask what the injected statement can do. The answer is whatever the executing role can do. An application that connects as the table owner, or worse as a superuser, gives an attacker the ability to read every table, alter schemas and, on some engines, reach the operating system through file or program-execution features. An application role with narrow grants turns the same bug into a much smaller incident.

-- Owner role holds the schema; the app role only gets what it uses
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_rw LOGIN PASSWORD '...';
ALTER SCHEMA shop OWNER TO app_owner;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA shop TO app_rw;
GRANT SELECT, INSERT, UPDATE ON shop.orders, shop.order_items TO app_rw;
GRANT SELECT ON shop.products TO app_rw;          -- no access to shop.payment_tokens
ALTER ROLE app_rw SET statement_timeout = '5s';  -- caps slow, time-based probing too

-- Row-level security as a second fence for multi-tenant tables
ALTER TABLE shop.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_rows ON shop.orders
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

Three details decide whether this holds. The application must not own the tables, because owners bypass row-level security unless FORCE ROW LEVEL SECURITY is set. Separate roles for separate duties (a reporting role with read-only grants, a migration role used only by deploys) mean one injectable endpoint does not expose everything. And be honest about the session-variable pattern above: injected SQL running as app_rw can call set_config and change app.tenant_id itself, so it limits mistakes in correct code rather than stopping an attacker who already has injection. Policies keyed on the login role, or tenant isolation by separate roles or databases, are stronger against that threat. See privileged access management for handling the owner and migration credentials.

Worked example: auditing a vulnerable routine

Suppose a code review flags search_orders from the first example in a legacy PostgreSQL database. A full response takes four steps, in this order:

1. Inventory siblings. The pattern is rarely unique. Search routine bodies for dynamic execution: SELECT n.nspname, p.proname FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE p.prosrc ILIKE '%execute%'. In SQL Server, search sys.sql_modules for EXEC( and sp_executesql; in Oracle, ALL_SOURCE for EXECUTE IMMEDIATE. Every hit needs the values-versus-identifiers review.
2. Fix and test. Apply the USING plus allowlist rewrite, then add tests that pass quotes, semicolons, comment markers and an unknown sort column, and assert either a correct result or the explicit exception, never a different row count.
3. Check exposure. Look at which roles hold EXECUTE on the function and whether it is a definer routine. A definer routine owned by a privileged role is an escalation path and moves up the priority list.
4. Look back. Search logs and statement statistics for evidence the flaw was used before it was found, using the detection queries below. A fix without a look-back closes the door but never tells you whether someone already walked through.

Static and dynamic scanners help with the inventory step but rarely see inside database routines, which is one reason this class survives; SAST and DAST explains where each tool's coverage ends.

Detecting injection from inside the database

Applications issue a small, stable set of query shapes. Injection changes the shape. That makes two database-side signals unusually precise.

New query fingerprints for the application role. PostgreSQL's pg_stat_statements extension (loaded through shared_preload_libraries) normalises each statement by replacing constants with placeholders and tracks it under a queryid. A bound query keeps one fingerprint however its values change; an injected statement produces a new one. Snapshot the set after each deploy and alert on fingerprints that appear later:

-- Fingerprints run by the app role that were not in the post-deploy baseline
SELECT s.queryid, s.calls, left(s.query, 120) AS query_shape
FROM pg_stat_statements s
JOIN pg_roles r ON r.oid = s.userid
WHERE r.rolname = 'app_rw'
  AND s.queryid NOT IN (SELECT queryid FROM ops.query_baseline)
ORDER BY s.calls DESC;

Syntax and type errors from the application role. Correct, tested code almost never sends the server a malformed statement, so a stream of syntax errors (SQLSTATE 42601), undefined-object errors (42P01, 42883) or text-to-number conversion failures (22P02) from the app role is a strong probing signal. Add the SQLSTATE and role to server log lines (%e and %u in log_line_prefix), ship the logs to your SIEM, and alert on rate per role and client address. Statement timeouts firing in bursts are a third, weaker signal of time-based probing.

These signals complement a WAF rather than replace it: the WAF sees the HTTP request, the database sees what actually executed, and only the second is immune to encoding tricks.

Failure modes and trade-offs

The trade-offs are real. Narrow grants and separate roles add friction to migrations and debugging. Fingerprint alerts need a baseline refreshed on every deploy, or every release looks like an attack. Row-level security adds planner overhead and its own subtle bugs. Each is still far cheaper than discovering that one concatenated sort column exposed a payments table.

What to do next

• Grep every routine body in each database for dynamic execution and review each hit for values versus identifiers.
• Replace EXEC(@sql) and concatenated EXECUTE with bound parameters; add allowlists for every dynamic identifier.
• List all definer-rights routines; pin search_path, schema-qualify names and revoke execute from PUBLIC.
• Make sure no application connects as a table owner or superuser; split read, write, reporting and migration roles.
• Enable pg_stat_statements or your engine's equivalent, store a post-deploy fingerprint baseline and alert on new shapes.
• Log SQLSTATE and role, ship to the SIEM, and alert on syntax-error rates from application roles.
• Add negative tests with quotes, comment markers and unknown identifiers to every routine that builds SQL.