Saturday, December 1, 2007
Dynamic Sql Tricks
Dynamic SQL is easy enough, but is often susceptible to SQL Injection. One way around SQL Injection is to use parameters always. This is easier said than done. Here are some examples that show how this can be done. They get more complex with each example.
This example shows how to use sp_executesql with parameters.
create PROCEDURE Test3 @MinNumberOfGuests INT, @MaxNumberOfGuests INT
AS
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = 'select * from Site where MinNumberOfGuests = @MinNumberOfGuests and MaxNumberOfGuests = @MaxNumberOfGuests'
EXEC sp_executesql @Sql,
N'@MinNumberOfGuests INT, @MaxNumberOfGuests INT',
@MinNumberOfGuests, @MaxNumberOfGuests
GO
Test3 25, 300
This shows how to handle parameters that are null which in this case signifies that the parameter is not to be in the where clause. The important thing to note is that "select top 0 " followed by all the parameters. This allows the for the value parameters of sp_executesql to always be included since there is no way to conditionally include them. It adds a very small amount of overhead to select variables, and top 0.
create PROCEDURE Test5 @MinNumberOfGuests INT, @MaxNumberOfGuests INT
AS
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = 'select * from Site where MinNumberOfGuests = @MinNumberOfGuests and MaxNumberOfGuests = @MaxNumberOfGuests; select top mailto:0@MaxNumberOfGuests
EXEC sp_executesql @Sql,
N'@MinNumberOfGuests INT, @MaxNumberOfGuests INT',
@MinNumberOfGuests, @MaxNumberOfGuests
GO
Test5 25, 300
This is a more robust implementation of the above.
create PROCEDURE Test4 @MinNumberOfGuests INT = null, @MaxNumberOfGuests INT = null
AS
DECLARE @Sql NVARCHAR(MAX)
DECLARE @AllSql NVARCHAR(MAX)
SET @Sql = 'select * from Site WHERE 1 = 1 '
IF @MinNumberOfGuests is not null
BEGIN
Set @Sql = @Sql + ' AND MinNumberOfGuests = @MinNumberOfGuests'
END
IF @MaxNumberOfGuests is not null
BEGIN
Set @Sql = @Sql + ' AND MaxNumberOfGuests = @MaxNumberOfGuests'
END
Set @AllSql = @Sql + N'; Select top 0 @MinNumberOfGuests, @MaxNumberOfGuests'
EXEC sp_executesql @AllSql,
N'@MinNumberOfGuests INT, @MaxNumberOfGuests INT',
@MinNumberOfGuests, @MaxNumberOfGuests
GO
Test4 25, null
Test4 null, 300
Test4 25, 300
Test4 null, null
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment