Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Defensive Database Programming Expand / Collapse
Author
Message
Posted Wednesday, February 24, 2010 9:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, Visits: 824
Comments posted to this topic are about the item Defensive Database Programming
Post #871993
Posted Wednesday, December 4, 2013 7:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
I noticed the examples in the book for creating a Numbers table. Of course they all work fine! I would like to contribute a variant that I happen to like. It depends on the (undocumented but heavily used) table master.dbo.spt_values, though any sufficiently-large table will do:

SELECT TOP <quantity desired> IDENTITY(int, 0,1) AS n 
INTO #Numbers
FROM master.dbo.spt_values v1, master.dbo.spt_values v2;

ALTER TABLE #Numbers ADD PRIMARY KEY CLUSTERED (n);

SELECT COUNT(*) from #Numbers;

This gave me 5503716 natural numbers to use on my SQL Server 2005 instance and 6290064 on my SQL Server 2008 R2 instance. I believe that it is also much faster than the while-loop approach. Since the table is populated using a cartesian product, one can rapidly build tables of any size, subject to disk space limitations of course. You can fill in <quantity desired> with the upper limit of the range you need.
Post #1519638
Posted Tuesday, August 5, 2014 8:48 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:54 AM
Points: 25, Visits: 125
I was reading Chapter 1 of your interesting book and was a bit puzzled by the solution to the Defending Against Cases of Unintended Use example. If you do not want people to use special SQL characters in the search string, why not just recode your stored procedure as a basic string comparison, like:
CREATE PROCEDURE dbo.SelectMessagesBySubjectBeginning_v2
@SubjectBeginning VARCHAR(30)
AS
SET NOCOUNT ON;
SELECT Subject, Body
FROM dbo.Messages WHERE LEFT(Subject, LEN(@SubjectBeginning)) = @SubjectBeginning;

which removes the problems with
LIKE

and works with searches on '[OT]' or '50%' which was one of the problem examples given?
When it comes to parameters in SQL or XSLT, the more strongly-typed the better, I would have thought. Primitive strings should be primitive strings without magical symbols.
If you wanted to provide variations on "starts-with" or "contains" or "all these words" or "exact phrase" or even "synonyms" then you could provide separate parameters. I am probably biased against wildcard searches, mind you, perhaps as they are associated in my mind with people messing about with non-atomic identifiers.
Post #1599762
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse