Pdo::Exec() or Pdo::Query()

PDO::exec() or PDO::query()?

When using PDO::EXEC the result returned is not of an PDOStatement but an integer of the rows affected.

When using PDO::QUERY the result returned is a PDOStatement.

So the answer is it depends on what you need to do with the data, if you need to run query and not do anything with the results, then you should use exec to execute the query, otherwise if you need the number of rows, the data returned you should use pdo::query and then use the results returned by the call.

• http://www.php.net/manual/en/pdo.query.php
• http://php.net/manual/en/pdo.exec.php

---

in regards to the bug there are several work around that you can take

• Install PDO_MYSQL
• Replace MYSQL_ATTR_INIT_COMMAND with 1002
• Update your PHP To the latest stable release where it has been passed and patched.

the second issue may have some issues on 64bit's OS's and Some windows configurations.

Bug Information: http://bugs.php.net/bug.php?id=47224

When to use PDO exec() vs execute() vs query()

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

The reason we must use PDOStatement::prepare() and bound variables for user data is to prevent SQL injection attacks. You should never put user input directly into a query. In fact, this is just personal preference, but even if I'm hard-coding a value into a query, I will typically use PDOStatement::prepare() anyway. 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? Probably not. Binding the variables to the query will also prevent accidental syntax errors in the query as well.

The reason to use PDOStatement::prepare() when reusing the query with different variables is because when you execute this, it sends the prepared statement to the database server, but not the values. When you execute() the PDOStatement you then send the values to the database server. So for subsequent queries, all you need to send are the values and not the whole query.

However, if 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.

P.S. Sorry for the mouse.

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

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.

PDO::exec or PDO::execute?

Although both methods have a similar naming (exec,execute) they're meant to be used in different scenarios:

1. exec is used to get the number of affected rows.

   /**
    * (PHP 5 >= 5.1.0, PECL pdo >= 0.1.0)<br/>
    * Execute an SQL statement and return the number of affected rows
    * @link http://php.net/manual/en/pdo.exec.php
    * @param string $statement <p>
    * The SQL statement to prepare and execute.
    * </p>
    * <p>
    * Data inside the query should be properly escaped.
    * </p>
    * @return int <b>PDO::exec</b> returns the number of rows that were modified
    * or deleted by the SQL statement you issued. If no rows were affected,
    * <b>PDO::exec</b> returns 0.
    * </p>
    * This function may
    * return Boolean <b>FALSE</b>, but may also return a non-Boolean value which
    * evaluates to <b>FALSE</b>. Please read the section on Booleans for more
    * information. Use the ===
    * operator for testing the return value of this
    * function.
    * <p>
    * The following example incorrectly relies on the return value of
    * <b>PDO::exec</b>, wherein a statement that affected 0 rows
    * results in a call to <b>die</b>:
    * <code>
    * $db->exec() or die(print_r($db->errorInfo(), true));
    * </code>
    */
   public function exec ($statement) {}
   

   Example:

   $myQuery = "UPDATE users SET email = 'testing'";
   $affectedRows = $db->exec($myQuery);
   

2. execute is used when you want to pass an array of parameters to be bind in the query.

   /**
    * (PHP 5 >= 5.1.0, PECL pdo >= 0.1.0)<br/>
    * Executes a prepared statement
    * @link http://php.net/manual/en/pdostatement.execute.php
    * @param array $input_parameters [optional] <p>
    * An array of values with as many elements as there are bound
    * parameters in the SQL statement being executed.
    * All values are treated as <b>PDO::PARAM_STR</b>.
    * </p>
    * <p>
    * You cannot bind multiple values to a single parameter; for example,
    * you cannot bind two values to a single named parameter in an IN()
    * clause.
    * </p>
    * <p>
    * You cannot bind more values than specified; if more keys exist in
    * <i>input_parameters</i> than in the SQL specified
    * in the <b>PDO::prepare</b>, then the statement will
    * fail and an error is emitted.
    * </p>
    * @return bool <b>TRUE</b> on success or <b>FALSE</b> on failure.
    */
   public function execute (array $input_parameters = null) {}
   

   Example (of course this might be an UPDATE or DELETE query as well):

   $myQuery = 'SELECT * FROM users WHERE username = :username';
   $params = array(':username' => 'admin');
   $db->query($myQuery)->execute($params);
   

