April 7, 2008 at 10:46 am
We've recently found that 2 tables (at current count) have had data inserted into existing populated fields adding themselves to the data that was already there.
An example of this being;
"Existing Data in Database Field"
The site in the url is nmidahena, which I've removed to ensure no one clicks the above link and blames me for any problems!
Having Googled it, there's some limited information that seems to indicate this issue affects asp sites only (our site is half asp, half asp.net) and that it is a problem affecting a number of businesses since 3rd April.
Worryingly, one of the 2 tables it's infected is a table that is ONLY populated by a trigger and it has infected existing rows that were fine on Friday.
My question is, has anyone come across this and also are there any alternatives to using Full Text Search to search an entire database or table by table for the occurence of any text that has the above mentioned url in? From researching the problem it only seems to be affecting nvarchar fields.
Any help on this will be much appreciated and being I need your help I'm happy to answer any questions!! :crazy:
April 7, 2008 at 11:38 am
We too had a couple of tables that ended up updated with similar text. It seems to target varchar and nvarchar data types. We used the following query and you could put that in a for each dB script if you want to find all the tables affected. We have quite a few databases so I wanted to do 1 database at a time. This only outputs the queries you would run in the database. Good luck fixing the data, I know we sure are having fun with this issue. We too had some old ASP pages out on our site that were vulnerable to SQL injection. Of course this only will work on SQL 2005, but you can modify to work with SQL 2000 and below.
--Returns all tables that use datatype varchar or nvarchar with the column like <script you can replace the text with anything you want to search for.
SELECT 'select count(*) From [' + table_name + '] where [' + column_name + '] like ''%<script%''' FROM information_schema.columns c
INNER JOIN sys.objects o ON o.NAME = c.table_name
WHERE data_type LIKE '%varchar%'
AND TYPE = 'u'
April 7, 2008 at 11:54 am
Maybe using a tool like SQL Data Compare? It's effectively doing the same thing, but it may help you find/display the problem children a bit easier.
On a related note, anyone fixing the application which permitted the SQL injection attack in the first place?
K. Brian Kelley
@kbriankelley
April 8, 2008 at 1:47 am
Hi Jen - many thanks for the reply and also the code to pull off a list of all of the nvarchar and varchar tables in our database, very useful and time saving! 😀
We spent a good part of last night restoring the affected tables from a clean backup (luckily all of the infected tables were low usage tables that had no changes since backup) and changing the code on the web page we thought was susceptible to the attacks. Having come into work this morning we find that the tables are once again infected, so back to the drawing board!
On a side note, the infected tables are the top 6 that are returned from the statement we produced running the code you provided and the 7th table is an archived data table with millions of rows that would probably cause whatever's making the changes to timeout or the transaction to be killed before it has a chance to infect.
If we make any developments that are worth sharing I'll be sure to let you and the rest of the community know.
April 8, 2008 at 5:45 am
Any idea how data got into a table which you say uses only a trigger to insert data.
Any success or progress that you wish to share? thanks
April 8, 2008 at 6:02 am
It looks as though a page on our asp site had a field that was susceptible to sql injection and from this they were able to pass a query that pulled all of the columns in our database that had varchar and nvarchar fields in (no doubt similar to the sql code posted by Jen). One of these tables was the table that was (or should) be only populated by a trigger. Updates were then made to the existing entries in this table.
April 8, 2008 at 7:35 pm
Is the account which connects to SQL Server running as the database owner? If not, is INSERT, UPDATE, DELETE granted against those tables? If so, is there a need to do it that way? There may not be a lot you can do immediately on the app side, but you can certainly tighten things down on the SQL Server side (hopefully).
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply