May 13, 2008 at 12:12 pm
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 :
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 + ']))+
'' '''
);
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.
May 13, 2008 at 12:42 pm
This may stop the particular attack but it won't stop all SQL injection attacks. "Blind injection" is actually a phrase used for discovery of a database by taking advantage of a SQL Injection vulnerability. Basically, the objects the login does have access to gets crawled piece by piece to find out what is available and what is not. And then the attack begins. So moving objects into different schema, trying to block access to system objects, those all fail at some point because there are some system objects which the login will have to have access to.
Really the only solution is to fix it at the application level.
K. Brian Kelley
@kbriankelley
May 13, 2008 at 1:15 pm
K. Brian Kelley (5/13/2008)
This may stop the particular attack but it won't stop all SQL injection attacks. "Blind injection" is actually a phrase used for discovery of a database by taking advantage of a SQL Injection vulnerability. Basically, the objects the login does have access to gets crawled piece by piece to find out what is available and what is not. And then the attack begins. So moving objects into different schema, trying to block access to system objects, those all fail at some point because there are some system objects which the login will have to have access to.Really the only solution is to fix it at the application level.
Hey, Thanks for the prompt rely.
Some of our databases have been prone to sql injection due to poor coding practises at the application level, we are working through some of the critical systems to secure them at the application level. But this is time consuming and just not an option for legacy systems.
Blind sql injection is quite rare luckily, but I am well aware it can be used to discover tables, even with generic error messages.
Perhaps the only option in the short term is take frequent backups and try the schema transfer and hope for the legacy systems will be put to rest...
PS: does the book you co-authored consider sql injection?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply