New PDO Behavior In PHP 5.2.1

UPDATE (2016-05-30): Rasmus Schultz comments that “this does work – it was fixed after this article was published.” So apparently the issue described herein has been fixed. I’ll leave the article in place for archival purposes.


Prior to PHP 5.2.1, you could do this with PDO …

<?php
// assume $pdo is PDO connection
$sql = "SELECT * FROM some_table
        WHERE col1 = :foo
        OR col2 = :foo
        OR col3 = :foo";

$sth = $pdo->prepare($sql);

$sth->bindValue('foo', 'bar');

$sth->execute();
?>

… and PDO would happily bind the value ‘bar’ to every ‘:foo’ placeholder in the statement.

Sadly, this is no longer the case in PHP 5.2.1. For valid reasons of security and stability in memory handling, as noted to me by Wez Furlong, the above behavior is no longer supported. That is, you cannot bind a single parameter or value to multiple identical placeholders in a statement. If you try it, PDO will throw an exception or raise an error, and will not execute the query. In short, you now need to match exactly the number of bound parameters or values with the number of placeholders.

In most cases, I’m sure that’s not a problem. However, in Solar, we can build queries piecemeal, so we can’t necessarily know in advance how many placeholders there are going to be in the final query.

Also, it’s often convenient to throw an array of data against a statement with placeholders, and only bind to the placeholders that have elements in the data array. Alas, this too is no longer allowed in PDO under PHP 5.2.1, because the number of bound values might not match the number of placeholders.

As a result, the newest Solar_Sql_Adapter::query() method includes some code to examine the statement and try to extract the named placeholders that PDO expects to see. Given the above example statement, PDO will expect placeholders for :foo, :foo2, and :foo3 (PDO auto-numbers repeated placeholder names). While a bit brain-dead, it does seem to do its job tolerably well … at least well enough to get around this newly-implemented (but apparently always-planned) behavior.

The code in the query() method looks something like this; note that we call it by sending along an array of $data to bind as values into the statement.

// prepare the SQL command and get a statement handle
$sth = $this->_pdo->prepare($sql);

// find all :placeholder matches.  note that this will
// find placeholders in literal text, which will cause
// errors later.  so in general, you should *either*
// bind at query time *or* bind as you go, not both.
preg_match_all(
    "/W:([a-zA-Z_][a-zA-Z0-9_]+?)W/m",
    $sql . "n",
    $matches
);

// bind values to placeholders, adding numbers as needed
// in the way that PDO renames repeated placeholders.
$repeat = array();
foreach ($matches[1] as $key) {

    // only attempt to bind if the data key exists.
    // this allows for nulls and empty strings.
    if (! array_key_exists($key, $data)) {
        // skip it
        continue;
    }

    // what does PDO expect as the placeholder name?
    if (empty($repeat[$key])) {
        // first time is ":foo"
        $repeat[$key] = 1;
        $name = $key;
    } else {
        // repeated times of ":foo" are treated by PDO as
        // ":foo2", ":foo3", etc.
        $repeat[$key] ++;
        $name = $key . $repeat[$key];
    }

    // bind the $data value to the placeholder name
    $sth->bindValue($name, $data[$key]);
}

// now we can execute, even if we had multiple identical
// placeholders in the statement.
$sth->execute();

With this code in place, we can now bind one ‘foo’ value to many identical ‘:foo’ placeholders.

NB: Do not try doing this with bound parameters, or you are likely to run into memory problems.


UPDATE (2016-05-30): Rasmus Schultz comments that “this does work – it was fixed after this article was published.” So apparently the issue described herein has been fixed. I’ll leave the article in place for archival purposes.

Are you stuck with a legacy PHP application? You might like my book because it gives you a step-by-step guide to improving your codebase, all while keeping it running the whole time.
Share This!Share on Google+Share on FacebookTweet about this on TwitterShare on RedditShare on LinkedIn

