Is there a better way to do this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Is there a better way to do this?

Author

Topic

kalwork
Starting Member

5 Posts

Posted - 2008-07-17 : 08:16:19

Hi,

I have table fields that have been hacked so that fields have <script... appended to them.

I wrote the below code to get rid of it - but can some clever sql guru combine the select from sysobjects... statement with the dynamic update statement - or is this a good way to do it?

Thanks

Kal

-- 2008 07 16
-- script to remove appended data from fields
-- Note: only works with appended data

set nocount on

-- the malicious tag -
declare @malicious varchar(10)
set @malicious = '<script'

-- the while counter
declare @cnt int

-- maximum text type rows in all the tables
declare @maxRows int

-- the current table name being worked on
declare @tn varchar(50)

-- the current column being worked on
declare @c varchar(50)

-- holds the dynamic sql
declare @sql varchar(1000)

-- holder for working data
create table #TableHolder
(
ID int identity(1,1),
TableName varchar(50) not null,
ColumnName varchar(100) not null,
CleanUpCode varchar(8000) null
)

-- gather the list of tables and columns
insert into #TableHolder
select [name] as TableName, Column_Name as ColumnName, null from sysobjects, INFORMATION_SCHEMA.COLUMNS
where xtype='u'
and [name]=Table_Name
and (
Data_Type='char' or
Data_Type='nchar' or
Data_Type='ntext' or
Data_Type='text' or
Data_Type='varchar' or
Data_Type='nvarchar' or
Data_Type='xml')
and [name] not like '%sys%'
order by name

-- remove any tables you dont want to search - incase you have any script tags in them
delete #TableHolder where TableName in ('')

-- get the max rowcount
select @maxRows = count(*) from #TableHolder
set @cnt = 1

-- go through each row in the holder
while @cnt < @maxRows + 1
begin
select @tn = TableName, @c = ColumnName from #TableHolder
where ID = @cnt

-- the command to remove any malicious code
set @sql = 'update ' + @tn + ' set ' + @c + ' = substring(' + @c + ',0,charindex(''' + @malicious + ''',' + @c + ')) '
set @sql = @sql + 'where ' + @c + ' like ''%' + @malicious + '%'''

-- keep track of what we did
update #TableHolder set CleanUpCode = @sql where ID = @cnt

-- EXTERMINATE!
exec (@sql)
set @cnt = @cnt + 1
end

-- show what we have done
select * from #TableHolder

drop table #TableHolder

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-17 : 14:46:26

Personally, I would suggest you find a backup from before the attack and restore it. The SQL injection attack may have done more than just add data into some fields.

Next, very important thing, find the source. One or more of the apps that connect to that DB is vulnerable to SQL injection. You need to get the app(s) fixed or this will just happen again.
Important things - all database access should be done via properly parametrised stored procedures. The application users must have execute rights on the stored procs only, no access to the base tables

--
Gail Shaw
SQL Server MVP

kalwork
Starting Member

5 Posts

Posted - 2008-07-18 : 08:59:38

Hi Gail,

You are right - that is being done, but in the mean time this cleans up appended hacks.

Also can I just make a change...

set @sql = 'update [' + @tn + '] set [' + @c + '] = substring([' + @c + '],0,charindex(''' + @malicious + ''',[' + @c + '])) '
set @sql = @sql + 'where [' + @c + '] like ''%' + @malicious + '%'''

I have just added [] to the table and column names

Thanks

Kal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources