我可以通过转义单引号和用单引号包围用户输入来防止 SQL 注入吗? [英] Can I protect against SQL injection by escaping single-quote and surrounding user input with single-quotes?

问题描述

我意识到参数化 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.

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

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.

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

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 注入攻击.一些人建议反斜杠会转义一个单引号并让另一个结束字符串，这样字符串的其余部分将作为 SQL 命令的一部分执行，我意识到这种方法可以将 SQL 注入到MySQL 数据库，但在 SQL Server 2000 中(我已经找到)转义单引号的唯一方法是使用另一个单引号；反斜杠不行.

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.

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

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.

推荐答案

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

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(披露，最后一个是我的；))
• https://www.owasp.org/images/d/d4/OWASP_IL_2007_SQL_Smuggling.pdf(基于之前的论文，现已不再可用)

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

重点是，您所做的任何黑名单(以及过于宽松的白名单)都可以绕过.我论文的最后一个链接显示了甚至可以绕过引号转义的情况.

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?

正确的做法:

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

这篇关于我可以通过转义单引号和用单引号包围用户输入来防止 SQL 注入吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！