转义单引号的卫生如何被 SQL Server 中的 SQL 注入打败? [英] How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?

问题描述

首先，我很清楚参数化查询是最好的选择，但我想知道是什么让我在下面介绍的策略容易受到攻击.人们坚持认为下面的解决方案不起作用，所以我正在寻找一个为什么它不起作用的例子.

To start this off, I am well aware that parameterized queries are the best option, but I am asking what makes the strategy I present below vulnerable. People insist the below solution doesn't work, so I am look for an example of why it wouldn't.

如果在将动态 SQL 发送到 SQL Server 之前使用以下转义在代码中构建了动态 SQL，那么什么样的注入可以解决这个问题?

If dynamic SQL is built in code using the following escaping before being sent to a SQL Server, what kind of injection can defeat this?

string userInput= "N'" + userInput.Replace("'", "''") + "'"

回答了类似的问题 此处，但我不认为任何答案都适用于此.

A similar question was answered here, but I don't believe any of the answers are applicable here.

在 SQL Server 中无法用\"转义单引号.

Escaping the single quote with a "\" isn't possible in SQL Server.

我相信 SQL Smuggling 与 Unicode(概述 here) 会因为正在生成的字符串被单引号前面的 N 标记为 Unicode 而受阻.据我所知，SQL Server 不会自动转换为单引号的其他字符集.如果没有未转义的单引号，我认为注入是不可能的.

I believe SQL Smuggling with Unicode (outlined here) would be thwarted by the fact that the string being produced is marked as Unicode by the N preceding the single quote. As far as I know, there are no other character sets that SQL Server would automatically translate to a single quote. Without an unescaped single quote, I don't believe injection is possible.

我也不相信字符串截断是一个可行的向量.SQL Server 肯定不会进行截断，因为 nvarchar 的最大大小为 2GB 根据微软.2 GB 的字符串在大多数情况下是不可行的，而在我的情况下是不可能的.

I don't believe String Truncation is a viable vector either. SQL Server certainly won't be doing the truncating since the max size for an nvarchar is 2GB according to microsoft. A 2 GB string is unfeasible in most situations, and impossible in mine.

二阶注入是可能的，但如果:

1. 进入数据库的所有数据都使用上述方法进行清理
2. 永远不会将数据库中的值附加到动态 SQL 中(既然您可以在任何动态 SQL 字符串的静态部分引用表值，那么您为什么还要这样做呢?).

我并不是说这比使用参数化查询更好或替代它，但我想知道我概述的内容是如何容易受到攻击的.有什么想法吗?

I'm not suggesting that this is better than or an alternative to using parameterized queries, but I want to know how what I outlined is vulnerable. Any ideas?

推荐答案

此转义函数在某些情况下会失败.最明显的是不使用单引号时:

There are a few cases where this escape function will fail. The most obvious is when a single quote isn't used:

string table= "\"" + table.Replace("'", "''") + "\""
string var= "`" + var.Replace("'", "''") + "`"
string index= " " + index.Replace("'", "''") + " "
string query = "select * from `"+table+"` where name=\""+var+"\" or id="+index

在这种情况下，您可以使用双引号、反引号突破".在最后一种情况下，没有什么可以突破"的，因此您只需编写 1 union select password from users-- 或攻击者想要的任何 sql 负载.

In this case, you can "break out" using a double-quote, a back-tick. In the last case there is nothing to "break out" of, so you can just write 1 union select password from users-- or whatever sql payload the attacker desires.

此转义函数将失败的下一个条件是，如果在转义字符串后采用子字符串(并且是，我在野外发现了这样的漏洞):

The next condition where this escape function will fail is if a sub-string is taken after the string is escaped (and yes I have found vulnerabilities like this in the wild):

string userPassword= userPassword.Replace("'", "''")
string userName= userInput.Replace("'", "''")
userName = substr(userName,0,10)
string query = "select * from users where name='"+userName+"' and password='"+userPassword+"'";

在这种情况下，用户名 abcdefgji' 将被转义函数转换为 abcdefgji'' ，然后又变回 abcdefgji'通过取子字符串.这可以通过将密码值设置为任何 sql 语句来利用，在这种情况下 or 1=1-- 将被解释为 sql 而用户名将被解释为 abcdefgji'' 和密码=.结果查询如下:

In this case a username of abcdefgji' will be turned into abcdefgji'' by the escape function and then turned back into abcdefgji' by taking the sub-string. This can be exploited by setting the password value to any sql statement, in this case or 1=1-- would be interpreted as sql and the username would be interpreted as abcdefgji'' and password=. The resulting query is as follows:

select * from users where name='abcdefgji'' and password=' or 1=1-- 

T-SQL 和其他已经提到的高级 sql 注入技术.SQL Server 应用程序中的高级 SQL 注入 是一篇很棒的论文，如果您没有，应该阅读它不是已经.

T-SQL and other advanced sql injection techniques where already mentioned. Advanced SQL Injection In SQL Server Applications is a great paper and you should read it if you haven't already.

最后一个问题是 unicode 攻击.出现此类漏洞是因为转义函数不知道多字节编码，这可以是 被攻击者用来消耗"转义字符.在字符串前添加N"无济于事，因为这不会影响字符串后面的多字节字符的值.但是，这种类型的攻击非常罕见，因为必须将数据库配置为接受 GBK unicode 字符串(我不确定 MS-SQL 是否可以做到这一点).

The final issue is unicode attacks. This class of vulnerabilities arises because the escape function is not aware of multi-byte encoding, and this can be used by an attacker to "consume" the escape character. Prepending an "N" to the string will not help, as this doesn't affect the value of multi-byte chars later in the string. However, this type of attack is very uncommon because the database must be configured to accept GBK unicode strings (and I'm not sure that MS-SQL can do this).

二阶代码注入仍然是可能的，这种攻击模式是通过信任攻击者控制的数据源创建的.转义用于将控制字符表示为其字符文字.如果开发人员忘记对从 select 获得的值进行转义，然后在另一个查询中使用该值，那么 bam 攻击者将可以使用字符文字单引号.

Second-Order code injection is still possible, this attack pattern is created by trusting attacker-controlled data sources. Escaping is used to represent control characters as their character literal. If the developer forgets to escape a value obtained from a select and then uses this value in another query then bam the attacker will have a character literal single quote at their disposal.

测试一切，不要相信任何事情.

这篇关于转义单引号的卫生如何被 SQL Server 中的 SQL 注入打败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！