证明SQL注入
我试图在这里简单地证明这个简单的函数不足以阻止世界上的每一个 sql 注入：
Function CleanForSQL(ByVal input As String) As String
Return input.Replace("'", "''")
End Function
这是来自我们的应用程序之一的典型插入语句：
Database.DBUpdate("UPDATE tblFilledForms SET Text1 = '" + CleanForSQL(txtNote.Text) + "' WHERE FilledFormID = " + DGVNotes.SelectedRows(0).Cells("FilledFormID").Value.ToString)
我知道它不安全，因为谷歌搜索和查找StackOverflow.com 上的其他问题。 这里是我发现的一个问题，其中所有函数因为我上面介绍的内容是无关紧要且毫无意义的。
因此，根据我链接到的帖子，只需输入
'Chr(8);更新 tblMaint SET Value1 = 2 WHERE ValueID = 2--
into txtNote 应该足以清除整个 tblFilledForms 表中 text1 中的每个值，然后将 tblmaint 表的第二行更新为 2 正确吗？
这里应该发生的是 VB 会将其解释为
更新 tblFilledForms SET Text1 = '''Chr(8);更新 tblMaint SET Value1 = 2 WHERE ValueID = 2--' WHERE FilledFormID = 5120327
并将其发送到 SQL，SQL 将执行 Chr(8) 来删除第三个 ' ，这将产生
更新 tblFilledForms SET Text1 = '';更新 tblMaint SET Value1 = 2 WHERE ValueID = 2--' WHERE FilledFormID = 5120327
在数据库上实际执行正确吗？
然后，我从剪贴板复制了 Chr(8)，并用剪贴板内容替换了文本框中的 Chr(8)，但仍然不行。它将整个字符串直接放入字段中，不会出现问题。
那么我在这里做错了什么？或者我还能做什么来打破它？
技术和背景： 我正在使用 MS SQL Server 2005 和 VB .NET 2005。 数据库中的 Text1 字段是 Varchar(600) 字段（不要问我为什么它不是 MAX，它毫无意义，我知道） 表中的某些触发器会阻止像这样的大规模更新，并且如果注入实际上工作正常，则会引发一些错误。
附言。我知道参数化查询是这里的方法，我并不是在寻找诸如“我不知道为什么它不起作用，但参数化查询是方法”之类的答案。我正在寻找能够证明我们的软件已损坏并且我们需要使用更好的原则重写它的能力。
对于任何阅读此问题以了解如何更好地过滤文本字段的人来说，答案是“不要”！使用参数！它们更好、更安全、更容易！
I'm trying to simply prove here that this simple function isn't good enough to prevent every sql injection in the world:
Function CleanForSQL(ByVal input As String) As String
Return input.Replace("'", "''")
End Function
Here is a typical insert statement from one of our apps:
Database.DBUpdate("UPDATE tblFilledForms SET Text1 = '" + CleanForSQL(txtNote.Text) + "' WHERE FilledFormID = " + DGVNotes.SelectedRows(0).Cells("FilledFormID").Value.ToString)
I know its not secure, because of googling and looking up other questions on StackOverflow.com. Here is one question that I found in which all functions such as the one I presented above are irrelevant and pointless.
So based on the post I linked to, simply typing
'Chr(8); update tblMaint SET Value1 = 2 WHERE ValueID = 2--
into txtNote should be enough to clear every value in text1 in the entire tblFilledForms table, and then update the tblmaint table's second row to be 2 correct?
What SHOULD happen here is that VB will interpret this as
UPDATE tblFilledForms SET Text1 = '''Chr(8); update tblMaint SET Value1 = 2 WHERE ValueID = 2--' WHERE FilledFormID = 5120327
and send it to SQL which will intern execute the Chr(8) to erase the third ' which would produce
UPDATE tblFilledForms SET Text1 = ''; update tblMaint SET Value1 = 2 WHERE ValueID = 2--' WHERE FilledFormID = 5120327
to be actually executed on the database correct?
I then coppied a Chr(8) from the clipboard and replaced the Chr(8) in the textbox with the clipboard contents and still a no-go. It puts the whole string directly into the field w/o problems.
So what am I doing wrong here? or what else can I do to break it?
Technologies and background:
I'm using MS SQL Server 2005, and VB .NET 2005.
the Text1 field in the database is a Varchar(600) field (don't ask my why its not MAX, its pointless, i know)
There are certain triggers on the table that would prevent a mass update such as this and throw some errors if the injection actually worked right.
PS. I know parametrized queries are the way to go here and I'm not looking for answers like "well i dunno why it doesn't work, but parametrized queries are the way to go". I'm looking for the ability to prove that our software is broken and that we need to rewrite it using better principles.
To anyone reading this question to figure out how to better filter your text fields, the answer is DON'T! Use the parameters! they are much better, safer, and easier!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
Chr(8) 是带引号的文字字符串的一部分，更新语句也是如此，因此 SQL Server 不会将其解释为函数调用。在这个例子中，Text1 将被设置为文字值：（
是的，包括单引号）
因此，在这个例子中，您的代码是安全的。大多数关于 SQL 注入的棘手问题都与意外未能验证和引用值有关，正确引用的 SQL 语句本身并没有什么不安全的地方。
The Chr(8) is part of the quoted literal string, as is the update statement, so SQL Server is not going to interpret it as a function call. With this example, Text1 will be set to the literal value:
(yes, including that single quote)
So, with this example, your code is secure. Most hang-wringing over SQL injection is about accidentally failing to validate and quote values, there is nothing inherently unsafe in a properly-quoted SQL statement.
您的 CleanForSQL 方法仅处理字符串情况。当您不使用字符串而是使用 INT 时会发生什么？在这种情况下，将没有结束标记来结束，因此注入仍然会发生。考虑这个例子...
在这种情况下，只需输入以下内容即可...
0;更新 tblMaint SET Value1 = 2 WHERE ValueID = 2--Your CleanForSQL method only handles string situations. What happens when you're not using a string but an INT instead? In that case, there would be no end tick to close with, so the injection would still happen. Consider this example...
in that case, just entering the following will work...
0; update tblMaint SET Value1 = 2 WHERE ValueID = 2--Scott Ivey 有一个可以打破它的经典案例，即缺少保护数字输入的引号。 （+1）
根据语言以及字符串被“清理”的位置以及所使用的数据库，您的直接风险是它们的语言允许字符串被转义。此时，您试图避免通过的单引号会出错
\'; DROP yourTable;-- =>; \''; DROP yourTable;--
这会进入你的sql字符串，
然后是：
如果你的数据库支持转义字符，'\''将被视为单引号的文字字符串 - 宾果你的妥协。
同样，必须记住保护是有效的，即使提供的示例更新语句也无法保护 where 子句中的参数，是因为 DGVNotes.SelectedRows(0).Cells("FilledFormID").Value.ToString) 永远无法保护由用户输入？这对于应用程序的整个生命周期都是如此吗？
Scott Ivey has the classic case that can break it, the lack of quotes protecting a numeric input. (+1'ed that)
Depending on the language and where the string is being 'cleansed' and the database being used your immediate risk is that they language permits the string to be escaped. At that point the single quote you are trying to avoid getting thru goes wrong
\'; DROP yourTable;-- => \''; DROP yourTable;--
That goes into your sql string as
Which is then:
'\'' is taken as the literal string of a single quote, if your database supports escaped characters - bingo your compromised.
Equally the protection has to be remembered to be effective, even the example update statement provided failed to protect the parameter in the where clause, was it because DGVNotes.SelectedRows(0).Cells("FilledFormID").Value.ToString) could never be entered by a user? will that hold true for the entire lifetime of the app etc?
你没有做错任何事。这就是 SQL Server 解析字符串的方式。第一个引号打开字符串，然后紧随其后的是转义引号，后跟 Chr(8)。
作为练习，如果您在 SQL Server 中运行此命令：
SELECT '''Hello'，会发生什么情况？在本例中应用了完全相同的解析规则。You're not doing anything wrong. This is how SQL Server parses strings. The first quote opens the string, then you've followed that immediately with an escaped quote followed by Chr(8).
As an exercise, what happens if you run this in SQL Server:
SELECT '''Hello'? Exactly the same parsing rules are being applied in this case.我认为你的问题是
Chr(8)没有执行，你需要找到另一种方法来获取前导引号。I think your problem is that
Chr(8)is not executed, you need to find another way to get the leading quote mark in.