19 thoughts on “New PDO Behavior In PHP 5.2.1

  1. Hmm, could you look into getting this one added to the upgrading guide?

    I do not quite understand why bindValue() would care if you reuse the same parameter. I could see this being an issue with bindParam(). I would not be suprised if a fair number of people rely on this behavior to for example set a created and last change column inside an INSERT.

    I know that PostgreSQL specifically supports this, and I would assume that Oracle does too.

    Oh well .. pretty hefty change for a minor release imho, but if memory corruption is the alternative .. I guess it had to be done.

  2. It was never technically supported. It just happened to work for the drivers that most people use. This is (not very clearly) documented, and something that I mention in my PDO talks.

    The change was made for two reasons; first and foremost, if you re-use the same variable in a bind, it is possible to induce a crash when using some drivers. It’s not possible to guarantee to do the right thing, and having a way to trigger a crash can sometimes be used as an attack vector for a security exploit.

    The second reason is that of portability. Some drivers would internally perform this check and error out. If you code against the drivers that don’t enforce this, then your code won’t work on those that don’t.

    I’m sorry that this bit you; it’s hard to notice a change in the handling of “bad” uses if you don’t use it that way.

    • IMO, this was entirely the wrong fix for this problem – what should have been done, is emulating this feature for drivers that don’t support it.

      Why? Because it’s completely natural and intuitive to expect this to work.

      The reasons why it doesn’t work are entirely technical in nature; hence, the fix should have been a technical fix, rather than a crippling change to the specification, requiring everyone to implement the above facility (or something similar) for themselves.

      IMO, we desperately need a major PDO 2.0 update addressing this and countless other major issues, including weird semantics, global state in the connection object, spooky action in the distance and other weird quirks…

      • Okay, no, this does work – it was fixed after this article was published. My problem was somewhere else.

        @pmjones maybe you should update this article – or just take it down, since it has no particular relevance anymore.

  3. Hi Wez —

    Poor phrasing on my part about “supported.” Perhaps “the incorrect behavior was not explicitly disallowed” is more correct? 😉

    I completely agree that the “new” behavior is the right thing to do; security and stability have to be the primary goals.

    The bite was not too bad, just unexpected — and even then, nothing that we couldn’t work around. (Having a method in PDO that returns a list of the parsed placeholders, though, would be a wonderful addition; then there’d be no need for the above regex routine, and I’m sure it’d be much faster.)

    Finally, my many many thanks for creating and maintaining PDO in the first place; it’s a great piece of work and makes my life a lot easier. 🙂

  4. Actually I find Wez reasoning quite surprising. So why dont we have a universal DSN format yet? Why is there support for cursors, which are not supported on all RDBMS? In the past your argument was that PDO is just a thin layer. Since when do you limit things to a lowest common denominator? I am just not understanding the pattern by which you make decisions Wez.

    I have argued in the past for two different modes for PDO. Once that maximizes portability and specifically disallows features that are not portable. And one that tries to expose as much native functionality as possible. IMHO the long term goal of PDO must be to one day replace the native extensions. This is obviously only possible if we do not artificially limit any of the drivers.

    However let me ask my question once more: Why does this apply to bindValue() as well?

  5. As with previous posters, this “small” “bug-fix” is a fundamental change. The only reason I chose PDO over mysqli was the ability to name bind and bind one to many.

    Don’t see a good reason why for bindValue cannot work for multiple placements without memory corruption.

    The primary goal is to make life easier for coders and this change does not in my opinion.

  6. Hi!

    Interesting post, and interesting comments. Hacking on some older (as in 4 months old..) libraries tonight, I got bit by this change after upgrading my laptop to PDO 5.2.1… For me, the change affected code in a save() routine that built an INSERT … VALUES … ON DUPLICATE KEY UPDATE … statement from an array of table fields. Previously, a single named parameter worked fine for both the VALUES clause and the ON DUPLICATE KEY UPDATE clause. Now, I’ll have to hack in a version check and bind the parameters again for PDO >= 5.2.1. Not a huge deal, and I understand the reasoning behind the change. Heck, MySQL certainly has had it’s fair share of such happenings.

    However, from the perspective of the PDO user, it *does* make sense to think that a *named* parameter would not need to be bound more than once, no?

    Cheers,

    Jay

  7. Hi Jay, thanks for commenting. Yeah, I fully sympathize with Wez (et al) on the need for this change; security has to take first precedence.

    When you say this, though: “from the perspective of the PDO user, it *does* make sense to think that a *named* parameter would not need to be bound more than once, no?” … I must disagree a little.

    I am not a database guru, so it seems to me that a “:foo” placeholder should be replaced with its respective value everywhere in the statement, not just the first time it shows up. (See the first example I give in the article above.) I think this becomes more-so the case when you get into things like “UNION” queries; why would I bind the same key-value pair more than once, if it’s always the same value for the a particular key?

    When it comes to binding *variables*, then I see the argument and agree completely. But for replacement *values*, I don’t so much.

  8. Paul,
    I realize that this post is a little dated, however I wondered if it was still relevant. I tried implementing your code in my own, internal framework and I’m having a little difficulty. Your code above seems to rename the bind names at the time of binding, but it doesn’t appear to rename the placeholders in the query. I tried running it this way and on my version of PHP (5.3) at least, it does not appear that PDO automatically renames duplicate placeholder names as your comment “// bind values to placeholders, adding numbers as needed in the way that PDO renames repeated placeholders.” made me think it would. What am I missing?

Leave a Reply

Your email address will not be published. Required fields are marked *