Skip to content


Using PDO::MYSQL ?

I've recently discovered a few things about how the mysql client library does things that seem a bit silly to me, so I'm going to share them with you.

  • native prepared statements cannot take advantage of the query cache, resulting in lower performance.
  • native prepared statements cannot execute certains types of queries, like "SHOW TABLES"
  • native prepared statements don't correctly communicate column lengths for certain other "SHOW" queries, resulting in garbled results.
  • calling stored procedures multiple times using native prepared statements causes the connection to drop.

I recommend that you use the following attribute when working with PDO::MYSQL, available in the current PHP 5.1.3 release candidates and snapshots:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

This causes the PDO native query parser to be used instead of the native prepared statements APIs in the mysql client, and effectively eliminates those problems.

I'll admit that the last point could well be a bug in my code; since I'll be at the MySQL Users Conference next week, I should be able to sit down with the right people and fix it.

Upcoming conference appearances

I'll be giving my "usual" PDO talk at both MySQL Users Conference 2006 and OSCON 2006 (the MySQLUC version of the talk will have a MySQL focus).

If you're interested in PDO and haven't had the opportunity to see me give this talk yet, please consider trying to get to one of these conferences. My talk covers the design decisions behind PDO, suggests some best practices for using it, highlights portability concerns (particularly because PHP programmers have been "dummied down" by the older mysql client library API) and more.

As usual, I'm always open for questions during the talk (I'm there for your benefit after all) and I try to make myself more generally available during the conference, so if you want to ask me questions, or even just have a chat, then feel free to approach me.

Happy New Year, and by the way, we think you don't do shit

I have to say that I'm disgusted at the attitude of two of our prominent PHP developers.

PHP is a volunteer project guys. If you don't like the fact that I lead a very busy professional life then that's your problem.

I know how many hours I've spent developing PHP and maintaining its systems over the last year. (Guess who gets paged when our mail systems run into problems?)

Getting snarky because I'm on vacation for a week is just plain wrong. Really.

PS: if you think that attitude will motivate me into doing more for PHP you're wrong. It just makes me want to go and do anything else instead.

Lesson of the day: PRINT in T-SQL has side effects

Not strictly PHP this one, but worth mentioning to the people that use SQL Server from PHP. This is an example based on a stored procedure that I spent too much time debugging today:

   IF @@ROWCOUNT = 0

This always fails--why? The PRINT statement resets the @@ROWCOUNT variable back to 0. Nasty eh?

On a related note, PRINT generates 'SUCCESS_WITH_INFO' diagnostic records (one for each print call), so you can capture the output over ODBC. A gotcha here is that, with SQL Server at least, once you start pulling diagnostic records, you must pull them all, otherwise you block your database connection, leading to invalid cursor state errors when you try to fetch.

Double bad day for PRINT in SQL Server for me.

PDO goes stable

With the release of PHP 5.1 stable, it's time to mark PDO as stable too. I've pushed the first batch of stable PDO extensions out to PECL.

Now that PDO 1.0 is out the door, we can start looking at new features. I've put together a preliminary list of things, which can be found on the tip of the 5.1 branch.

Reasonable suggestions for additions to this list are welcomed. I'm not sure that everything on that list will make it into PDO 1.1, because I'd like to keep the development cycle shorter, rather than longer. There's always PDO 1.2 to think of, after all.

Happy PDO'ing!

Oracle's PHP Developer of the year 2005

You may remember that Oracle Magazine were asking for nominations to honour people that have worked in some Oracle-related facility over the past year. Someone nominated me for the "PHP Developer of the year" category, and I "won" the award.

I'm normally a bit sceptical of awards because they're somewhat arbitrary and tend to be rather intangible, however, Oracle Magazine have gone the whole nine yards to counter that intangibility aspect; I received this weighty chunk of glass as the physical manifestation of the award:

I suspect that I won the award as a result of my efforts in developing PDO, the PDO OCI driver and my role in the redesign of the oci8 extension. Antony Dovgal has put in a similar amount of effort (perhaps even more) in his work on Oracle support in PHP. As far as I'm concerned, Tony, both our names are on the award :)

If you're really interested, you can read the blurb from the magazine here; paraphrasing-for-print has slightly twisted my words, but it's more or less what I said.

Some observations about that chunk of glass:

  • it's difficult to photograph
  • it's invisible on my white mantelpiece
  • it's waaay too easy to get finger and palm prints all over it

Don't blog bugs, file bug reports (and read the manual before you do that)

Well, I'm disappointed again by John Lim's continual lack of a decent bug report; claiming that you lack the time while having had time to post two blog entries about it is pretty poor.

Let's see the code you're using to call into PDO, John; there's not much time before we release, and without your cooperation, the problems you're seeing won't get addressed.

I'll speculate that John's ADOdb snippet:

   $rs = $db->Execute("select * from table where a=? and b=?",array('a'=>1,'b'=>2));

