这个 SQL 查询代码如何被用户输入破坏/利用？
我们有一个遗留应用程序，它不使用位置参数进行查询，而且到处都有 SQL。我们决定（在我开始之前），由于用户输入可以包含撇号，因此每个字符串输入都应该针对这些撇号手动转义。
这是基本的原始代码（不是我写的），已翻译成 C# 以方便使用：
private string _Escape(string input)
{
return input.Replace("'", "''");
}
private bool _IsValidLogin(string userName, string password)
{
string sql =
string.Format
(
@"SELECT COUNT(*) FROM UserAccounts
WHERE UserName = '{0}' AND Password = '{1}'",
_Escape(userName),
_Escape(password)
);
// ...
}这看起来确实可以以某种方式被破坏，但我不知道如何通过用户输入来利用它。假设用户输入在输入 _IsValidLogin 之前未经过滤，并且忘记密码似乎以纯文本形式存储。
永久支持它的解决方案是显而易见的——使用位置参数——但我需要一些弹药来向管理层演示为什么/如何这段代码不安全，以便可以分配时间/美元来修复它。
注意：我假设这可以被破坏，但实际情况可能并非如此。我不是 SQL 超级明星。
注 2：我已将这个问题表述为与数据库无关，但如果您可以针对某个引擎利用此代码，我欢迎您的贡献。
Possible Duplicate:
Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes?
We have a legacy app that doesn't do queries using positional parameters, and there's SQL everywhere. It was decided (before I started here) that since user input can contain apostrophes, every string input should be manually escaped for those apostrophes.
Here is the essential original code (not written by me), translated into C# for easier consumption:
private string _Escape(string input)
{
return input.Replace("'", "''");
}
private bool _IsValidLogin(string userName, string password)
{
string sql =
string.Format
(
@"SELECT COUNT(*) FROM UserAccounts
WHERE UserName = '{0}' AND Password = '{1}'",
_Escape(userName),
_Escape(password)
);
// ...
}This really seems like it can be broken in some way, but I'm at a loss as to how it could be exploited by user input. Assume user input is unfiltered until it hits _IsValidLogin, and forget that passwords appear to be stored in plain text.
The solution to shore it up for good is obvious -- use positional parameters -- but I need some ammunition to demonstrate to management why/how this code is insecure so time/$ can be allocated for it to get fixed.
Note: I'm assuming this can be broken, but that may not actually be the case. I'm not a SQL superstar.
Note 2: I've expressed this question as database-agnostic, but if you can exploit this code for a certain engine, I welcome your contribution.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
它可以通过反斜杠来利用。
变为：
查询：
--本例中是注释标记。该解决方案假设程序仅过滤（重复）撇号。
It could be exlpoited by backslashes.
becomes:
the query:
--Is the comment mark in this example.The solution assumes the program only filters (duplicates) apostrophes.
好吧，我看不出它有什么脆弱之处。所以，让我们争论一下为什么应该改变它的另一个原因——它相当低效。在 MSSQL（以及我认为大多数其他高端 SQL 服务器）中，解析查询并设计执行计划，然后存储查询和计划。如果再次请求查询的
精确副本，则使用保存的执行计划。参数不会影响这个，所以如果你使用参数，它将重用计划；如果您嵌入文本，它永远不会。Well, I can't see a way it's vulnerable. So, let's argue a different reason why it should be changed --- it's rather ineffiecent. In MSSQL (and, I think, most other high end SQL servers), queries are parsed, and execution plan is devised, and then the query and plan are stored. If an
exactcopy of the query is requested again, the saved execution plan is used. Parameter don't affect this, so if you use parameters, it will reuse the plans; if you embed the text, it never will.