안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 8-4. 저장 프로시저 - 동적 SQL(Dynamic SQL)과 SQL 주입(Injection) 공격을 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 저장 프로시저 동적 SQL(Dynamic SQL)과 SQL 주입(Injection) 공격입니다.

TL;DR

동적 SQL은 SQL 구문과 파라미터를 문자열 연결로 조합해 사용하는 패턴으로, 저장 프로시저에서 동적 SQL을 사용하는 방법과 ,문자열 연결 시 주의사항을 설명합니다. 동적 SQL 사용은 보안상 취약점인 SQL Injection을 야기할 수 있기 때문에 파라미터화와 검사 루틴을 사용해야 합니다.

동적 SQL

동적 쿼리(Dynamic Query), 다이나믹 쿼리 등 여러 방식으로 불립니다. 마이크로소프트 공식 명칭은 동적 SQL이며, 국내에서는 동적 쿼리가 많이 사용됩니다.

동적 SQL은 SQL 구문과 파라미터 등을 문자열 연결(Concatenation) 방식으로 조합해 사용하는 패턴을 의미합니다. 일반적으로 아래와 같은 패턴입니다.

☑️ 챗GPT 활용: 문자열 연결(Concatenation) 방식을 알려줘

SELECT SQL 구문과 뒤의 param 변수를 조합해 SQL을 생성하는 패턴입니다.

저장 프로시저 - 동적 SQL(Dynamic SQL) 방식

아래와 같은 테이블 이름을 프로시저 파라미터로 받아야 하는 경우가 있습니다.
지금까지 저장 프로시저로 WHERE절이나, INSERT, UPDATE도 했는데 쉬워 보입니다. 바로 SQL 구문을 작성합니다.

-- 테이블 이름을 파라미터로 받는 저장 프로시저 - 실행 안됨
CREATE PROC pProcTest 
@tblname NVARCHAR(20)
AS
SELECT * FROM @tblname
GO

메시지 1087, 수준 16, 상태 1, 프로시저 pProcTest, 줄 4 [배치 시작 줄 140]
Must declare the table variable "@tblname".
 

아예 저장 프로시저 생성 구문이 실행조차 안됩니다. 그 이유는, 테이블명과 같은 개체값(Object value)은 파라미터로 받아 처리할 수 없기 때문입니다. SQL Server의 디자인이며, 이런 패턴의 쿼리를 프로시저로 생성하기 위해서는 동적 SQL을 이용해야 합니다.
여담으로, SELECT 절에서 상위 X건의 로우를 가져오는 TOP X 명령도 저장 프로시저로 생성할 수 없습니다.

--프로시저 생성 - 수행 안됨
CREATE PROC pProcTest
@topN INT
AS
SELECT TOP @topN * FROM Employee
GO

TOP X 구문은 OFFSET / FETCH로 변경해 저장 프로시저에서 생성할 수 있습니다. - MySQL의 LIMIT과 유사하며, 페이징(pagination) 처리에도 사용됩니다.

☑️ 챗GPT 활용: 데이터베이스 페이징(pagination) 처리에 대해서 알려줘

CREATE PROC pProcTest
@offset INT
, @fetch INT
AS
SELECT * FROM Employee
ORDER BY emp_num
    OFFSET @offset ROWS
    FETCH NEXT @fetch ROWS ONLY;
GO

EXEC pProcTest 0, 2;
EXEC pProcTest 2, 2;

OFFSET / FETCH는 아래 예제도 참고하세요.
10건씩 테이블에서 페이징 하면서 데이터를 조회합니다.
 

USE AdventureWorks;
GO

SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader 
ORDER BY SalesOrderID
    OFFSET 0 ROWS
    FETCH NEXT 10 ROWS ONLY;
GO

SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader 
ORDER BY SalesOrderID
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY;
GO

저장 프로시저 - 동적 SQL로 생성

그렇다면, 동적 SQL로 문자열 연결 패턴을 이용해 저장 프로시저를 생성합니다.

-- 저장 프로시저 생성
CREATE OR ALTER PROC pEmpTableParam
@tblname NVARCHAR(20)
, @emp_num INT
AS
DECLARE @SQL NVARCHAR(200)  -- 전체 SQL 구문을 저장할 변수 선언
-- @SQL 변수에 연결된 문자열 저장
SET @SQL = N'SELECT * FROM ' + @tblname + ' WHERE emp_num = ' + CONVERT(NVARCHAR(10), @emp_num)
-- SELECT @SQL   -- 디버깅 용도로 생성된 문자열을 출력해 확인
EXEC(@SQL);  -- 연결된 문자열을 실행
GO

-- 프로시저 실행
EXEC pEmpTableParam N'Employee', 2;

EXEC pEmpTableParam N'Employee', 3;