3. query returns a PDOStatement object

   /**
    * (PHP 5 >= 5.1.0, PECL pdo >= 0.2.0)<br/>
    * Executes an SQL statement, returning a result set as a PDOStatement object
    * @link http://php.net/manual/en/pdo.query.php
    * @param string $statement <p>
    * The SQL statement to prepare and execute.
    * </p>
    * <p>
    * Data inside the query should be properly escaped.
    * </p>
    * @return PDOStatement <b>PDO::query</b> returns a PDOStatement object, or <b>FALSE</b>
    * on failure.
    */
   public function query ($statement) {}
   

For more information you can visit the PHP Docs or in case you're using PHPStorm you can go though the source code of the PDO.php class.

Is there a difference between PDO::exec and PDO::query when using PDO::ATTR_PERSISTENT = true?

If you are using exec() for SELECT then you are already doing something wrong. This function (as is mentioned in the manual) does not fetch results from the database. It can only be used for queries, which produce no result set and which have no variable input. If a query produces a result then you need to fetch this result from MySQL using the same connection.

A persistent connection cannot be reused if it is still in use. This could happen for many reasons, one of which is unfetched result. MySQL will keep the connection open waiting for the client to perform some actions on the result set. Once the result is fetched in its entirety from MySQL server, it can then accept new queries.

This is not only a problem with persistent connections, because unfetched result set can be a problem if you use exec() one after the other with normal connections too.

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];
$pdo = new PDO("mysql:host=localhost;dbname=test;port=3307", "root", "", $options);

// The following will give:
// SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.
var_dump($pdo->exec("SELECT 1"));
var_dump($pdo->exec("SELECT 1"));

PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. in C:\wamp64\www\test\index.php on line 16

To answer your question from the title: Yes. There is difference between using exec() and query() when using persistent connections, but this difference is also there when not using persistent connections.

tl;dr: Do not use exec() for queries, which produce results. Use prepared statements instead.

Difference between exec and execute in php

tl;dr

• Use PDO::exec to issue one-off non-prepared statements that don't return result sets.
• Use PDO::query to issue one-off non-prepared statements that return result sets.

Both of these are useful if the statements are only executed one time and/or if they are constructed dynamically in a way that is not supported by prepared statements. Usually requires additional tooling to properly construct statements (and avoid things like SQL injection vulnerabilities). That coupled to the fact their flexibility is seldom needed means that it's often preferred to:

• Use PDOStatement::prepare and PDOStatement::execute to prepare statements and execute them, regardless of whether they return results or not. Useful if executed multiple times and/or in a hot path. Also doesn't require additional tooling to handle statement construction. Almost always recommended whenever possible.

---

exec and query act on PDO objects and thus only within the context of a connection. exec is used for statements that don't return a result set (e.g. INSERT, UPDATE, DELETE) while query will return result sets (e.g. from a SELECT statement). They are simply two similar interfaces to do essentially the same thing (issue a statement). The SQL statements are passed as-is to the server and thus may be considered dynamic from the perspective of the client.

What this means is that in theory they might always (i.e. on every call) be parsed, interpreted/compiled and optimized to a query plan by the DBMS before being executed. This is costly in terms of performance if they're executed multiple times.

In practice, they're often cached and reused if executed multiple times, but a DBMS can only do this opportunistically and without any guarantees. Depending on how they are matched, changing the query slightly might require that the DBMS recompiles it entirely. Sometimes the client will construct the query dynamically (too often with primitive string concatenations, sometimes with proper language-based or library-based tooling support) such that it's simply impossible for the DBMS to cache the query plan.

