首页 > 代码库 > 一道CTF题引发的思考-MySQL的几个特性(续)

一道CTF题引发的思考-MySQL的几个特性(续)

0x00 背景

  这两天处于转牛角尖的状态,非常不好。但是上一篇的中提到的问题总算是总结了些东西。

传送门:疑问点0x02(4)

 

0x01 测试过程

(1)测试环境情况:创建了如下测试表test,

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

(2)测试过程要点:主要测试MySQL的大小写是否是强匹配,在哪些情况下是强匹配,以及如何使MySQL对大小写进行强匹配

  • 在没有选择表的情况下测试,发现不进行编码(这里说的编码是使用char或16进制进行编码) 时大小写不是强匹配的。

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

 

  • 在没有选择表的情况下,使用char或者16进制进行编码后 大小写是强匹配的

mysql> select ‘AbC‘ like char(97,37); #小a
+------------------------+
| ‘AbC‘ like char(97,37)    |
+------------------------+
| 0             |
+------------------------+
1 row in set

mysql> select ‘AbC‘ like char(65,37); #大A
+------------------------+
| ‘AbC‘ like char(65,37)|
+------------------------+
| 1           |
+------------------------+
1 row in set

mysql> select ‘AbC‘ like 0x6125; #小a
+-------------------+
| ‘AbC‘ like 0x6125   |
+-------------------+
| 0          |
+-------------------+
1 row in set

mysql> select ‘AbC‘ like 0x4125;  #大A
+-------------------+
| ‘AbC‘ like 0x4125  |
+-------------------+
| 1           |
+-------------------+
1 row in set

 

  • 在没有选择表的情况下,可以使用binary对大小写进行强匹配,当然也可以使用hex,这个在http://www.cnblogs.com/Z3roTo0ne/p/6883132.html已经有说过了,本文就不再说明了。

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

 


以下是使用正则regexp的方式,与like相似这里就不啰嗦了。

  • 在没有选择表的情况下测试,发现不进行编码,大小写不是强匹配

mysql> select ‘AbC‘ regexp ‘^a‘;
+-------------------+
| ‘AbC‘ regexp ‘^a‘  |
+-------------------+
| 1             |
+-------------------+
1 row in set
mysql> select ‘AbC‘ regexp ‘^A‘;
+-------------------+
| ‘AbC‘ regexp ‘^A‘  |
+-------------------+
| 1             |
+-------------------+
1 row in set

 

  • 在没有选择表的情况下测试,发现进行编码后,大小写是强匹配

mysql> select ‘AbC‘ regexp char(94,97);#小a
+--------------------------+
| ‘AbC‘ regexp char(94,97) |
+--------------------------+
| 0             |
+--------------------------+
1 row in set

mysql> select ‘AbC‘ regexp char(94,65);#大A
+--------------------------+
| ‘AbC‘ regexp char(94,65) |
+--------------------------+
| 1             |
+--------------------------+
1 row in set

mysql> select ‘AbC‘ regexp 0x5E61;#小a
+---------------------+
| ‘AbC‘ regexp 0x5E61 |
+---------------------+
| 0           |
+---------------------+
1 row in set
mysql> select ‘AbC‘ regexp 0x5E41;#大A
+---------------------+
| ‘AbC‘ regexp 0x5E41 |
+---------------------+
| 1           |
+---------------------+
1 row in set

 

  • 在没有选择表的情况下,可以使用binary对大小写进行强匹配

mysql> select ‘AbC‘ regexp binary ‘^a‘;
+--------------------------+
| ‘AbC‘ regexp binary ‘^a‘  |
+--------------------------+
| 0             |
+--------------------------+
1 row in set

mysql> select ‘AbC‘ regexp binary ‘^A‘;
+--------------------------+
| ‘AbC‘ regexp binary ‘^A‘   |
+--------------------------+
| 1              |
+--------------------------+


在选择表或者是通过数据库函数如user(),database()等获取数据的测试情况:

  • 在查询表中字段数据时,无论是否进行编码 ,大小写都是不强匹配

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 ‘^A‘;
+---------+-------+---------------------------------------+
| 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 测试结论

  MYSQL大小写不进行强匹配的。要想匹配大小写可以使用binary,或者使用http://www.cnblogs.com/Z3roTo0ne/p/6883132.html中的两次16进制编码的方式。进行大小写强匹配。

0x03 另外的一种方式

使用10进制和16进制混合也可以达到区分大小写的效果,因为大小写的16进制是不一样的。

mysql> select conv(hex(substr((user()),1,8)),16,10);
+---------------------------------------+
| conv(hex(substr((user()),1,8)),16,10) |
+---------------------------------------+
| 8245931987826405219        |
+---------------------------------------+
1 row in set

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

一道CTF题引发的思考-MySQL的几个特性(续)