Advanced Search

Clean Injection from Database Tables

/ Published in: SQL
Save to your folder(s)
You can use this SP like that;
Exec CleanInjection 'INFECTED WORD','CLEAN WORD'
(in use : Exec CleanInjection '','')

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. CREATE PROC CleanInjection
  2. (
  3. 	@SearchStr nvarchar(100),
  4. 	@ReplaceStr nvarchar(100)
  5. )
  6. AS
  7. BEGIN
  8.  
  9. 	-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
  10. 	-- Purpose: To search all columns of all tables for a given search string and replace it with another string
  11. 	-- Written by: Narayana Vyas Kondreddi
  12. 	-- Site: http://vyaskn.tripod.com
  13. 	-- Tested on: SQL Server 7.0 and SQL Server 2000
  14. 	-- Date modified: 2nd November 2002 13:50 GMT
  15.  
  16. 	SET NOCOUNT ON
  17.  
  18. 	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR INT
  19. 	SET  @TableName = ''
  20. 	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
  21. 	SET @RCTR = 0
  22.  
  23. 	WHILE @TableName IS NOT NULL
  24. 	BEGIN
  25. 		SET @ColumnName = ''
  26. 		SET @TableName = 
  27. 		(
  28. 			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
  29. 			FROM 	INFORMATION_SCHEMA.TABLES
  30. 			WHERE 		TABLE_TYPE = 'BASE TABLE'
  31. 				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
  32. 				AND	OBJECTPROPERTY(
  33. 						OBJECT_ID(
  34. 							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
  35. 							 ), 'IsMSShipped'
  36. 						       ) = 0
  37. 		)
  38.  
  39. 		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  40. 		BEGIN
  41. 			SET @ColumnName =
  42. 			(
  43. 				SELECT MIN(QUOTENAME(COLUMN_NAME))
  44. 				FROM 	INFORMATION_SCHEMA.COLUMNS
  45. 				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
  46. 					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
  47. 					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
  48. 					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
  49. 			)
  50.  
  51. 			IF @ColumnName IS NOT NULL
  52. 			BEGIN
  53. 				SET @SQL=	'UPDATE ' + @TableName + 
  54. 						' SET ' + @ColumnName 
  55. 						+ ' =  REPLACE(' + @ColumnName + ', ' 
  56. 						+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') + 
  57. 						') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
  58. 				EXEC (@SQL)
  59. 				SET @RCTR = @RCTR + @@ROWCOUNT
  60. 			END
  61. 		END	
  62. 	END
  63.  
  64. 	SELECT 'Replaced ' + CAST(@RCTR AS VARCHAR) + ' occurence(s)' AS 'Outcome'
  65. END

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.