参数化语句可以阻止所有 SQL 注入吗？

Can parameterized statement stop all SQL injection?

提问by iceagle

If yes, why are there still so many successful SQL injections? Just because some developers are too dumb to use parameterized statements?

如果是，为什么还有这么多成功的 SQL 注入？仅仅因为一些开发人员太笨，不会使用参数化语句？

采纳答案by Mike

The links that I have posted in my comments to the question explain the problem very well. I've summarised my feelings on why the problem persists, below:

我在对问题的评论中发布的链接很好地解释了这个问题。我总结了我对为什么问题仍然存在的感受，如下：

1. Those just starting out may have no awareness of SQL injection.

2. Some are aware of SQL injection, but think that escaping is the (only?) solution. If you do a quick Google search for php mysql query, the first page that appears is the mysql_querypage, on which there is an example that shows interpolating escaped user input into a query. There's no mention (at least not that I can see) of using prepared statements instead. As others have said, there are so many tutorials out there that use parameter interpolation, that it's not really surprising how often it is still used.

3. A lack of understanding of how parameterized statements work. Some think that it is just a fancy means of escaping values.

4. Others are aware of parameterized statements, but don't use them because they have heard that they are too slow. I suspect that many people have heard how incredibly slow paramterized statements are, but have not actually done any testing of their own. As Bill Karwin pointed out in his talk, the difference in performance should rarely be used as a factor when considering the use of prepared statements. The benefits of prepare once, execute many, often appear to be forgotten, as do the improvements in security and code maintainability.

5. Some use parameterized statements everywhere, but with interpolation of unchecked values such as table and columns names, keywords and conditional operators. Dynamic searches, such as those that allow users to specify a number of different search fields, comparison conditions and sort order, are prime examples of this.

6. False sense of security when using an ORM. ORMs still allow interpolation of SQL statement parts - see 5.

7. Programming is a big and complex subject, database management is a big and complex subject, security is a big and complex subject. Developing a secure database application is not easy - even experienced developers can get caught out.

8. Many of the answers on stackoverflow don't help. When people write questions that use dynamic SQL and parameter interpolation, there is often a lack of responses that suggest using parameterized statements instead. On a few occasions, I've had people rebut my suggestion to use prepared statements - usually because of the perceived unacceptable performance overhead. I seriously doubt that those asking most of these questions are in a position where the extra few milliseconds taken to prepare a parameterized statement will have a catastrophic effect on their application.

1. 那些刚开始的人可能没有意识到 SQL 注入。

2. 有些人知道 SQL 注入，但认为转义是（唯一的？）解决方案。如果您在 Google 上快速搜索php mysql query，则出现的mysql_query第一个页面是该页面，该页面上有一个示例显示将转义的用户输入插入查询中。没有提到（至少不是我能看到的）使用准备好的语句。正如其他人所说，有很多使用参数插值的教程，所以它仍然被使用的频率并不奇怪。

3. 对参数化语句的工作原理缺乏了解。有些人认为这只是一种逃避价值的奇特手段。

4. 其他人知道参数化语句，但没有使用它们，因为他们听说它们太慢了。我怀疑很多人都听说过参数化语句是多么慢得令人难以置信，但实际上并没有对他们自己进行任何测试。正如 Bill Karwin 在他的演讲中指出的那样，在考虑使用准备好的语句时，性能差异很少被用作一个因素。一次准备，多次执行的好处似乎经常被遗忘，安全性和代码可维护性的改进也是如此。

5. 有些在任何地方都使用参数化语句，但会插入未经检查的值，例如表名和列名、关键字和条件运算符。动态搜索，例如那些允许用户指定多个不同搜索字段、比较条件和排序顺序的搜索，就是最好的例子。

6. 使用 ORM 时的虚假安全感。ORM 仍然允许插入 SQL 语句部分 - 参见 5。

7. 编程是一门大而复杂的学科，数据库管理是一门大而复杂的学科，安全是一门大而复杂的学科。开发一个安全的数据库应用程序并不容易——即使是有经验的开发人员也会被发现。

8. stackoverflow 上的许多答案都没有帮助。当人们编写使用动态 SQL 和参数插值的问题时，通常缺乏建议使用参数化语句的答复。有几次，有人反对我使用准备好的语句的建议——通常是因为认为不可接受的性能开销。我严重怀疑那些问大多数这些问题的人是否处于准备参数化语句所花费的额外几毫秒会对他们的应用程序产生灾难性影响的位置。

回答by Josip Ivic

When articles talk about parameterized queries stopping SQL attacks they don't really explain why, it's often a case of "It does, so don't ask why" -- possibly because they don't know themselves. A sure sign of a bad educator is one that can't admit they don't know something. But I digress. When I say I found it totally understandable to be confused is simple. Imagine a dynamic SQL query

