August 29, 2008 at 6:51 am
Jacob,
Thanks for a great post! I have been doing these dynamic queries for years and thought I had them pretty well laid out and nailed down, but you showed me some great new stuff and some things to be cautious about.
Excellent article, many cudos!
August 29, 2008 at 7:42 am
Jacob,
Thank you for an excellent article. It will come in handy in more ways than one.
Thanks,
Sainey
August 29, 2008 at 7:55 am
yohannn (8/29/2008)
I dont think it will make much difference. However, i would suggest reading this article: http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx
.
August 29, 2008 at 8:15 am
Cool, so what I learned is:
Performance testing can be effected by many factors.
ISNULL can be slightly faster but is not an industry standard
COALESCE is slightly more robust and is an industry standard
In the near future I will need to create a version that is Oracle compatible. I am glad I used COALESCE! Once again thanks for the time you spent collecting all these techniques into a single concise location. I have learned a lot today.
August 29, 2008 at 9:26 am
You can easily eliminate a lot of this type of conditional logic :
-- We should add the WHERE clause if at least one parameter
-- is non-null
IF @ProductNumber IS NOT NULL
OR @Color IS NOT NULL
OR @ReOrderPoint IS NOT NULL
BEGIN
SET @sql = @sql + ' WHERE '
END
...with a simple trick. Just always add a where clause, like this
SET @sql = @sql + ' WHERE 1 = 1'
The '1 = 1' will never filter out any records or cause SQL Server any work, yet it saves you a lot of conditional logic, trying to figure out if you need a 'where' or an 'and'. You just add all filters with an 'and'.
August 29, 2008 at 9:54 am
Hello Jacob,
Wanted to say: nice article. I've been doing dynamic sql for some time and there are advantages and disadvantages to the various methods presented. I've used them all in the past, but would like to humbly submit the following as my currently preferred method of "where clauses generator" (showing there is yet another solution):
--
--EXEC [ProductSearch2] @ProductNumber = N'1234', @Color = N'Red', @ReOrderPoint = 5
--EXEC [ProductSearch2] @ProductNumber = N'1234', @ReOrderPoint = 5
--EXEC [ProductSearch2] @Color = N'Red', @ReOrderPoint = 5
--EXEC [ProductSearch2]
--EXEC [ProductSearch2] @ReOrderPoint = 5
CREATE PROCEDURE ProductSearch3 (
@ProductNumber VARCHAR(20) = NULL,
@Color VARCHAR(10) = NULL,
@ReOrderPoint INT = NULL)
AS
Declare @SQL nvarchar(max)
CREATE TABLE ##WhereClause (
[StatementClause] [nvarchar](max) NOT NULL)
--Add ProductNumber clause
IF @ProductNumber IS NOT NULL
INSERT INTO ##WhereClause
([StatementClause])
Values ('ProductNumber LIKE ''' + @ProductNumber + '%''')
--Add Color clause
IF @Color IS NOT NULL
INSERT INTO ##WhereClause
([StatementClause])
Values ('Color LIKE ' + @Color)
--Add ReOrderPoint clause
IF @ReOrderPoint IS NOT NULL
INSERT INTO ##WhereClause
([StatementClause])
Values ('ReorderPoint = ' + CAST(@ReOrderPoint as nvarchar(max)))
--Build the actual statement
select @SQL = coalesce( @SQL + ' AND (' + [StatementClause] + ')', 'WHERE ' + [StatementClause] ) from ##WhereClause
-- If the SQL statement where clause is NULL, simply set it to return a blank
IF @SQL is null
SET @SQL = ' '
select @SQL
Drop Table ##WhereClause
The disadvantages to this method are:
1) Creation of a temp table
2) Converting (some) datatypes to character representations. Admittedly, this issue exists in all the solutions for some datatypes.
The advantages are:
1: Simple 'IF' checking
2: Ease of building a "WHERE" statement with all the appropriate ' And ' included
August 29, 2008 at 9:54 am
Excellent article Jacob, however, there is one thing that I must take issue with. The following statement:
By using sp_executesql to execute your dynamc query (instead of EXEC() ) you can safeguard your queries against almost every possible SQL Injection.
This is a severe overstatement of what sp_executesql can do wrt Injection vulnerabilities and IMHO is outright dangerous as it tends to give readers the impression that sp_executesql is doing some kind of parameter checking for Injection attacks, which it most certainly is not.
In fact, the scope of dynamic SQL procedures that sp_executesql on its own, can protect against Injection attacks is very narrow and limited to those cases where the parameters passed to sp_executesql will be used as parameters to static SQL itself, AND the command text produced and passed to sp_executesql does not incorporate any text from the original parameters AND is not itself also dynamic SQL.
This is a very small set of the ways that dynamic SQL is used, including in Conditional WHERE clauses. Consider the most common use of dynamic SQL for conditional WHERE clauses: allowing the user to interactively name the WHERE columns and conditions for an ad-hoc report: sp_executesql does almost nothing for you here. Readers need to be aware that just calling sp_executesql does not in any way protect them from Injection attacks in this case (and most other cases as well).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 29, 2008 at 12:10 pm
Yes, I agree that "...protects from almost every possible sql injection..." is some what misleading.
Paremeterization helps to prevent SQL injection to a good extend. sp_executesql helps to execute parameterized queries and hence it helps to avoid many of the sql injection possibilities that exist with EXEC().
sp_executesql does not alone protect from all possible sql injection attacks. I would suggest reading the following:
http://msdn.microsoft.com/hi-in/magazine/cc163523(en-us).aspx
http://msdn.microsoft.com/hi-in/magazine/cc163917(en-us).aspx
http://msdn.microsoft.com/en-us/library/ms161953(SQL.90).aspx
regards
Jacob
.
August 29, 2008 at 12:46 pm
Julie Zeien (8/12/2008)
I agree that it is a good article. However, if you *have* to use EXEC for whatever reason, you can prevent SQL Injection by just replacing all of your single quotes with two single quotes as follows.../*
EXECUTE ProductSearch1 NULL
EXECUTE ProductSearch1 'AR'
*/
CREATE PROCEDURE ProductSearch1
(
@ProductNumber VARCHAR(20)
)
AS
SET NOCOUNT ON
DECLARE @sql VARCHAR(MAX)
SET @sql = ' SELECT * FROM Production.Product '
IF @ProductNumber IS NOT NULL BEGIN
SET @sql = @sql + ' WHERE ProductNumber LIKE ''' +
REPLACE( @ProductNumber, '''', '''''' ) + '%'''
END
-- use the print statement for debugging
-- PRINT @sql
EXEC(@sql)
Hi Julie;
This will help prevent SQL injection for certain cases, although the use of the QUOTENAME(...) function is the preferred method rather than concatenation (in T-SQL).
One thing people need to be aware of, however, is that this type of single-quote escaping is still vulnerable to second-order injection in the case where any kind of update is happening based on the user's input, so the technique, when "necessary" should only be used for reading statements.
Another important thing to note is that the escaping must occur in T-SQL -- a client-level routine that attempts to scrub the user input before passing into SQL Server can easily be circumvented.
TroyK
August 29, 2008 at 1:09 pm
Someone mentioned using joins to table variables as an alternative approach. We've gotten excellent results using that technique where we could declare that all parameters of the search must have some value(s). Our procs accept lists of pipe(|) delimited strings which are parsed and used to populate indexed single-column table variables, which are then joined to the view or primary table being queried.
For example, we have a property_sale table that contains a zipCode column, a propertyType column, a saleType column and a saleDate column. There are thousands of zip codes and sales dates, but only three property types and only two sale types. The calling application builds and submits a string like this:
exec stGetSales '38655|38677|90210', -- zip codes
'S|C', -- property types (single family, condo)
'S',
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 29, 2008 at 1:20 pm
... continued after hiccup/entry
exec stGetSales '38655|38677|90210', -- zip codes
'S|C', -- property types (single family, condo)
'S', -- sales type (regular sale)
'10/2007|11/2007' -- (october-november 2007 sales dates)
Even with parsing and populating four table variables, execution time against a 200 million row table with dozens of columns is still averaging under .4 seconds. The proc will also accept 'ALL' as a parameter for any of the above strings, in which case the table variables get populated with all possible combinations. This obviously slows it down for zips and dates, but ALL is rarely used for these columns.
Within the procedure itself, the actual query that does the work is simply a
select (field list)
from property_sale p
join @zips on z on z.zip = p.zip
join @proptype pt on pt.proptype = p.proptype
join @saletype s on s.saletype = p.saletype
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 29, 2008 at 1:36 pm
You're parsing 200 million rows of data in 0.4 seconds or less? Gotta see that one... would you mind posting the code?
--Jeff Moden
August 29, 2008 at 1:47 pm
I have got to start proofreading better. My apologies.
The table being queried isn't 200 million rows, it's closer to 20 (TWENTY).
Also, my statement wasn't that we parsed 20 million rows, it's that we parsed the four input lists to populate the four table variables. Nothing mysterious about the parsing, its tally-table based (which I learned from you, and thank you very much). Typically we see less than 10 zip codes, a couple of dates, one or two property types, and a single sales type.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 30, 2008 at 9:29 am
Jacob:
Great article!
I think I spotted a small error in sprocs ProductSearch6 and ProductSearch7 when a search parameter is not Null, and you are using the LIKE operator. You should add '%' before and after the parameter, because you are searching for the value anywhere within the relevant database column.
ProductSearch6
ProductNumber LIKE CASE
WHEN @ProductNumber IS NULL THEN '%'
ELSE @ProductNumber END
should be
ProductNumber LIKE CASE
WHEN @ProductNumber IS NULL THEN '%'
ELSE ('%' + @ProductNumber + '%') END
ProductSearch7
(@ProductNumber IS NULL OR ProductNumber LIKE @ProductNumber)
should be
(@ProductNumber IS NULL OR ProductNumber LIKE ('%' + @ProductNumber + '%')
Viewing 15 posts - 46 through 60 (of 106 total)
You must be logged in to reply to this topic. Login to reply