7

I have been reusing the same variable $stmt in my PHP script to write prepared statements:

$stmt = $dbh->prepare("SELECT column_A FROM Table1 WHERE id=?");
$stmt->bindValue(1, $id, PDO::PARAM_INT);
$stmt->execute();
....

$stmt = $dbh->prepare("UPDATE Table2 SET column_B=? WHERE column_A=?");
$stmt->bindValue(1, $name);
$stmt->bindValue(2, $column_A);
$stmt->execute();

My question is, how do I know if the two statements are being written to cache and that the second statement did not overwrite the first statement though both statements are sharing the same variable name?

Question Overflow
  • 10,925
  • 18
  • 72
  • 110

2 Answers2

2

Statements are prepared by the database engine and not PHP, see:

So reusing the same variable name in PHP won't invalidate the MySQL prepare "cache".

Alix Axel
  • 151,645
  • 95
  • 393
  • 500
  • 1
    Is this applicable only when `setAttribute(PDO::ATTR_EMULATE_PREPARES, false)` or is it applicable for both true and false cases? – Question Overflow Nov 28 '11 at 15:53
  • It says in the link that "If a prepared statement with the given name already exists, it is deallocated implicitly before the new statement is prepared." How do I know if `stmt_name` is assigned by MySQL and not taken from the PHP variable name? – Question Overflow Nov 28 '11 at 16:05
  • @BenHuh: Honestly, I've no idea. My guess is that `stmt_name` is some sort of hash computed by PHP from the SQL query itself. I took a quick look at the MySQL docs and the PDO/mysql source code and couldn't find anything that points in either direction but I think the MySQL logs may provide some sort of useful information. – Alix Axel Nov 29 '11 at 11:34
  • @BenHuh: Another way you may be able to further test this is by issuing a `SET GLOBAL max_prepared_stmt_count=2;` and prepare several statements with all the methods you described - that way, when it fails, you'll know for sure that your statements are being kept prepared. Not sure how this plays along with `PDO::ATTR_EMULATE_PREPARES` though. – Alix Axel Nov 29 '11 at 11:39
  • @BenHuh: One other thing, the whole point of preparing statements is to give you speed (by taking some extra initial time to analyze it) and security/flexibility with the binded variables. You lose all of these advantages as soon as you reuse the `$stmt` variable that allows you to bind/execute them, regardless of whether or not they are kept prepared by MySQL. – Alix Axel Nov 29 '11 at 11:43
  • @ Alix Axel: Thanks for your replies and good suggestions. I will take a further look at them. Regarding your last point, is there any references that further elaborates on what you wrote? – Question Overflow Nov 29 '11 at 13:12
  • @BenHuh: Humm... I don't recall anything more elaborated but the PHP manual (http://www.php.net/manual/en/pdo.prepare.php) states that: **Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information, and helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters**. – Alix Axel Nov 29 '11 at 14:13
  • @ Alix Axel: I mean your last sentence on losing the benefits of prepared statement when `$stmt` variable is reused. – Question Overflow Nov 29 '11 at 14:17
  • @BenHuh: It's pretty straightforward, lets see... You wasted resources to prepare the `SELECT column_A FROM Table1 WHERE id=?` and executed the query (once?). Later you prepared the `UPDATE Table2 SET column_B=? WHERE column_A=?` query and also executed it (once?). If you hadn't reused the variable you could keep executing the `SELECT column_A FROM Table1 WHERE id=?` query with other binded values, without having to make an additional call to `PDO::prepare()`. – Alix Axel Nov 29 '11 at 15:35
  • 1
    @BenHuh: Regarding PDO::ATTR_EMULATE_PREPARES, I just read an interesting answer by Bill Karwin (stackoverflow.com/a/210693/89771). Apparently, PDO just combines the arguments with the query and sends the complete SQL, so, MySQL doesn't have the ability to analyze / optimize that - you just gain the security / flexibility. – Alix Axel Nov 30 '11 at 09:00
  • @ Alix Axel: Thanks. I get your point now. Since I am executing each prepared statement only once in the script, am I right to say that I wouldn't be able to take advantage of the optimisation benefit regardless of whether `$stmt` is reused or not? – Question Overflow Nov 30 '11 at 12:53
  • @ Alix Axel: Yes, I have read his post too. Thanks for helping me gain deeper insight on the inner workings of prepared statement. Really appreciate your response. :) – Question Overflow Nov 30 '11 at 13:00
  • @BenHuh: No problem! Regarding your last question, I would say that in the worst case (if `PDO::ATTR_EMULATE_PREPARES = false`) you're *sacrificing* performance, although do not underestimate the security benefit you also gain by having your parameters automatically escaped. – Alix Axel Nov 30 '11 at 16:17
  • @AlixAxel, so is PHP really that bad? Based on the [link to Bill's answer](http://stackoverflow.com/a/210693/99923) it sounds like whoever made PDO wasn't thinking straight by adding *the bound values* to the hash of the statement name making it only usable once and defeating the purpose of the cache. I think I need more information, perhaps I need to dig in the source. – Xeoncross Dec 27 '11 at 19:38
0

You don't. But overriding the variable won't change much - you're assigning a new value to the variable, not editing anything.

Tom van der Woerdt
  • 29,532
  • 7
  • 72
  • 105