Let us review this dba.exchange Oracle question for SQL-Server.
This is SaUce's code, after a little formatting:
CREATE PROCEDURE GetCustomer @FirstN nvarchar(20) = NULL, @LastN nvarchar(20) = NULL, @CUserName nvarchar(10) = NULL, @CID nvarchar(15) = NULL as begin DECLARE @sql nvarchar(4000) SELECT @sql = 'C_FirstName, C_LastName, C_UserName, C_UserID FROM CUSTOMER WHERE 1=1 ' IF @FirstN IS NOT NULL SELECT @sql = @sql + ' AND C_FirstName like @FirstN ' IF @LastN IS NOT NULL SELECT @sql = @sql + ' AND C_LastName like @LastN ' IF @CUserName IS NOT NULL SELECT @sql = @sql + ' AND C_UserName like @CUserName ' IF @CID IS NOT NULL SELECT @sql = @sql + ' AND C_UserID like @CID ' EXEC sp_executesql @sql, N'@C_FirstName nvarchar(20), @C_LastName nvarchar(20), @CUserName nvarchar(10), @CID nvarchar(15)', @FirstN, @LastN, @CUserName, @CID end go
SaUce mentioned in his second note, that he had 1000 lines of code to prevent SQL-Injection. My feeling is that he needn't do this to shield his stored procedure.
- is this SQL-Server Procedure immune against SQL-Injection by itself
- is it as satisfying solution with respect to performance.