如何清理(防止SQL注入)SQL Server中的动态SQL?

发布于 2024-09-30 21:14:25 字数 328 浏览 9 评论 0 原文

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

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

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

假设我们有以下约束:

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

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

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

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

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

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

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

Assume we have these constraints:

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

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

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

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

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

扫码二维码加入Web技术交流群

发布评论

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

评论(8

且行且努力 2024-10-07 21:14:25

我相信您必须担心三种不同的情况:

  • 字符串(任何需要引号的内容): '''' + Replace(@string, '''', '''''') + '' ''
  • 名称(不允许使用引号的任何内容):quotename(@string)
  • 无法引用的内容:这需要白名单

注意字符串变量中的所有内容(charvarcharncharnvarchar 等)来自用户控制的源必须使用上述方法之一。这意味着,即使您希望是数字的东西,如果存储在字符串变量中,也会被引用。

有关详细信息,请参阅Microsoft 杂志 (废弃链接:2016-10-19)

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

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 定义为 < 大小的两倍code>@str (假设 @str 中的每个字符都可以是引号)。当然,保存最终 SQL 语句的字符串变量必须足够大,以容纳所有静态 SQL 以及所有结果变量。

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

  • strings (anything that requires quotes): '''' + replace(@string, '''', '''''') + ''''
  • names (anything where quotes aren't allowed): quotename(@string)
  • things that cannot be quoted: this requires whitelisting

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.

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

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.

何以笙箫默 2024-10-07 21:14:25

琐碎的情况可以通过 QUOTENAME 修复和 REPLACE:

set @sql = N'SELECT ' + QUOTENAME(@column) + 
   N' FROM Table WHERE Name = ' + REPLACE(@name, '''', '''''');

虽然 QUOTENAME 也可以用在文字上来添加单引号并用双单引号替换单引号,但因为它会将输入截断为 128 个字符,所以不建议这样做。

但这只是冰山一角。您需要妥善处理多部分名称 (dbo.table):引用多部分名称将导致无效标识符 [dbo.table],它必须是解析和拆分(使用 PARSENAME),然后正确引用到[dbo].[table]中。

另一个问题是截断攻击,即使您对文字进行简单的 REPLACE,也可能发生这种情况,请参阅 新的 SQL 截断攻击以及如何避免它们

SQL 注入问题永远无法通过在每个过程上放置一个神奇函数来解决。这就像问“我想要一个能让我的代码运行得更快的函数”。防止注入攻击是一个端到端的游戏,需要自始至终的编码纪律,它不能简单地作为事后添加。最好的机会是检查每个过程并逐行分析 T-SQL 代码,密切关注漏洞,然后在发现问题时进行修复。

The trivial cases can be fixed by QUOTENAME and REPLACE:

set @sql = N'SELECT ' + QUOTENAME(@column) + 
   N' FROM Table WHERE Name = ' + REPLACE(@name, '''', '''''');

Although QUOTENAME may be used on literals too to add the single quotes and replace single quotes with double single quotes, because it truncates the input to 128 characters it is not recommended.

But this is just the tip of the iceberg. There are multipart names (dbo.table) you need to take proper care of: quoting the multipartname would result in an invalid identifier [dbo.table], it has to be parsed and split (using PARSENAME), then properly quoted into [dbo].[table].

Another problem is truncation attacks, which can happen even if you do the trivial REPLACE on literals, see New SQL Truncation Attacks And How To Avoid Them.

The issue of SQL Injection can never be solved with one magic function placed on every procedure. It is like asking 'I want a function that will make my code run faster'. Preventing injection attacks is and end-to-end game that requires coding discipline all the way through, it cannot be simply added as an afterthought. Your best chance is to inspect every single procedure and analyze the T-SQL code line-by-line, with an eye opened for vulnerabilities, then fix the problems as you find them.

九歌凝 2024-10-07 21:14:25

有了这些限制,你就完蛋了。

以下两个选项可能会给您一些指导:

  1. 使用白名单验证器/解析器,仅接受采用某种格式且带有预期关键字和表的查询。这可能只适用于真正理解语法的非常好的 SQL 解析器。

  2. 在受限环境中执行查询。例如,使用权限非常有限的用户帐户。例如,仅允许(读取)访问某些永远不会返回敏感数据的视图,并禁止访问所有其他视图、所有存储过程、函数和表。更安全的是在另一台数据库服务器上执行这些查询。另外,不要忘记禁用 OPENROWSET 命令。

请注意以下几点:

  1. 当您接受除无效关键字之外的所有查询时,您肯定会失败,因为黑名单总是失败。尤其是像SQL这样复杂的语言。

  2. 不要忘记,即使您使用了这些技巧,从最纯粹的意义上来说,允许来自您不信任的源的动态 SQL 也是邪恶的,因为偶尔 错误被发现,可以通过向服务器发送特制的 SQL 来滥用。因此,即使您应用这些技巧,风险仍然存在。

  3. 当您决定采用允许动态 SQL 的解决方案时。请不要认为您自己可以提出安全的解决方案,特别是当您试图保护敏感的业务数据时。聘请数据库服务器安全专家来帮助您解决此问题。

With these constraints you are pretty screwed.

Here are a two options that might give you some direction:

  1. Use white list validator/parser that only accept queries that are in a format and with keywords and tables that are expected. This will probably only work with a very good SQL parser that really understands the syntax.

  2. Execute queries in a restricted environment. For instance, use a user account with very limited rights. For instance, only allow (read) access to certain views that will never return sensitive data and disallow access to all other views, all stored procedures, functions and tables. Even safer is to execute those queries on another database server. Also don't forget to disable the OPENROWSET command.

Please note the following:

  1. When you accept all queries except those that have invalid keywords, you will definitely fail, because black listing always fails. Especially with such a complicated language as SQL.

  2. Don't forget that allowing dynamic SQL from sources that you cannot trust is evil in its purest sense, even when you use these tips, because once in a while bugs are discovered that can be abused by sending specially crafted SQL to a server. Therefore, even if you apply these tips, the risk is still there.

  3. When you decide to go with a solution that allows dynamic SQL. Please don't think you can come up yourself with a safe solution, especially if you're trying to protect sensitive business data. Hire a database server security specialist to help you with that.

静水深流 2024-10-07 21:14:25

这是一个非常棘手的问题,它不是您想要解决的问题,但是这里有一个可行的小案例,(审稿人,如果我错过了一个案例,请告诉我,这带有<强>无保证)

create proc Bad 
  @param nvarchar(500) 
as 

exec (N'select ''' + @param + N'''') 

go

-- oops injected
exec Bad 'help'' select ''0wned!'' select ''' 

go 

create proc NotAsBad
   @param nvarchar(500) 
as 

declare @safish nvarchar(1000), @sql nvarchar(2000) 
set @safish = replace(@param, '''', '''''')

set @sql = N'select ''' + @safish  + N''''

exec (@sql) 

go 

-- this kind of works, but I have not tested everything
exec NotAsBad 'help'' select ''0wned!'' select ''' 

This is a really nasty problem, its not a problem you want to be solving, however here is a trivial case that works, (reviewers, please let me know if I missed a case, this comes with NO guarantees)

create proc Bad 
  @param nvarchar(500) 
as 

exec (N'select ''' + @param + N'''') 

go

-- oops injected
exec Bad 'help'' select ''0wned!'' select ''' 

go 

create proc NotAsBad
   @param nvarchar(500) 
as 

declare @safish nvarchar(1000), @sql nvarchar(2000) 
set @safish = replace(@param, '''', '''''')

set @sql = N'select ''' + @safish  + N''''

exec (@sql) 

go 

-- this kind of works, but I have not tested everything
exec NotAsBad 'help'' select ''0wned!'' select ''' 
梦中的蝴蝶 2024-10-07 21:14:25

OWASP 有一些有关此策略的信息。它应该始终是最后的选择(如我链接到的文章中所述),但如果这是您唯一的选择...

http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

文章中关于它是最后选择的引用

但是,这种方法很脆弱
与使用参数化相比
查询。该技术应该只
谨慎使用来改造旧版
以具有成本效益的方式编写代码。
从头开始构建的应用程序,或者
需要低风险的应用
应建立宽容或
使用参数化重写
查询。

从本质上讲,反对这种方法的论点是,即使您确实逃避了所有已知的不良输入,也不能保证将来有人不会想出一种方法来规避它。

但是,为了具体回答您的问题...

要转义的字符列表位于我上面链接的文章中。

编辑 如前所述,该文章没有提供很好的链接。然而,对于 SQL Server,这个是: http://msdn.microsoft.com /en-us/library/ms161953.aspx

请注意,您需要转义的字符列表将根据数据库平台的不同而有所不同,但看起来您正在使用 SQL Server,因此这应该是相关的。

引用下面的文章:

过滤输入也可能有助于通过删除转义字符来防止 SQL 注入。然而,由于可能产生问题的字符数量较多,这并不是一种可靠的防御。以下示例搜索字符串定界符。

private string SafeSqlLiteral(string inputSQL)
{
  return inputSQL.Replace("'", "''");
}

LIKE 子句

请注意,如果您使用 LIKE 子句,则仍然必须对通配符进行转义:

s = s.Replace("[", "[[]");
s = s.Replace("%", "[%]");
s = s.Replace("_", "[_]");

OWASP has some information on this strategy. It should always be a last-ditch option (as explained in the article I'm linking to) but if it's your only option...

http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

a quote from the article about it being a last-ditch option

However, this methodology is frail
compared to using parameterized
queries. This technique should only be
used, with caution, to retrofit legacy
code in a cost effective way.
Applications built from scratch, or
applications requiring low risk
tolerance should be built or
re-written using parameterized
queries.

In essence, the argument against this approach is even if you do escape all the known bad input, there's no guarantee that someone won't come up with a way to circumvent it inthe future.

However, to answer your question specifically...

a list of characters to escape is in the article I linked to above.

Edit As noted, the article doesn't provide very good links. However, for SQL Server, this one does: http://msdn.microsoft.com/en-us/library/ms161953.aspx

Note that the list of characters you need to escape will vary based on the DB platform, but it looks like you're using SQL Server, so this should be relevant..

Quote from the article below:

Filtering input may also be helpful in protecting against SQL injection by removing escape characters. However, because of the large number of characters that may pose problems, this is not a reliable defense. The following example searches for the character string delimiter.

private string SafeSqlLiteral(string inputSQL)
{
  return inputSQL.Replace("'", "''");
}

LIKE Clauses

Note that if you are using a LIKE clause, wildcard characters still must be escaped:

s = s.Replace("[", "[[]");
s = s.Replace("%", "[%]");
s = s.Replace("_", "[_]");
魔法唧唧 2024-10-07 21:14:25

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

NO

SQL 注入不被称为“某些字符集注入”,这是有原因的。过滤掉某些字符可能会使特定的利用变得复杂,但并不能阻止 SQL 注入本身。要利用 SQL 注入,必须编写 SQL。而且 SQL 并不局限于少数特殊字符。

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

NO

SQL injection is not called a "Certain Set Of Characters Injection", and for a reason. Filtering out certain character could complicate the particular exploit, but does not prevent SQL injection itself. To exploit an SQL injection one have to write SQL. And SQL is not limited to few special characters.

晨光如昨 2024-10-07 21:14:25

CLR 可以发挥很大的作用——您至少可以使用它来编写比使用 T-SQL 更有效的清理方法。在完美的世界中,您可以用参数化语句和其他更强大的结构完全替换存储过程。

Can you get SQL CLR can be of great use -- you can at least use it to write much, much more effective sanitization than one can do using T-SQL. In a perfact world, you can replace the stored procs completely with parameterized statements and other stronger structures.

北斗星光 2024-10-07 21:14:25

还有另一种方法可能可行,尽管它取决于存储过程的参数中允许使用哪些字符。不要转义可用于 SQL 注入的麻烦字符,而是删除这些字符。例如,如果您有此 SP:

create procedure dbo.MYSP(@p1 varchar(100))
as begin
  set @p1 = Replace(@p1, '''',' '); -- Convert single quotes to spaces
  set @p1 = Replace(@p1, ';', ' ');
  set @p1 = Replace(@p1, '--', ' ');      
  set @p1 = Replace(@p1, '/*', ' ');      
  set @p1 = Replace(@p1, '*/', ' ');      
  set @p1 = Replace(@p1, 'xp_', ' ');      
  ...
end;

您可以用空格或空字符串替换任何单引号。这种方法还可以用于通过使用更多替换命令来替换注释字符,例如 /* */(如我上面所示)。但请注意,只有当您从不期望在正常输入中出现这些字符时,此方法才有效,这取决于您的应用程序。

请注意,替换字符集基于 https:// msdn.microsoft.com/en-us/library/ms161953(SQL.105).aspx

There is another approach that may possibly work, although it depends on what characters are allowed in the stored procedure's parameters. Instead of escaping the troublesome characters that can be used for SQL injection, delete the characters instead. Eg if you have this SP:

create procedure dbo.MYSP(@p1 varchar(100))
as begin
  set @p1 = Replace(@p1, '''',' '); -- Convert single quotes to spaces
  set @p1 = Replace(@p1, ';', ' ');
  set @p1 = Replace(@p1, '--', ' ');      
  set @p1 = Replace(@p1, '/*', ' ');      
  set @p1 = Replace(@p1, '*/', ' ');      
  set @p1 = Replace(@p1, 'xp_', ' ');      
  ...
end;

you can replace any single quotes with spaces or with an empty string. This approach can also be used to replace comment characters such as /* */ -- by using more Replace commands (as I have just shown above). But note this approach will only work if you never expect these characters in normal input, and this depends on your application.

Note the set of replaced characters is based on https://msdn.microsoft.com/en-us/library/ms161953(SQL.105).aspx

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文