PHDays CTF Quals - Oracle writeup27 Jan 2014
This writeup was cross-posted from balidani.blogspot.hu. Author: Dániel Bali.
The PHDays CTF Qualifier just ended. The tasks were pretty hard, but rewarding as well. Oracle was one of the harder tasks, but after a lot of trying and failing, I managed to solve it and was quite happy.
All we get in the task description is an IP address and basic auth credentials. It is also hinted that there is a secret code for a product in the database that we need to find. When we try to access the page we get a message saying that the website is under construction. There are no cookies, nothing in the source of the page, and nothing in the metadata of the displayed PNG image. But if we check robots.txt, we get a clue:
User-agent: * Disallow: /address_shops.php?city=Moscow
Moving on, we can check what
Since the task is called Oracle, there is no way that this is not an SQLi challenge. Before we jump straight to the injection part, we can find a very helpful hint in the source of the page:
<!-- /address_shops.php?debug= -->
If we pass the debug parameter in the request, we will see the whole SQL query as a comment in the page source. This helps a lot, especially since the original query is not very typical or guessable.
Let's start injecting stuff and see if we can get some data from the database. The original query looks like this, and there are no restrictions or filters on the input:
SELECT shop_pkg.get_city_branches('$city') AS branch FROM dual
These injected queries worked as expected, but UNION SELECT just didn't work.
We guessed that it's because the stored procedure returns something that cannot
be unioned with normal rows. So the only solution seemed like blind injection.
I have a homebrew 'framework' for blind injection, I just had to write a
get_bit function that leaks data from the database one bit at a time. It
looked something like this:
def get_bit(payload): url = "http://126.96.36.199/address_shops.php?debug&city=" resp = requests.get(url + payload, auth=('admin', 'P@ssw0rd9823_#@!hhqqyi')) return len(resp.text) > 1500
The payload was something like this, depending on what we wanted to leak:
payload = "Moscow') as branch from dual where chr(%d) < (select substr(text,%d,1) from (select text,row_number() over (order by line asc) as rn from dba_source where owner='PHD_IV') where rn=" + sys.argv + ")--"
Ugly huh? So we were stuck for a long time here. I tried dumping various parts of the db, but I always seemed to ask the wrong questions. The goal was of course to find something related to products in the database, but we didn't have access to some key parts of the db, and I didn't realize this until later.
Then I found that there are 2 other users that may be interesting:
PHD_IV_OWNER2. The original user was
this, it was a bit clearer that there are parts that just cannot be accessed
from our own user, and we need to do something about it.
Then finally, we dumped the code for the package that contains the function
get_city_branches. This was very useful, because there was another SQL
injection in the procedure itself that we didn't realize earlier. We had to go
The problem is on line 21, where the parameter is just concatenated with the query string, which is then evaluated. Since we are one level deeper here, we need to use 2 apostrophes, so that they are escaped on the first level, but evaluated on the second. This is how the new query will look like, which finally enables us to inject stuff using UNION queries.
?city=a'' union select to_char(1) from dual--
We were also stuck here for some time, but not quite as long as on the previous
step. We dumped many parts of the database, and found out about the table
secret_products. We still didn't have access to this table, so we had to keep
looking for other ways. Eventually we found that
PHD_IV_OWNER2 has a package
shop_private_pkg. This package has functions that can access the table
with the secret products.
After loading the sources for this package, we found yet another vulnerability.
After some inspection we can find that the function
the same problem as we've seen before, so we can exploit this the same way. One
small problem is that the result for this query has to be a number, so we had
to convert things accordingly. Since this package is owned by
we now have access to
secret_products. Single apostrophes now have to be
passed as '''' to be interpreted properly.
We started by dumping the column names for the
secret_products table. One of
the first column names was
hidden_code, where we quickly stopped, because
that's a very strong hint for the flag. This is a query that we used to dump
?city=a'' union all select to_char(PHD_IV_OWNER2.shop_private_pkg.GET_PRODUCT_QUANTITY(''A'''' union select ASCII(substr(a,%d,1)) from (select column_name as a,row_number() over (order by column_name asc) as rn from ALL_TAB_COLUMNS where table_name=''''SECRET_PRODUCTS'''') where rn=%d--'')) from dual--
There were 23 products, but only 1 with a
hidden_code, so we dumped it with
the following query.
?city=x'' union select to_char(PHD_IV_OWNER2.shop_private_pkg.GET_PRODUCT_QUANTITY(''A'''' union select ASCII(substr(hidden_code,%d,1)) from (select hidden_code from PHD_IV_OWNER1.SECRET_PRODUCTS where hidden_code is not null)--'')) from dual--
The resulting flag was:
Thanks to the PHDays guys for making a very challenging CTF! I enjoyed it a lot.