Thoughts on a CTF question-MySQL features (continued), ctf-mysql

Thoughts on a CTF question-MySQL features (continued), ctf-mysql

0x00 background

These two days are very difficult. However, the problem mentioned in the previous article is summarized.

Portal: question point 0x02 (4)

 

0x01 Testing Process

(1) test environment: the following test table is created,

Mysql> select * from test;
+ --------- + ------- + ------------------------------------------- +
| User_id | user | password |
+ --------- + ------- + ------------------------------------------- +
| 1 | admin | 5f4dcc3b5aa765d61d8327deb882cf99 |
| 2 | ADMIN | 5f4dcc3b5aa765d61d8327deb882cf99 |
+ --------- + ------- + ------------------------------------------- +
2 rows in set

(2) Key Points of the test process: it mainly tests whether the MySQL Case is a strong match, under which conditions is a strong match, and how to make MySQL perform a strong match on the case.

• If no table is selected, the test finds that uppercase and lowercase letters are not strongly matched if no encoding is performed (the encoding is in char or hexadecimal format.

Mysql> select 'abc' like 'a % ';
+ ----------------- +
| 'Abc' like 'a % '|
+ ----------------- +
| 1 |
+ ----------------- +
1 row in set

Mysql> select 'abc' like 'a % ';
+ ----------------- +
| 'Abc' like 'a % '|
+ ----------------- +
| 1 |
+ ----------------- +
1 row in set

 

• If no table is selected, the case is strongly matched after encoding in char or hexadecimal format.

Mysql> select 'abc' like char (97,37); # small
+ ------------------------ +
| 'Abc' like char (97,37) |
+ ------------------------ +
| 0 |
+ ------------------------ +
1 row in set

Mysql> select 'abc' like char (); # large
+ ------------------------ +
| 'Abc' like char (65,37) |
+ ------------------------ +
| 1 |
+ ------------------------ +
1 row in set

Mysql> select 'abc' like 0x6125; # small
+ ------------------- +
| 'Abc' like 0x6125 |
+ ------------------- +
| 0 |
+ ------------------- +
1 row in set

Mysql> select 'abc' like 0x4125; # large
+ ------------------- +
| 'Abc' like 0x4125 |
+ ------------------- +
| 1 |
+ ------------------- +
1 row in set

 

• If you do not select a table, you can use binary to perform a strong matching On The Case sensitivity. Alternatively, you can use hex.

Mysql> select 'abc' like binary 'a % ';
+ ------------------------ +
| 'Abc' like binary 'a % '|
+ ------------------------ +
| 0 |
+ ------------------------ +
1 row in set

Mysql> select 'abc' like binary 'a % ';
+ ------------------------ +
| 'Abc' like binary 'a % '|
+ ------------------------ +
| 1 |
+ ------------------------ +
1 row in set

 

The following is a regular regexp method, which is similar to like.

• If no table is selected, the test finds that the encoding is not performed and the case is not strongly matched.

Mysql> select 'abc' regexp '^ ';
+ ------------------- +
| 'Abc' regexp '^ a' |
+ ------------------- +
| 1 |
+ ------------------- +
1 row in set
Mysql> select 'abc' regexp '^ ';
+ ------------------- +
| 'Abc' regexp '^ a' |
+ ------------------- +
| 1 |
+ ------------------- +
1 row in set

 

• Test without selecting a table. After encoding, The Case sensitivity is strongly matched.

Mysql> select 'abc' regexp char (94,97); # small
+ -------------------------- +
| 'Abc' regexp char (94,97) |
+ -------------------------- +
| 0 |
+ -------------------------- +
1 row in set

Mysql> select 'abc' regexp char (); # large
+ -------------------------- +
| 'Abc' regexp char (94,65) |
+ -------------------------- +
| 1 |
+ -------------------------- +
1 row in set

Mysql> select 'abc' regexp 0x5E61; # small
+ --------------------- +
| 'Abc' regexp 0x5E61 |
+ --------------------- +
| 0 |
+ --------------------- +
1 row in set
Mysql> select 'abc' regexp 0x5E41; # large
+ --------------------- +
| 'Abc' regexp 0x5E41 |
+ --------------------- +
| 1 |
+ --------------------- +
1 row in set

 

• If no table is selected, you can use binary to perform strong matching On The Case sensitivity.

Mysql> select 'abc' regexp binary '^ ';
+ -------------------------- +
| 'Abc' regexp binary '^ a' |
+ -------------------------- +
| 0 |
+ -------------------------- +
1 row in set

Mysql> select 'abc' regexp binary '^ ';
+ -------------------------- +
| 'Abc' regexp binary '^ a' |
+ -------------------------- +
| 1 |
+ -------------------------- +

Test results when selecting a table or using database functions such as user () and database:

• When querying field data in a table, no matter whether the data is encoded or not, the case sensitivity is not strong.

Mysql> select * from test where user like 'a % ';
+ --------- + ------- + ----------------------------------------- +
| User_id | user | password |
+ --------- + ------- + ----------------------------------------- +
| 1 | admin | 5f4dcc3b5aa765d61d8327deb882cf99 |
| 2 | ADMIN | 5f4dcc3b5aa765d61d8327deb882cf99 |
+ --------- + ------- + ----------------------------------------- +
2 rows in set

Mysql> select * from test where user like char (65,37 );
+ --------- + ------- + ----------------------------------------- +
| User_id | user | password |
+ --------- + ------- + ----------------------------------------- +
| 1 | admin | 5f4dcc3b5aa765d61d8327deb882cf99 |
| 2 | ADMIN | 5f4dcc3b5aa765d61d8327deb882cf99 |
+ --------- + ------- + ----------------------------------------- +
2 rows in set

Mysql> select * from test where user regexp '^ ';
+ --------- + ------- + ----------------------------------------- +
| User_id | user | password |
+ --------- + ------- + ----------------------------------------- +
| 1 | admin | 5f4dcc3b5aa765d61d8327deb882cf99 |
| 2 | ADMIN | 5f4dcc3b5aa765d61d8327deb882cf99 |
+ --------- + ------- + ----------------------------------------- +
2 rows in set

Mysql> select * from test where user regexp 0x5E61;
+ --------- + ------- + ----------------------------------------- +
| User_id | user | password |
+ --------- + ------- + ----------------------------------------- +
| 1 | admin | 5f4dcc3b5aa765d61d8327deb882cf99 |
| 2 | ADMIN | 5f4dcc3b5aa765d61d8327deb882cf99 |
+ --------- + ------- + ----------------------------------------- +
2 rows in set

Mysql> select * from test where user regexp binary 0x5E41;
+ --------- + ------- + ------------------------------------------------ +
| User_id | user | password |
+ --------- + ------- + ------------------------------------------------ +
| 2 | ADMIN | 5f4dcc3b5aa765d61d8327deb882cf99 |
+ --------- + ------- + ------------------------------------------------ +
1 row in set

0x02 test conclusion

MYSQL is case-insensitive. To match the case, you can use binary, or use the two-step 16-in-one format in http://www.cnblogs.com/z3roto0ne/p/6883132.html. Perform case-sensitive matching.

0x03 ANOTHER METHOD

The case sensitivity can also be achieved by mixing the hexadecimal and hexadecimal values, because the hexadecimal values are different.

Mysql> select conv (hex (substr (user );
+ --------------------------------------- +
| Conv (hex (substr (user (),),) |
+ --------------------------------------- +
| 1, 8245931987826405219 |
+ --------------------------------------- +
1 row in set

Mysql> select unhex (conv (8245931987826405219), 10, 16 ));
+ ---------------------------------------------------- +
| Unhex (conv (8245931987826405219), 10, 16) |
+ ---------------------------------------------------- +
| Root @ loc |
+ ---------------------------------------------------- +
1 row in set