파라미터를 두 개 받았습니다. @tblname과 @emp_num입니다. 이어서, 생성한 전체 SQL 구문을 저장할 @SQL 변수를 프로시저 안에서 선언합니다.
이제 어려운 부분은 @SQL 구문을 만드는 작업인데요, 공백과 문자열 연결, 작은따옴표(Single quotation) 등의 연결이 필요합니다.
문자열 연결을 하고 EXEC 전에 주석처리된 “SELECT @SQL” 구문을 이용해 프로시저를 생성하고 파라미터를 넣어 테스트하면 생성된 SQL 구문을 테스트로 출력해 볼 수 있습니다. - 디버깅 용도로 사용할 수 있습니다.
이렇게 Employee 테이블명과 emp_num 파라미터를 전달해 테이블명을 파라미터로 받는 동적 SQL로 저장 프로시저를 생성할 수 있습니다.

문자열 파라미터 동적 SQL

문자열을 연결하는 과정이 어려울 때가 있습니다. WHERE 절에서 문자열을 비교하는 경우인데요, 이때 작은따옴표를 잘 사용해야 합니다.

-- 저장 프로시저 생성
CREATE OR ALTER PROC pEmpTableParamEmail
@tblname NVARCHAR(20)
, @email NVARCHAR(20)  -- 문자열 파라미터
AS
DECLARE @SQL NVARCHAR(200)  -- 전체 SQL 구문을 저장할 변수 선언
-- 문자열 비교 조건 주의
SET @SQL = N'SELECT * FROM ' + @tblname + ' WHERE email like ''' + @email + ''''
-- SELECT @SQL   -- 디버깅 용도로 생성된 문자열을 출력해 확인
EXEC(@SQL);  -- 연결된 문자열을 실행
GO

-- 프로시저 실행
EXEC pEmpTableParamEmail N'Employee', 'peb@example.com';

EXEC pEmpTableParamEmail N'Employee', 'ssk%';

저장 프로시저 구문 중간의 문자열 연결 부분에서 WHERE 절의 작은따옴표 개수에 주의합니다. 문자열 안에서 작은따옴표 두 개가 한 개로 치환되므로 필요한 숫자만 넣어야 합니다.

이렇게 동적 SQL로 처리는 가능하지만, 사용을 권장하지 않습니다. 테이블 이름을 파라미터로 받아야 하는 구조라면 데이터베이스 스키마 변경을 고려하시고, 아래 내용을 참조하세요.

저장 프로시저 동적 쿼리 회피 방안

한때, 게시판을 만들거나 유사한 패턴을 생성할 때 게시판마다 1개의 테이블로 만들어 생성하는 경우가 있었습니다.

어떤 신문사는 신문사의 섹션마다(정치, 경제, 생활…) 테이블을 생성하기도 했습니다.(실화)
당시에 왜 이런 패턴을 사용했냐고요? 이유는 간단합니다. 

“테이블을 분리해 만들면 속도가 빠를 것 같아서요.”

다시 강조하지만, 모두가 그랬던 것은 아닙니다. 몇몇 애플리케이션에서만 이런 패턴을 이용했습니다. 

이렇게 유사한 구조의 테이블을 논리적인 접근을 위해 개별 테이블로 분리하면, 어쩔 수 없이 위와 같은 테이블명을 파라미터로 받는 동적 SQL이나 저장 프로시저를 동적 SQL로 만들어야만 합니다. - 좋지 않은 방법입니다.

--동작 안하는 예시 쿼리
CREATE OR ALTER PROC 게시판조회
@tblname NVARCHAR(20)
AS
DECLARE @SQL NVARCHAR(200)
SET @SQL = N'SELECT * FROM ' + @tblname
EXEC(@SQL);
GO

EXEC pEmpTableParamEmail N'자유게시판';

해결 방안은 간단합니다. 위와 같이 개별 게시판마다 테이블을 추가하는 패턴이 아니라, 하나의 게시판 테이블에 컬럼을 하나 추가해 개발하는 게 더 데이터베이스 스키마 디자인에 적합하고, 확장성이 뛰어난 방법입니다.

예를 들어, 이렇게 게시판 식별자 컬럼을 추가하고, 이 식별자로 게시판을 구별해야 합니다.

--동작 안하는 예시 쿼리
CREATE TABLE tBoard(
idx INT,
title NVARCHAR(50),
read_num INT,
writer NVARCHAR(20),
board_name NVARCHAR(20),   -- 게시판 식별자
contents NVARCHAR(MAX)
)

이 방법만 사용해도 저장 프로시저에서 동적 SQL을 사용하는 패턴을 최소화할 수 있습니다.

왜 동적 SQL을 최소화해야 하냐고요? 데이터베이스 개발자에게 가장 무서운 SQL Injection 공격 때문입니다.

SQL Injection (SQL 주입) 공격

동적 쿼리는 반드시 주의해야 할 보안사항이 있습니다. 바로, SQL Injection 공격입니다. 예를 들어, 애플리케이션에서 SQL 쿼리를 실행하는 패턴이 아래와 유사할 수 있습니다.

이렇게 애플리케이션에서 동적 SQL을 생성해 실행하는 패턴이 사용되던 시기가 있었습니다. 사용자의 입력을 받이 실행되는 이런 패턴의 문제는 param 값을 변조할 때 심각한 문제가 됩니다.

예를 들어, 이렇게 파라미터가 입력되었다고 가정합니다.

SQL 구문은 이렇게 변조됩니다. 상상만 해도 끔찍합니다.

회원 테이블의 모든 정보를 추출하거나, SELECT 구문 대신 DROP이나 DELETE 구문을 넣거나, 컬럼 개수에 맞춰 중요 정보 테이블에 UNION을 실행하면 말 그대로, 돌이킬 수 없는 정보 유출 사태가 발생할 수 있는 심각한 패턴입니다. (맨 마지막의 --은 이후 구문은 모두 주석처리하는 명령입니다.)

이런 SQL Injection 공격을 방어하기 위해 문자열 연결(string concatenation) 방식 SQL 패턴을 사용하지 않고, 파라미터화와 모든 파라미터를 검사하는 루틴을 이용합니다.

SQL Server에서 저장 프로시저를 이용하는 이유도 기본적으로 파라미터화 되어 실행되기 때문에 이런 공격 위협을 완화할 수 있기 때문입니다. 하지만, 특수한 경우에 주의해 SQL 저장 프로시저를 사용할 필요가 있습니다.

SQL Injection 공격 방어

사용자의 입력을 믿지 마세요.

사용자 입력을 어떤 경우에도 신뢰하지 않습니다. 항상 사용자의 입력이 개입된 모든 파라미터에 대해 SQL Injection 공격과 같은 최악의 상황을 상정하고 개발합니다.

최신 데이터베이스 연결 프레임워크 활용

프로그래밍 언어나 개발 프레임워크마다 제공하는 데이터베이스 연결 라이브러리가 있습니다. 항상 최신의 프레임워크를 사용합니다. SQL Injection 공격은 2000년대 초부터 있던 공격입니다. 예를 들어, .NET 프레임워크의 데이터베이스 연결 라이브러리인 ADO.NET은 기본적으로 command 객체에서 파라미터 자동 체크 루틴을 제공하고 SQL Injection 방어 루틴이 제공됩니다.

 
☑️ 챗GPT 활용: .NET Framework ADO.NET에 대해서 알려줘

동적 SQL 구문 사용을 최소화하고 저장 프로시저로 생성

SQL 구문에 애플리케이션에서 받아 처리하는 동적 SQL 구문이 있다면 저장 프로시저로 변경하고, 데이터베이스 연결 라이브러리의 파라미터 처리 루틴을 활용해 모든 입력을 검사하세요. 저장 프로시저로 얻는 다양한 장점과 보안성을 적극 활용하시기 바랍니다.

데이터베이스 계정 권한 최소화

애플리케이션에서 로그인되어 데이터베이스로 연결하는 계정의 권한을 최소화합니다. 예를 들어, SQL Server의 모든 권한이 있는 System Admin 역할이 아니라, 특정 데이터베이스, 특정 테이블들 또는 뷰 조회나 저장 프로시저 조회/실행 권한만 있는 계정을 사용해 실행 권한을 최소화하면, 여러 공격으로부터 피해를 최소화할 수 있습니다.

별도의 데이터베이스 연결 로직에 로깅(Logging) 루틴 사용

데이터베이스 연결을 시도하는 로직에 로깅 루틴을 추가하고 주기적으로 검사합니다. SQL Injection 공격이 있는지, 어떤 패턴으로 공격이 시도되는지 확인할 수 있습니다.

☑️ 챗GPT 활용: 애플리케이션 로깅(Logging) 루틴에 대해서 알려줘

SQL 실행 전, 파라미터 변조 체크 루틴이 있는지 확인

만약, 파라미터 체크 루틴 여부가 불확실하고, 아주 오래전 레거시 프로그래밍 언어와 데이터베이스 연결 라이브러리를 사용한다면, 직접 escape 캐릭터를 체크하고 SQL Injection에 사용되는 구문을(예를 들어 구문 종결자 ; 또는 -- 주석 구문) replace 하는 루틴을 직접 다양한 패턴으로 생성하고 공용 라이브러리로 만들어 모든 데이터베이스 연결에서 사용할 수 있습니다. 더 좋은 방법은 최신의 데이터베이스 연결 라이브러리를 활용하세요.
 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022