보안 점검에서 주문 상태 변경 API로 주문 금액까지 0원으로 바꿀 수 있었다

기초PLSQL한빛유통679 / 700

상황

고객센터와 쇼핑몰 관리 화면은 주문 상태를 바꿀 때 프로시저 P_SET_ORD_ST를 부른다. 지난주 외부 보안 점검에서 상태 값 칸에 CANCEL', ORD_AMT = '0을 넣자 주문 금액이 0원으로 바뀌었다는 지적이 나왔다. 프로시저를 짠 개발자는 "화면에서 받은 값을 SQL에 넣으려면 동적 SQL을 써야 한다고 배웠다"라고 한다. DBA는 같은 모양의 UPDATE가 공유 풀에 하루 1만 개 넘게 쌓인다는 것도 함께 알려 왔다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · ORD_ST는 COMPLETE, PENDING, CANCEL 세 가지 · ORD_AMT NUMBER
상태 변경은 하루 약 12,000번이고, 한 번에 주문 한 건을 고친다
CURSOR_SHARING = EXACT · 통계 최신

원본 SQL

sql

PROCEDURE P_SET_ORD_ST(p_ord_seq IN NUMBER, p_ord_st IN VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 'UPDATE T_ORD SET ORD_ST = ''' || p_ord_st || ''''
                 || ' WHERE ORD_SEQ = ' || p_ord_seq;
  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, '주문 없음: ' || p_ord_seq);
  END IF;
END;

실행계획 학습용 예시

xplan

[보안 점검 때 실제로 실행된 문장 (V$SQL.SQL_TEXT)]
  UPDATE T_ORD SET ORD_ST = 'CANCEL', ORD_AMT = '0' WHERE ORD_SEQ = 61873

[V$SQL: 이 프로시저가 만든 문장 · 하루]
  SQL_TEXT 앞부분                                          문장 수    실행 합계    하드 파싱
  UPDATE T_ORD SET ORD_ST = 'COMPLETE' WHERE ORD_SEQ = …     9,212        9,240        9,212
  UPDATE T_ORD SET ORD_ST = 'CANCEL' WHERE ORD_SEQ = …       2,104        2,111        2,104
  UPDATE T_ORD SET ORD_ST = 'PENDING' WHERE ORD_SEQ = …        649          649          649
  합계                                                      11,965       12,000       11,965

[TKPROF: 그중 한 문장]
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          2          1           1
Misses in library cache during parse: 1

[USER_DEPENDENCIES: P_SET_ORD_ST가 기대는 객체]
  SYS.STANDARD · SYS.DBMS_STANDARD   (T_ORD는 없다)

문제

상태 값 하나로 주문 금액까지 바뀐 이유를 실행된 문장으로 설명하시오. 같은 모양의 문장이 하루 1만 개 넘게 생긴 이유도 쓰시오.
프로시저를 고치시오. 동적 SQL이 필요한 경우와 필요 없는 경우를 가려 쓰고, 고친 판이 보안 말고도 무엇을 얻는지 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

PROCEDURE P_SET_ORD_ST(p_ord_seq IN NUMBER, p_ord_st IN VARCHAR2) IS
BEGIN
  -- 상태 값은 정해진 세 가지뿐이다: 다른 값은 문장까지 가기 전에 거절한다
  IF p_ord_st IS NULL OR p_ord_st NOT IN ('COMPLETE', 'PENDING', 'CANCEL') THEN
    RAISE_APPLICATION_ERROR(-20002, '상태 값 오류');
  END IF;

  -- 테이블과 컬럼이 정해진 문장: 정적 SQL 로 쓴다 (PL/SQL 변수는 바인드 변수로 넘어간다)
  UPDATE T_ORD
     SET ORD_ST = p_ord_st
   WHERE ORD_SEQ = p_ord_seq;

  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, '주문 없음: ' || p_ord_seq);
  END IF;
END;

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

xplan

[고친 뒤: V$SQL · 하루]
  UPDATE T_ORD SET ORD_ST = :B2 WHERE ORD_SEQ = :B1      문장 1 · 실행 12,000 · 하드 파싱 1

[보안 점검 값을 다시 넣으면]
  ORA-20002 상태 값 오류 · UPDATE는 실행되지 않는다 (검사가 없더라도 입력 전체가 ORD_ST 값 하나로 들어가려다 길이 초과로 실패한다)

[USER_DEPENDENCIES]
  P_SET_ORD_ST → SQLP.T_ORD (TABLE)

모범 답안 서술

보안 점검 때 실제로 실행된 문장은 UPDATE T_ORD SET ORD_ST = 'CANCEL', ORD_AMT = '0' WHERE ORD_SEQ = 61873이다. 프로시저는 p_ord_st를 따옴표 사이에 그대로 이어 붙였는데, 입력값 안의 따옴표가 문자열을 닫았고 그 뒤의 , ORD_AMT = '0이 SQL의 일부가 되었다. 화면 값이 문장의 모양을 바꾼 것이고, 이것이 SQL 주입이다. ORD_AMT는 NUMBER지만 '0'이 묵시적 형변환으로 0이 되어 에러도 나지 않았다. 문장이 하루 11,965개인 것도 같은 원인이다. 상태 값과 주문 번호가 문장 텍스트에 박혀 있어서 주문마다 다른 문장이 되고, 처음 보는 문장이라 그때마다 하드 파싱을 한다(TKPROF의 Misses in library cache during parse: 1). 이 UPDATE에서 실행할 때마다 바뀌는 것은 값뿐이고 테이블과 컬럼은 정해져 있다. 그러니 동적 SQL이 필요 없다. 동적 SQL은 테이블 이름이나 컬럼 이름 같은 식별자가 실행 때 정해질 때만 쓰고, 그때도 값은 USING으로 바인드한다. 고친 판은 PL/SQL 안에 UPDATE를 그대로 적는다. 정적 SQL 안의 PL/SQL 변수는 바인드 변수(:B1, :B2)로 넘어가므로 어떤 값이 들어와도 값으로만 쓰이고, 문장은 하나다. 하드 파싱은 11,965 → 1이 된다. 상태 값은 정해진 세 가지만 받도록 먼저 검사한다. 보안 말고도 얻는 것이 있다. 정적 SQL은 컴파일할 때 테이블과 컬럼이 검사되므로 ORD_ST를 잘못 적으면 배포 전에 드러난다. USER_DEPENDENCIES에 T_ORD가 남아서, 나중에 T_ORD를 고칠 때 영향 분석에 이 프로시저가 잡힌다. 동적 SQL 판은 둘 다 없었다.

해설

함정: DBMS_ASSERT.ENQUOTE_LITERAL로 값을 감싸 따옴표를 막아도 값마다 다른 문장이 되는 것은 그대로다. 값은 감싸지 말고 바인드한다.
동적 SQL이 맞는 자리: 월별 보관 테이블처럼 테이블 이름이 실행 때 정해지면 동적 SQL을 쓴다. 그때도 이어 붙이는 것은 검증한 식별자뿐이고 값은 USING으로 넘긴다(PARSE-010).
숫자 p_ord_seq를 이어 붙인 쪽도 위험하다. 지금은 NUMBER 인자라 주입이 어렵지만 문장은 주문마다 달라지고, 누가 인자 타입을 VARCHAR2로 바꾸는 날 같은 구멍이 생긴다.
CURSOR_SHARING=FORCE로 하드 파싱을 줄여도 주입은 막지 못한다. FORCE가 리터럴을 바인드 자리로 바꾸는 대상은 이미 주입이 끝난 문장이기 때문이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황