如何清理(防止 SQL 注入)SQL Server 中的动态 SQL? [英] How to cleanse (prevent SQL injection) dynamic SQL in SQL Server?

问题描述

我们有大量依赖动态 SQL 的 SQL Server 存储过程.

We have a ton of SQL Server stored procedures which rely on dynamic SQL.

存储过程的参数在动态 SQL 语句中使用.

The parameters to the stored procedure are used in a dynamic SQL statement.

我们需要在这些存储过程中使用标准验证函数来验证这些参数并防止 SQL 注入.

We need a standard validation function inside these stored procedures to validate these parameters and prevent SQL injection.

假设我们有这些限制:

1. 我们不能重写程序以不使用动态 SQL

1. We can't rewrite the procedures to not use Dynamic SQL

我们不能使用 sp_OACreate 等来使用正则表达式进行验证.

We can't use sp_OACreate etc., to use regular expressions for validation.

在将参数传递给存储过程之前，我们无法修改调用存储过程以验证参数的应用程序.

We can't modify the application which calls the stored procedure to validate the parameters before they are passed to the stored procedure.

是否有一组我们可以过滤掉的字符以确保我们不会受到 SQL 注入的影响?

Is there a set of characters we can filter out to ensure we are not susceptible to SQL injection?

推荐答案

我相信您需要担心三种不同的情况:

I believe there are three different cases that you have to worry about:

• 字符串(任何需要引号的东西):'''' + replace(@string, '''', '''''') + ''''
• 名称(不允许使用引号的任何内容):quotename(@string)
• 不能引用的东西:这需要列入白名单

注意:字符串变量中的一切(char、varchar、nchar>、nvarchar 等)必须使用上述方法之一.这意味着即使您希望是数字的东西如果存储在字符串变量中也会被引用.

Note: Everything in a string variable (char, varchar, nchar, nvarchar, etc.) that comes from user-controlled sources must use one of the above methods. That means that even things you expect to be numbers get quoted if they're stored in string variables.

有关详细信息，请参阅微软杂志 _{(废弃链接:2016-10-19)}.

For more details, see the Microsoft Magazine _{(Obsolete link: 2016-10-19)}.

以下是使用所有三种方法的示例:

Here's an example using all three methods:

EXEC 'SELECT * FROM Employee WHERE Salary > ''' +
     REPLACE(@salary, '''', '''''') +   -- replacing quotes even for numeric data
     ''' ORDER BY ' + QUOTENAME(@sort_col) + ' ' +  -- quoting a name
     CASE @sort_dir WHEN 'DESC' THEN 'DESC' END     -- whitelisting

另请注意，通过在 EXEC 语句中内联执行所有字符串操作，无需担心截断问题.如果将中间结果分配给变量，则必须确保变量足够大以保存结果.如果您执行 SET @result = QUOTENAME(@name)，您应该定义 @result 以容纳至少 258 (2 * 128 + 2) 个字符.如果你执行 SET @result = REPLACE(@str, '''', '''''') 你应该定义 @result 为 @result 的两倍code>@str(假设 @str 中的每个字符都可以是引号).当然，保存最终 SQL 语句的字符串变量必须足够大以保存所有静态 SQL 以及所有结果变量.

Also note that by doing all the string operations inline in the EXEC statement there is no concern with truncation problems. If you assign the intermediate results to variables, you must make sure that the variables are big enough to hold the results. If you do SET @result = QUOTENAME(@name) you should define @result to hold at least 258 (2 * 128 + 2) characters. If you do SET @result = REPLACE(@str, '''', '''''') you should define @result to be twice the size of @str (assume every character in @str could be a quote). And of course, the string variable holding the final SQL statement must be large enough to hold all the static SQL plus all of the result variables.

这篇关于如何清理(防止 SQL 注入)SQL Server 中的动态 SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！