SQL Stored Proc With Dynamic Where
A Learning Opportunity
Today I had a lovely learning opportunity when I discovered a stored procedure that was not quite safe. It used parameters, and the C# code sent them over using proper parameters. The problem came to how the entire SQL statement was being built and executed.
In fact, you could say it was more about how the dynamic WHERE clause was being built.
Naturally I went on a quick quest to find a better way to do this and be secure. This MSDN article really helped. It gave me enough knowledge to write an example against a Northwind database.
What we are going to do is create a stored procedure that will take two parameters. These parameters might also be NULL, so we need to take that into account. We will use the EmpoyeeID and Region to query our Employees table.
-- ============================================= -- Author: SheldonS -- Create date: 1/15/2016 -- Description: An example of writing a stored proc with -- dynamic WHERE clause -- ============================================= CREATE PROCEDURE sst_SheldonS_Test -- Add the parameters for the stored procedure here @nEmpID AS INT, @sRegion AS NVARCHAR(25) AS
Using the sp_executesql procedure we will need to look at this a little differently. Normally you know you need something to hold your SELECT clause, and something to hold your WHERE clause, but you will also need something to hold your parameter definitions. For us, our SELECT clause is going to be very straight forward, we want to select everything in the Employees table.
BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE @SQLSelect AS NVARCHAR(MAX); --Holds the select clause DECLARE @SQLWhere AS NVARCHAR(MAX); --Holds the where clause DECLARE @PDef AS NVARCHAR(500); --Holds definitions of parameters used in the where clause --SELECT SET @SQLSelect = N'SELECT * FROM Employees';
Here comes the fun part, creating our dynamic WHERE clause. Basically we are going to check each parameter to see if it has a value, if so we will add a WHERE condition to look for that value. If it is NULL or doesn’t have a value we will still add a WHERE condition but to look for something else like a default value.
When we do this we again have to use parameters. So we will create new ones in our WHERE conditions. This means we have to define those new parameters in our definitions parameter. I know, it sounds confusing, but really it is quite simple.
--Build WHERE IF(@sRegion <> '') BEGIN SET @SQLWhere = N' WHERE Region = @Reg'; END ELSE BEGIN SET @sRegion = ISNULL(@sRegion, ''); --Take into account the @sRegion param could be null SET @SQLWhere = N' WHERE @Reg = @Reg'; END SET @PDef = N'@Reg NVARCHAR(15)'; --Add @Reg definition IF(@nEmpID > 0) BEGIN SET @SQLWhere = @SQLWhere + N' AND EmployeeID = @ID'; END ELSE BEGIN SET @nEmpID = ISNULL(@nEmpID, 0); --Take into account the @nEmpID param could be null SET @SQLWhere = @SQLWhere + N' AND @ID = @ID'; END SET @PDef = @PDef + N', @ID INT' --Add @ID definition
Our next step is really simple. We need to stitch our SELECT and WHERE clauses together.
--Build the actual command DECLARE @SQL AS NVARCHAR(MAX); SET @SQL = @SQLSelect + @SQLWhere;
Finally we need to execute our SQL. The sp_executesql procedure is looking for our SQL command, our parameter definitions, and finally the values for the parameters we defined.
--Execute the command with parameters EXECUTE sp_executesql @SQL, @PDef, @Reg = @sRegion, @ID = @nEmpID;
Lets run some queries and see what we get!
As you can see, we get the expected data. This works even when I try to simulate an SQL Injection attack. I hope you found this useful and maybe even saved you some pain.