MS SQLServer Stored Procedure

• 동적 쿼리는 SQL Injection에 매우 유의해야한다. Stored Procedure에서 동적 쿼리를 생성할 때도 파라미터 방식으로 해야한다.
• Sql server, .net and c# video tutorial: Dynamic SQL in Stored Procedure
• (4) Prevent sql injection with dynamic sql - YouTube

-- 동적으로 생성하는 SQL 문자열에서 조건 값을 파라미터화 한다.
CREATE PROCEDURE spSearchEmployeesGoodDynamicSQL
@FirstName nvarchar(100) = NULL,
@LastName nvarchar(100) = NULL,
@Gender nvarchar(50) = NULL,
@Salary INT = NULL
AS
BEGIN
     DECLARE @SQL nvarchar(MAX)
     DECLARE @sqlParams nvarchar(MAX)
 
     SET @SQL = 'Select * from Employees where 1 = 1'
 
     IF(@FirstName IS NOT NULL)
          SET @SQL = @SQL + ' and FirstName=@FN'
     IF(@LastName IS NOT NULL)
          SET @SQL = @SQL + ' and LastName=@LN'
     IF(@Gender IS NOT NULL)
          SET @SQL = @SQL + ' and Gender=@Gen'
     IF(@Salary IS NOT NULL)
          SET @SQL = @SQL + ' and Salary=@Sal'
 
     EXECUTE sp_executesql @SQL,
     N'@FN nvarchar(50), @LN nvarchar(50), @Gen nvarchar(50), @sal int',
     @FN=@FirstName, @LN=@LastName, @Gen=@Gender, @Sal=@Salary
END
GO