BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by David Wakefield on 02 July 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Dynamic SQL No, No, No!
I am not going to tell you the dangers of dynamic SQL (there are hundreds of articles on the internet explaining the problem of SQL Injection attacks). This article gives you a technique to allow you to avoid dynamic SQL where it is misused the most, the WHERE clause.
The problem
It is very often the case that you need to present a form for your users which allows them to select data by multiple disparate criteria:
This form allows you to search for pop stars by various criteria
In the form above we may just want to see all Black, Female pop stars or maybe we want to find all pop stars whose last name is Osmand. The problem for the programmer is writing code which allows us to use only those parts of the selection criteria the user is interested in.
The Dynamic SQL solution
The following code constructs an SQL string by concatenating only the conditions where the user has made a selection:
Create Proc spSelectPopStar
@FirstName as varchar(Max) = ''
,@LastName as varchar(Max) = ''
,@Sex as varchar(Max) = ''
,@Race as varchar(Max) = ''
,@DOBFrom as Datetime
,@DOBTo as Datetime
as
declare @SQLStr as varchar(Max)
-- start the SQL
set @SQLStr = 'SELECT * FROM tblPopstar
WHERE 1=1 '
-- concatenate only those conditions required
if @FirstName <> ''
begin
set @SQLStr = @SQLStr +
' and firstname like ''%' + @FirstName + '%'''
end
if @LastName <> ''
begin
set @SQLStr = @SQLStr +
' and lastname like ''%' + @LastName + '%'''
end
if @Sex <> ''
begin
set @SQLStr = @SQLStr + ' and sex=''' + @Sex + ''''
end
if @Race <> ''
begin
set @SQLStr = @SQLStr + ' and race=''' + @Race + ''''
end
if @DOBFrom is not null
begin
set @SQLStr = @SQLStr + ' and dob>''' + @DOBFrom + ''''
end
if @DOBTo is not null
begin
set @SQLStr = @SQLStr + ' and dob<> + @DOBTo + ''''
end
-- run the built up string
exec (@SQLStr)
The above would work fine. The only clever bit is WHERE 1=1 this means that even if the user chooses nothing we still have correctly syntaxed SQL, and we don't have to write complicated code to determine when (and when not) to concatenate in an AND.
The problem is that the above code leaves us vulnerable to an SQL Injection attack - e.g. the user typing:
''; DELETE FROM tblPopStart WHERE 'a%'='a
in the Last Name search box.
The better solution using default parameters
The following uses default parameter values to substitute in values which ensure that if the user has typed nothing in the criteria box, the SELECT statement will select all records.
Create Proc spSelectPopStar
-- provide defaults which we can test to determine
-- if the user has typed anything in
@FirstName as varchar(Max) = ''
,@LastName as varchar(Max) = ''
,@Sex as varchar(Max) = ''
,@Race as varchar(Max) = ''
,@DOBFrom as Datetime = '01 Jan 1753'
,@DOBTo as Datetime = '31 Dec 2100'
as
-- Parameters may be populated with NULLS so replace
-- with our well thought out defaults
set @FirstName = isnull(@FirstName,'')
set @LastName = isnull(@LastName,'')
set @Sex = isnull(@Sex,'')
set @Race = isnull(@Race,'')
set @DOBFrom = isnull(@DOBFrom,'01 Jan 1753')
set @DOBTo = isnull(@DOBTo,'31 Dec 2100')
-- compare fields against parameters
-- OR whether the parameter is the default value
SELECT * FROM tblPopstar
WHERE
(FirstName like '%' @FirstName '%') -- Blank value selects all
and (LastName like '%' @LastName '%') -- Blank value selects all
and ((Sex = @Sex) or (@Sex = '')) -- Blank value selects all
and ((race = @Race) or (@Race = ''))-- Blank value selects all
and (DOB > @DOBFrom) -- all popstars were born after 1753
and (DOB < @dobto)="">-- All popstars are born before 2100
The most interesting lines are the Sex and Race lines. The OR operator means that if the user leaves @Sex blank, every record will be selected because @Sex='' even though the column Sex will always be male or female.
The fact that every name matches LIKE '%%' means that a blank @FirstName or @LastName will select all records.
The fact that for the next 88 years we can assume all pop stars are born between 1753 and 2100 means the last two lines will select all records, if @DOBFrom and @DOBTo are left blank.
Conclusion
It is always possible to construct a WHERE clause without resorting to Dynamic SQL.