Update: For the curious, Pinq is an example of language-based query builder for PHP and Doctrine LDBAL is library-based example. Note that Pinq only parses PHP expressions for predicates (it seems) and still uses a fluent API for the rest (though some consider that fluent interfaces can form types of DSLs).

With proper tooling and/or when the statement is only executed one time (or a very small number of times), this is fine and sometimes necessary.

For cases where you know you will be issuing the same statement multiple times, only perhaps with different parameters (e.g. a different value in a predicate/WHERE clause), wouldn't it be great if there was a way to communicate it to the DBMS so that it doesn't throw away the whole query plan for sure? It might also allow it to do more heavyweight optimization that it might not otherwise do since it has more time to prepare the statement (slow initialization phase) before it's executed (perhaps in a hot path).

Most database systems offer this capability in the form of prepared statements (using various mechanisms, non-standard AFAIK). PDO exposes it in a unified way through the prepare method, which returns another object which represents the prepared statement.

You can then reuse that object and particularly its execute method (which issues a statement to the DBMS to execute the previously prepared statement). If the statement is parameterized, you can even pass new parameters for each execute call.

This also forces you to use sufficiently appropriate tooling to construct your statements and issue them. As I alluded to earlier, basic string concatenation and other ad-hoc techniques will only get you so far before you shoot yourself in the foot, most likely by failing to escape dynamic parts/parameters properly. This is the #1 source of SQL injection vulnerabilities.

Note that if the statement returns a result set (e.g. SELECT), you will need to use the various fetch variants to retrieve the results.

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
$result = $sth->fetchAll();
print_r($result);

(Source)

Also note that very confusingly, query itself returns its results in the form of a PDOStatement object (just like those returned by prepare). Although one might understand why this interface is reused here, it's probably not the best design (to say the least). For example, and although (a) it doesn't appear to be documented explicitly and (b) I haven't tested it, I would assume calling execute on a PDOStatement returned by a query is illegal (produces an error).

Disclaimer: Only interpreting the docs, not a frequent user anymore.

Similar questions:

• PDO's query vs execute
• PDO::query vs. PDOStatement::execute (PHP and MySQL)
• PHP PDO Prepare queries

PDO's query vs execute

query runs a standard SQL statement without parameterized data.

execute runs a prepared statement which allows you to bind parameters to avoid the need to escape or quote the parameters. execute will also perform better if you are repeating a query multiple times. Example of prepared statements:

$sth = $dbh->prepare('SELECT name, colour, calories FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories);
$sth->bindParam(':colour', $colour);
$sth->execute();
// $calories or $color do not need to be escaped or quoted since the
//    data is separated from the query

Best practice is to stick with prepared statements and execute for increased security.

See also: Are PDO prepared statements sufficient to prevent SQL injection?

PDO::exec() blocking further query from working

Used a function of the STATEMENT object had after using querying to count the rows instead of exec:

$dbq = $db->query("SELECT * FROM gig");
$rows = $dbq->rowCount();

About the latter code block not working because of the exec failing - it seems to just be the way php queries work, if one fails, all fail. The foreach() error is for the object it's provided is not an array, for it failed.

PHP PDO What are the difference between exec() and commit()

To understand what is going on, you need to know about database transactions.

From Wikipedia:

A simple transaction is usually issued to the database system in a language like SQL wrapped in a transaction, using a pattern similar to the following:

1. Begin the transaction
2. Execute a set of data manipulations and/or queries
3. If no errors occur then commit the transaction and end it
4. If errors occur then rollback the transaction and end it

In your example code, you start a new transaction with beginTransaction().

Then execute one query with exec(). In here you could execute a bunch more queries.

Note, at this stage nothing outside of your transaction can see any changes made by your queries inside the transaction. Only after committing the transaction will anything else be able to see those changes.

When inside a transaction, there is also the ability to roll back, which means abort any changes that have happened to the data since starting the transaction.

I want to know which stage data will be saved permanently in DB.

Hopefully, it's clear from the above that only after the commit() call will the changes be saved permanently.

---

Related Topics