How to remove SQL Server code injections in a hurry?

So you suddenly find that your web server has been compromised. How? Who knows. In my case we had an ASP.NET website which used the default settings for ViewState (i.e. sent to the client as a hidden form field, expecting the client to return it unaltered when the form is submitted). Of course, the default settings (recommended by Microsoft) leaves IIS wide open to an exploit called SQL Injection. Some crafty buggers discovered how to do this and they've been crawling the web looking for ASPX pages and throwing all sorts of nasty stuff into the ViewState fields until they hit pay-dirt...

So, what's the harm? This virus discovers the names of all the text columns in all your tables in all your database(s) to which the website has access, then it appends a string like <script src="http://www.doubleclickr.ru/index.js"></script> to all values in that column. Your website may well return some of these text fields back to the client as HTML to be displayed, and that's when the problem starts for the client. The client's browser interprets this HTML coming from your site as a legitimate request to fetch a mash-up script from DoubleClick (or whatever), and that script then tries to turn the client's PC into a zombie that obeys the bidding of the Russian Mafia (or whoever controls the virus). Will the client blame you? For sure they will, because you are supposed to ensure your server is virus-free, right! Well, with this kind of attack, your server is not itself running any virus code, and therefore no anti-virus program running on your server will pick up this 'infection' in the data. Tricky one.

So here is a little SQL script to create a procedure that will remove such a string from every text field in every table of the current database. This script employs much the same tricks to discover all the text fields, as I'm sure the virus must be employing..

   1:  Alter procedure RemoveSqlInjections

   2:  (

   3:      @remove nVarChar(4000) = '<script src=http://www.doubleclickr.ru/index.js></script>'

   4:  )

   5:  as

   6:   

   7:  /*

   8:   

   9:  Inject one test...

  10:  -------------------

  11:   

  12:  Update    users 

  13:  set        messageText = messageText + @remove 

  14:  where    userid = 

  15:  (

  16:      Select top 1    userId 

  17:      from            users 

  18:      where            messageText is not null

  19:  )

  20:   

  21:  -------------------

  22:  */

  23:   

  24:  Declare @sql nVarChar(max), 

  25:          @table nVarChar(64), 

  26:          @column nVarChar(64), 

  27:          @value nVarChar(128),

  28:          @count int

  29:   

  30:  Set noCount on

  31:   

  32:  Declare injectionRemover cursor for

  33:  Select    [table], 

  34:          [column], 

  35:          case when [convert] is not null

  36:              then 'convert(' + [convert] + ', ' + [column] + ')'

  37:              else [column]

  38:          end as [value]

  39:  from

  40:  (

  41:      Select         '[' + a.name + ']' as 'table', 

  42:                  '[' + b.name + ']' as 'column', 

  43:                  case 

  44:                      when b.xType in (98,99) then 'nVarChar(4000)'

  45:                      when b.xType = 35 then 'varChar(8000)'

  46:                  end as 'convert'

  47:      from        sysobjects a

  48:      inner join    syscolumns b

  49:          on        a.id = b.id

  50:      where        a.xtype='U' 

  51:          and        b.xtype in (1,6,7,35,98,99,167,175,231,239)

  52:  ) c

  53:  order by 1

  54:   

  55:  Open injectionRemover

  56:   

  57:  While 1=1 begin

  58:      Fetch next from injectionRemover into @table, @column, @value

  59:      If @@fetch_status <> 0 break

  60:   

  61:      Set @sql =    'Update ' + @table + 

  62:                  ' set ' +  @column + 

  63:                  ' = replace(' + @value + ', ''' + @remove + ''', '''') where ' +

  64:                  @value + ' like ''%<script%'''

  65:   

  66:      Exec(@sql)

  67:      Set @count = @@rowCount

  68:      If @count > 0 begin

  69:          Set @sql = 'Replaced ' + convert(nVarChar(10), @count) + ' occurence(s) in ' + @table + '.' + @column

  70:          Print @sql

  71:      end

  72:  end

  73:   

  74:  Close injectionRemover

  75:  Deallocate injectionRemover

After creating the procedure just run it. With no parameters it will remove the the same nasty that infected my server, but you can supply a single parameter like so:

Exec RemoveSqlInjections '<script src="some other nasty thing"></script>'

It is not case sensitive (by default, but if your server's collation is binary or some other case-sensitive variant, then watch out for case variations).