2023. 12. 20. 02:52ㆍnormaltic 취업반 5기/과제
ORDER BY
SQL 질의문에서 ORDER BY 구문은 조회 데이터를 임의 기준으로 정렬하여 출력할 때 사용된다. ORDER BY 구문은 SQL 질의문의 가장 마지막 부분에 위치한다. 만약 ORDER BY 구문이 생략되어 있다면 조회 데이터는 임의의 순서로 출력이 된다.
ORDER BY 구문에서 정렬 방식은 오름차순 정렬, 내림차순 정렬이 존재한다. 오름차순 정렬을 나타내는 단어는 ASC이며 내림차순 정렬을 나타내는 단어는 DESC이다. 정렬 방식을 표기하지 않았으면(생략하면) 기본적으로 오름차순 정렬로 출력된다.
ORDER BY 구문은 정렬 기준이 되는 컬럼과 정렬 방식으로 구성되어 있다. 또한 여러 컬럼을 기준으로 정렬할 수 있으며 이때의 구분자는 ','이다.
예를 들어 <학과>라는 테이블에서 데이터 조회 시 학번 컬럼을 기준으로 선택하여 출력한다. 다음은 학번이 가장 낮은 순서대로 정렬하여 출력(오름차순 정렬) 시 사용된 SQL 질의문과 출력 결과이다.
다음은 학번이 가장 높은 순서대로 정렬하여 출력(내림차순 정렬) 시 사용된 SQL 질의문과 출력 결과이다.
여러 컬럼의 ORDER BY
ORDER BY 구문은 여러 컬럼을 기준으로도 정렬이 가능하다. 정렬 방법은 왼쪽에서부터 순서대로 적힌 컬럼을 기준으로 정렬을 수행한다. 이때 정렬로 수행된 결과값이 같다면 다음 컬럼을 기준으로 결과값이 같은 데이터를 정렬한다.
예를 들어 <성적>이라는 테이블에서 데이터 조회 시 순서대로 학점, 점수 컬럼을 기준으로 선택하여 출력한다. 만약 학점이 같다면 점수가 높은 학생을 먼저 출력한다. 다음은 성적이 가장 높은 순서대로 학생들을 정렬할 때 사용된 SQL 질의문과 출력 결과이다.
[1] 학점 컬럼 기준 오름차순 정렬
먼저 학점 컬럼을 기준으로 오름차순 정렬을 수행한 결과이다. 정렬되어 출력된 결과를 보면 A,B,C,D 순으로 정렬되었다. 그러나 SQL 질의문을 보면 학점값이 같을 시 점수 컬럼을 기준으로 내림차순 하라고 되어있다. 출력된 결과에 따르면 학점값이 'B'와 'D'인 학생들이 여러명 존재하며 이는 점수 컬럼을 기준으로 내림차순 정렬을 수행해야 한다.
[2] 점수 컬럼 기준 내림차순 정렬
점수 컬럼을 기준으로 내림차순 정렬을 수행한 결과이다. 학점이 'B'인 학생들은 점수 컬럼을 기준으로 내림차순 정렬로 순서가 바뀌었으며 학점이 'D'인 학생들은 순서가 바뀌지 않았다.
이렇든 여러 컬럼으로 ORDER BY 구문 사용 시 왼쪽 컬럼 순으로 정렬을 수행하며 이전 컬럼에서의 결과값이 같을 시 다음 컬럼을 기준으로 이전 컬럼에서의 결과값이 같은 데이터를 정렬 수행한다.
4번 과제
과제 목표 : SQL 질의문이 쓰인 의도를 추측하라.
- sotingAd=,(case+when+ascii(substr((select+user+from+dual),1,1))=0+then+1+else+(1/0)+end)
- page=1&board_id=&sorting=A.REG_DT&sotingAd=ASC;if+substring((select%20user_name()),1,1)=%27a%27+waitfor+delay+%270:0:1%27&startDt=&endDt=&keyword=
1번 SQL 질의문
sotingAd=,(case+when+ascii(substr((select+user+from+dual),1,1))=0+then+1+else+(1/0)+end)
[1] 파라미터 이름
해당 질의문의 파라미터 이름을 먼저 분석하자면 sotingAd로서 soting은 sorting이란 단어에서 'r'이 빠져 철자 오류가 난 것으로 확인이 된다. sorting이란 단어는 정렬을 의미하며 이는 SQL 질의문에서 ORDER BY 구문을 사용한 것으로 추측이 된다. 또한 soting 단어 뒤에 붙어 있는 Ad는 정렬 방식인 ASC/DESC의 맨 앞글자만으로 딴 것으로 추측이 된다. 이는 곧, 해당 파라미터 데이터는 ORDER BY 구문의 정렬 방식을 나타내는 것으로 추측해 볼 수 있다.
[2] 파라미터 데이터
해당 질의문의 파라미터 이름을 분석한 결과 ORDER BY 구문의 정렬 방식을 나타내는 것으로 추측이 되었다. 그렇다면 데이터의 가장 첫 번째 문자인 ','가 ORDER BY의 구분자를 나타내는 것으로 추측 가능하다. 이것으로 보아 해당 파라미터를 사용하여 완성되는 SQL 질의문은 ORDER BY 구문에 여러 컬럼이 존재하는 것으로 추정된다.
다음으로 해당 질의문은 SQL 질의문의 CASE 구문으로 구성되어 있다. CASE 구문은 조건 결과에 따라 출력되는 값을 정할 수 있다.
CASE 구문에서 조건의 결과가 참인 경우 '값 1'을 출력하고 거짓인 경우 '값 2'를 출력한다. 이 구성에 따라 해당 SQL 질의문을 분석하면 조건은 ascii(substr((select+user+from+dual),1,1))=0이다. 해당 질의문의 작동 흐름을 보면 DUAL 테이블의 USER 컬럼을 조회한 결과의 문자열 첫 번째 문자를 아스키코드로 변경했을 때 0이면(참이면) '1'을 출력하고 0이 아니면(거짓이면) '1/0'을 출력하여 에러를 유발한다.
2번 SQL 질의문
page=1&board_id=&sorting=A.REG_DT&sotingAd=ASC;if+substring((select%20user_name()),1,1)=%27a%27+waitfor+delay+%270:0:1%27&startDt=&endDt=&keyword=
[1] 파라미터 이름
2번 SQL 질의문 중 유효한 파라미터는 sorting과 sotingAd다. sorting 파라미터는 단어의 의미가 정렬이라는 뜻으로 사용되는 SQL 질의문의 구문에 ORDER BY가 포함되어 있다고 추측 가능하다. 또한 sorting과 sotingAd 파라미터가 나눠진 것으로 보아 sorting 파라미터는 ORDER BY 구문의 정렬 기준을 나타내는 컬럼명을 나타내는 것으로 추정할 수 있다. sotingAd 파라미터는 1번 SQL 질의문에서와 같이 ORDER BY 구문의 정렬 방식을 나타내는 것으로 추측해볼 수 있다.
[2] 파라미터 데이터
sorting 파라미터 데이터는 A.REG_DT인 것으로 보아 컬럼명 자체가 A.REG_DT이거나 아니면 현재 JOIN 된 상태의 A라는 테이블의 REG_DT 컬럼을 나타내는 것으로 추측 가능하다.
sortingAd 파라미터 데이터는ASC;if+substring((select%20user_name()),1,1)=%27a%27+waitfor+delay+%270:0:1%27으로 오름차순 정렬을 나타내는 ASC와 세미콜론(;) 그리고 IF문으로 구성되어 있다. 이를 하나하나 찬찬히 살펴보자.
먼저 sortingAd 파라미터는 ORDET BY 구문의 정렬 방식을 나타낸다고 추측하였다. 그렇다면 sortingAd 파라미터 데이터는 ASC/DESC 중 하나로 이루어진다. 이에 따라 처음 나타나는 문자열은 ASC로 확인이 되었다. 그다음으로 올 수 있는 문자열은 해당 SQL 질의문의 ORDER BY 구문이 여러 컬럼으로 이루어져 있다는 증거 문자인 ','이 가능하다. 그러나 전혀 다른 문자인 세미콜론(;)이 나왔다. 세미콜론은 SQL 질의문에서 문장의 끝을 나타내는 문자다. 이에 따르면 '~~ORDER BY A.REG_DT ASC;'으로 이루어진 SQL 질의문이 실행된 후 다음으로 IF문 코드가 실행된다는 것으로 추측이 가능하다.
다음으로 IF문의 구성을 살펴보면 URL 대체코드로 인코딩 되어있어 이를 디코딩하여 보도록 하자.
URL 인코딩 : if+substring((select%20user_name()),1,1)=%27a%27+waitfor+delay+%270:0:1%27
URL 디코딩 : if substring((select user_name()),1,1)='a' waitfor delay '0:0:1'
URL 디코딩 후의 IF문을 분석하면 조건이 substring((select user_name()),1,1)='a'이다. 해당 질의문의 작동 흐름을 보면 현재 사용자의 이름을 조회한 결과의 첫 번째 문자가 'a'면(참이면) 0:0:1의 시간(1초)만큼 지연시킨 후 다음 코드로 넘어간다.