php PDO->query() 和 PDO->exec() 的区别

Difference between PDO->query() and PDO->exec()

提问by Bailey Parker

let references to PDO equal a PDO object and not PDO itself--

让对 PDO 的引用等于 PDO 对象而不是 PDO 本身——

I see herethat there is both PDO->query()and PDO->exec(). In the page that was linked, it appears that PDO->query();is used for SELECTstatements ONLY, and PDO->exec()is used for UPDATE,INSERT,DELETEstatements. Now, I am brand new to PDO, so I'm not sure what is going on in terms of using it quite yet, so I would appreciate an explanation on why to use the different methods, and why there are different methods.

我看到这里同时存在PDO->query()和PDO->exec()。在链接的页面中，它似乎仅PDO->query();用于SELECT语句，并且PDO->exec()用于UPDATE, INSERT,DELETE语句。现在，我是全新的，以PDO，所以我不知道什么是使用它的条款上会相当尚未，所以我将不胜感激为什么要使用不同的方法解释，为什么有不同的方法。

回答by Your Common Sense

Despite of whatever theoretical difference, none of these functions should be used anyway - so, there is nothing to concern of.

尽管有任何理论上的差异，但无论如何都不应该使用这些函数 - 所以，没有什么可担心的。

The only reason of using PDO is support for prepared statements, but none of these functions offers it. So, they shouldn't be used.

使用 PDO 的唯一原因是支持预处理语句，但这些函数都没有提供它。所以，它们不应该被使用。

Use prepare()/execute()instead, especiallyfor UPDATE,INSERT,DELETE statements.

使用prepare()/execute()替代，特别是对于UPDATE，INSERT，DELETE语句。

Please note that although prepared statements are widely advertised as a security measure, it is only to attract people's attention. But their realpurpose is proper query formatting. Which gives you security too - as properly formatted query cannot be injected as well - just as side effect. But again - formatting is a primary goal, just because even innocent data may cause a query error if not formatted properly.

请注意，虽然准备好的声明被广泛宣传为一种安全措施，但它只是为了引起人们的注意。但它们的真正目的是正确的查询格式。这也为您提供了安全性 - 因为格式正确的查询也不能被注入 - 就像副作用一样。但同样 - 格式化是一个主要目标，因为如果格式不正确，即使是无辜的数据也可能导致查询错误。

EDIT: Please note that execute()returns only TRUEor FALSEto indicate success of the operation. For other information, such as the number of records affected by an UPDATE, methods such as rowCount()are provided. See the docs.

编辑：请注意execute()仅返回TRUE或FALSE表示操作成功。对于其他信息，例如受 影响的记录数，提供了UPDATE诸如此类的方法rowCount()。请参阅文档。

回答by Bailey Parker

Look at the official docs for PDO:

查看PDO的官方文档：

• PDO::exec()- "Execute an SQL statement and return the number of affected rows"
• PDO::query()- "Executes an SQL statement, returning a result set as a PDOStatement object"

• PDO::exec()- "执行一条 SQL 语句并返回受影响的行数"
• PDO::query()- “执行 SQL 语句，返回结果集作为 PDOStatement 对象”

Both functions execute the query, but exec()only returns the number of rows affected. This is useful for an UPDATEquery where nothing useful is returned and it is only useful to know if the proper number of rows were modified.

这两个函数都执行查询，但exec()只返回受影响的行数。这对于UPDATE没有返回任何有用信息的查询很有用，并且只有知道是否修改了正确的行数才有用。

回答by Mike

I made a flow chart to try to help you determine which you should be using for any given situation:

我制作了一个流程图来尝试帮助您确定在任何给定情况下应该使用哪个：

PDOStatement::prepare()combined with bound variables will:

PDOStatement::prepare()结合绑定变量将：

• prevent accidental syntax errors
• prevent SQL injection attacks
• make repeated queries with different values more efficient. Preparing a query sends only the query to the database server without the values. When you execute()the PDOStatementyou then send only the values without the query. Executing the same query 10 times with different values will be far more efficient with prepared statements.

• 防止意外的语法错误
• 防止 SQL 注入攻击
• 使具有不同值的重复查询更有效。准备查询仅将查询发送到数据库服务器，而没有值。当你execute()将PDOStatement你那么只发送值，而无需查询。对于准备好的语句，使用不同的值执行 10 次相同的查询会更有效。

You should never, under any circumstances, put user input directly into a query. However, if your query does not have values and you decide to use PDO::query()instead of PDO::exec(), or if you use PDOStatement::prepare()instead of either of the other two, it is not a security issue, but more of an efficiency issue.

在任何情况下，您都不应该将用户输入直接放入查询中。但是，如果您的查询没有值并且您决定使用PDO::query()而不是PDO::exec()，或者如果您使用PDOStatement::prepare()而不是其他两个中的任何一个，则这不是安全问题，而是效率问题。

Common objections:

常见反对意见：

"But what if I pull the variables from the database? If I used prepared statements to put it in the database then it's safe to put them straight into other queries WITHOUT prepared statements, right?"

“但是，如果我从数据库中提取变量呢？如果我使用准备好的语句将其放入数据库中，那么将它们直接放入其他查询中而不使用准备好的语句是安全的，对吗？“

Definitely not. Just because you used a prepared statement when inserting into a database does not sanitize it for future queries. You will need to use prepared statements again when using those values in subsequent queries.

当然不。仅仅因为您在插入数据库时​​使用了准备好的语句并不会为将来的查询清理它。在后续查询中使用这些值时，您将需要再次使用准备好的语句。

"But what if I just manually hard-code a string into a query, then it's safe to put them straight into other queries WITHOUT prepared statements, right?"

“但是，如果我只是手动硬编码字符串转换成一个查询，那么它的安全要放什么东西他们直接进入其他查询，而无需预处理语句，对吧？”

Yes, it's safe... for now. Maybe today I'm hard-coding it. Tomorrow I pull it from the database, and after that I allow users to edit that field in the database. But will I remember to go back and update the query to use prepared statements to guarantee integrity? Probably not. Prepared statements are just good practice any time you are sending values in your query.

是的，它是安全的……暂时。也许今天我正在对其进行硬编码。明天我从数据库中提取它，然后我允许用户在数据库中编辑该字段。但是我会记得回去更新查询以使用准备好的语句来保证完整性吗？可能不是。任何时候在查询中发送值时，准备好的语句都是很好的做法。

"Can I ever put values directly into a query?"

“我可以将值直接放入查询中吗？”

The only time I may put values directly into a query would be, for example, sending boolean values (1or 0), an empty string, or NULL. In any of these cases, the value would be hard-coded right into the query, not as a variable.

例如，我可以将值直接放入查询的唯一时间是发送布尔值（1或0）、空字符串或NULL. 在任何这些情况下，该值都将硬编码到查询中，而不是作为变量。