CODEWITHSUNDEEP
phppdo
user1316498
user1316498

Reputation:

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

I see that there is both PDO::query() and PDO::exec(). In the page that was linked, it appears that PDO::query() is used for SELECT statements ONLY, and PDO->exec() is used for UPDATE,INSERT,DELETE statements. Why do these methods exist and when to use them?

Upvotes: 17

Views: 23457

Answers (4)

Dharman
Dharman

Reputation: 33242

Rule of the thumb: Use neither of them.

PDO::query() executes a prepared statement without any placeholders. If you try to execute a query with placeholders, then PDO will throw an error that parameters were not bound to the query. The only reason to use this method is to have cleaner code if you have SQL without any placeholders. Under the hood, it still calls prepare and execute so there's no performance benefit.

PDO::exec does not use prepared statement. Depending on the driver, the actual invocation might differ, but MySQL driver uses the textual protocol and does not check for incoming data from the server. This means that you can't bind any data to this SQL and you can't receive any data back either. It virtually limits its usefulness to 0. The only time you might want to use it is if you want to execute static SQL that doesn't produce any result set, such as CREATE TABLE.

Use PDO::prepare() and execute() whenever possible.

Upvotes: 2

Your Common Sense
Your Common Sense

Reputation: 157828

Regardless of whatever theoretical difference, neither PDO::query() nor PDO::exec() should be used anyway. These functions don't let you bind parameters to the prepared statement and should never be used.

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

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

Upvotes: 18

Mike
Mike

Reputation: 24363

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

enter image description here

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

  • 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 PDOStatement you 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.

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.

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 (1 or 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.

Upvotes: 7

Bailey Parker
Bailey Parker

Reputation: 15905

Look at the official docs for 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"

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

Upvotes: 15

Related Questions