PHDays CTF Quals - Oracle writeup

This writeup was cross-posted from 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.

Van chase

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 address_shops.php does.


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.

The hotel

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 = ""
    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[1] + ")--" 

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_OWNER1 and PHD_IV_OWNER2. The original user was PHD_IV. After 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 deeper.

Snow fortress

Here is the code for the package that had the second vulnerability

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 called 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.


Here is the code for the third vulnerability.

After some inspection we can find that the function GET_PRODUCT_QUANTITY has 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 PHD_IV_OWNER2, 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 column names.

?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: 9l5_q24y_2g7_r18_6g4

Thanks to the PHDays guys for making a very challenging CTF! I enjoyed it a lot.