Qiao Qiao - 6 months ago 51
PHP Question

PDO's query vs execute

Are they both do the same thing, only differently?

Is there any difference besides using

prepare
between

$sth = $db->query("SELECT * FROM table");
$result = $sth->fetchAll();


and

$sth = $db->prepare("SELECT * FROM table");
$sth->execute();
$result = $sth->fetchAll();


?

Answer

query runs a standard SQL statement and requires you to properly escape all data to avoid SQL Injections and other issues.

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?