Friday, November 26, 2010

Remove Scripts from database/ Remove Sql Injections


USE [DBFORUMSNEW]
GO
/****** Object: StoredProcedure [dbo].[sp_sqlinjection] Script Date: 11/26/2010 19:46:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_sqlinjection]
@DBName varchar(100)
as
Begin
declare @DB_Name varchar(40), @Table_Name varchar(40),@Column_Name varchar(40)
declare @sql1 varchar(1000), @sql2 varchar(1000)
Set @DB_Name = @DBName
begin
exec ('use ' + @Db_Name)
DECLARE Table_Cursor CURSOR FOR SELECT name from sysobjects where type= 'U'
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor into @Table_Name
WHILE @@FETCH_STATUS = 0
begin
declare Column_Cursor CURSOR for select name from syscolumns where id = object_id(@Table_Name) and xtype in (239,175,231,167) and length > 20
open Column_Cursor
Fetch next from Column_Cursor into @Column_Name
WHILE @@FETCH_STATUS = 0
begin
set @sql1='if exists (SELECT 1 FROM ['+@Table_Name+'] where ['+@Column_Name+'] like ''%< script%'')
begin
update ['+@Table_Name+'] set ['+@Column_Name+'] = replace(['+@Column_Name+'],
substring(['+@Column_Name+'],charindex(''< script'',['+@Column_Name+']),
case when charindex(''< /script'',['+@Column_Name+']) >charindex(''< script'',['+@Column_Name+']) then
charindex(''< /script'',['+@Column_Name+'])-charindex(''< script'',['+@Column_Name+'])+9
else
len (['+@Column_Name+'])
end ),
'''') where ['+@Column_Name+'] like ''%< script%''
end '
exec (@sql1)
set @sql2='if exists (SELECT 1 FROM ['+@Table_Name+'] where ['+@Column_Name+'] like ''%< title%'')
begin
update ['+@Table_Name+'] set ['+@Column_Name+'] = replace(['+@Column_Name+'],
substring(['+@Column_Name+'],charindex(''< title'',['+@Column_Name+']),
case when charindex(''< /title'',['+@Column_Name+']) >charindex(''< title'',['+@Column_Name+']) then
charindex(''< /title'',['+@Column_Name+'])-charindex(''< title'',['+@Column_Name+'])+9
else
len (['+@Column_Name+'])
end ),
'''') where ['+@Column_Name+'] like ''%< title%''
end '
exec (@sql2)
FETCH NEXT FROM Column_Cursor into @Column_Name
end
close Column_Cursor
DEALLOCATE Column_Cursor
FETCH NEXT FROM Table_Cursor into @Table_Name
end
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
--FETCH NEXT FROM DB_Cursor into @DB_Name
end
end







Solution By: Rajesh Rolen

Share This!


No comments:

Powered By Blogger · Designed By Seo Blogger Templates