更新每个表中的每一列

发布于 2024-09-27 00:05:38 字数 248 浏览 3 评论 0原文

我的数据库最近被 SQL 注入攻击，在我的数据库中所有表的所有列中的数据中留下了标签。有没有一种快速方法可以在所有表上运行 REPLACE UPDATE ？像这样的东西：

UPDATE [all tables] SET [all columns]=REPLACE([all columns], '<script>....</script>', '')

原文

My database has recently been hacked by SQL injection leaving tags throughout data in all columns in all tables in my database. Is there a quick way of running a REPLACE UPDATE on all tables? Something like:

UPDATE [all tables] SET [all columns]=REPLACE([all columns], '<script>....</script>', '')

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

高跟鞋的旋律 2024-10-04 00:05:38

不，SQL Server 中没有任何东西可以直接让您执行此操作。

然而，间接地 - 有一种方法：您可以检查系统目录视图并枚举所有列 - 还将这些列限制为 (N)VARCHAR/(N)CHAR 列 - 并且您可以让您的第一个 T-SQL 语句生成一个这些系统目录视图中的 UPDATE 语句的列表。

有了该 T-SQL 语句列表，您就可以运行实际的命令来清理数据库 - 这将涉及数据库中所有表中的所有面向字符的列。

SELECT
    'UPDATE ' + sch.name + '.' + t.name + ' SET ' + c.name + ' = REPLACE("<script>....</script>", "")'
FROM 
    sys.columns c
INNER JOIN 
    sys.tables t ON c.object_id = t.object_id
INNER JOIN 
    sys.schemas sch ON t.schema_id = sch.schema_id  
WHERE   
    t.is_ms_shipped = 0
    AND c.user_type_id IN (167, 175, 231, 239)

这将生成一个 UPDATE 语句列表作为结果，然后您可以将这些语句复制并粘贴到第二个 SSMS 窗口中，然后运行它们来更新您的列。

UPDATE dbo.T_ServicePSSAAudit SET RunType = REPLACE(RunType, '<script>....</script>', '')
UPDATE dbo.T_PromoStatus SET StatusText = REPLACE(StatusText, '<script>....</script>', '')
UPDATE dbo.T_PromoStatus SET StatusCode = REPLACE(StatusCode, '<script>....</script>', '')
UPDATE dbo.T_DSLSpeed SET CaptionCode = REPLACE(CaptionCode, '<script>....</script>', '')
UPDATE dbo.T_DVPTransfer SET RequestType = REPLACE(RequestType, '<script>....</script>', '')
UPDATE dbo.T_Promo SET Description = REPLACE(Description, '<script>....</script>', '')

您可以在 sys.types 目录视图中找到所有已定义的系统类型 - 我刚刚获取了 char、nchar 的 user_type_id 值本示例中的、varchar 和 nvarchar。

No, there's nothing directly in SQL Server that would allow you to do this.

However, indirectly - there is a way: you could inspect the system catalog views and enumerate all the columns - also limiting those to just (N)VARCHAR/(N)CHAR columns - and you could have your first T-SQL statement generate a list of UPDATE statement from those system catalog views.

With that list of T-SQL statements, you could then run the actual command to clean up your database - this will touch on all the character-oriented columns in all your tables in your database.

SELECT
    'UPDATE ' + sch.name + '.' + t.name + ' SET ' + c.name + ' = REPLACE("<script>....</script>", "")'
FROM 
    sys.columns c
INNER JOIN 
    sys.tables t ON c.object_id = t.object_id
INNER JOIN 
    sys.schemas sch ON t.schema_id = sch.schema_id  
WHERE   
    t.is_ms_shipped = 0
    AND c.user_type_id IN (167, 175, 231, 239)

This will generate a list of UPDATE statements as result, and you can then take those statements, copy&paste them into a second SSMS window, and run them to update your columns.

UPDATE dbo.T_ServicePSSAAudit SET RunType = REPLACE(RunType, '<script>....</script>', '')
UPDATE dbo.T_PromoStatus SET StatusText = REPLACE(StatusText, '<script>....</script>', '')
UPDATE dbo.T_PromoStatus SET StatusCode = REPLACE(StatusCode, '<script>....</script>', '')
UPDATE dbo.T_DSLSpeed SET CaptionCode = REPLACE(CaptionCode, '<script>....</script>', '')
UPDATE dbo.T_DVPTransfer SET RequestType = REPLACE(RequestType, '<script>....</script>', '')
UPDATE dbo.T_Promo SET Description = REPLACE(Description, '<script>....</script>', '')

You can find all the defined system types in the sys.types catalog view - I just grabbed the user_type_id values for char, nchar, varchar and nvarchar here in this sample.

回复收藏 0 原文

锦欢 2024-10-04 00:05:38

一种方法是查看元数据。数据库中的 sys.tables 表可以为您提供所有表的列表：

select *
from sys.tables

然后您可以使用 sys.columns 表获取这些表中的列。例如，然后您需要生成 SQL 来循环更新。

请在此处查看有关在 SQL Server 2005 中查询数据库架构的一般信息：http://msdn.microsoft.com/en-us/library/ms345522(v=SQL.90).aspx

One way to do it is by going through metadata. The sys.tables table in your database can give you a list of all your tables:

select *
from sys.tables

Then you can use the sys.columns table to obtain the columns in those tables. And then you would need to generate SQL to make the updates in a loop, for example.

Have a look here for general info on querying the database schema in SQL Server 2005: http://msdn.microsoft.com/en-us/library/ms345522(v=SQL.90).aspx

回复收藏 0 原文

~没有更多了~