User Name:


User Email:




This information will only be saved for the purposes of communicating with those who have provided this information voluntarily regarding our services.We will never sell your name or email address to anyone.
© 2018 - First Crazy Developer (Abhishek Kumar)
  

crazydeveloper How to Find and Replace Values in All Tables and All Text Columns in SQL Server

Problem 1:

We have a tables (SQL Server) which references paths (with specific name/ value), but now the path is going to change. In the path column, we have many records and we need to change just a portion (name/ value) of the path, but not the entire path. And we need to change the same string to the new one, in every record.

How can we do this with a simple update?


Problem 2:

We have read about a SQL injection attack where text is inserted and this could be a good way to remove the offending text.



In above discussion we have two problem, second problem I faced in last few days when one of my client got this issues. I went through lots of articles and find this solution. I modified that as per our requirements.

Here I want to share the solution with my all friends.


Solution:

First step to resolve the above problems are need to find a certain string in any text data type such as char, nchar, nvarchar, varchar, text and ntext.

Second step to either replace that certain string or remove that, if that was SQL Inject.



Look at the following Script :


  1. SET NOCOUNT ON
  2. DECLARE @stringToFind VARCHAR(100)
  3. DECLARE @stringToReplace VARCHAR(100)
  4. DECLARE @schema sysname
  5. DECLARE @table sysname
  6. DECLARE @count INT
  7. DECLARE @sqlCommand VARCHAR(8000)
  8. DECLARE @where VARCHAR(8000)
  9. DECLARE @columnName sysname
  10. DECLARE @object_id INT
  11. SET @stringToFind = 'Abhishek Kumar'
  12. SET @stringToReplace = 'FirstCrazyDeveloper.com'
  13. DECLARE TAB_CURSOR CURSOR FOR
  14. SELECT B.NAME AS SCHEMANAME,
  15. A.NAME AS TABLENAME,
  16. A.OBJECT_ID
  17. FROM sys.objects A
  18. INNER JOIN sys.schemas B
  19. ON A.SCHEMA_ID = B.SCHEMA_ID
  20. WHERE TYPE = 'U'
  21. ORDER BY 1
  22. OPEN TAB_CURSOR
  23. FETCH NEXT FROM TAB_CURSOR
  24. INTO @schema,
  25. @table,
  26. @object_id
  27. WHILE @@FETCH_STATUS = 0
  28. BEGIN
  29. DECLARE COL_CURSOR CURSOR FOR
  30. SELECT A.NAME
  31. FROM sys.columns A
  32. INNER JOIN sys.types B
  33. ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID
  34. WHERE OBJECT_ID = @object_id
  35. AND IS_COMPUTED = 0
  36. AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext')
  37. OPEN COL_CURSOR
  38. FETCH NEXT FROM COL_CURSOR
  39. INTO @columnName
  40. WHILE @@FETCH_STATUS = 0
  41. BEGIN
  42. SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName
  43. + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),'''
  44. + @stringToFind + ''',''' + @stringToReplace + ''')'
  45. SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%'''
  46. EXEC( @sqlCommand + @where)
  47. SET @count = @@ROWCOUNT
  48. IF @count > 0
  49. BEGIN
  50. PRINT @sqlCommand + @where
  51. PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count)
  52. PRINT '----------------------------------------------------'
  53. END
  54. FETCH NEXT FROM COL_CURSOR
  55. INTO @columnName
  56. END
  57. CLOSE COL_CURSOR
  58. DEALLOCATE COL_CURSOR
  59. FETCH NEXT FROM TAB_CURSOR
  60. INTO @schema,
  61. @table,
  62. @object_id
  63. END
  64. CLOSE TAB_CURSOR
  65. DEALLOCATE TAB_CURSOR


We can see in the above Script, here we tried to find "Abhishek Kumar" and replace with "FirstCrazyDeveloper.com" in SQL Server. The above code traverse all table column and replace all columns having value of "Abhishek Kumar" with "FirstCrazyDeveloper.com" values in SQL Server.


After the updated the column, we are also displaying the message to shows the command that was run and how many rows were affected.


Look at the following Scripts:



  1. SET NOCOUNT ON
  2. DECLARE @schema sysname
  3. DECLARE @table sysname
  4. DECLARE @count INT
  5. DECLARE @sqlCommand VARCHAR(8000)
  6. DECLARE @where VARCHAR(8000)
  7. DECLARE @columnName sysname
  8. DECLARE @object_id INT
  9. DECLARE TAB_CURSOR CURSOR FOR
  10. SELECT B.NAME AS SCHEMANAME,
  11. A.NAME AS TABLENAME,
  12. A.OBJECT_ID
  13. FROM sys.objects A
  14. INNER JOIN sys.schemas B
  15. ON A.SCHEMA_ID = B.SCHEMA_ID
  16. WHERE TYPE = 'U'
  17. ORDER BY 1
  18. OPEN TAB_CURSOR
  19. FETCH NEXT FROM TAB_CURSOR
  20. INTO @schema,
  21. @table,
  22. @object_id
  23. WHILE @@FETCH_STATUS = 0
  24. BEGIN
  25. DECLARE COL_CURSOR CURSOR FOR
  26. SELECT A.NAME
  27. FROM sys.columns A
  28. INNER JOIN sys.types B
  29. ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID
  30. WHERE OBJECT_ID = @object_id
  31. AND IS_COMPUTED = 0
  32. AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext')
  33. OPEN COL_CURSOR
  34. FETCH NEXT FROM COL_CURSOR
  35. INTO @columnName
  36. WHILE @@FETCH_STATUS = 0
  37. BEGIN
  38. SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName
  39. + '] = LEFT([' + @columnName+ '], CHARINDEX(''</title>'', [' + @columnName+ ']) - 1)'
  40. SET @where = ' WHERE CHARINDEX(''</title>'', [' + @columnName+ ']) > 0 and [' + @columnName+ '] is not null'
  41. EXEC( @sqlCommand + @where)
  42. SET @count = @@ROWCOUNT
  43. IF @count > 0
  44. BEGIN
  45. PRINT @sqlCommand + @where
  46. PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count)
  47. PRINT '----------------------------------------------------'
  48. END
  49. FETCH NEXT FROM COL_CURSOR
  50. INTO @columnName
  51. END
  52. CLOSE COL_CURSOR
  53. DEALLOCATE COL_CURSOR
  54. FETCH NEXT FROM TAB_CURSOR
  55. INTO @schema,
  56. @table,
  57. @object_id
  58. END
  59. CLOSE TAB_CURSOR
  60. DEALLOCATE TAB_CURSOR


We can see in the above Script, here we tried to find "</title>" which are injected by some hackers as SQL Injection and remove the all characters after that in SQL Server. The above code traverse all table column and update all columns having value of "</title>" (remove injected strings).


After the updated the column, we are also displaying the message to shows the command that was run and how many rows were affected.

If we did not use the CONVERT function we would have to use these two functions TEXTPTR and UPDATETEXT to change the data in the text and ntext columns.  This is a lot more work and therefore the approach we used is much simpler.  The downside is that this only works for SQL 2005 and later where the nvarhcar(max) datatype is supported.  In addition, this is another reason that Microsoft suggests moving away from text and ntext to varchar(max) and nvarchar(max).

One thing to note is that if your replacement text is longer than the text you are searching for you may run into issues of truncating data which is not handled in this script.

Important Point:

  • Depending on the size of our database this could take some time to run, so be careful when we run this since it will be hitting every table and every column that has one of these datatypes: char, nchar, nvarchar, varchar, text and ntext.
  • Give it a try and see how it works.  We can use the BEGIN TRAN and ROLLBACK statements to see what will be updated and than rollback the transactions.  Just be careful on large databases and production databases since this will hold locks on the UPDATEs until the rollback statement is issued.




crazydeveloper Home Page 08 August 2015

Become a Fan