For anyone who has been wondering where I've been, let's jsut say that it has been an eventful two weeks. I am writing this now from my hospistal bed, which it turns out is very difficult, so please forgive any errors.

I gave my SQL Injection speech two weeks ago (which I think that I did blog about). Then part one of my series "There must be fifteen ways to lose your cursors" was published over at SqlServerCentral.com to a firestorm of response (over 300 posts so far). Then I gave speech on the same topic to Philly.net code camp to very favoarable reviews (so far anyway).

Then last monday I had to go to the ER for intense stomach pains that were diagnosed a Gallstones. Tuesday I go to see a Surgeon about getting my Gall bladder removed. He schedules me for surgery on May 4 and puts me on an "Absolutely no Fat or Glycol" diet, meaning fruits, vegetables, water and skim milk only. Joy!

Thursday I have to leave work with stomach pains so bad I almost don't make it home. Then I have my younger son, Chris drive me to the ER again. They say that is is another Gallstone attack but as an added bonus I now have pancreaitis too.

They say they cannot remove the gall bladder until the pancreaitis goes away, but it is taking its own sweet time. So on monday they will do some surgical procedure to relieve the pancreaitis and assuming that goes OK remove my gall bladder on tuesday.

Of course part 2 of mu series comes out on Monday, so I guess that Jeff and Lynn and Gus and the others will have to field the questions.

Hopefully, I will be back soon ...

The downlod link in the prior update was pointing to the wrong presentation.  It has been corrected there and you can get it here as well.

I gave this presentation to PSSUG at SCP last night to very favorable reviews.  Nonetheless IMHO it is only 2/3rds done, so I hope to refine it quite a bit by the next time that I give it.  You can download the slides and code here.

Note:  Link was bad (old presentation), it has been fixed.

Halfway though the most overloaded two weeks I have had in a long time, ... I have finally finished installment #2 of my series "There Must Be 15 Ways to Lose Your Cursors" for SQLServerCentral and I will submit it to Steve soon (lat minut formatting). 

