To PDO or not?

Suhana · 01-15-2010, 12:23 PM

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:

Code:

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:

Code:

$sql = sprintf("INSERT INTO `table` (`f1`, `f2`, `f3`) VALUES (NULL, %u, '%s')", $f2, $f3);
$res = mysql_query($sql, $conn);

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).

Parvus · 01-15-2010, 01:59 PM

I've been sitting with this as well and the choice really is yours to make.

For benchmarks and extra info you might want to visit this link: *Click me*

Personally I have decided to switch from MySQL to PDO (instead of switching from MySQL to MySQLi) after reading a lot of different things on the web.
For me, the following points were the reason to go to PDO:
- MySQL will slowly go and PDO will be the new standard
- PDO is now standard in PHP and since it is pretty new, I'm convinced it will be improved
- If I need to change my database, it certainly will be easier with PDO and only a vew queries might have to be changed (since I use pretty standard queries)
- PDO is OOP and while it might not be a real argument for some people, I do like programming OOP above Procedural since a while now

*Some info I found may be false, if so, then I would like to hear this as well =]
- I like prepared statements above using mysql_real_escape_string() all the time

ETbyrne · 01-16-2010, 05:15 PM

I looked into using PDO not too long ago for the Dingo Framework, but I quickly found that it did not meet my requirements.

In other words, I found that using PDO was not the right solution for what I was trying to do. Not saying PDO is bad, in fact it is quite good, but it is not a one-size fits all kind of deal. Like you said, PDO doesn't solve the issue of having to rewrite queries if you switch DBMS, which I believe is PDO's main failure.

delayedinsanity · 01-16-2010, 06:00 PM

Quote:

Like you said, PDO doesn't solve the issue of having to rewrite queries if you switch DBMS, which I believe is PDO's main failure.

That's not a shortcoming of PDO - PDO, as it states, is not a database abstraction layer, it's a data access abstraction. Using it as such is using a phillips to screw in a robertson.

ETbyrne · 01-18-2010, 02:01 PM

True, but PDO is supposed to make switching DBMS easier, right? If it doesn't handle the SQL part of the problem, then in my opinion it doesn't do the job. In order to easily switch DBMS you would have to write your own wrapper for PDO, which is the exact same thing I would have to do with MySQLi/PgSQL.