我可以通过转义单引号和用单引号包围用户输入来防止 SQL 注入吗？

Can I protect against SQL injection by escaping single-quote and surrounding user input with single-quotes?

提问by Patrick

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:

我意识到参数化 SQL 查询是在构建包含用户输入的查询时清理用户输入的最佳方式，但我想知道获取用户输入并转义任何单引号并用单引号包围整个字符串有什么问题。这是代码：

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.

我们正在使用 Microsoft SQL Server 2000，我相信单引号是唯一的字符串分隔符，也是转义字符串分隔符的唯一方法，因此无法执行用户输入的任何内容。

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.

我看不出有什么方法可以对此发起 SQL 注入攻击，但我意识到，如果这在我看来是防弹的，那么其他人早就想到了，这将是常见的做法。

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.

这段代码有什么问题？有没有办法通过这种清理技术来获得 SQL 注入攻击？利用此技术的示例用户输入将非常有帮助。

---

---

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.

我仍然不知道有什么方法可以有效地针对此代码发起 SQL 注入攻击。一些人建议反斜杠会转义一个单引号并让另一个结束字符串，这样字符串的其余部分将作为 SQL 命令的一部分执行，我意识到这种方法可以将 SQL 注入到MySQL 数据库，但在 SQL Server 2000 中（我已经找到）转义单引号的唯一方法是使用另一个单引号；反斜杠不会这样做。

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.

我知道有更好的方法来清理输入，但我真的更感兴趣的是了解为什么我上面提供的方法不起作用。如果有人知道针对这种清理方法发起 SQL 注入攻击的任何特定方法，我很乐意看到它。

采纳答案by AviD

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:

首先，这只是不好的做法。输入验证总是必要的，但它也总是不确定的。
更糟糕的是，黑名单验证总是有问题的，最好明确且严格地定义您接受的值/格式。诚然，这并不总是可能的——但在某种程度上，它必须始终完成。
关于该主题的一些研究论文：

• http://www.imperva.com/docs/WP_SQL_Injection_Protection_LK.pdf
• http://www.it-docs.net/ddata/4954.pdf(Disclosure, this last one was mine ;) )
• https://www.owasp.org/images/d/d4/OWASP_IL_2007_SQL_Smuggling.pdf(based on the previous paper, which is no longer available)

• http://www.imperva.com/docs/WP_SQL_Injection_Protection_LK.pdf
• http://www.it-docs.net/ddata/4954.pdf（披露，最后一个是我的；））
• https://www.owasp.org/images/d/d4/OWASP_IL_2007_SQL_Smuggling.pdf（基于之前的论文，已经不再可用）

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:

正确的做法：

• Whitelist validation: type, length, format or accepted values
• If you want to blacklist, go right ahead. Quote escaping is good, but within context of the other mitigations.
• Use Command and Parameter objects, to preparse and validate
• Call parameterized queries only.
• Better yet, use Stored Procedures exclusively.
• Avoid using dynamic SQL, and dont use string concatenation to build queries.
• If using SPs, you can also limit permissions in the database to executing the needed SPs only, and not access tables directly.
• you can also easily verify that the entire codebase only accesses the DB through SPs...

• 白名单验证：类型、长度、格式或接受的值
• 如果您想加入黑名单，请继续。报价转义是好的，但在其他缓解措施的范围内。
• 使用 Command 和 Parameter 对象来预解析和验证
• 仅调用参数化查询。
• 更好的是，专门使用存储过程。
• 避免使用动态 SQL，并且不要使用字符串连接来构建查询。
• 如果使用 SP，您还可以将数据库中的权限限制为仅执行所需的 SP，而不是直接访问表。
• 您还可以轻松验证整个代码库仅通过 SP 访问数据库...

回答by AviD

Okay, this response will relate to the update of the question:

好的，此回复将与问题的更新有关：

"If anyone knows of any specific way to mount a SQL injection attack against this sanitization method I would love to see it."

“如果有人知道针对这种清理方法发起 SQL 注入攻击的任何具体方法，我很乐意看到它。”

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

现在，除了 MySQL 反斜杠转义 - 并考虑到我们实际上是在谈论 MSSQL，实际上还有 3 种可能的方式仍然 SQL 注入您的代码

sSanitizedInput = "'" & Replace(sInput, "'", "''") & "'"

sSanitizedInput = "'" & Replace(sInput, "'", "''") & "'"

Take into account that these will not all be valid at all times, and are very dependant on your actual code around it:

考虑到这些并非在任何时候都有效，并且非常依赖于您周围的实际代码：

1. Second-order SQL Injection - if an SQL query is rebuilt based upon data retrieved from the database after escaping, the data is concatenated unescaped and may be indirectly SQL-injected. See
2. String truncation - (a bit more complicated) - Scenario is you have two fields, say a username and password, and the SQL concatenates both of them. And both fields (or just the first) has a hard limit on length. For instance, the username is limited to 20 characters. Say you have this code:

