Thought of Genius

許多專案主導者都會要求程式設計師不要在程式中組裝 SQL 語句，而改用 Stored Procedure，並使用 Parameter 來傳遞參數。但是這樣就真的能夠對 SQL Injection 完全免疫了嗎? 其實不然，SQL Injection 是由組裝式 SQL 語句而引發的，得注意有任何有【組裝式 SQL】發生的程式碼，當然也包含了 Stored Procedure。
假設今天我們有一個需求，是需要組合多個條件的查詢，我們應該在預存程序裡面再使用一次傳遞參數方式，來避免有任何的組裝式 SQL 的程式碼。

<br />Use Northwind<br />GO<br /><br />-- If store procedure exists, drop store procedure<br />IF (EXISTS (SELECT * FROM sys.procedures WHERE name = N'usp_ComplexQuery')) DROP PROCEDURE usp_ComplexQuery<br />GO<br /><br />-- Create store procedure<br />CREATE PROCEDURE usp_ComplexQuery<br /> @CustomerID nchar(5),<br /> @CompanyName nvarchar(40),<br /> @City nvarchar (15),<br /> @Country nvarchar(15),<br /> @STR nvarchar(1024) OUTPUT<br />AS<br /> DECLARE @WK nvarchar(512)<br /> SET @STR = 'SELECT * FROM Customers'<br /> SET @WK = ''<br /> <br /> IF not (@CustomerID is null or @CustomerID ='')<br /> BEGIN<br /> SET @WK = @WK + ' CustomerID = @pCustomerID and '<br /> END<br /> <br /> IF not (@CompanyName is null or @CompanyName = '')<br /> BEGIN<br /> SET @WK = @WK + ' CompanyName like @pCompanyName and '<br /> SET @CompanyName = '%' + @CompanyName + '%'<br /> END<br /> <br /> IF not (@City is null or @City = '')<br /> BEGIN<br /> SET @WK = @WK + ' City like @pCity and '<br /> SET @City = '%' + @City + '%'<br /> END<br /> <br /> IF not (@Country is null or @Country = '')<br /> BEGIN<br /> SET @WK = @WK + ' Country like @pCountry and '<br /> SET @Country = '%' + @Country + '%'<br /> END<br /> <br /> IF LEN(@STR) > 0<br /> BEGIN<br /> IF LEN(@WK) > 0<br /> BEGIN<br /> SET @STR = @STR + ' WHERE ' + SUBSTRING(@WK,0,LEN(@WK)-3)<br /> EXEC sp_executesql @STR,<br /> N'@pCustomerID nchar(5),@pCompanyName nvarchar(40),@pCity nvarchar (15),@pCountry nvarchar(15)',<br /> @pCustomerID=@CustomerID,@pCompanyName=@CompanyName,@pCity=@City,@pCountry=@Country<br /> END<br /> ELSE<br /> BEGIN<br /> EXEC sp_executesql @STR<br /> END<br /> END<br /> RETURN @@rowcount<br />GO<br /><br />-- Execute procedure<br />DECLARE @return_value int, @STR nvarchar(1024)<br />EXEC @return_value = [dbo].[usp_ComplexQuery]<br /> @CustomerID = NULL,<br /> @CompanyName = NULL,<br /> @City = NULL,<br /> @Country = NULL,<br /> @STR = @STR OUTPUT<br />SELECT 'Return Value' = @return_value, @STR as N'@STR'<br />GO<br />