当文章谈论参数化查询阻止 SQL 攻击时，他们并没有真正解释原因，通常是“确实如此，所以不要问为什么”——可能是因为他们不了解自己。一个糟糕的教育者的一个明确标志是不能承认他们不知道一些事情。但我离题了。当我说我发现混淆是完全可以理解的很简单。想象一个动态 SQL 查询

sqlQuery='SELECT * FROM custTable WHERE User=' + Username + ' AND Pass=' + password

so a simple sql injection would be just to put the Username in as ' OR 1=1-- This would effectively make the sql query:

所以一个简单的 sql 注入只是将用户名输入为 ' OR 1=1-- 这将有效地进行 sql 查询：

sqlQuery='SELECT * FROM custTable WHERE User='' OR 1=1-- ' AND PASS=' + password

This says select all customers where they're username is blank ('') or 1=1, which is a boolean, equating to true. Then it uses -- to comment out the rest of the query. So this will just print out all the customer table, or do whatever you want with it, if logging in, it will log in with the first user's privileges, which can often be the administrator.

这表示选择用户名为空 ('') 或 1=1 的所有客户，这是一个布尔值，等于 true。然后它使用 -- 注释掉查询的其余部分。所以这只会打印出所有的客户表，或者用它做任何你想做的事情，如果登录，它将以第一个用户的权限登录，通常可以是管理员。

Now parameterized queries do it differently, with code like:

现在参数化查询的做法有所不同，代码如下：

sqlQuery='SELECT * FROM custTable WHERE User=? AND Pass=?'

parameters.add("User", username)
parameters.add("Pass", password)

where username and password are variables pointing to the associated inputted username and password

其中用户名和密码是指向相关输入用户名和密码的变量

Now at this point, you may be thinking, this doesn't change anything at all. Surely you could still just put into the username field something like Nobody OR 1=1'--, effectively making the query:

现在，您可能会想，这根本不会改变任何事情。当然，您仍然可以在用户名字段中输入类似Nobody OR 1=1'-- 的内容，从而有效地进行查询：

sqlQuery='SELECT * FROM custTable WHERE User=Nobody OR 1=1'-- AND Pass=?'

And this would seem like a valid argument. But, you would be wrong.

这似乎是一个有效的论点。但是，你错了。

The way parameterized queries work, is that the sqlQuery is sent as a query, and the database knows exactly what this query will do, and only then will it insert the username and passwords merely as values. This means they cannot effect the query, because the database already knows what the query will do. So in this case it would look for a username of "Nobody OR 1=1'--" and a blank password, which should come up false.

参数化查询的工作方式是将 sqlQuery 作为查询发送，数据库确切地知道该查询将执行什么操作，然后才将用户名和密码仅作为值插入。这意味着它们不能影响查询，因为数据库已经知道查询将做什么。所以在这种情况下，它会寻找“Nobody OR 1=1'--”的用户名和一个空白密码，这应该是错误的。

This isn't a complete solution though, and input validation will still need to be done, since this won't effect other problems, such as XSS attacks, as you could still put javascript into the database. Then if this is read out onto a page, it would display it as normal javascript, depending on any output validation. So really the best thing to do is still use input validation, but using parameterized queries or stored procedures to stop any SQL attacks.

但这不是一个完整的解决方案，输入验证仍然需要完成，因为这不会影响其他问题，例如 XSS 攻击，因为您仍然可以将 javascript 放入数据库中。然后，如果将其读出到页面上，它将根据任何输出验证将其显示为普通的 javascript。所以真正最好的做法是仍然使用输入验证，但使用参数化查询或存储过程来阻止任何 SQL 攻击。

回答by Andreas Venieris

Well good question. The answer is more stochastic than deterministic and I will try to explain my view, using a small example.

好问题。答案比确定性更随机，我将尝试使用一个小例子来解释我的观点。

There many references on the net that suggest us to use parameters in our queries or to use stored procedure with parameters in order to avoid SQL Injection (SQLi). I will show you that stored procedures (for instance) is not the magic stick against SQLi. The responsibility still remains on the programmer.

网上有很多参考资料建议我们在查询中使用参数或使用带参数的存储过程以避免 SQL 注入 (SQLi)。我将向您展示存储过程（例如）并不是对抗 SQLi 的魔法棒。责任仍然在程序员身上。

Consider the following SQL Server Stored Procedure that will get the user row from a table 'Users':

考虑以下 SQL Server 存储过程，它将从表“用户”中获取用户行：

create procedure getUser
 @name varchar(20)