1. 二阶 SQL 注入 - 如果 SQL 查询是基于转义后从数据库中检索到的数据重建的，则数据将被未转义地连接起来，并且可能会被间接 SQL 注入。看
2. 字符串截断 - （有点复杂） - 场景是您有两个字段，比如用户名和密码，并且 SQL 将它们连接起来。并且这两个字段（或只是第一个）对长度都有严格的限制。例如，用户名限制为 20 个字符。假设你有这个代码：

username = left(Replace(sInput, "'", "''"), 20)

username = left(Replace(sInput, "'", "''"), 20)

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

然后你得到 - 是用户名，转义，然后修剪为 20 个字符。这里的问题 - 我会在第 20 个字符（例如在 19 a 之后）粘贴我的引号，并且您的转义引号将被修剪（在第 21 个字符中）。然后是 SQL

sSQL = "select * from USERS where username = '" + username + "'  and password = '" + password + "'"

combined with the aforementioned malformed username will result in the password already being outsidethe 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 lookslike 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.

结合上述格式错误的用户名将导致密码已经在引号之外，并且将直接包含有效负载。
3. Unicode Smuggling - 在某些情况下，可以传递一个看起来像引号的高级 unicode 字符，但不是- 直到它到达数据库，突然它是. 因为当您验证它时它不是引用，所以它会通过简单的...查看我之前的回复以了解更多详细信息，并链接到原始研究。

回答by Nick Johnson

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.

简而言之：永远不要逃避自己的查询。你肯定会出错。相反，请使用参数化查询，或者如果由于某种原因您不能这样做，请使用为您执行此操作的现有库。没有理由自己做。

回答by J?rn Jensen

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.

对“引用参数”过程发起攻击的一种方法是使用字符串截断。根据 MSDN，在 SQL Server 2000 SP4（和 SQL Server 2005 SP1）中，太长的字符串会被悄悄截断。

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.

当您引用一个字符串时，该字符串的大小会增加。每个撇号都会重复。然后，这可用于将部分 SQL 推送到缓冲区之外。所以你可以有效地修剪掉 where 子句的部分内容。

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)

我建议使用参数。总是。只是希望我可以在数据库中强制执行。作为副作用，您更有可能获得更好的缓存命中率，因为更多的语句看起来相同。（这在 Oracle 8 上当然是正确的）

回答by Pontus Gagge

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.)

我在处理“高级搜索”功能时使用了这种技术，其中从头开始构建查询是唯一可行的答案。（例如：允许用户基于对产品属性的无限约束来搜索产品，将列及其允许值显示为 GUI 控件，以降低用户的学习门槛。）

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).

AFAIK 本身就是安全的。然而，正如另一位回答者指出的那样，您可能还需要处理退格转义（尽管在使用 ADO 或 ADO.NET 将查询传递给 SQL Server 时至少不需要 - 不能保证所有数据库或技术）。

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.

问题是你真的必须确定哪些字符串包含用户输入（总是潜在的恶意），哪些字符串是有效的 SQL 查询。陷阱之一是，如果您使用数据库中的值——这些值最初是用户提供的吗？如果是这样，他们也必须逃脱。我的答案是在构建 SQL 查询时尝试尽可能晚地清理（但不要晚！）。

However, in most cases, parameter binding is the way to go -- it's just simpler.

然而，在大多数情况下，参数绑定是可行的方法——它只是更简单。

回答by tom.dietrich

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.

输入卫生不是你想半途而废的东西。用你的整个屁股。在文本字段上使用正则表达式。尝试将您的数字转换为正确的数字类型，如果不起作用则报告验证错误。在您的输入中搜索攻击模式非常容易，例如“-”。假设来自用户的所有输入都是恶意的。

回答by Invalid Character

Simple answer: It will work sometimes, but not all the time. You want to use white-list validation on everythingyou 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.

简单的回答：它有时会起作用，但不是一直有效。你想对你所做的一切使用白名单验证，但我意识到这并不总是可行的，所以你被迫使用最佳猜测黑名单。同样，您想在所有内容中使用参数化存储过程，但同样，这并不总是可能的，因此您不得不使用带有参数的 sp_execute。

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

一个体面的文章在这里：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.

如果您需要将其作为快速解决方案来让您有时间获得真正的解决方案，请执行此操作。但不要认为你是安全的。

回答by olle

There are two ways to do it, no exceptions, to be safe from SQL-injections; prepared statements or prameterized stored procedures.

有两种方法可以做到这一点，没有例外，可以避免 SQL 注入；准备好的语句或参数化的存储过程。

回答by WW.

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.

如果反斜杠转义了第一个引号，则第二个引号已结束字符串。

回答by Kev

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.

如果您有可用的参数化查询，则应始终使用它们。所需要的只是一个查询从网络中溜走，您的数据库就会处于危险之中。