我可以通过转义单引号并用单引号包围用户输入来防止 SQL 注入吗？
我意识到参数化 SQL 查询是在构建包含用户输入的查询时清理用户输入的最佳方法，但我想知道获取用户输入并转义任何单引号并用单引号包围整个字符串有什么问题。 代码如下：
sSanitizedInput = "'" & Replace(sInput, "'", "''") & "'"
用户输入的任何单引号都会替换为双单引号，这消除了用户结束字符串的能力，因此他们可能输入的任何其他内容（例如分号、百分号等）都将成为字符串的一部分字符串的一部分，而不是作为命令的一部分实际执行。
我们使用的是 Microsoft SQL Server 2000，我相信单引号是唯一的字符串分隔符，也是转义字符串分隔符的唯一方法，因此无法执行用户输入的任何内容。
我没有看到任何对此进行 SQL 注入攻击的方法，但我意识到，如果这像我认为的那样万无一失，那么其他人可能已经想到了它，并且这将是常见的做法。
这段代码有什么问题？ 有没有办法让 SQL 注入攻击绕过这种清理技术？ 利用此技术的示例用户输入将非常有帮助。
更新：
我仍然不知道有什么方法可以有效地针对此代码发起 SQL 注入攻击。 一些人建议反斜杠可以转义一个单引号并留下另一个来结束字符串，以便字符串的其余部分将作为 SQL 命令的一部分执行，我意识到这种方法可以将 SQL 注入到MySQL 数据库，但在 SQL Server 2000 中（我已经找到的）转义单引号的唯一方法是使用另一个单引号； 反斜杠不会这样做。
除非有一种方法可以阻止单引号的转义，否则用户输入的其余部分都不会被执行，因为它们都将被视为一个连续的字符串。
我知道有更好的方法来清理输入，但我真的更感兴趣的是了解为什么我上面提供的方法不起作用。 如果有人知道针对这种清理方法发起 SQL 注入攻击的任何具体方法，我很乐意看到它。
I realize that parameterized SQL queries is the optimal way to sanitize user input when building queries that contain user input, but I'm wondering what is wrong with taking user input and escaping any single quotes and surrounding the whole string with single quotes. Here's the code:
sSanitizedInput = "'" & Replace(sInput, "'", "''") & "'"
Any single-quote the user enters is replaced with double single-quotes, which eliminates the users ability to end the string, so anything else they may type, such as semicolons, percent signs, etc., will all be part of the string and not actually executed as part of the command.
We are using Microsoft SQL Server 2000, for which I believe the single-quote is the only string delimiter and the only way to escape the string delimiter, so there is no way to execute anything the user types in.
I don't see any way to launch an SQL injection attack against this, but I realize that if this were as bulletproof as it seems to me someone else would have thought of it already and it would be common practice.
What's wrong with this code? Is there a way to get an SQL injection attack past this sanitization technique? Sample user input that exploits this technique would be very helpful.
UPDATE:
I still don't know of any way to effectively launch a SQL injection attack against this code. A few people suggested that a backslash would escape one single-quote and leave the other to end the string so that the rest of the string would be executed as part of the SQL command, and I realize that this method would work to inject SQL into a MySQL database, but in SQL Server 2000 the only way (that I've been able to find) to escape a single-quote is with another single-quote; backslashes won't do it.
And unless there is a way to stop the escaping of the single-quote, none of the rest of the user input will be executed because it will all be taken as one contiguous string.
I understand that there are better ways to sanitize input, but I'm really more interested in learning why the method I provided above won't work. If anyone knows of any specific way to mount a SQL injection attack against this sanitization method I would love to see it.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（19）
首先，这只是不好的做法。 输入验证总是必要的，但它也总是不确定的。
更糟糕的是，黑名单验证总是有问题的，最好明确并严格地定义您接受的值/格式。 诚然，这并不总是可能的 - 但在某种程度上它必须始终做到。
关于该主题的一些研究论文：
要点是，您所做的任何黑名单（以及过于宽松的白名单）都可以被绕过。 我论文的最后一个链接显示了甚至可以绕过引用转义的情况。
即使这些情况不适用于您，但这仍然是一个坏主意。 此外，除非您的应用程序非常小，否则您将不得不处理维护问题，也许还需要进行一定程度的治理：如何确保它在任何时候、任何地方都正确完成？
正确的方法是：
First of all, it's just bad practice. Input validation is always necessary, but it's also always iffy.
Worse yet, blacklist validation is always problematic, it's much better to explicitly and strictly define what values/formats you accept. Admittedly, this is not always possible - but to some extent it must always be done.
Some research papers on the subject:
Point is, any blacklist you do (and too-permissive whitelists) can be bypassed. The last link to my paper shows situations where even quote escaping can be bypassed.
Even if these situations do not apply to you, it's still a bad idea. Moreover, unless your app is trivially small, you're going to have to deal with maintenance, and maybe a certain amount of governance: how do you ensure that its done right, everywhere all the time?
The proper way to do it:
好的，这个回复将与问题的更新相关：
现在，除了 MySQL 反斜杠转义之外 - 并考虑到我们实际上讨论的是 MSSQL，实际上还有 3 种可能的方法仍然可以通过 SQL 注入代码
请考虑到这些并不总是有效，并且非常依赖于您周围的实际代码：
然后您得到的是用户名，经过转义，然后修剪为 20 个字符。 这里的问题是 - 我将把我的引号放在第 20 个字符中（例如在 19 个 a 之后），并且您的转义引号将被修剪（在第 21 个字符中）。 然后，SQL
与上述格式错误的用户名相结合将导致密码已经在引号之外，并且将直接包含有效负载。
3. Unicode 走私 - 在某些情况下，可以传递一个看起来像引号的高级 unicode 字符，但不是 - 直到它到达数据库，突然就在。 由于验证它时它不是引用，因此它会很容易......请参阅我之前的回复以了解更多详细信息，并链接到原始研究。
Okay, this response will relate to the update of the question:
Now, besides the MySQL backslash escaping - and taking into account that we're actually talking about MSSQL, there are actually 3 possible ways of still SQL injecting your code
Take into account that these will not all be valid at all times, and are very dependant on your actual code around it:
Then what you get - is the username, escaped, and then trimmed to 20 characters. The problem here - I'll stick my quote in the 20th character (e.g. after 19 a's), and your escaping quote will be trimmed (in the 21st character). Then the SQL
combined with the aforementioned malformed username will result in the password already being outside the quotes, and will just contain the payload directly.
3. Unicode Smuggling - In certain situations, it is possible to pass a high-level unicode character that looks like a quote, but isn't - until it gets to the database, where suddenly it is. Since it isn't a quote when you validate it, it will go through easy... See my previous response for more details, and link to original research.
简而言之：永远不要对自己进行转义查询。 你肯定会弄错什么。 相反，请使用参数化查询，或者如果由于某种原因无法执行此操作，请使用可以为您执行此操作的现有库。 没有理由自己做。
In a nutshell: Never do query escaping yourself. You're bound to get something wrong. Instead, use parameterized queries, or if you can't do that for some reason, use an existing library that does this for you. There's no reason to be doing it yourself.
我意识到问题提出已经很久了，但是..
对“引用参数”过程发起攻击的一种方法是使用字符串截断。
根据 MSDN 的说法，在 SQL Server 2000 SP4（和 SQL Server 2005 SP1）中，太长的字符串将被悄悄截断。
当引用字符串时，字符串的大小会增加。 每个撇号都会重复。
然后，这可以用于将 SQL 的一部分推送到缓冲区之外。 因此，您可以有效地删除 where 子句的部分内容。
这可能在“用户管理”页面场景中最有用，在这种情况下，您可以滥用“更新”语句来不执行它应该执行的所有检查。
因此，如果您决定引用所有参数，请确保您知道字符串大小的情况，并确保不会遇到截断。
我建议使用参数。 总是。 只是希望我可以在数据库中强制执行这一点。 作为副作用，您更有可能获得更好的缓存命中，因为更多语句看起来相同。 （这在 Oracle 8 上确实如此）
I realize this is a long time after the question was asked, but ..
One way to launch an attack on the 'quote the argument' procedure is with string truncation.
According to MSDN, in SQL Server 2000 SP4 (and SQL Server 2005 SP1), a too long string will be quietly truncated.
When you quote a string, the string increases in size. Every apostrophe is repeated.
This can then be used to push parts of the SQL outside the buffer. So you could effectively trim away parts of a where clause.
This would probably be mostly useful in a 'user admin' page scenario where you could abuse the 'update' statement to not do all the checks it was supposed to do.
So if you decide to quote all the arguments, make sure you know what goes on with the string sizes and see to it that you don't run into truncation.
I would recommend going with parameters. Always. Just wish I could enforce that in the database. And as a side effect, you are more likely to get better cache hits because more of the statements look the same. (This was certainly true on Oracle 8)
我在处理“高级搜索”功能时使用了这种技术，其中从头开始构建查询是唯一可行的答案。 （例如：允许用户根据产品属性的无限约束集来搜索产品，将列及其允许值显示为 GUI 控件，以降低用户的学习门槛。）
AFAIK 本身是安全的。 然而，正如另一位回答者指出的那样，您可能还需要处理退格转义（尽管至少在使用 ADO 或 ADO.NET 将查询传递到 SQL Server 时不需要——不能保证所有数据库或技术）。
问题在于，您确实必须确定哪些字符串包含用户输入（始终可能是恶意的），以及哪些字符串是有效的 SQL 查询。 陷阱之一是如果您使用数据库中的值——这些值最初是用户提供的吗？ 既然如此，他们也必须逃走。 我的答案是在构建 SQL 查询时尝试尽可能晚地进行清理（但不能晚！）。
然而，在大多数情况下，参数绑定是可行的方法——它更简单。
I've used this technique when dealing with 'advanced search' functionality, where building a query from scratch was the only viable answer. (Example: allow the user to search for products based on an unlimited set of constraints on product attributes, displaying columns and their permitted values as GUI controls to reduce the learning threshold for users.)
In itself it is safe AFAIK. As another answerer pointed out, however, you may also need to deal with backspace escaping (albeit not when passing the query to SQL Server using ADO or ADO.NET, at least -- can't vouch for all databases or technologies).
The snag is that you really have to be certain which strings contain user input (always potentially malicious), and which strings are valid SQL queries. One of the traps is if you use values from the database -- were those values originally user-supplied? If so, they must also be escaped. My answer is to try to sanitize as late as possible (but no later!), when constructing the SQL query.
However, in most cases, parameter binding is the way to go -- it's just simpler.
输入卫生不是你想要半途而废的事情。 使用你的整个屁股。 在文本字段上使用正则表达式。 尝试将您的数字转换为正确的数字类型，如果不起作用，则报告验证错误。 在输入中搜索攻击模式非常容易，例如“--”。 假设来自用户的所有输入都是敌意的。
Input sanitation is not something you want to half-ass. Use your whole ass. Use regular expressions on text fields. TryCast your numerics to the proper numeric type, and report a validation error if it doesn't work. It is very easy to search for attack patterns in your input, such as ' --. Assume all input from the user is hostile.
正如你似乎知道的那样，无论如何这都是一个坏主意。
像这样转义字符串中的引号怎么样： \'
您的替换将导致： \''
如果反斜杠转义第一个引号，则第二个引号结束了字符串。
It's a bad idea anyway as you seem to know.
What about something like escaping the quote in string like this: \'
Your replace would result in: \''
If the backslash escapes the first quote, then the second quote has ended the string.
简单的答案：有时会有效，但并非总是有效。
您希望对您所做的所有事情使用白名单验证，但我意识到这并不总是可行，因此您被迫使用最佳猜测黑名单。 同样，您希望在所有中使用参数化存储过程，但同样，这并不总是可行，因此您被迫将 sp_execute 与参数一起使用。
您可以想出一些方法来绕过任何可用的黑名单（还有一些白名单）。
一篇不错的文章在这里： http://www.owasp.org/index.php/Top_10_2007 -A2
如果您需要这样做作为一种快速修复，以便有时间进行真正的修复，那就这样做吧。 但不要以为你就安全了。
Simple answer: It will work sometimes, but not all the time.
You want to use white-list validation on everything you do, but I realize that's not always possible, so you're forced to go with the best guess blacklist. Likewise, you want to use parametrized stored procs in everything, but once again, that's not always possible, so you're forced to use sp_execute with parameters.
There are ways around any usable blacklist you can come up with (and some whitelists too).
A decent writeup is here: http://www.owasp.org/index.php/Top_10_2007-A2
If you need to do this as a quick fix to give you time to get a real one in place, do it. But don't think you're safe.
有两种方法可以做到这一点，没有例外，可以避免 SQL 注入； 准备好的语句或参数化的存储过程。
There are two ways to do it, no exceptions, to be safe from SQL-injections; prepared statements or prameterized stored procedures.
如果您有可用的参数化查询，您应该始终使用它们。 只需要一个查询漏网，您的数据库就会面临风险。
If you have parameterised queries available you should be using them at all times. All it takes is for one query to slip through the net and your DB is at risk.
帕特里克，您是否在所有输入（甚至数字输入）周围添加单引号？ 如果您有数字输入，但没有在其周围加上单引号，那么您就有风险。
Patrick, are you adding single quotes around ALL input, even numeric input? If you have numeric input, but are not putting the single quotes around it, then you have an exposure.
是的，这应该可以正常工作，直到有人运行 SET QUOTED_IDENTIFIER OFF 并且对你使用双引号。
编辑：这并不像不允许恶意用户关闭带引号的标识符那么简单：
有很多方法可以在您不知情的情况下关闭 QUOTED_IDENTIFIER。 诚然，这不是您正在寻找的确凿证据，但它是一个相当大的攻击面。 当然，如果您也转义了双引号 - 那么我们又回到了开始的地方。 ;)
Yeah, that should work right up until someone runs SET QUOTED_IDENTIFIER OFF and uses a double quote on you.
Edit: It isn't as simple as not allowing the malicious user to turn off quoted identifiers:
There's a lot of ways QUOTED_IDENTIFIER could be off without you necessarily knowing it. Admittedly - this isn't the smoking gun exploit you're looking for, but it's a pretty big attack surface. Of course, if you also escaped double quotes - then we're back where we started. ;)
如果出现以下情况，您的防御将会失败：
（在后一种情况下，它必须是仅在完成替换后才扩展的内容）
Your defence would fail if:
(in the latter case, it would have to be something which were expanded only after you've done your replace)
所有对用户输入的清理将是多么丑陋的代码啊！ 然后是用于 SQL 语句的笨重的 StringBuilder。 准备好的语句方法会产生更清晰的代码，并且 SQL 注入的好处是一个非常好的补充。
另外为什么要重新发明轮子呢？
What ugly code all that sanitisation of user input would be! Then the clunky StringBuilder for the SQL statement. The prepared statement method results in much cleaner code, and the SQL Injection benefits are a really nice addition.
Also why reinvent the wheel?
与其将一个单引号更改为（看起来像）两个单引号，为什么不将其更改为撇号、引号或完全删除它呢？
无论哪种方式，这都有点混乱......尤其是当你合法地拥有可能使用单引号的东西（如名称）时......
注意：你的方法还假设每个在你的应用程序上工作的人总是记得在输入之前对其进行清理。数据库，这在大多数情况下可能是不现实的。
Rather than changing a single quote to (what looks like) two single quotes, why not just change it to an apostrophe, a quote, or remove it entirely?
Either way, it's a bit of a kludge... especially when you legitimately have things (like names) which may use single quotes...
NOTE: Your method also assumes everyone working on your app always remembers to sanitize input before it hits the database, which probably isn't realistic most of the time.
我不确定你的情况，但我刚刚在Mysql中遇到一个情况，
Replace(value, "'", "''")不仅不能防止SQL注入，而且<导致注射。如果输入以
\'结尾，则无需替换即可，但替换尾随'时，字符串引号末尾之前的\会导致SQL 错误。I'm not sure about your case, but I just encountered a case in Mysql that
Replace(value, "'", "''")not only can't prevent SQL injection, but also causes the injection.if an input ended with
\', it's OK without replace, but when replacing the trailing', the\before end of string quote causes the SQL error.虽然您可能会找到适用于字符串的解决方案，但对于数字谓词，您还需要确保它们仅传递数字（简单检查是否可以将其解析为 int/double/decimal？）。
这是很多额外的工作。
While you might find a solution that works for strings, for numerical predicates you need to also make sure they're only passing in numbers (simple check is can it be parsed as int/double/decimal?).
It's a lot of extra work.
它可能有效，但对我来说似乎有点做作。 我建议通过针对正则表达式进行测试来验证每个字符串是否有效。
It might work, but it seems a little hokey to me. I'd recommend verifing that each string is valid by testing it against a regular expression instead.
是的，你可以，如果......
在研究了这个主题之后，我认为按照你建议的方式清理的输入是安全的，但只有在这些规则下：
你永远不允许来自用户的字符串值变成字符串文字以外的任何东西（即避免提供配置选项：“在此处输入其他 SQL 列名称/表达式：”）。 字符串以外的值类型（数字、日期等）：将它们转换为其本机数据类型，并为每种数据类型的 SQL 文字提供例程。
您使用
nvarchar/nchar列（并使用N前缀字符串文字）或限制进入的值时varchar/char列仅转换为 ASCII 字符（例如，创建 SQL 语句时抛出异常）您始终验证值长度以适合实际列长度（如果更长，则抛出异常）
您确保
SET QUOTED_IDENTIFIER始终ON也要遵守这 4 点，你应该安全了。 如果您违反其中任何一条，就会出现 SQL 注入的方式。
Yes, you can, if...
After studying the topic, I think input sanitized as you suggested is safe, but only under these rules:
you never allow string values coming from users to become anything else than string literals (i.e. avoid giving configuration option: "Enter additional SQL column names/expressions here:"). Value types other than strings (numbers, dates, ...): convert them to their native data types and provide a routine for SQL literal from each data type.
you either use
nvarchar/ncharcolumns (and prefix string literals withN) OR limit values going intovarchar/charcolumns to ASCII characters only (e.g. throw exception when creating SQL statement)you always validate value length to fit actual column length (throw exception if longer)
you ensure that
SET QUOTED_IDENTIFIERis alwaysONComplying with these 4 points, you should be safe. If you violate any of them, a way for SQL injection opens.