mysql ex_MySQL约束攻击

测试环境为MariaDB：mysql Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2。

该攻击是因为MySQL对于字符串验证不严格而导致的漏洞，先来看看下面的例子。

例子环境

MariaDB [test]> create table login (id int primary key auto_increment,user varchar(100) not null, password varchar(100) not null);

Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> create table secert (id int primary key auto_increment, user varchar(100) not null references login(user), content TEXT);

Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> insert into login (user,password)values('admin','very difficult');

Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into secert (user, content)values('admin', "This is admin's secert!");

Query OK, 1 row affected (0.00 sec)

上面的例子可以看成一个简单的秘密记录系统，只要验证通过的话，就可以通过用户名进行秘密查询：

MariaDB [test]> select secert.content from secert join login on login.user=secert.user where login.user='admin' and login.password='very difficult';

+-------------------------+

| content |

+-------------------------+

| This is admin's secert! |

+-------------------------+

1 row in set (0.00 sec)

由于MySQL设计的弱类型原则，在处理MySQL中的字符串时，字符串末尾的空格字符都会被省略。换句话说，“admin”与“admin ”几乎是等效的，有时这种不太精确的设计确实会使开发更简单，但是带来的安全隐患却不容忽视。

由于上面数据库本身设计不是用id做外键查询，而是用user字符串，加上MySQL的偏向于弱类型语言，所以就留下一个漏洞。

但是如果有恶意用户进行注册的话，那么那就可以读取管理员的秘密。

MariaDB [test]> select 'Now, I want to hack it!' hacker;

+-------------------------+

| hacker |

+-------------------------+

| Now, I want to hack it! |

+-------------------------+

1 row in set (0.00 sec)

MariaDB [test]> insert into login (user,password)values('admin ','123456');

Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into secert (user, content)values('admin ', "hacker");

Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select secert.content from secert join login on login.user=secert.user where login.user='admin ' and login.password='123456';

+-------------------------+

| content |

+-------------------------+

| This is admin's secert! |

| hacker |

+-------------------------+

2 rows in set (0.00 sec)

MariaDB [test]> select 'Oh, I got it!' hacker;

+---------------+

| hacker |

+---------------+

| Oh, I got it! |

+---------------+

1 row in set (0.00 sec)

预防

对于密码验证这种比较要求严格的验证可以使用二进制验证，这些就不会出现因为弱类型而导致的字符串的验证绕过问题。

MariaDB [test]> select secert.content from secert join login on binary login.user=secert.user where binary login.user='admin ' and login.password='123456';

+---------+

| content |

+---------+

| hacker |

+---------+

1 row in set (0.01 sec)

或者可以一开始就养成良好的数据库设计风格，不用过于user来作为外键，而用id作为外键，这样，我们的数据库才能更安全。

MariaDB [test]> create table login (id int primary key auto_increment,user varchar(100) not null, password varchar(100) not null);

Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> create table secert (id int primary key auto_increment, user_id int not null references login(id), content TEXT);

Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> insert into login (user,password)values('admin','very difficult');

Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into secert (user_id, content)values(1, "This is admin's secert!");

Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select 'Now, I want to hack it!' hacker;

+-------------------------+

| hacker |

+-------------------------+

| Now, I want to hack it! |

+-------------------------+

1 row in set (0.00 sec)

MariaDB [test]> insert into login (user,password)values('admin ','123456');

Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into secert (user_id, content)values(2, "hacker");

Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select content from secert join login on login.id=secert.user_id where user='admin ' and password='123456';

+---------+

| content |

+---------+

| hacker |

+---------+

1 row in set (0.01 sec)

MariaDB [test]> select 'Shit! I got nothing!' hacker;

+----------------------+

| hacker |

+----------------------+

| Shit! I got nothing! |

+----------------------+

1 row in set (0.00 sec)

总结

好的设计习惯和代码习惯无论对于开发还是编程来说，都是非常重要的。