Understanding SQLMAP payload

Question

I was participating in a CTF and there was a SQL Injection challenge. There is a Wordpress page with a vulnerable plugin parameter (let's call the website https://vulnerable.com/), and the solution comes from leaking values from the database. Using SQLMAP, it quickly found the payload.

When visiting the page, there is a "N" seconds delay.

http://vulnerable.com/wp-admin/admin.php?action=dt_duplicate_post_as_draft&post=1 AND (SELECT 1749 FROM (SELECT(SLEEP(N)))nQtm)

SQLMAP went on to solve the problem with ease. This left me feeling a little guilty though, because I do not really understand the payload. I tried changing it to the following, but no delay takes place.

No delay took place.

http://vulnerable.com/wp-admin/admin.php?action=dt_duplicate_post_as_draft&post=1 AND SELECT(SLEEP(N))

Could someone help me understand the nested SELECT statements, the number 1749 and the seemingly random string nQtm?, are these random?, why does the payload fail without nested SELECT statements?

Answer 1

I assume that it is a MySQL database.

1749 (is greater that 0) and nQtm (valid alias - "variable name" for derived table) were chosen randomly by sqlmap. The problem with sleep(N) is that SQL database evaluates it to 0 and hence post=1 AND 0 will be evaluated to zero (FALSE : 1 AND 0 = 0) too. Value 1749 gets interpreted by SQL database as TRUE (similar things (if (42) { ... }) happen in PHP or in python or in C and probably elsewhere but I'm not using that style of coding because it is hard to read and might lead to unintentional bugs).

No delay took place.

http://vulnerable.com/wp-admin/admin.php?action=dt_duplicate_post_as_draft&post=1 AND SELECT(SLEEP(N))

I created just a random query and it still delayed for 3 seconds. So i cannot really explain why it didn't delay in your case (maybe it is not MySQL database (maybe it is Sqlite) or maybe some internal database optimizer skipped that part with sleep(N) because it was immediately evaluated to zero, because there are no if-branches. I ran once in a similar problem with SLEEP(N))

select t.a from (select 1 as a, 2 as b, 3 as c) as t where t.b = 2 and 1=(select(sleep(3)))

---

Details:

SELECT(SLEEP(1)) returns 0

(SELECT 1749 FROM (SELECT(SLEEP(1))) nQtm) where nQtm is an alias (you can call it a variable if you wish) for derived table (SELECT(SLEEP(1))). If you try to execute (SELECT nQtm.* FROM (SELECT(SLEEP(1))) as nQtm) the result will be just 0 (one row with result 0).

This is same as writing select t.* from (select 0) as t which will result in 0 (you select all columns and all rows here, but since we have only one row and one column the result is 0).

So (SELECT 1749 FROM (SELECT(SLEEP(N))) nQtm) will always result in 1749 - it doesn't matter what you put into SLEEP(N) - it will delay N seconds, but result will be the same (select 42 from (select 0) will always select 42 so it is in some sense independent from derived table (select 0)).

Imagine the original SQL query is:

"UPDATE posts
SET     data='hello world'
WHERE   user_id=42
        AND post=".$_POST["post"];

Now if you make an injection it will result in:

UPDATE   posts
SET      data='hello world'
WHERE    user_id=42
         AND post=1 AND (SELECT 1749 FROM (SELECT(SLEEP(N)))nQtm)

where ... AND post=1 AND (SELECT 1749 FROM (SELECT(SLEEP(N)))nQtm) which is the same as ... AND post=1 AND 1749 which probably will be evaluated to 1 (which is TRUE) if stored entry for post is also 1 (1=1 AND 1749 same as 1 AND 1749 same as 1). So it will sleep N seconds and then make an update to the DB.

You might exploit it with if...then...else statement. There are 2 of them in MySQL: (if (isMyDogBrown?, true, false)) and (case (isMyDogBrown?) then (true) else (false) end). So now you can construct your query and if something evaluates to TRUE it will sleep for N seconds: (SELECT IF ( substr(@@version, 0, 1)='1'), sleep(5), 0).

---

Note: You can optimize your query by avoiding time based exploitation (very slow). You can use binary search based exploitation technique e.g. by using the fact the errors in MySQL regular expressions are treated as MySQL errors (so "MySQL/server error" you will evaluate to TRUE and "regular page" to FALSE; ... and it might spam the mysql.log with error messages on that server):

(select (1) rlike (if (".$cmd.", true, 0x28)))

(0x28 = () (https://www.systutorials.com/4670/ascii-table-and-ascii-code/)

Additional notes:

1. Here is a great source of different exploitation methods which was many years ago my source for learning SQL injection: https://websec.wordpress.com/category/sqli/
2. If you need to exploit heavy query (time based): https://github.com/sqlmapproject/sqlmap/issues/2909 - you will need to write your own wrapper on localhost (localhost/wrapper.php?id=INJECT_HERE) which will make exploitation easier for sqlmap. sqlmap has sometimes hard time to detect vulnerability.
3. SLEEP(N) will not always work everywhere (in cases where your SQL query MUST be somewhat broken and not return results which might be evaluated by some filters in the main PHP/whatever script (like login attempts counter etc.)). Sometimes you'll need heavy query based exploitation: https://stackoverflow.com/questions/45666126/strange-behaviour-of-mysql-sleep-function

Answer 2

In general SQL injection depends on the database engine used, I think in your example you provide a sql for MariaDB/MySQL database. The function sleep on PostgreSQL is pg_sleep, so your injection will not work on PostgreSQL.

MariaDB [CODINGGROUND]> (SELECT 1749 FROM (SELECT(SLEEP(1)))nQtm)                                                                                                                
    -> ;                                                                                                                                                                         
+------+                                                                                                                                                                         
| 1749 |                                                                                                                                                                         
+------+                                                                                                                                                                         
| 1749 |                                                                                                                                                                         
+------+ 

And the other query

MariaDB [CODINGGROUND]> select (sleep(1));                                                                                                                                       
+------------+                                                                                                                                                                   
| (sleep(1)) |                                                                                                                                                                   
+------------+                                                                                                                                                                   
|          0 |                                                                                                                                                                   
+------------+                                                                                                                                                                   
1 row in set (1.00 sec)  

So basically you can figure out why one works and the other not. But bear in mind, that in general SQL injection depends on the database backend you are using. Because SQL is a standard language but not all the engines implements on the same way and they have their differences.