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:
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
with1002
- 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
andPDOStatement::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:
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);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
orDELETE
query as well):$myQuery = 'SELECT * FROM users WHERE username = :username';
$params = array(':username' => 'admin');
$db->query($myQuery)->execute($params);query
returns aPDOStatement
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
Difference Between Method Calls $Model-≫Relation(); and $Model-≫Relation;
Get in Woocommerce Cart the Product Id of a Cart Item
Export MySQL Data to Excel in PHP
Smtp Connect() Failed PHPmailer - PHP
Insert Query on Page Load, Inserts Twice
Sending Multiple Iphone Push Notifications + Apns + PHP
How to Write Specific CSS For Mozilla, Chrome and Ie
How to Retrieve Request Payload
Laravel Migration: Unique Key Is Too Long, Even If Specified
Create Ini File, Write Values in PHP
A Problem Occurred Somewhere in the Ssl/Tls Handshake
How to Get the Sqlsrv Extension to Work With PHP, Since Mssql Is Deprecated