SQL Server：对 @param 进行清理以防止注入攻击

Question

举个例子，假设我必须创建一个包含INSERT的SQL查询的本地变量：

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

这个INSERT语句也会包含一个列值：

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

现在，我很明显关注注入攻击，并希望确保@message的值不能使@insert的值恶意或畸形成一个查询到EXEC。

这就引出了我的问题：在@message中转义单引号就足够了吗？还有其他可能出现在@message中的字符可以进行转义吗？

示例：

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

当我说“必须”的时候，是因为我的查询在存储过程中，而这个存储过程接受@table参数，这个参数是要插入的目标表名。我不想讨论我的架构或者为什么要通过过程参数“动态”指定要插入的表名。请勿评论此事，除非有其他方法可以指定要插入的表名而不是通过执行查询获得过程参数接收的表名。

- Chris

---

我们真的想要处理那些涉及如何故意编写糟糕代码的问题吗？ - dkretz

如果这意味着找到解决方案，那是肯定的。开发人员经常不得不处理烂代码，特别是当你咨询并继承以8美元/小时在孟加拉国编写的项目时。我们并不总是有奢侈的条件去从头开始构建项目，并按照本来应该的方式进行构建。 :) - core

Answer 1

使用内置函数 quotename() 和 sp_executesql。这篇文章《The Curse and Blessings of Dynamic SQL》是动态SQL的权威参考。

- Mitch Wheat

---

显然，即使在2008年，quotename（）也有128个长度限制，因为它期望一个SQL标识符。参考建议创建一个quotestring（）函数（http://www.sommarskog.se/dynamic_sql.html#quotestring），它与REPLACE（@variable，''''，''''''）完全相同。 - core

Answer 2

建议使用sp_executesql存储过程，而不是调用EXEC(@somesql)。具体来说，这允许您传递参数，并且系统将检查这些参数是否有效。

- Scott W.

---

是的，不幸的是，执行该代码后，SQL Server 2008 Express 告诉我“必须声明表变量 '@my_table'。”。 - core

我很确定我在 SQL Server 2005 中做过这件事，但我承认我可能非常非常累了。抱歉。我明天再试一下。 - Scott Whitlock

Answer 3

你可以先使用普通的T-SQL查询模式来查询架构信息，并确保表名存在。这样，如果SQL语句格式错误，它将不会作为代码执行，而只是一个VARCHAR表名。

DECLARE @Table AS VARCHAR(MAX)
DECLARE @Exists AS BIT

SET @Table = 'Vicious malformed dynamic SQL'

SELECT  @Exists = COUNT(TABLE_NAME) 
FROM    INFORMATION_SCHEMA.TABLES 
WHERE   TABLE_NAME = @Table

IF (@Exists = 1)
    BEGIN
    PRINT 'Table exists'
    -- Execute dynamic SQL.
    END
ELSE
    PRINT 'Invalid table'

（或者直接使用IF EXISTS（SELECT ...））

- HardCode

---

好想法。我一直在做以下的事情。有什么优势吗？我一直在做的是："IF OBJECT_ID(@table,'U') IS NULL..." - core

Answer 4

显然，即使在2008年的测试中，quotename()也有一个128长度限制，因为它期望一个SQL标识符。参考建议创建一个quotestring()函数，它与以下内容相同：

REPLACE(@variable,'''','''''')

因此我建议将上面的REPLACE()创建为一个函数，方法如下：

CREATE FUNCTION quotestring(@string nvarchar(MAX)) 
RETURNS nvarchar(MAX) AS
BEGIN
    RETURN(REPLACE(@string,'''',''''''))
END

...除非我误解了什么。

- Chris

---

你可以像这样将nvarchar(MAX)作为参数传递吗？ - Scott Whitlock

当然可以啊！它现在已经是我开发环境中的一个操作函数了。 :) - core

Answer 5

在编写动态SQL时，您应尽可能地参数化，并仅在绝对必要时才使用字符转义。您无法将@table参数化，但可以将@message参数化。

DECLARE @insert NVARCHAR(MAX)
set @insert = 'INSERT INTO [dbo].' + quotename(@table) + ' values(@message)'
exec sys.sp_executesql @insert, N'@message nvarchar(max)', @message = @inMessage;

有很多方法攻击者可以利用动态SQL，包括缓冲区长度攻击和使用Unicode等价字符。我曾经遇到一个例子，在那里转义单引号字符仍然存在漏洞，其中引号字符的某个Unicode等价物可能被传递进来。软件栈的一部分正在进行Unicode到ASCII的转换，因此在它们被转义后插入引号是可能的。糟糕的是。

- Trent