is trying to bind 'a' and 'b' by parameter names, but the parameters in his query are identified only by their positions (using question marks). I wonder how that is supposed to work? Maybe he should try this:

   $rs = $db->Execute("select * from table where a=:a and b=:b",array('a'=>1,'b'=>2));

It's also worth noting (again) that PDOStatement::getColumnMeta is intentionally unimplemented on this first PDO release, hence its experimental status; in other words, don't use it. Most people don't need this kind of feature anyway.

Techie Books

In addition to some leisure reading, I've also read a couple of tech books in recent months. I was given a copy of Ilia Alshanetsky's book php|architect's Guide to PHP Security (signed by Ilia himself :) and also a copy of Chris Shiflett's book Essential PHP Security.

I like both books, and I'm not just saying that because the authors are friends of mine. Both books are short, with Ilia's book weighing in the heavier of the two. They're written in different styles; Chris very clearly presents the most common styles of web application attacks and explains how they work and what you should look out for, and how to defend against them. Ilia's book is more of a whirlwind exploration of a lot of different attacks coupled with (fairly specific) examples of how to counter them, while at the same time considering application usability based on his experiences with FUDForum. He also talks about the use of honeypots to draw attackers away from important or sensitive areas of your site.

Chris' book is easier to read, and although it is the shorter of the books, it will help instill the right kind of security thinking that will serve the reader very well. Ilia's book is much more comprehensive, although the very dense writing style can make it a bit harder to read and use as a reference.

If I had to recommend just one book, I'd find it hard. If really pushed, I'd probably suggest that Chris' book be recommended for more junior developers and Ilia's for more senior developers. I'd actually recommend both books to anyone who's really serious; use Chris' book as an introduction to firmly set you on the right track and then follow up with Ilia's book for some more advanced techniques.

I read SQL Server 2005 Service Broker Beta Preview on the flight back from Paris. I picked this up from the Microsoft booth at the Zend Conference. In truth, I only read it because I'd run out of reading material and it was still stashed in the side pocket of my suitcase. This book talks about the Service Broker feature in the new version of SQL Server. This is a reliable, optionally distributed, message queuing facility that ensures message ordering. You're either thinking "Big Deal", "Eh?" or "Neat". This facility is ideal for implementing workflows in a very robust manner. If you're on Windows and have a project coming up in this space, it's worth a read. It sounds like pretty neat stuff; enough so that I might even find some time to play around with it and write some examples in PHP + PDO.

Back from Paris

I'm back home from my Paris trip; it was a long day of travelling, but it all went smoothly. On the train from Gare du Nord to Charles de Gaule I saw a burnt out wreck of a truck; perhaps it was from the rioting, but I couldn't say for sure. This was the only sign of unrest that I saw on the whole trip; a big contrast from the country-on-fire images broadcast via CNN. Admittedly, I only saw a limited section of the Paris area while I was there, so who am I to make a sound judgement?

I've updated the PDO slides from my presentation at Forum PHP 2005. These slides are from the long version of my talk, rather than the shorter 45-minute version that I gave there.

After a slightly shaky start, the trip turned out to be a success. The start was shaky because the service at my hotel wasn't all that great; apparently just giving my surname was not good enough for them to find my reservation, so I had to scrabble around my email (lucky that I had my IMAP mailbox sync'd to my HD!) to find additional information. After then waiting for the english speaker to finish his cigarette break, I could finally confirm my reservation. I then had to wait for my room to be made up. Not the best service I've ever had; I think they probably assumed that I'd come from England that morning, when in fact I'd been travelling from the USA since the day before. Oh well.

The hotel was ok though, and they had free wifi access for guests. It was WEP protected and when I asked about the WEP key, the guy at the desk said he didn't have any more boxes. Strange. The next night I was lucky and managed to obtain a box... it turns out that they use a little box with a wifi-to-ethernet bridge in them; one end of the box plugs into the AC outlet in the wall, and the other into your ethernet port. Interesting approach; it means that they don't need to worry about giving out their WEP keys, can provide service to people without wifi cards and that they don't need to plumb in wired ethernet either.

I've arrived at Forum PHP

I'm sitting in the soundproof booth at the Forum PHP in Paris. I've been up for more than 24 hours straight (don't you just love timezone shifts!) so I'm feeling pretty tired. Contrary to the doom and panic mongering that was on CNN while I was waiting at the airport in Atlanta (that's the airport with the really long walk between the domestic and international concourses), Paris doesn't seem like it's about to be torn down by rioters; seems pretty peaceful and not tense at all. I've been told that the unrest is mainly in the suburbs, and was mostly outside of Paris itself last night.

I've also heard pretty bad things about Charles de Gaule airport, but found it to be quite empty and easy to navigate when I arrived just before 7am local time; maybe I've just been lucky :)

While I'm here, I'll be giving my PDO talk and sitting in on a Q&A panel with the other PHP big wigs that are also here (Rasmus, Zeev, Andrei and Zak). After the conference itself, we'll be holding a meeting to hammer out some of the finer details for PHP 6; the other other PHP big wigs are making their way to Paris after the PHP conference that is concurrently underway in Frankfurt.