Update every column in every table

Question

My database has recently been hacked by SQL injection leaving tags throughout data in all columns in all tables in my database. Is there a quick way of running a REPLACE UPDATE on all tables? Something like:

UPDATE [all tables] SET [all columns]=REPLACE([all columns], '<script>....</script>', '')

Answer 1

One way to do it is by going through metadata. The sys.tables table in your database can give you a list of all your tables:

select *
from sys.tables

Then you can use the sys.columns table to obtain the columns in those tables. And then you would need to generate SQL to make the updates in a loop, for example.

Have a look here for general info on querying the database schema in SQL Server 2005: http://msdn.microsoft.com/en-us/library/ms345522(v=SQL.90).aspx

Answer 2

No, there's nothing directly in SQL Server that would allow you to do this.

However, indirectly - there is a way: you could inspect the system catalog views and enumerate all the columns - also limiting those to just (N)VARCHAR/(N)CHAR columns - and you could have your first T-SQL statement generate a list of UPDATE statement from those system catalog views.

With that list of T-SQL statements, you could then run the actual command to clean up your database - this will touch on all the character-oriented columns in all your tables in your database.

SELECT
    'UPDATE ' + sch.name + '.' + t.name + ' SET ' + c.name + ' = REPLACE("<script>....</script>", "")'
FROM 
    sys.columns c
INNER JOIN 
    sys.tables t ON c.object_id = t.object_id
INNER JOIN 
    sys.schemas sch ON t.schema_id = sch.schema_id  
WHERE   
    t.is_ms_shipped = 0
    AND c.user_type_id IN (167, 175, 231, 239)

This will generate a list of UPDATE statements as result, and you can then take those statements, copy&paste them into a second SSMS window, and run them to update your columns.

UPDATE dbo.T_ServicePSSAAudit SET RunType = REPLACE(RunType, '<script>....</script>', '')
UPDATE dbo.T_PromoStatus SET StatusText = REPLACE(StatusText, '<script>....</script>', '')
UPDATE dbo.T_PromoStatus SET StatusCode = REPLACE(StatusCode, '<script>....</script>', '')
UPDATE dbo.T_DSLSpeed SET CaptionCode = REPLACE(CaptionCode, '<script>....</script>', '')
UPDATE dbo.T_DVPTransfer SET RequestType = REPLACE(RequestType, '<script>....</script>', '')
UPDATE dbo.T_Promo SET Description = REPLACE(Description, '<script>....</script>', '')

You can find all the defined system types in the sys.types catalog view - I just grabbed the user_type_id values for char, nchar, varchar and nvarchar here in this sample.