SQL Server:清除@param 以防止注入攻击 [英] SQL Server: Sanitizing @param against injection attacks

问题描述

为了论证起见，假设我必须创建一个包含具有 INSERT 的 SQL 查询的局部变量:

For the sake of argument, let's just say I have to create a local variable containing a SQL query that has an INSERT:

 DECLARE @insert NVARCHAR(MAX)
 SELECT @insert = 'INSERT INTO [dbo].[' + @table + '] VALUES...
 EXEC (@insert) 

这个 INSERT 也将包含一个列值:

This INSERT is also going to contain a column value:

 DECLARE @insert NVARCHAR(MAX)
 SELECT @insert = 
  'INSERT INTO [dbo].[' + @table + '] VALUES (N''' + @message + ''')'
 EXEC (@insert) 

现在，我显然担心注入攻击，并希望确保@message 的值不会使@insert 的值成为恶意或格式错误的 EXEC 查询.

Now, I'm obviously concerned about an injection attack, and would like to ensure that @message's value can't make @insert's value malicious or malformed as a query to EXEC.

这让我们想到了我的问题:转义@message 中的 ' 字符就足够了吗?@message 中是否还有其他字符可以逃逸?

This brings us to my question: is escaping the ' characters in @message sufficient? Are there any other characters that could appear in @message that could escape out?

示例:

 DECLARE @insert NVARCHAR(MAX)
 SELECT @message = REPLACE(@message,'''','''''')
 SELECT @insert = 
  'INSERT INTO [dbo].[' + @table + '] VALUES (N''' + @message + ''')'
 EXEC (@insert)  

(当我说必须"时，这是因为我的查询在一个存储过程中，而这个存储过程接受@table，这是要插入的目标表.我是对讨论我的架构或为什么要插入的表是通过过程参数动态"指定的不感兴趣.请不要对此发表评论除非除了 EXEC() 查询之外还有另一种方法来指定要插入的表当然后表名作为过程参数接收时.)

(When I say "have to", this is because my query is in a stored procedure, and this stored procedure accepts @table, which is the destination table to INSERT into. I'm not interested in discussing my architecture or why the table to INSERT into is "dynamically" specified via a procedure parameter. Please refrain from commenting on this unless there's another way besides EXEC()ing a query to specify a table to INSERT into when then table name is received as a procedure parameter.)

推荐答案

使用 sp_executesql 和内置的 quotename().这篇文章，动态 SQL 的诅咒和祝福，几乎是权威参考.

Use sp_executesql and the built-in quotename(). This article, The Curse and Blessings of Dynamic SQL, is pretty much the definitive reference.

这篇关于SQL Server:清除@param 以防止注入攻击的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！