SQL 동적 쿼리(Dynamic SQL)

요구사항: YEARXX 년도 사업장별 사고 유형 건수를 보여주세요.

1. 사고유형: 기준정보 테이블에서 ROOTTP = 'DSTTYP' AND CODELV = 2 AND USEYNX = 'Y' 인 조건을 만족하는 데이터

2. 사업장: 사업장 테이블에서 USEYNX = 'Y'인 값

3. 사고: 사고관리테이블에 저장되어 있음, COMPCD와 DOCSEQ을 primary key로 가지며 DISTYP에 사고유형 기준정보의 CODEVL(사고 유형 코드)을 저장.

 

핵심 제약사항

1. 기준정보가 바뀌더라도 쿼리를 수정하고 싶지 않음

2. 사업장이 추가, 삭제되더라도 쿼리를 수정하고 싶지 않음

 

Dynamic SQL 기법 선택 이유

1. 사업장, 기준정보, 건수만 구해서 소스코드에서 정리하게 되면, 성능이 저하됨. 소스코드에서 반복문을 돌며 집계하는 과정은 데이터베이스 내에서 SQL로 한 번에 처리하는 것보다 훨씬 느리며, SPU 및 메모리 사용량 크게 증가.

2. 사업장 또는 기준정보별로 쿼리를 반복적으로 실행시키면, 데이터베이스에 한 번의 요청으로 끝낼 수 있는 작업을 여러 번의 요청으로 나누게 되며, 이는 네트워크를 오가는 통신 비용(왕복 지연 시간)이 증가되어 총 실행 시간을 크게 늘림

Dynamic SQL 기법 우려사항

-  SQL 인젝션(Injection) 가능성: 사용자 입력값(_region, COMPCD 등)을 직접 쿼리에 포함시키기 때문에, 입력값에 악의적인 SQL 코드가 포함될 경우 보안 취약점이 발생할 수 있음.

 

SQL Injection 해결방법

 