Next up is my presentation to PSSUG (Philadelphia Sql Server Users Group, a PASS chapther) the Wednesday evening entitled "Injected, Inspected, Detected, Infected, Neglected and Selected!  Using Dynamic SQL Safely, without SQL Injection".  (Hmm, I notice that my titles keep getting longer and longer, I wonder if that's a trend?) 

I'll be working on this brand new Injection speech over the next few days and then I have the PASS speaker proposals due by midnight Friday.  Never mind work and three Easter week services this week.  Whew!

Wow, what a time for SQL books.  First I received my pre-ordered SQL Server 2008 Query Performance Tuning Distilled by Grant Fritchey last week, about a week earlier than expected.  Grant is a friend from SQLServerCentral and PASS 2008 and a great guy to boot.  Follow the link to his blog where you will find more links to order his book online.

Then yesterday, I received my pre-ordered SQL Server 2008 Internals by Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, Connor Cunningham, Adam Machanic, and Ben Nevarez two weeks ahead of schedule.  The incredible line up of writers on this book has made it the most hotly anticipated new SQL book in many years.

The irony is that becuase of the demands of my current project and because I am already behind in sumbitting my PASS 2009 proposals, I probable won't be able to get to them for a while yet.    Oh well.  Just thumbing through, both look great though ...

Heh.  Well I forgot to post the best version of the XML aggregator when I posted the other day.  Then Ward Pond tagged in a post on it (here), and pointed out that I was "improving" on an old version that had been fixed by Adam Machanic among others...

Here is my "good" version that fixes the entitization problem:

SELECT (

SELECT n + ','
FROM (

  SELECT 'a<b' AS n
UNION ALL
  SELECT 'b>a'
UNION ALL
  SELECT 'b&a'
UNION ALL
  SELECT 'b

a') r

FOR XML PATH(''), TYPE

).value('.[1]','varchar(max)')
-- =====

 And here is the version currenlty being used by Ward and company...:

;WITH ColumnToPivot ([data()]) AS (
    SELECT p.ParentString + N', '
    FROM Parent p
    JOIN Child c
    ON c.ParentId = p.ParentId
    WHERE c.ChildId = 2
    ORDER BY p.ParentId
    FOR XML PATH(''), TYPE
),
    XmlRawData (CSVString) AS (
        SELECT (SELECT [data()] AS mydata FROM ColumnToPivot AS d FOR XML RAW, TYPE).value( '/row[1]/mydata[1]', 'NVARCHAR(max)') AS CSV_Column
)
SELECT LEFT(CSVString, LEN(CSVString)-1) AS CSVList
FROM XmlRawData

Finally, I heard yesterday that Adam is has a new challenge, involving, you guessed it, grouping concatenated strings.  Guess I better get busy...

Just a quick note ...

Upon further testing and analysis, I feel confident that the best solution for string concatenation in SQL Server 2005+ is the FOR XML method, like so:

SELECT CAST(
   (SELECT TABLE_NAME+', '
     From INFORMATION_SCHEMA.TABLES
     Order By TABLE_NAME
     FOR XML PATH('')
) as VARCHAR(MAX))

It has a lot of advantages, which I will go into later.  It also has one big problem(Entitization) which I will also demonstrate how to fix...

Last night I premiered my presentation "There Must Be 15 Ways To Lose Your Cursors" to the South Jersey division of the Philadelphia SQL Server Users Group.

As promised, I have uploaded the presentation and all of the SQL Scripts as a ZIP it to this site.  You can download it here.

Just a quick note on string concatenation:

  Concatenation(appending) a table of many small strings together into one big string is easy in SQL Server, but slow.  Fixng it is hard for the reasons that I list here.  I beleive that I have "solved" this, reducing the O(n²) operation to effectively O(n*Log(n)).

A preliminary version of this technique is demonstrated here.  The approach seen in these forum posts can be greatly improved by "stacking" the 2^k roll-ups concatenating all of the lower level strings together at once, instead of "cacading" them up one level at a time, as demonstrated.

I will post a more complete version later...

A fast and accurate way to calculate the distance in miles between two points, based on the latitudes and longitudes.

There are basically three (actually four) strategies for protecting against SQL Injection while allowing some use of Dynamic SQL:

  1.  BlackListing
  2.  Firewalling
  3.  Parametizing
  4.  Parsing*

"Blacklisting" is the practice of scanning the client-supplied text for certain "blacklisted" strings, such as semicolons or words like "EXEC" and "DROP", as a way of detecting injection attacks.  Blacklisting is a bad practice and fails for two simple reasons, first, it is not possible to detect every possible injection attack without actually fully parsing the final Dynamic SQL execution string.  No matter how many banned strings you included in your scanner, you still cannot be sure that you have accounted for every posibility.  For instance, consider the alternate quotation characters in the upper half of the ASCII character set (and many more in Unicode).  Do you know which ones of them can be used to close a quotation?  Neither do I, and neither does anybody that I know.

Secondly, blacklisting invariably breaks the application that it is trying to protect by preventing perfectly valid queries from being executed.  For instance, a query for an employee named "Sally Waldrop" is unlikley to make it through the blacklist.  A report query on a client called "Executive Cleaning" is also likely to be rejected.  And the more that you try to fix the first problem by adding more and more strings to the blacklist, the worse you will be making the second problem.  So blacklisting is a bad choice both because you cannot be sure that it will work and because you can be sure that it will break your application.

"Firewalling" is the practice of using security settings to restrict the Dynamic SQL's execution context permissions down to only what that Dynamic SQL statement is supposed to do.  Thus, for a procedure that is supposed to execute a dynamic SQL string to produce a report on the CLIENTS table, a special database User called "CLIENTS_Reports" would be created that had SELECT permission granted to the CLIENTS table, Like this:

    Grant Select on OBJECT::dbo.CLIENTS to CLIENTS_Reporter

 but no rights to anything else, nor any other rights on the CLIENTS table.  Then the procedure would execute the dynamic SQL like this:

    Execute(@query) As USER='CLIENTS_Reporter'

The weaknesses of firewalling are first, that it is a point solution only, as a separate database User needs to be created for each separate stored procedure that uses client text in Dynamic SQL.  Using a single User for multiple stored procedures can comprise the integrity of this strategy because you may have to combine different permission sets to be sure that this one user can do everything that it might be legitimatelly called on to do.  Seperate User accounts for each one is fine if you only have a few of these, but quickly becomes unmanageble with larger numbers.  More discussion on this appraoch by me can be found here.

Secondly, it can be difficult with just security settings and permissions to get the fine control that you may need.  For instance, an application that supports bank tellers may use dynamic SQL to access account information.  While this could be managed with firewalling, you would probably also want to insure that the query could only return information for a single account at a time as any query from a teller that lists information for ALL accounts is likely to be something illegitmate.  In this case, firewalling cannot do much for you.

"Parametizing" is the practice of only allowing client-supplied text to be passed through parameters and variables and never actualized as executable code.  Thus, instead of code like this:

    Select @query = 'SELECT Column FROM table WHERE key=''' + @UserInput + ''''
    Execute(@query)

The code would be rewritten like this:

    Select @query = 'SELECT Column FROM table WHERE key=@p1'
    Set @P1Def = N'@P1 NVarchar(255)';
    EXEC sp_executesql @query, @P1Def, @UserQuery;

In the first case, a client string of " '; DELETE FROM CLIENTS; --" could spell disaster, however, in the parametized instance, it would just return no records.

The shortcomings of parametization are first, it requires more work and thought than other approaches.  Secondly, it initially seems to be applicable to only the simplest cases of dynamic SQL (as above).  However, it turns out that there are some tricky ways to use parametization that allow it to be applied safely and sucessfully to all but the most complex cases of dynamic SQL.  These methods (variable execution, reconstitution, and directed expression) are beyond the scope of this article, however, it should be noted that they have some disadvantages of their own: primarily that they typically require a stricter and more complex interface between the client and the server procedure and thus cannot usually be implemented with making changes in the client as well.

The fourth stategy, "Parsing" is really only included for completeness as it is not actually implmented in any case that I know of.  If it were used, parsing would invovle completely parsing the SQL syntax of dynamic text to be executed and then insuring that it conforms to restrictions defined for that particular use of dynamic SQL (for instance, that it consist of only a single SQL statement).  Parsing is not used primarily because it would be just too hard to implement on SQL Server.  SQL is not an easy language to parse completely and correctly and SQL is not a language that is well suited the task of syntactically and semantically parsing a string of text.  Even setting aside the difficulty of it, the overhead of doing it repeatedly for a heavily used procedure could be severe.  Consequently, without some substantial help in the future from SQL Server itself (or some other Microsoft facility), this approach will probably always remain impractical.

Here's a good article on SQL Injection by one of my favorite authors, Michael Coles: http://www.sqlservercentral.com/articles/Security/updatedsqlinjection/2065/.

Note that most of the SQL Injection articles will be filed under the "Dynamic SQL" heading.

I have implemented large shared schema, multi-tenant applications & databases before and it is suprisingly straight-forward as long as you follow certain rules:

1) All application users must be easily distinguishable by tenant to SQL. 

 Practically speaking this means that either each tenant has a separate Login or each user has a sepearate Login to SQL Server and a Tenant_Users table to map the user back to their tenant association.

2) Every table that the application can write to must have a tenant_id column that identifies the tenant-owner of the data.

3) Every such table must have a corresponding "security" view that insure that any user can only access rows in that table from the same tenant., like so:

CREATE VIEW Secure_TABLE as
 Select * 
  From Physical_TABLE T
   Inner Join Tenant_Users U
    ON U.tenant_id = T.tenant_id
     And U.UserName = sUser_sName()

4) No application code, including client code, application stored procedures, views, etc., is permitted to directly access the physical tables.  All such data access is only permitted through the Security views.  (use database roles, schemas and permissions to implement this, do not rely on code).

If you notice, this approach insures that, except for the user Logon's, the application has no knowledge of the multi-tenancy, and no application changes should normally be necessary.  This means that you can move a tenants data into or out of this approach without any changes to most applications, other than changing the Server.Database address. (note that to be truly transparent you would have to remove the tenant_id from the output of the security views).

The tenant_id should be made the first field of the primary key and probably also the Clustered Index (if different).

Recently asked question on SQLServerCentral.com:

Well here's my question. In some of the inline sql they are using variables to create the table name.
The code is in vb.net
name = "joesshop"
Example: "Select * from data_" + name
So the end result would be "Select * from data_joesshop"

Is there anyway to put this in a stored proc?

Dynamic SQL is the standard solution for this kind of problem, but Injection is an overriding concern.

Here is how you can recode this simple example into a stored procedure that protects itself from SQL Injection:

Create proc spSelectFromDataTable(

        @SuffixName Nvarchar(255))

/*        Procedure to demonstrate how to safely incorporate client

 text parameters into a SQL command.

        Note that the key to this technique is to NEVER actually

EXECute any string that has client-supplied text.  Rather all

of the client text must be purified by replacing it with the known

valid names of the objects being referenced.  Then Dynamic

SQL commands can be safely constructed using ONLY our own text and

these idealized replacement values.

*/

 AS

BEGIN

 Declare @ActualTableName SYSNAME

 --Find the actual table name that matches the clients

 -- text parameter:

 Select @ActualTableName = TABLE_NAME

  From INFORMATION_SCHEMA.TABLES

  Where TABLE_SCHEMA = N'dbo'

   And TABLE_NAME = N'data_' + @SuffixName

   --Note that it is safe to use the clients parameter

   -- here because it is only being used as a data value,

   -- it is not being EXECuted.

 IF @ActualTableName IS NOT NULL

  BEGIN

        --Here is where we construct and execute the Dynamic SQL

        EXEC(N'Select * From '+@ActualTableName)

        --Note that this execution string contains no part of

        -- the clients text parameter, it has been completely

        -- replaced with the known, valid, actual table name.

  END

 ELSE

  BEGIN

        Declare @msg as NVarchar(MAX)

        Select @msg = @SuffixName+N' is an invalid data table name.'

        RAISERROR(@msg, 11, 1)

        Return

  END

END

Comments welcome.

I've been thinking about writing an article that would be titled "15 Ways to Leave Your Cusors".  Ideas:

1. Just put it in a Set, Brett  (change the declare cursor select statement  into a select)
2. Put it all together, Heather (later one; using women's names opens this  up a lot too)
3. Put it on the Stack, Jack (recursive CTE's)
4. Its your last resort Mort (or While/Kyle?)
5. Select into a variable, Aribel (simple pseudocursor)
6. Use an ordered update, Nate (complex psuedocursor)
7. Window on OVER, Grover (Row_Number(), etc.)
8. Add a WITH CUBE, Noob (Rollup and Cube)
9. Use a temproary table, Mabel
10. Just begin again, Man (last one; start over with just specs)
11. With OUTPUT (for loops with multiple outputs; no cute rhyme yet)
12. Make a big string, Bing (dynamic SQL)
13. bring it inline, Caroline
14. ...?

Other ideas: Joins, Exists, IN(), derived tables, Merge(?)

Well at Jack Corbett's suggestion I have tunrned on RSS feeds for the blog.  I am still not sure if it will work right, so feel free to let me knowof any problems...

I've been invovled in some recent discussion on Multi-Tenenacy databases and in particular, the Shared Schema implementation:

http://www.sqlservercentral.com/Forums/FindPost610335.aspx
http://www.sqlservercentral.com/Forums/Topic529170-361-1.aspx
http://www.sqlservercentral.com/Forums/Topic569567-149-1.aspx

Multi-Tenancy databases are databases with multiple customer organizations ("tenants") all running the same application and all using the same database server.  The three stadard approaches to this are:

1. Seperate Databases
2. Same Database, Seperate Schemas
3. Shared Schemas

In the last one, all of the tenants use the same tables, but a tenant_id is added to each table to distinguish them.  The is an MSDN article that explains this (http://msdn.microsoft.com/en-us/library/aa479086.aspx)

As it happens I designed and implemented a large Shared Schema Multi-Tenant DB several years ago, and  learned many insights from that.

Way back in March I answered question from two posters about the notorious Two-Hop Rule here: www.sqlservercentral.com/Forums/Topic471172-359-1.aspx#bm473248.  The thread has a good summary of the problem and cause by me, and an excellent post by Brian Kelley on how to use Kerberos to fix it.

A good article and monitoring script from Johan Bijnens (ALZDBA) at SQLServerCentral.com:

The Article: Adventures with Service Broker

And the monitoring script with just selects from every Service Broker DMV and catalog view: http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31867/

Jon Reade describes a little known microsoft upgrade utility (SCPTXFR.EXE) from SQL Server 2000 that can be used to script the objects in a SQL Server 2000 database.  Apparently it works for SQL Server 2005 also:  www.sqlservercentral.com/articles/Administering/howtoscheduleasqlserverdatabasecreationscript/1834/