,@pass varchar(20)
as
declare @sql as nvarchar(512)
set @sql = 'select usrID, usrUName, usrFullName, usrRoleID '+
           'from Users '+
           'where usrUName = '''+@name+''' and usrPass = '''+@pass+''''
execute(@sql)

You can get the results by passing as parameters the username and the password. Supposing the password is in free text (just for simplicity of this example) a normal call would be:

您可以通过将用户名和密码作为参数传递来获取结果。假设密码是自由文本（只是为了简单起见），正常调用将是：

DECLARE @RC int
DECLARE @name varchar(20)
DECLARE @pass varchar(20)

EXECUTE @RC = [dbo].[getUser] 
   @name = 'admin'
  ,@pass = '!@Th1siSTheP@ssw0rd!!'
GO

But here we have a bad programming technique used by the programmer inside the stored procedure, so an attacker can execute the following:

但是这里我们有一个程序员在存储过程中使用的糟糕的编程技术，因此攻击者可以执行以下操作：

DECLARE @RC int
DECLARE @name varchar(20)
DECLARE @pass varchar(20)

EXECUTE @RC = [TestDB].[dbo].[getUser] 
   @name = 'admin'
  ,@pass = 'any'' OR 1=1 --'
GO

The above parameters will be passed as arguments to the stored procedure and the SQL command that finally will be executed is:

上述参数将作为参数传递给存储过程，最终执行的 SQL 命令为：

select usrID, usrUName, usrFullName, usrRoleID 
from Users 
where usrUName = 'admin' and usrPass = 'any' OR 1=1 --'

..which will get all rows back from users

..这将从用户那里取回所有行

The problem here is that even we follow the principle "Create a stored procedure and pass the fields to search as parameters" the SQLi is still performed. This is because we just copy our bad programming practice inside the stored procedure. The solution to the problem is to rewrite our Stored Procedure as follows:

这里的问题是，即使我们遵循“创建存储过程并将字段作为参数传递给搜索”的原则，仍然会执行 SQLi。这是因为我们只是在存储过程中复制了我们的不良编程习惯。问题的解决方法是将我们的存储过程重写如下：

alter procedure getUser
 @name varchar(20)
,@pass varchar(20)
as
select usrID, usrUName, usrFullName, usrRoleID 
from Users 
where usrUName = @name and usrPass = @pass

What I am trying to say is that the developers must learn first what an SQLi attack is and how can be performed and then to safeguard their code accordingly. Blindly following 'best practices' is not always the safer way... and maybe this is why we have so many 'best practices'- failures!

我想说的是，开发人员必须首先了解什么是 SQLi 攻击以及如何执行，然后相应地保护他们的代码。盲目遵循“最佳实践”并不总是更安全的方式……也许这就是为什么我们有这么多“最佳实践”——失败！

回答by kelunik

Yes, the use of prepared statements stops all SQL injections, at least in theory. In practice, parameterized statements may not be real prepared statements, e.g. PDOin PHP emulates them by default so it's open to an edge case attack.

是的，使用准备好的语句会停止所有 SQL 注入，至少在理论上是这样。在实践中，参数化语句可能不是真正的准备好的语句，例如PDO在 PHP 中默认模拟它们，因此它容易受到边缘情况攻击。

If you're using real prepared statements, everything is safe. Well, at least as long as you don't concatenate unsafe SQL into your query as reaction to not being able to prepare table names for example.

如果您使用的是真正准备好的语句，那么一切都是安全的。好吧，至少只要您不将不安全的 SQL 连接到您的查询中，作为对无法准备表名的反应。

If yes, why are there still so many successful SQL injections? Just because some developers are too dumb to use parameterized statements?

如果是，为什么还有这么多成功的 SQL 注入？仅仅因为一些开发人员太笨，不会使用参数化语句？

Yes, education is the main point here, and legacy code bases. Many tutorials use escaping and those can't be easily removed from the web, unfortunately.

是的，教育和遗留代码库是这里的重点。不幸的是，许多教程使用转义，并且不能轻易从网络中删除。

回答by Derek

I avoid absolutes in programming; there is always an exception. I highly recommend stored procedures and command objects. A majority of my back ground is with SQL Server, but I do play with MySql from time to time. There are many advantages to stored procedures including cached query plans; yes, this can be accomplished with parameters and inline SQL, but that opens up more possibilities for injection attacks and doesn't help with separation of concerns. For me it's also much easier to secure a database as my applications generally only have execute permission for said stored procedures. Without direct table/view access it's much more difficult to inject anything. If the applications user is compromised one only has permission to execute exactly what was pre-defined.

我在编程中避免绝对；总有例外。我强烈推荐存储过程和命令对象。我的大部分背景是使用 SQL Server，但我确实不时使用 MySql。存储过程有很多优点，包括缓存查询计划；是的，这可以通过参数和内联 SQL 来完成，但这为注入攻击开辟了更多可能性，并且无助于关注点分离。对我来说，保护数据库也容易得多，因为我的应用程序通常只有上述存储过程的执行权限。如果没有直接的表/视图访问，注入任何东西都困难得多。如果应用程序用户受到威胁，则用户只有执行预定义内容的权限。

My two cents.

我的两分钱。

回答by evil otto

Because most code isn't written with security in mind, and management, given a choice between adding features (especially something visible that can be sold) and security/stability/reliability (which is a much harder sell) they will almost invariably choose the former. Security is only a concern when it becomes a problem.

因为大多数代码都没有考虑到安全性和管理，如果在添加功能（特别是可以出售的可见的东西）和安全性/稳定性/可靠性（这是一个更难卖的）之间做出选择，他们几乎总是会选择以前的。只有当它成为问题时，安全才会成为一个问题。

回答by Markus Winand

I wouldn't say "dumb".

我不会说“愚蠢”。

I think the tutorials are the problem. Most SQL tutorials, books, whatever explain SQL with inlined values, not mentioning bind parameters at all. People learning from these tutorials don't have a chance to learn it right.

我认为教程是问题。大多数 SQL 教程、书籍，无论是用内联值解释 SQL 的任何内容，都根本没有提到绑定参数。从这些教程中学习的人没有机会正确学习。

回答by TomS

First my answer to your first question: Yes, as far as I know, by using parameterized queries, SQL injections will not be possible anymore. As to your following questions, I am not sure and can only give you my opinion on the reasons:

首先我回答你的第一个问题：是的，据我所知，通过使用参数化查询，SQL注入将不再可能。对于你的以下问题，我不确定，只能就原因给你我的看法：

I think it's easier to "just" write the SQL query string by concatenate some different parts (maybe even dependent on some logical checks) together with the values to be inserted. It's just creating the query and executing it. Another advantage is that you can print (echo, output or whatever) the sql query string and then use this string for a manual query to the database engine.

我认为通过将一些不同的部分（甚至可能依赖于某些逻辑检查）与要插入的值连接起来，“只是”编写 SQL 查询字符串更容易。它只是创建查询并执行它。另一个优点是您可以打印（回显、输出或其他）sql 查询字符串，然后使用此字符串对数据库引擎进行手动查询。

When working with prepared statements, you always have at least one step more: You have to build your query (including the parameters, of course) You have to prepare the query on the server You have to bind the parameters to the actual values you want to use for your query You have to execute the query.

使用准备好的语句时，您总是至少要多做一步：您必须构建查询（当然包括参数）您必须在服务器上准备查询您必须将参数绑定到您想要的实际值用于您的查询 您必须执行查询。

That's somewhat more work (and not so straightforward to program) especially for some "quick and dirty" jobs which often prove to be very long-lived...

这需要更多的工作（并且编程起来并不那么简单），尤其是对于一些通常被证明是非常长寿的“快速而肮脏”的工作......

Best regards,

此致，

Box

盒子

回答by Your Common Sense

Can parameterized statement stop all SQL injection?

参数化语句可以阻止所有 SQL 注入吗？

Yes, as long as your database driver offers a placeholder for the every possible SQL literal.Most prepared statement drivers don't. Say, you'd never find a placeholder for a field name or for an array of values. Which will make a developer to fall back into tailoring a query by hand, using concatenation and manual formatting. With predicted outcome.

是的，只要您的数据库驱动程序为每个可能的 SQL 文字提供占位符。大多数准备好的语句驱动程序没有。比如说，您永远找不到字段名称或值数组的占位符。这将使开发人员退回到手动定制查询，使用串联和手动格式化。与预测的结果。

That's why I made my Mysql wrapper for PHP that supports most of literals that can be added to the query dynamically, including arrays and identifiers.

这就是我为 PHP 制作 Mysql 包装器的原因，它支持大多数可以动态添加到查询中的文字，包括数组和标识符。

If yes, why are there still so many successful SQL injections? Just because some developers are too dumb to use parameterized statements?

如果是，为什么还有这么多成功的 SQL 注入？仅仅因为一些开发人员太笨，不会使用参数化语句？

As you can see, in reality it's just impossible to have allyour queries parameterized, even if you're not dumb.

如您所见，实际上，即使您不笨，也无法将所有查询参数化。

回答by Fahad Hussain

To protect your application from SQL injection, perform the following steps:

要保护您的应用程序免受 SQL 注入，请执行以下步骤：

Step 1. Constrain input. Step 2. Use parameters with stored procedures. Step 3. Use parameters with dynamic SQL.

步骤 1. 约束输入。步骤 2. 在存储过程中使用参数。步骤 3. 对动态 SQL 使用参数。

Refer to http://msdn.microsoft.com/en-us/library/ff648339.aspx

请参阅http://msdn.microsoft.com/en-us/library/ff648339.aspx