Account Home | Help | Blog | Contact us | Log Out

sql replace in text column sql2000

Posted By: siteadmin on 08/06/2011 11:33:00

If you've been attacked by some nasty SQL injection, then, yes, you're going to have to fix the code.  But what about clean up?

If you've got a backup then that's probably best to go back to that.

But if its an old backup, or for some other reason e.g. the SQL injection hasn't actually overwritten anything, just appended junk then maybe you can just clean it up.

Narayana Vyas Kondreddi gives a super way to search all the tables in SQL at Site: http://vyaskn.tripod.com

I've edited it slightly to generate further SQL statements to actually mop up the junk it inserts.  If you've got text columns over 8K long in SQL 2000 then you're out of luck here too as it has to CAST for the replace statement to work.

Simply run ReplaceALLtables 'http://www.kbytes.co.uk

 CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

 SET NOCOUNT ON

 DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SearchStr3 nvarchar(110)
 SET  @TableName = ''
 SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 SET @SearchStr3 = QUOTENAME('' + @SearchStr + '','''')

 WHILE @TableName IS NOT NULL
 BEGIN
  SET @ColumnName = ''
  SET @TableName =
  (
   SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
   FROM  INFORMATION_SCHEMA.TABLES
   WHERE   TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(
      OBJECT_ID(
       QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
        ), 'IsMSShipped'
             ) = 0
  )

  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  BEGIN
   SET @ColumnName =
   (
    SELECT MIN(QUOTENAME(COLUMN_NAME))
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA = PARSENAME(@TableName, 2)
     AND TABLE_NAME = PARSENAME(@TableName, 1)
     AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar','text')
     AND QUOTENAME(COLUMN_NAME) > @ColumnName
   )
 
   IF @ColumnName IS NOT NULL
   BEGIN
    INSERT INTO #Results
    EXEC
    (
     'SELECT ''' + @TableName + '.' + @ColumnName + ''', ''UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = REPLACE(cast(' + @ColumnName + ' as varchar(8000)),'''+ @SearchStr3+''','''''''') ''
     FROM ' + @TableName + ' (NOLOCK) ' +
     ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    )
   END
  END 
 END

 SELECT distinct(ColumnName), ColumnValue FROM #Results
END
GO