Use Dynamic SQL to Improve Query Performance & Avoiding Sql Injections, sp_depends

/*This article shows an example of a poor performing query and how to make it perform better using Dynamic SQL. It also demonstrates a way of coding dynamic SQL so it can easily be debugged. When we write dynamic SQL within a stored procedure not only do we have to give the user EXECUTE rights to the procedure but also specific access to the tables in the dynamic SQL.

*/

/*A sample of Poor performance query */

/*

It is a stored procedure that was taking a long time to execute. The purpose of the stored procedure is to return rows that match a given set of optional inputs. The way this is achieved using non-dynamic SQL is for the query to test the value of the parameter for NULL or a column equal to the value of the parameter. All columns on the table are defined to be NOT NULL and the passing in of a parameter with a NULL value logically means that we don't care what the value is. You might think that SQL Server would short-circuit the query when it sees that the parameter is null and not bother to look at the values on the table but it actually does a full table scan instead.

*/

USE [AdventureWorks]

GO

CREATE PROCEDURE SearchProducts

(

@Name nvarchar(50) = NULL,

@Color nvarchar(15) = NULL,

@ListPriceMin money = NULL,

@ListPriceMax money = NULL,

@ModifiedDateMin datetime = NULL,

@ModifiedDateMax datetime = NULL

)

AS

BEGIN

SELECT

[Name],

[Color],

[ListPrice],

[ModifiedDate]

FROM

[Production].[Product]

WHERE

([Name] LIKE @Name OR @Name IS NULL)

AND

([Colour] LIKE @Color OR @Color IS NULL)

AND

([ListPrice] >= @ListPriceMin OR @ListPriceMin IS NULL)

AND

([ListPrice] <= @ListPriceMax OR @ListPriceMax IS NULL) AND (ModifiedDate >= @ModifiedDateMin OR @ModifiedDateMin IS NULL)

AND

(ModifiedDate <= @ModifiedDateMax OR @ModifiedDateMax IS NULL) END GO --Well Performing but open to SQL Injection Dynamic SQL

USE [AdventureWorks]

GO

/**********************************************************************

-- Sample:

-- EXEC [SearchProducts_SQLInjectable] 'W%', 'SH%'

-- Injected SQL

-- EXEC [SearchProducts_SQLInjectable] @Name='W%''; SELECT * FROM HumanResources.EmployeeAddress --'

********************************************************************** /

CREATE PROCEDURE [dbo].[SearchProducts_SQLInjectable]

(

@Name nvarchar(62) = NULL,

@ProductNumber nvarchar(25) = NULL,

@ListPriceMin money = NULL,

@ListPriceMax money = NULL,

@ModifiedDateMin datetime = NULL,

@ModifiedDateMax datetime = NULL

)

AS

BEGIN

DECLARE @sSql nvarchar(4000)

DECLARE @sWhereClause nvarchar(4000)

DECLARE @NewLine nvarchar(4000)

SET @NewLine = CHAR(13) + CHAR(10)

SET @sWhereClause = ' ' -- Initialise

SET @sSql = 'SELECT [Name],' + @NewLine

+ ' [ProductNumber],' + @NewLine

+ ' [ListPrice],' + @NewLine

+ ' [ModifiedDate]' + @NewLine

+ ' FROM [Production].[Product]' + @NewLine

+ ' WHERE 1 = 1' -- Always true

IF @ProductNumber IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ProductNumber] LIKE ''' + @ProductNumber + ''''

IF @ListPriceMin IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ListPrice] >= ' + Cast(@ListPriceMin as varchar(30))

IF @ListPriceMax IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ListPrice] <= ' + Cast(@ListPriceMax as varchar(30)) IF @ModifiedDateMin IS NOT NULL SET @sWhereClause = @sWhereClause + @NewLine + ' AND ModifiedDate >= ' + Convert(varchar(30), @ModifiedDateMin, 121)

IF @ModifiedDateMax IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND ModifiedDate <= ' + Convert(varchar(30), @ModifiedDateMax, 121) IF @Name IS NOT NULL SET @sWhereClause = @sWhereClause + @NewLine + ' AND [Name] LIKE ''' + @Name + '''' SET @sSql = @sSql + @sWhereClause EXEC (@sSql) END GO

/*
Constructing SQL this way is not good, it leaves the procedure open to SQL Injection; little Bobby Tables might enter his name! Also, it does not allow the optimiser to reuse the execution plan. An example of how SQL Injection can be performed is given in the comments below:

*/

EXEC [SearchProducts_SQLInjectable] @Name='W%''; SELECT * FROM HumanResources.EmployeeAddress --'

