Various discussions of late in a number of IRC channels has caused me to again examine the PDO interface however I am having difficulty in seeing what if any advantages there are over my current preference of the MySQLi interface.
Statements such as "you are using prepared statements of course?" are usually met on my side with a large dose of sighing .. and if I am foolish enough to ask the fatal "why?" the conversation deteriorates into a discussion about the pros and cons of preventing SQLi.
Now while I may only be a "fair" to "passable" programmer, I an more than fully aware of blocking this type of attack, which I hasten to add, I believe has nothing to do with SQL at all, and is simple a failure to sanitize user input correctly.
Again, with prepared statements there's the case for "well it's easier to use". Not sure I can agree with this. For one, he very fact of preparing a statement generates traffic to the database server unless I;m very much mistaken, and that in turn has the scope to kill one application I manage - which is already pushing my knowledge of scaling with MySQL.
Binding parameters? so:
INSERT INTO `table` (`f1`, `f2`, `f3`) VALUES (?, ?, ?)
followed by some lines of PHP to prepare, and bind the values is easier and/or more legible than a quick bit of sanitization of user input followed by:
Frankly, I'm not convinced. So what else does the PDO supply? OOPS - Well I favor procedural code however I do "nod" in the direction of classes and have sufficient strength in classes and the skill to link them correctly without issue.
Exceptions? Nothing really special here. Sure, I throw a few where I believe the application has reached a state that could cause a problem, and given the fact that there still appears to be some small unusual bugs in PDO, I'd rather use something that has a proven track record.
Speed? I've no idea on this one - if anybody has experience here on the comparison speed-wise between MySQL, MySQLi, and PDO/MySQL, I'd be delighted to see some benchmarks, comments etc.
"It's the done thing" Unfortunately I often hear this from people who run Windows servers, or small database - and by small I mean < 1 million rows. The MySQLi interface is for me handling 50 million rows without any headache, in a master/master (active/passive) + redundant r/o slave configuration.
"Using PDO makes it easy to change to different DBMS". Well that's a new one me. For one, I'd have to rewrite almost all queries, and probably a small chunk of the application to handle the differences, so sorry, but that argument fails.
So finally, I really have to ask: just why should I change to the PDO interface? What sort of problems am I liable to encounter? What sort of speed issues are there? Are there server-side issues I need to be aware off? (I already use mysql-proxy to permit certain tricks plus my applications are always read-write split aware).