July 1, 2008 at 10:12 am
Hey guys! Hopefully you can help me out... I am trying to cleanup a database that was compromised and suffered from a SQL injection attack. First, I would like to know if there is a way to search all of the columns, in all of the tables of a database for specific text. Beyond that, I have been cleaning up the database using a simple replace query:
update TABLE
set COLUMN = REPLACE(COLUMN, ' ','')
Is there a way to run a massive replace query that will replace that text in all columns of a table/all tables in a database in one sqift code? Thanks guys...
July 1, 2008 at 10:49 am
Short answer, no.
You can check sp_msforeachtable, but that doesn't cover columns.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 1, 2008 at 11:01 am
In the master database. Look at sp_msforeachtable
for an explanation of what and how it works try
http://www.databasejournal.com/features/mssql/article.php/3441031
July 5, 2008 at 2:04 am
Hi,
Here is a little something I often use to perform a table wide replace.
You could quite easily extend this for all tables within a database and additional data types should you wish.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create procedure [dbo].[tableReplace]
@schemaNamevarchar(100),
@tableNamevarchar(100),
@searchExpression varchar(100),
@replaceExpressionvarchar(100)
as
set nocount on
--Delcare the variables for use with this procedure
declare @SQLTextvarchar(8000)
declare @ColumnName varchar(100)
--Create a cursor to cycle through all of the columns specified in the user supplied table.
declarecXursor cursor for
selectquotename(column_name)
frominformation_schema.columns
wheretable_name=@tableName and data_type in('varchar','nvarchar')
orderby ordinal_position
opencXursor
fetchnext
fromcXursor
into@ColumnName
--Loop through the following exectuion phase while a "next" cursor result is available.
while @@fetch_status=0
begin
--Creat the SQL query to apply the update statement to the current column
set@SQLText=' update ' + @schemaName + '.' + @tableName +
' set ' + @ColumnName + ' = replace( ' + @ColumnName + ' , ' +
quotename(@searchExpression,'''') + ' ,' + quotename(@replaceExpression,'''') + ') '
--Execute the generated query.
--print @SQLText
exec (@SQLText)
--Inform the user of progress.
print 'Column ' + @columnName + ' updated sucessfully.'
fetchnext
fromcXursor
into@ColumnName
end
--Free up the resource taken by the cursor.
closecXursor
deallocate cXursor
print 'Execution Complete'
RETURN(0)
July 5, 2008 at 6:55 am
To assist you in expanding BigJohn's procedure the following will give you the name of each table, the columns which are character type, perhaps you can merge these 2 to come up with an acceptable solution, of course you could also add/delet to/from the WHERE clause to further filter which columns are selected for updating. (this will get you around using sp_MsForEachTable)
SELECT
TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
, COLUMN_DEFAULT
, IS_NULLABLE
, DATA_TYPE
FROM
INFORMATION_SCHEMA.Columns
WHERE
TABLE_NAME NOT LIKE ('dt%') AND TABLE_NAME NOT LIKE ('sys%') AND DATA_TYPE LIKE '%char%'
ORDER BY
TABLE_NAME, ORDINAL_POSITION
July 5, 2008 at 10:33 am
Do you perhaps have a backup from before the attack hit? Restoring is probably the fastest way to clean up. Bear in mind, the attack may have done more than just add a certain text pattern into the tables.
If you can't restore over the database, you may be able to restore alongside and use something like RedGate's SQL Data Compare to generate the scripts to fix it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2008 at 5:37 pm
I found some good scripts to use to cleanup my database at portablegeek.com (not free, but worth every penny because they came with a step by step how to). Their scripts not only cleaned varchar/char fields, but also text fields, and ran a few searches that also helped me find what fields were probably truncated, as well as search for a few other things I hadn't even thought to look for.
Slick scripts!
Just be sure you fix your site after cleaning up your database, or you will likely be back in the same situation!
~ Rich
July 7, 2008 at 12:40 am
SQL injection attack can do anything. If it inserted blanks to columns, well, it looks too harmless. However, removing all blanks from all text columns would most probably do even more damage.
Your safest path would be to restore from backup, if you have one. If not, decompile the log, check what was the injected code and revert that.
July 7, 2008 at 10:03 am
Thanks for the feedback guys. I was able to write a simple SP/cursor that would write the infected tables/columns in a specific database to a table that I created (temporarily). We just truncated that table, and re-ran the call in order to check the status as far as how many were infected. It was a lengthy cleanup but I think we got it. Our programmer had a bit of code on a few websites tied to a hosted SQL server (luckily it was not our sql server it was tied to) involving direct selects rather then called stored procedures. An update to the code fixed the vulnerabilities. In case anyone else has any of these issues with the SQL injections, the HP tool Scrawlr was wonderful and helping isolate the affected pages. Again, thanks for the feedback!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply