보안 점검에서 주문 목록 API의 정렬 칸으로 다른 매장의 주문 수를 알아낼 수 있었다

모범 답안 SQL ​

PROCEDURE P_DAY_ORD(p_shop_no IN VARCHAR2, p_dt IN VARCHAR2,
                    p_sort    IN VARCHAR2,        -- 앱은 정렬 코드만 넘긴다: HMS_DESC · HMS_ASC · NO_DESC · NO_ASC
                    o_rc      OUT SYS_REFCURSOR) IS
  v_order VARCHAR2(100);
BEGIN
  -- 허용 목록: 문장에 이어 붙는 글자는 이 네 가지 중 하나뿐이다
  v_order := CASE p_sort
               WHEN 'HMS_DESC' THEN 'ORD_HMS DESC, ORD_NO DESC'
               WHEN 'HMS_ASC'  THEN 'ORD_HMS, ORD_NO'
               WHEN 'NO_DESC'  THEN 'ORD_NO DESC'
               WHEN 'NO_ASC'   THEN 'ORD_NO'
             END;
  IF v_order IS NULL THEN
    RAISE_APPLICATION_ERROR(-20010, '정렬 코드 오류');
  END IF;

  OPEN o_rc FOR
    'SELECT ORD_NO, ORD_DT, ORD_HMS FROM ORD WHERE SHOP_NO = :s AND ORD_DT = :d ORDER BY ' || v_order
    USING p_shop_no, p_dt;                -- 값은 지금처럼 바인드
END;

개선 후 실행계획 학습용 예시 ​

[고친 뒤: V$SQLAREA · 하루]
  ORDER BY ORD_HMS DESC, ORD_NO DESC      1 문장
  ORDER BY ORD_HMS, ORD_NO                1 문장
  ORDER BY ORD_NO DESC                    1 문장
  ORDER BY ORD_NO                         1 문장      (합계 4 문장 · 실행 수는 그대로)

[보안 점검 값을 다시 넣으면]
  ORA-20010 정렬 코드 오류 · 문장이 만들어지지 않는다

[한 번의 실행]  Buffers 10 그대로 (매장 하나의 하루 7건)

모범 답안 서술 ​

매장과 날짜는 USING으로 바인드했지만 정렬은 그렇게 할 수 없었다. 바인드 변수는 값 자리에만 쓸 수 있고, ORDER BY 뒤의 컬럼 이름이나 식은 문장의 일부이기 때문이다. 그래서 개발자는 앱이 만든 정렬 문자열을 문장 끝에 이어 붙였고, 이어 붙인 글자는 그대로 SQL이 된다. 보안 점검팀이 넣은 CASE WHEN (SELECT COUNT(*) FROM ORD WHERE SHOP_NO = 'SH0007' …) > 1500 THEN ORD_NO ELSE ORD_HMS END도 식으로 그대로 실행되었다. 조건이 참이면 주문 번호 순, 거짓이면 시각 순으로 목록이 나오므로 순서가 바뀌는지로 다른 매장의 주문 수를 알아낼 수 있었다. 값을 바인드했다고 끝난 것이 아니었다. 문장이 40개 넘게 생긴 것도 같은 구멍에서 나왔다. 세 앱이 정렬 문자열을 대소문자와 공백까지 서로 다르게 만들어서, 뜻이 같은 정렬도 텍스트가 다른 문장이 되었다(주문 시각 순 23개, 주문 번호 순 19개). 고친 판에서 앱은 정렬 코드(HMS_DESC 등)만 넘기고, 프로시저가 허용 목록에서 문장 조각을 고른다. 문장에 이어 붙는 글자는 프로시저에 적어 둔 네 가지뿐이라 앱이 무엇을 보내도 문장의 모양을 바꿀 수 없고, 모르는 코드는 거절한다. 문장 수도 정렬 가짓수인 4개로 정해진다. 동률을 정하는 둘째 키(ORD_NO)도 여기서 함께 정해 두어 앱마다 순서가 달라지지 않는다. ORDER BY :col처럼 정렬 컬럼을 바인드하는 안은 답이 아니다. 문법은 통과하지만 'ORD_HMS'라는 문자열 하나, 곧 모든 행에 같은 값 하나로 정렬하는 셈이라 순서가 바뀌지 않는다.

해설 ​

• DBMS_ASSERT.SIMPLE_SQL_NAME만으로는 모자라다. 식은 막지만 아무 컬럼 이름이나 통과시키고, 방향(DESC)과 둘째 정렬 키는 따로 다뤄야 한다. 고를 수 있는 정렬이 정해져 있으면 코드와 문장 조각을 짝지은 허용 목록이 가장 단순하다.
• 정적 SQL 하나로 쓰는 방법: ORDER BY CASE WHEN :sort = 'HMS_DESC' THEN ORD_HMS END DESC, CASE WHEN :sort = 'NO_ASC' THEN ORD_NO END …처럼 쓰면 문장이 하나이고 주입도 없다. 다만 정렬 키가 식이라 인덱스 순서를 쓸 수 없어 늘 정렬한다. 하루 7건이면 상관없지만, 행이 많고 앞의 몇 건만 보여 주는 화면이라면 정렬마다 문장을 나누는 편이 부분범위 처리에 유리하다.
• 동적 SQL의 두 규칙: 값은 바인드하고, 식별자는 허용 목록이나 검증을 거친 것만 이어 붙인다. PARSE-010의 월별 테이블 이름도 같은 규칙이다.
• 보안 점검이 넣은 312개 문장은 서브쿼리 때문에 실행마다 ORD를 따로 읽었다. 주입은 정보 유출만이 아니라 남의 화면 SQL로 무거운 일을 시키는 길이기도 하다.

관련 교재 ​

• 11화 · 같은 SQL인가 — 텍스트와 지문