New PDO Behavior In PHP 5.2.1
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.
February 28th, 2007 at 03:46
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.
February 28th, 2007 at 10:02
[...] http://www.phpdeveloper.org/form/view/type/addnews PHPDeveloper.org: PHP News, Views, and Community In a new post Paul Jones points out some of the new behaviors that the extension is showing in the latest of the PHP 5 seres (version 5.2). [...]
February 28th, 2007 at 17:04
Any change of getting a link to Wez’s reasonings? (Yes, I’m too lazy to Google it…) ;-)
February 28th, 2007 at 22:30
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.
February 28th, 2007 at 22:46
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. :-)
March 1st, 2007 at 09:49
[...] Paul M. Jones If it’s worth doing, it’s worth over-doing. « New PDO Behavior In PHP 5.2.1 [...]
March 1st, 2007 at 10:05
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?
March 2nd, 2007 at 00:54
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.
May 7th, 2007 at 22:24
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
May 8th, 2007 at 11:22
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.
January 8th, 2008 at 09:58
[...] of back tracing you find out that there is a version problem. Adding to the pool of problems is this. Yet another version issue. Apparently in one version it looks like that the developers decided [...]
October 27th, 2008 at 09:19
[...] een betaalbare Belgische host met een degelijke PHP 5 installatie; pigs do fly). En wat blijkt, sinds PHP 5.2.1, wordt in dit voorbeeld 10 enkel de eerste keer aan :x [...]