Prepare & Execute

Question

Often when using PDO I want to prepare a statement and then execute it only once. I do it this way so that I can be sure all my parameters are properly escaped.

As I understand it, by preparing a statement and then executing it you're sending 2 requests to the MySQL server, so this would actually be slower than manually escaping the parameters and sending one request via PDO::query.

Is there no way to send the parameterized query plus the parameter values in one swoop?

---

I wrote a little test,

$t = new WxTimer();
for($i=0; $i<1000; ++$i) {
    $db->prepare("SELECT user_id, $i FROM wx_user WHERE user_id=?")->execute($i)->fetch();
}
echo $t->elapsed().PHP_EOL;

and ran it with both ATTR_EMULATE_PREPARES on and off. With ATTR_EMULATE_PREPARES set to true (which does appear to be the default), it runs about twice as fast (295ms vs 639ms).

Curiously, this statement,

$db->query("SELECT user_id, $i FROM wx_user WHERE user_id=".$db->quote($i))->fetch();

Runs in about 633ms with emulate on, or 301ms with emulate off, despite not appearing to use prepared statements.

(If you're wondering about the syntax, I overrode a few methods in the PDO class)

Answer 1

This depends on the setting of PDO::ATTR_EMULATE_PREPARES,

If you use emulation of prepared statements, then the pdo will emulate the prepared statement for you, the escape process will be done with in the pdo. So this will send only one request to the database.

Answer 2

look here: http://php.net/manual/en/pdo.quote.php

example:

$pdo = new PDO ("mysql:host=$hostname;dbname=$dbname","$username","$pw");
$str = "gh'gh";
$str = $pdo->quote($str);
$pdo->query ("UPDATE table SET field=$str")

I asume it's what you want.

for:

$str = "gh'gh";
echo $pdo->quote($str);

the output will be:

'gh\'gh'