[MSSQL] 프로시저 동적쿼리 작성시 SQL Injection 우회 방법

팀Slack방에 에러가 올라와서 Kibana에서 trace를 찾아보니 

상품명 조회 파라미터에 ' (싱글쿼터)가 들어 온 것이었다.

" 엥? lucy필터를 사용하고 있을탠대??? "

(lucy필터는 네이버에서 만든 XSS 방지 라이브러리이다)

싱글쿼터가 들어오면 오면 치환이 되어야 됬는대 안된 것이다.

싱글쿼터가 들어오게되면 SQL Injection에 취약해 질수 있기 때문에 매우 위험한 상황이었다.

(최근에 "여기어*" 라는 숙박 서비스가 SQL Injection으로 약 100만명의 정보가 유출된 사건도 있었다.)

그래서 우리팀의 담당하고 있는 매뉴들에 대해서 a' or 1=1 or 'a'='a와 같은 문자열을 입력하니.

모든 데이터가 촥! ...OTL

이제서야 알게되었지만 lucy필터는 form-data에 대해서만 적용되고

Request Raw Body로 넘어가는 JSON에 대해서는 처리해 주지 않는다 

어설프게 그냥 용도 정도만 " lucy필터가 XSS처리해주는 구나~" 하고 대충 넘어 갔던게 후회됬다..

(참고 lucy-xss-servlet-filter의 한계)

팀내의 주요 API는 모두 RequestBody를 통해서 데이터를 받고 있었다. 그러니 당연히 필터링이 전혀안될 수 밖에.

이 이슈에대해서 팀원들과 회의를 한 결과 공수가 적게 드는 쪽으로 방향을 맞추기로 했다.

팀내 특성상(?) 쿼리를 프로시저에 작성하고있는대.

프로시저중에서도 문자열을 합쳐서 만든 동적쿼리들에 대해서만 문제가 발생했다.

아래는 검색화면에서 사용하는 SP와 유사하게 만든 것이다.

USE [cm]
GO
/****** Object:  StoredProcedure [dbo].[SQLINJECTION]    Script Date: 2019-06-04 오후 9:27:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[SQLINJECTION] 
    @pi_searchKeyword    NVARCHAR(MAX)
    ,@pi_start_date        NVARCHAR(10)
    ,@pi_end_date        NVARCHAR(10)
AS
BEGIN
 
    SET NOCOUNT ON;
    
    DECLARE 
        @v_select_sql NVARCHAR(MAX) = ''
        ,@v_from_sql NVARCHAR(MAX) = ''
        ,@v_where_sql NVARCHAR(MAX) = ''
        ,@v_sql  NVARCHAR(MAX) =''
 
    SET @v_select_sql += 'select top 100 * '
    
    SET @v_from_sql += ' from tb_user with(nolock) '
 
    IF @pi_start_date <> '' and @pi_end_date <> ''
    BEGIN
        SET @v_where_sql += ' where user_reg_date between ''' + @pi_start_date + ''' and '''+ @pi_end_date + ''''
    END 
 
    IF @pi_searchKeyword <> ''
    BEGIN
        SET @v_where_sql += 'and user_name='+ '''' + @pi_searchKeyword + ''''
    END
 
    SET @v_sql = @v_select_sql + @v_from_sql + @v_where_sql    
 
    PRINT @v_sql
 
    EXECUTE SP_EXECUTESQL @v_sql
 
END
 

SQL Injection은 바로 35행에서 발생한다.

문자열인 싱글쿼터로 값을 감싸고 있기때문이다

myBatis의 #{}을 쓴다해도, 프로시저 내부에서 싱글쿼터를 붙이기때문에 무용지물이다.

그래서 어떻게 처리했는대?

QUOTENAME()함수를 사용해서 처리했다.

https://docs.microsoft.com/ko-kr/sql/t-sql/functions/quotename-transact-sql?view=sql-server-2017

특정 구분자를 양옆으로 감싸주는 것이다.(문자열에 대해서만 처리하였음)

22행, 35행, 36행이 수정한 내용이다

USE [cm]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[SQLINJECTION] 
    @pi_searchKeyword    NVARCHAR(MAX)
    ,@pi_start_date        NVARCHAR(10)
    ,@pi_end_date        NVARCHAR(10)
AS
BEGIN
 
    SET NOCOUNT ON;
    
    DECLARE 
        @v_select_sql NVARCHAR(MAX) = ''
        ,@v_from_sql NVARCHAR(MAX) = ''
        ,@v_where_sql NVARCHAR(MAX) = ''
        ,@v_sql  NVARCHAR(MAX) =''
        ,@v_quote NVARCHAR(MAX)
 
    SET @v_select_sql += 'select top 100 * '
    
    SET @v_from_sql += ' from tb_user with(nolock) '
 
    IF @pi_start_date <> '' and @pi_end_date <> ''
    BEGIN
        SET @v_where_sql += ' where user_reg_date between ''' + @pi_start_date + ''' and '''+ @pi_end_date + ''''
    END 
 
    IF @pi_searchKeyword <> ''
    BEGIN
        SET @v_quote = QUOTENAME(@pi_searchKeyword,'''')
        SET @v_where_sql += 'and user_name=' + @v_quote
    END
 
    SET @v_sql = @v_select_sql + @v_from_sql + @v_where_sql    
 
    PRINT @v_sql
 
    EXECUTE SP_EXECUTESQL @v_sql
 
END
 

수정전 

' or 1=1 or 'a'='a 입력시 실행되는 SP내용

select top 100 *  from tb_user with(nolock)  where user_reg_date between '2018-01-01' and '2019-12-31'and user_name='' or 1=1 or 'a'='a'

수정후

' or 1=1 or 'a'='a 입력시 실행되는 SP내용

select top 100 *  from tb_user with(nolock)  where user_reg_date between '2018-01-01' and '2019-12-31'and user_name=''' or 1=1 or ''a''=''a'

or 1=1 or 'a'='a 입력시 실행되는 SP내용

select top 100 *  from tb_user with(nolock)  where user_reg_date between '2018-01-01' and '2019-12-31'and user_name='or 1=1 or ''a''=''a'

TO BE 쿼리의 뒤쪽 문자열을 보면 싱글쿼터로 인젝션을 무효화 시키는 것을 볼 수 있다.

처리하긴 하였지만 아쉬운점은 사실이다.  처음에 이 이슈가 발생했을 때 필터를 적용해보고 싶었지만..

그럴수 없는 상황이었기 때문에 이렇게 우회하는 방법으로 처리하였다.

더 좋은 방법이 있으면 댓글 부탁드립니다 ^^