동적 SQL 과 SQL 주입

CREATE OR ALTER PROCEDURE Board.P_SEARCH_BAD
    @keyword nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max) =
        N'SELECT COUNT(*) AS 건수 FROM Board.POSTS WHERE title LIKE N''%' + @keyword + N'%''';
    PRINT @sql;                 -- 만들어진 문장을 봅니다
    EXEC (@sql);
END
GO
EXEC Board.P_SEARCH_BAD @keyword = N'인덱스';              -- (가)
EXEC Board.P_SEARCH_BAD @keyword = N''' OR 1=1 --';      -- (나)

-- 같은 방식으로 만든, 목록을 내는 프로시저입니다. 열을 둘 냅니다.
CREATE OR ALTER PROCEDURE Board.P_SEARCH_BAD2
    @keyword nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max) =
        N'SELECT TOP 3 num, title FROM Board.POSTS WHERE title LIKE N''%' + @keyword + N'%''';
    PRINT @sql;
    EXEC (@sql);
END
GO

EXEC Board.P_SEARCH_BAD2
     @keyword = N''' UNION SELECT num, user_id FROM Member.USERS --';

CREATE OR ALTER PROCEDURE Board.P_SEARCH_OK
    @keyword nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;

    -- 값이 들어갈 자리에 @kw 라고만 적어 둡니다.
    DECLARE @sql nvarchar(max) =
        N'SELECT COUNT(*) AS 건수 FROM Board.POSTS WHERE title LIKE N''%'' + @kw + N''%''';

    -- 문장 · 매개 변수 선언 · 값을 따로 넘깁니다.
    EXEC sys.sp_executesql @sql, N'@kw nvarchar(100)', @kw = @keyword;
END
GO
EXEC Board.P_SEARCH_OK @keyword = N'인덱스';
EXEC Board.P_SEARCH_OK @keyword = N''' OR 1=1 --';
EXEC Board.P_SEARCH_OK @keyword = N''' UNION SELECT num, user_id FROM Member.USERS --';

DECLARE @col nvarchar(50) = N'hit_count';
DECLARE @sql nvarchar(max) =
    N'SELECT TOP 3 num, title, hit_count FROM Board.POSTS ORDER BY @c DESC';
EXEC sys.sp_executesql @sql, N'@c nvarchar(50)', @c = @col;

DECLARE @col nvarchar(50) = N'hit_count';
DECLARE @sql nvarchar(max) =
    N'SELECT TOP 3 num, title, hit_count FROM Board.POSTS ORDER BY '
    + QUOTENAME(@col) + N' DESC';
PRINT @sql;
EXEC sys.sp_executesql @sql;

DECLARE @col nvarchar(200) = N'hit_count DESC; DROP TABLE Board.T_X --';
SELECT QUOTENAME(@col) AS QUOTENAME결과;

DECLARE @col nvarchar(50) = N'hit_count';   -- 화면에서 받은 값입니다

IF @col NOT IN (N'num', N'title', N'hit_count', N'reg_date')
    THROW 50005, N'정렬할 수 없는 열입니다', 1;

DECLARE @sql nvarchar(max) =
    N'SELECT TOP 3 num, title, hit_count FROM Board.POSTS ORDER BY '
    + QUOTENAME(@col) + N' DESC';
EXEC sys.sp_executesql @sql;

DBCC FREEPROCCACHE;                      -- 실습 데이터베이스에서만
GO
EXEC Board.P_SEARCH_BAD @keyword = N'인덱스';    -- 이어 붙인 쪽
EXEC Board.P_SEARCH_BAD @keyword = N'조인';
EXEC Board.P_SEARCH_BAD @keyword = N'트랜잭션';
EXEC Board.P_SEARCH_OK  @keyword = N'인덱스';    -- 매개 변수화한 쪽
EXEC Board.P_SEARCH_OK  @keyword = N'조인';
EXEC Board.P_SEARCH_OK  @keyword = N'트랜잭션';
GO
SELECT cp.objtype AS 종류, cp.usecounts AS 사용횟수
FROM sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text LIKE '%Board.POSTS WHERE title LIKE%';

-- 같은 일을 하는 프로시저 둘. 표 권한은 주지 않고 실행 권한만 줍니다.
CREATE OR ALTER PROCEDURE Board.P_STATIC AS
BEGIN SET NOCOUNT ON; SELECT COUNT(*) AS 글수 FROM Board.POSTS; END
GO
CREATE OR ALTER PROCEDURE Board.P_DYNAMIC AS
BEGIN SET NOCOUNT ON;
    EXEC sys.sp_executesql N'SELECT COUNT(*) AS 글수 FROM Board.POSTS';
END
GO

-- 표 권한이 없는 사용자를 하나 만들어 실행 권한만 줍니다.
CREATE USER dyn_user WITHOUT LOGIN;
GRANT EXECUTE ON Board.P_STATIC  TO dyn_user;
GRANT EXECUTE ON Board.P_DYNAMIC TO dyn_user;
GO

EXECUTE AS USER = 'dyn_user';
EXEC Board.P_STATIC;
EXEC Board.P_DYNAMIC;
REVERT;

DECLARE @sql1 nvarchar(max) = N'SELECT COUNT(*) FROM Board.POSTS WHERE title LIKE ''%인덱스%''';
DECLARE @sql2 nvarchar(max) = N'SELECT COUNT(*) FROM Board.POSTS WHERE title LIKE N''%인덱스%''';
EXEC (@sql1);
EXEC (@sql2);