- QUOTE(): 이 함수는 입력된 문자열을 따옴표(')로 감싸고, 문자열 내에 포함된 따옴표를 이스케이프('' 또는 \') 처리함. 이는 입력값이 SQL 쿼리의 일부가 아닌 단순한 문자열 리터럴로 인식되게 하여 기본적인 SQL 인젝션을 막는 데 도움이 됨.

- LEFT(): 이 함수는 입력된 문자열의 왼쪽에서 지정된 길이만큼만 잘라내며. 이는 입력값의 길이를 제한하여 악의적인 코드가 길게 이어지는 것을 물리적으로 막는 역할을 함.

 

쿼리 작성

BEGIN NOT ATOMIC

-- 1. group_concat_max_len 설정 (많은 컬럼에 대비)
SET SESSION group_concat_max_len = 10000;

-- 2. 변수 생성
SET @compcd = QUOTE(LEFT(${param(COMPCD)}, 4));
SET @yearxx = QUOTE(LEFT(${param(YEARXX)}, 4));
SET @region = QUOTE(LEFT(${param(_region)}, 2));

SET @template = CONCAT(
      '(SELECT COUNT(*) FROM SMDSMT SMS '
      ,' INNER JOIN 문서관리테이블 DCS ON (DCS.COMPCD = SMS.COMPCD AND DCS.DOCSEQ = SMS.DOCSEQ)'
      ,' INNER JOIN 사고관리테이블 SMT ON (SMT.COMPCD = SMS.COMPCD AND SMT.ROTSEQ = SMS.DOCSEQ)'
      ,' INNER JOIN 문서관리테이블 DCT ON (DCT.COMPCD = SMT.COMPCD AND DCT.DOCSEQ = SMT.DOCSEQ)'
      ,'WHERE  1 = 1'
      ,'  AND  DCS.DELYNX = ''N''' 
      ,'  AND  DCT.DELYNX = ''N'''
      ,'  AND  SMS.COMPCD = ', @compcd 
      ,'  AND  SMS.PLCBUS = PBM.PLCBUS'
      ,'  AND  LEFT(SMS.DISTDA, 4) = ', @yearxx, ' '
    );

SET @cols = NULL;
SELECT 
  GROUP_CONCAT(
    CONCAT(
      @template
      ,'  AND  SMT.DISTYP = ''', CODEVL, ''') AS AA', CODEVL
    )
    SEPARATOR ', '
  ) INTO @cols
FROM 기준정보테이블
WHERE ROOTTP = 'DSTTYP' AND CODELV = 2 AND USEYNX = 'Y';

-- 3. 최종 SQL 문자열 생성
SET @sql = CONCAT(
  'SELECT PBM.PLCBUS AS "PLCBUS"'
  ,',FN_READ_PLCBMT_NAME(PBM.COMPCD, ', @region, ', PBM.PLCBUS) AS "PLCBNM"'
  ,','
  ,@template
  ,'  ) AS "AASUMX",'
  ,@cols
  ,' ,'
  ,@template
  ,'  AND  SMT.DISTYP = '' '') AS "AANONX"'
  ,'FROM 사업장테이블 PBM '
  ,'WHERE  1 = 1'
  ,'  AND  PBM.COMPCD = ', @compcd
  ,'  AND  PBM.USEYNX = ''Y'''
);

-- 4. 실행
PREPARE COMBINED_SQL FROM @sql;
EXECUTE COMBINED_SQL;
DEALLOCATE PREPARE COMBINED_SQL;

-- 오류 발생시 실행문 대신 아래 SELECT 문 실행 후 원인 파악
-- SELECT @sql;

END;

 

주요 문법 및 기능

1. SET: 변수를 선언하고 값을 할당하는 데 사용됩니다. 쿼리에서는 @compcd, @yearxx, @region, @template, @cols, @sql과 같은 사용자 정의 변수를 만듭니다. 이 변수들은 사용자 입력값과 동적으로 생성되는 쿼리 문자열을 저장하는 데 사용됩니다.
2. QUOTE() & LEFT(): 사용자 입력값을 처리하는 함수입니다.
   • LEFT(문자열, 길이): 문자열의 왼쪽에서 지정한 길이만큼 문자를 반환합니다. 쿼리에서는 사용자 입력값의 길이를 제한하여 악의적인 코드의 일부를 제거하는 역할을 합니다.
   • QUOTE(문자열): 문자열을 따옴표(')로 감싸고, 문자열 내부의 따옴표를 이스케이프 처리합니다. 이는 입력값을 SQL 리터럴로 변환하여 기본적인 SQL 인젝션을 방어하는 데 도움이 됩니다.
3. GROUP_CONCAT(): 여러 행의 값을 하나의 문자열로 결합하는 집계 함수입니다. 이 쿼리에서는 CMMDMT 테이블의 CODEVL 값들을 순회하며, 각 값에 대한 서브쿼리를 문자열로 생성하고 쉼표(,)로 구분하여 @cols 변수에 저장합니다. 이것이 바로 동적인 컬럼을 만드는 핵심 기능입니다.
4. CONCAT(): 여러 문자열을 하나로 합치는 함수입니다. 쿼리 전체가 CONCAT() 함수를 사용하여 여러 부분 문자열(변수 포함)을 조합하여 최종 SQL 문을 완성합니다.
5. PREPARE & EXECUTE: 동적으로 생성된 SQL 문을 실행하는 데 사용됩니다.
   • PREPARE COMBINED_SQL FROM @sql: @sql 변수에 저장된 텍스트를 COMBINED_SQL이라는 이름의 **준비된 문장(Prepared Statement)**으로 컴파일합니다.
   • EXECUTE COMBINED_SQL: 컴파일된 쿼리를 실행합니다.
   • DEALLOCATE PREPARE COMBINED_SQL: 사용이 끝난 준비된 문장을 메모리에서 해제합니다.

 

추가적인 SQL Injection 방지법

데이터베이스 서버 설정

1. 최소 권한의 원칙 (Principle of Least Privilege): SQL 인젝션 공격이 성공하더라도 피해를 최소화하는 가장 중요한 방법입니다. 애플리케이션이 데이터베이스에 접근할 때 사용하는 계정에 필요한 최소한의 권한만 부여해야 합니다. 예를 들어, 웹사이트의 게시판 데이터를 관리하는 계정에는 SELECT, INSERT, UPDATE, DELETE 권한만 주고, 절대 DROP TABLE이나 ALTER DATABASE 같은 권한은 주지 말아야 합니다.
2. 프로시저 사용: 민감한 데이터를 처리하는 로직을 **저장 프로시저(Stored Procedure)**에 담아두면, 애플리케이션은 프로시저만 호출하고 매개변수만 전달할 수 있습니다. 이 방식은 SQL 문이 데이터베이스에 미리 컴파일되어 있기 때문에, 인젝션 공격이 데이터베이스의 실행 계획을 변경하기 어렵게 만듭니다.

시스템 설계 및 애플리케이션 측면

1. 웹 애플리케이션 방화벽 (WAF): 웹 애플리케이션 방화벽은 웹 서버로 들어오는 모든 트래픽을 검사하여, 알려진 SQL 인젝션 패턴을 사전에 차단합니다. 이는 쿼리 계층 이전에 공격을 막아주는 외부 방어막 역할을 합니다.
2. 데이터베이스 접속 정보 관리: 데이터베이스 계정 정보는 암호화하여 안전하게 관리해야 합니다. 소스 코드에 하드코딩하거나, 평문으로 된 설정 파일에 저장해서는 안 됩니다.