SQL Injection « It's Not A Bug, It's A Feature

10Jun/100

Fixing SQL Injection Attacks with a Cursor

Recently I had to fix a database that had been attacked with a SQL injection attack, surprisingly enough I was able to find the hole they attacked the site through and patch it and I was also was able to translate the attack used to infect the database and then I used the very same malicious code to attack the system to fix it.

The original attack came in on a search page with a sting like

http://www.theattckedwebsite.com/list.asp?s=dummy serch term';declare @s vArChAr(8000); sEt @s=0x546869732069732074686520542D53514C2061747461636B; eXEc(@s)--

and unfortunately this managed to get by the checks that were in place at the time and infect the database. the VARCHAR string is actually a SQL statement converted into a VARBINARY string. The malicious SQL statement is easily converted back to a human readable statement done by executing the following T-SQL statement.

1
2
3

DECLARE @s VARCHAR(8000)
SET @s=0x546869732069732074686520542D53514C2061747461636B
SELECT CAST(@s AS VARCHAR)

This SQL cursor will find the @StringToReplace in any User table with a field type of NVARCHAR, VARCHAR, TEXT, and NTEXT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

DECLARE @StringToReplace VARCHAR(100)
SET @StringToReplace = '<script src=http://www.attackerwebsite.com/attack.js></script>'

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 + '] = REPLACE(cast([' + @c + '] as varchar(max)), ' + @StringToReplace + ', '''')')
PRINT 'UPDATE [' + @t + '] SET [' + @c + '] = REPLACE(cast[' + @c + '] as varchar(max)), ' + @StringToReplace + ', '''')'
FETCH NEXT FROM tAbLe_cursoR INTO @t, @c
END
CLOSE table_cursor
DEALLOCATE table_cursor

I my opinion people who write this stuff to begin with should just be shot in the head and get it over with. but it does keep me employed, kind of a necessary evil. so hopefully this will help someone someday when you least expect it.

The amount of these types of attacks have grown over the years proving it is our job to be more diligent in our coding standards to try and keep from leaving holes in our code for the types of attacks to infect our databases.

Tagged as: Cursor, Hacking, SQL Injection, T-SQL No Comments

It's Not A Bug, It's A Feature Just another Developer weblog

Fixing SQL Injection Attacks with a Cursor

Sponsors

Categories

Site Links

Blogroll

The Cloud