Pdo'S Query VS Execute

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?

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:

Sample Image

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.

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

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.

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::query vs. PDOStatement::execute (PHP and MySQL)

Yes, because when you call PDO::prepare, the server must create a query plan and meta information for that query, then there is additional overhead to bind the specified parameters when you use PDO::execute. So, to save this overhead and improve performance, you can use PDO::query for queries without parameters.

However, depending on the scale and size of your application, and your server / host configuration (shared / private), you may or may not see any performance increase at all.

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 an advantage to using prepared statements in PDO when there are no variables going into the sql query

No, there is no benefit. Although the difference is negligible, you can always write a function to implement even such a minor optimization:

class MyPDO extends PDO
{
public function run($sql, $args = NULL)
{
if (!$args)
{
return $this->query($sql);
}
$stmt = $this->prepare($sql);
$stmt->execute($args);
return $stmt;
}
}

now you've got PDO that can decide whether to run a prepared statement or not:

$pdo   = new MyPDO(...);
$count = $pdo->run("SELECT count(*) FROM t")->fetchColumn(); // no prepare
$user = $pdo->run("SELECT * FROM t WHERE id=?",[$id])->fetch(); // prepare

PDO query in loop

You could completely avoid all of that code by just doing this:

$db->query("UPDATE videos SET vkey = MD5(CONCAT(vkey, 'video'))");

(Or you could do this query in your backend like PHPMyAdmin UPDATE videos SET vkey = MD5(CONCAT(vkey, 'video')))


However, if you for some reason want to loop through your database, you could do this:

$sql = "SELECT id FROM videos";

//no reason to use prepare() because you aren't passing variables.
$stmp = $db->query($sql);
$stmp->execute();
$results = $stmp->fetchAll(PDO::FETCH_ASSOC);

//prepare UPDATE query outside of loop, this way you don't send 2 requests to your database for every row
$stmp = $db->prepare("UPDATE videos SET vkey = :vkey WHERE id = :id");

foreach($results as $result) {
$vkey = md5($result['id']."video");
$stmp->execute(
array(
":vkey" => $vkey,
":id" => $result['id']
)
);
}

Also, it's usually a good idea to check the return values inside the loop to make sure there were no errors, you could probably do this by using something like $stmp->rowCount() to check if there were any rows effected.

PHP's PDO execute() vs. fetch()?

Yes, you are right. $stmt->execute() return false only when it failed to execute the query. It won't return false with empty result set. So you need to use fetch() to check the result, fetch() return false for empty result set.

And for the exceptions, PDO will throw exception only when you set the exception mode for the methods execute() etc. But new PDO(...) will throw exception no matter whether the exception mode is set.



Related Topics



Leave a reply



Submit