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 '','')


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.