/*
A slightly better way to construct the WHERE clause and provide a little more protection is to double up the quotes in each parameter by calling SQL Server's Replace function e.g.:

/*

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [Name] LIKE ''' + Replace(@Name, '''', '''''') + ''''

/*

This above query is still not optimal as it still does not allow SQL Server to reuse the query when different values of @Name are passed in to the procedure, though it should prevent most forms of SQL injection.

*/

/* sp_depends is used to find out which procedures use which tables then this stored procedure will be listed

*/

EXEC sp_depends 'Production.Product'

/*The above lists out the all the stored procedure which uses this table*/

/*

Using Parameterised Dynamic SQL to avoid SQL Injection along with the debug option

*/

USE [AdventureWorks]

GO

-- **********************************************************************

-- Sample CALL:-- EXEC [SearchProducts_SQLNonInjectable] 'W%'

-- **********************************************************************

CREATE PROCEDURE [dbo].[SearchProducts_SQLNonInjectable]

(

@Name nvarchar(50) = NULL,

@ProductNumber nvarchar(25) = NULL,

@ListPriceMin money = NULL,

@ListPriceMax money = NULL,

@ModifiedDateMin datetime = NULL,

@ModifiedDateMax datetime = NULL,

@Debug bit = 0 -- When set to 1 just prints SQL and does not execute it

)

AS

BEGIN

DECLARE @sSql nvarchar(4000)

DECLARE @sWhereClause nvarchar(4000)

DECLARE @ParmDefinition nvarchar(4000)

DECLARE @NewLine nvarchar(4000)

SET @NewLine = CHAR(13) + CHAR(10)

SET @sWhereClause = ' ' -- Initialise

SET @sSql = 'SELECT [Name],' + @NewLine

+ ' [ProductNumber],' + @NewLine

+ ' [ListPrice],' + @NewLine

+ ' [ModifiedDate]' + @NewLine

+ ' FROM [Production].[Product]' + @NewLine

+ 'WHERE 1 = 1' -- Always true

IF @Name IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [Name] LIKE @Name'

IF @ProductNumber IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ProductNumber] LIKE @ProductNumber'

IF @ListPriceMin IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ListPrice] >= @ListPriceMin'

IF @ListPriceMax IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND [ListPrice] <= @ListPriceMax' IF @ModifiedDateMin IS NOT NULL SET @sWhereClause = @sWhereClause + @NewLine + ' AND ModifiedDate >= @ModifiedDateMin'

IF @ModifiedDateMax IS NOT NULL

SET @sWhereClause = @sWhereClause + @NewLine + ' AND ModifiedDate <= @ModifiedDateMax ' SET @ParmDefinition = '@Name nvarchar(50),' + @NewLine + ' @ProductNumber nvarchar(25),' + @NewLine + ' @ListPriceMin money,' + @NewLine + ' @ListPriceMax money,' + @NewLine + ' @ModifiedDateMin datetime,' + @NewLine + ' @ModifiedDateMax datetime' SET @sSql = @sSql + @sWhereClause IF @Debug = 0 EXEC sp_executesql @sSql, @ParmDefinition, @Name=@Name, @ProductNumber=@ProductNumber, @ListPriceMin=@ListPriceMin,

@ListPriceMax=@ListPriceMax,

@ModifiedDateMin=@ModifiedDateMin, @ModifiedDateMax=@ModifiedDateMax

ELSE

BEGIN

PRINT 'DECLARE ' + @ParmDefinition

PRINT ''

PRINT

'SET @Name = ' + IsNull('''' + @Name + '''','NULL')

PRINT 'SET @ProductNumber = ' + IsNull('''' + @ProductNumber + '''','NULL')

PRINT 'SET @ListPriceMin = ' + IsNull(Cast(@ListPriceMin AS varchar(30)),'NULL')

PRINT 'SET @ListPriceMax = ' + IsNull(Cast(@ListPriceMax AS varchar(30)),'NULL')

PRINT 'SET @ModifiedDateMin = ' + IsNull('''' + Convert(varchar(50), @ModifiedDateMin, 121) + '''','NULL')

PRINT 'SET @ModifiedDateMax = ' + IsNull('''' + Convert(varchar(50), @ModifiedDateMax, 121) + '''','NULL')

PRINT ''

PRINT @sSql

END

--END IF

//It is setted so as to get displayed in the sp_depends stored procedure.

-- Make sure this procedure is detected with sp_depends on table [Production].

--[Product]

IF 1 = 2 -- Always false

BEGIN

SELECT TOP(0) 0 FROM [Production].[Product]

END

--END IF

END

GO