Archive for the ‘Work’ Category
Binary encoded SQL injection
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¬es=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.