CTF SQL group by report error injection

Time:2020-9-27

This article mainly deals with the principle of group by error injection. If there is any error, please point out. (a table of contents is attached. Please click the lower right corner for more information)

1、 The following figure shows the user table used in this article. The database of the table is test

2、 First of all, I will introduce the grammar used in this article: (items 5 and 6 must be read, which involves the explanation of the principles later)

1. Group by statement: used in combination with aggregate function to group result sets according to one or more columns.

As shown in the figure below:

2. Rand() function: used to generate a random number between 0 and 1:

As shown in the figure below:

be careful:
When called with an integer value as a parameter, rand() uses the value as the seed of the random number generator. For each given seed, the rand() function produces a list of reproducible numbers

3. Floor() function: round down:

As shown in the figure below:

4. Count() function: returns the number of values in the specified column (null is not included), count (*): returns the number of records in the table

As shown in the figure below

5. Floor (rand() * 2): the rand() * 2 function generates numbers between 0 and 2, and uses the floor() function to round down, and the resulting value is “0” or “1” of [unfixed]

As shown in the figure below:

6. Floor (rand (0) * 2): the rand (0) * 2 function generates numbers between 0 and 2, and uses the floor() function to round down, but the obtained value [the first six bits (including the sixth bit) are fixed]. (for: 011011)

As shown in the figure below:

3、 Next, we will explain the principle of group by: (if you think you have understood the principle, please go to: 4)

First, let’s think about the following three SQL statements, from which we can learn:

1、select username,count(*) from user group by username;
			2、select username,count(*) from user group by "username";
			3、select username,count(*) from user group by userna;

The operation results are as follows:

Conclusion

We found that the parameter after group by can be a column_ Name (field name), which can be a string (or a function whose return value is a string), cannot be an incomplete column_ name。 At this point, you might think that the parameter is column_ I can understand how the name is grouped, but what happens when the parameter is a string? There is no “username” in the value of the username field? There are only “admin” and “Chen”. How can the result be 7? Let’s move on.

Reason: the grouping principle of the above SQL statements (although it is my conjecture, the explanation can be explained in this way)

1. If the parameter is column_ Name, or username, is not a string (“username”).

When column key is created, if there are two virtual key fields in the table, they will be executed_ Name, the system will query the value of the corresponding field (i.e. the value in the field indicated by the parameter) in the user table, and take the first value of the user name field as admin. At this time, the string admin will be found in the primary key of the virtual table. If it exists, the value of count (*) will be increased by 1; if it does not exist, the string admin will be inserted into the primary key key Field, and change count (*) to 1; then take the second value of the username field as admin, find the admin string already exists in the primary key key in the virtual table, and directly add count (*) by 1 When the fourth value of the username field is Chen, the value Chen does not exist in the primary key field of the virtual table. In this case, the string Chen is inserted into the key field of the primary key again, and the count (*) is changed to 1. The execution continues until all the field values are grouped. After that, the system will display it according to the results in the virtual table.

When the fourth value (i.e. Chen) of the username field is retrievedVirtual tableAs shown in the following figure:

2. If the parameter is a string: “username” instead of a field name:

When the statement is executed, a virtual table will still be created (there are two fields in it, namely, key, primary key and count (*)). If the parameter is the string “username”, the system will not retrieve the field value in the user table, Instead, take the string: “username” as the value, and then look up and compare the value of the key field in the virtual table. If the string “username” is not found, insert the string “username” and change the count (*) to 1. Then execute the second time to find the string “username” in the key field of the virtual table. If it is found, count (*) will be added by 1, and then 7 will be executed Count (*) becomes 7.

4、 After understanding the above, let’s get to the main topic. Let’s take a look at the following two SQL group by error injection statements and their running results:

1、select count(*) from information_schema.tables group by concat(database(),floor(rand(0)*2));
			2、select count(*) from information_schema.tables group by concat(database(),floor(rand()*2));

You can see that the test database where the user table is located is successfully exploded. But if you observe carefully, you will find that the explosion rate of the second SQL statement is not 100%, and sometimes it will not pop out. Why? Don’t worry, keep looking down:

Reason: after we have laid the groundwork above, it is not difficult to understand the principle of SQL group by error injection

Take the first statement as an example:select count(*) from information_schema.tables group by concat(database(),floor(rand(0)*2));
First of all, we know

  • Floor (rand (0) * 2)Top sixIt must be “011011” (mentioned above),
  • The concat() function is used to connect the first and second strings
  • The database() function returns the name of the currently used database.
  • Concat (database(), floor (rand (0) * 2)) generates a random sequence composed of ‘database () +’0’ and ‘database () +’1’. Then the first six sequences must be in order:
    • ‘database()+’0”
    • ‘database()+’1”
    • ‘database()+’1”
    • ‘database()+’0”
    • ‘database()+’1”
    • ‘database()+’1”

Error reporting process:

  • Empty virtual tables will be created by default before query
  • Take the first record and execute concat (database(), floor (rand (0) * 2))(First execution), the calculation result is’ database() + ‘0’ ‘, query the virtual table and find that the primary key value of’ database () + ‘0’ does not exist, the insert command will be executedAgainExecute concat (database(), floor (rand (0) * 2)) once(Second execution), evaluates to ‘database() +’ 1 ”, and inserts the value. (i.e. although the query is compared to ‘database () +’ 0 ”, what is really inserted is the result ‘database () +’1’ ‘executed the second time. This process is concat (database(), floor (rand (0) * 2))Twice, once during query comparison and once during insert).
  • Take the second record and execute concat (database(), floor (rand (0) * 2))(The third execution), the calculation result is’ database() + ‘1’ ‘, query the virtual table,It is found that the primary key value of ‘database() +’ 1 ‘exists, so the instrumentation is no longer executed, and the second concat (database(), floor (rand (0 * 2)), count (*) is directly increased by 1,(in other words, if the query is’ database() + ‘1’ ‘, add 1 directly. This process, concat (database(), floor (rand (0) * 2))Once)。
  • Take the third record and execute concat (database(), floor (rand (0) * 2))(The fourth execution), the calculation result is’ database() + ‘0’ ‘, query the virtual table and find that the primary key value of’ database () + ‘0’ does not exist, the insert command will be executedAgainExecute concat (database(), floor (rand (0) * 2)) once(The fifth implementation), the result is’ database () + ‘1’, which is taken as the primary key value,However, the primary key value ‘database() +’ 1 ‘already exists in the virtual table. Since the primary key value must be unique, an error will be reported. The result of the error report is’ database () + ‘1’, that is, ‘test1’, so as to get the name of the database test。%e6%b5%85%e6%98%93%e6%b7%b1

From the above process, it is found that a total of three records have been taken (so the number of records in the table is at least three), and floor (rand (0) * 2) has been executed five times

5、 Summary

Now, we have explained the principle of group by error injection. We must have known why:

  • select count(*) from information_schema.tables group by concat(database(),floor(rand(0)*2));Must be able to inject successfully (if there are at least three records in the table for successful injection)
  • andselect count(*) from information_schema.tables group by concat(database(),floor(rand()*2));But not necessarily. (for successful injection, the number of records in the table must be at least two)

Yes, because the order of the first few random numbers of floor (rand() * 2) is not fixed, so it can not guarantee that the injection will be successful. However, it only needs two records (because it may produce “0101”, so only two records can be injected successfully. You can try to deduce it). This is also its advantage.

The article is the blogger word for word, reprint please mark the source, thank you!