all feilds in all tables - SQL Server Forums

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)

all feilds in all tables

Author

Topic

svjith
Starting Member

5 Posts

Posted - 2009-07-26 : 13:33:08

How can we delete,

a perticular string from from all feilds(text/ntext/varchar ) in all tables in a database(sqlserver2005)
:(
my site was sql injected and i have <script>www.badsite.com</script>
in
all feilds(text/ntext/varchar ) in all tables
:(

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-26 : 13:44:41

I am afraid you have to script the updates for each table.
Maybe it is possible to code that for the whole database but I think that takes more time till it is correct coded so you would rather script the updates one by one and keep the scripts for the next injection.

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-26 : 18:23:54

you can find 'em like this (then you can use a similar script to update/delete):


Create Table #t (col sysname, tbl sysname)

Declare @col sysname
Declare @tbl sysname

Declare c Cursor
Read_Only
FOR
	select 	c.name, t.name
	FROM 	sys.columns c
	JOIN 	sys.tables t
	On	c.object_id = t.object_id

Open c
Fetch Next From c Into @col, @tbl
While @@Fetch_Status = 0
BEGIN
	Insert	#t
	Exec	('SELECT ''' + @col + ''',''' + @tbl + ''' FROM [' + @tbl + '] WHERE [' + @col + '] LIKE ''%badsite.com%''')
	Fetch Next From c Into @col, @tbl
END

CLOSE c
Deallocate c

SELECT * FROM #t
DROP TABLE #t

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-26 : 18:28:13

this modification to above will script the deletes for you. Set your output to text in SSMS:

Create Table #t (col sysname, tbl sysname)

Declare @col sysname
Declare @tbl sysname

Declare c Cursor
Read_Only
FOR
	select 	c.name, t.name
	FROM 	sys.columns c
	JOIN 	sys.tables t
	On	c.object_id = t.object_id

Open c
Fetch Next From c Into @col, @tbl
While @@Fetch_Status = 0
BEGIN
	Insert	#t
	Exec	('SELECT ''' + @col + ''',''' + @tbl + ''' FROM [' + @tbl + '] WHERE [' + @col + '] LIKE ''%badsite.com%''')
	Fetch Next From c Into @col, @tbl
END

CLOSE c
Deallocate c

SELECT 'DELETE ' + tbl + ' WHERE ' + col + ' like ''%badsite.com%''' from #t
DROP TABLE #t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources