Disadvantages of PDO (PHP Data objects)

Question

Everything I've read so far about PDO (PHP Data Objects) is almost too good to be true.

I mean:

• Its faster then mysql or mysqli.
• It has the same syntax for multiple database drivers.
• with prepared statements it is secure for SQL injection.
• you can fetch data directly into a object.

But what are the disadvantages of PDO?

Answer 1

Everything I've read so far about PDO (PHP Data Objects) is almost too good to be true.

I use PDO every day, and that's for a reason. I did write a wrapper over it though, because the default PDO instance does things I don't like (e.g. fail silently), and the API could've been a whole lot better. Configuration with constants is just not my default approach. Also, I've created some convenience methods.

Its faster then mysql or mysqli.

Is it? I don't know where you picked this up, and it might well be true, but I haven't heard of PDO being faster than the native MySQL libraries.

It has the same syntax for multiple database drivers.

Sort of. I use PostgreSQL a lot, and the code is different from when you're working with MySQL. This makes sense though, as PostgreSQL works with named sequences, while MySQL works with "auto increment", which is a sequence per table. There are differences between databases that PDO can not abstract, even if it's just for database access.

with prepared statements it is secure for sql injection.

You can prepare statements with mysqli too, so I don't see this as a definitive upside. I do generally use prepared statements though, and I like the :field syntax PDO provides.

But where are the disadvantages of PDO, something that has so many pros must also have a contra.

The API is less than intuitive for me, I think the API of mysqli makes more sense. Nevertheless, if you write a wrapper for it yourself, it's a very decent library. Here's the wrapper I wrote to make using PDO a little bit more sane, there are many more examples drifting on the internet though.

EDIT: Oh, and James Anderson is right; it has poor Oracle support. I don't use Oracle, so I don't see that as a huge downside though.

Answer 2

Bind mechanism doesn't work with column or table names.

Simple examples:

CREATE TABLE :bar (rowId int)

SELECT :foo FROM :bar

On the bright side, this is not something you often need or want to do.

But when you do... PDO leaves you hanging. The solution is manually concating together your query strings while doing by-hand escaping:

$foo = some_escape_logic( $dirtyFoo );
$bar = some_escape_logic( $dirtyBar );

$db->query( "SELECT {$foo} FROM {$bar}" );

SQL results always returned as strings

fetch() returns an array of string values, even if the SQL table types are numeric. For example, a table with bigint/string/bigint columns returns:

array( 'rowId' => '1', 'name' => 'Fred', 'age' => '12' );

instead of:

array( 'rowId' => 1, 'name' => 'Fred', 'age' => 12 );

As a positive, you'll never lose precision from a mismatch between PHP and SQL types. The type juggling in PHP also ensures that you'll seldom even notice that the data was originally encoded as strings.

As a negative, this can be a pain when passing DB results to something like json_encode(), since you'll end up with the numeric values quoted:

{ "rowId": "1", "name": "Fred", "age": "12" }

instead of

{ "rowId": 1, "name": "Fred", "age": 12 }

In an ideal world, auto-casting output types from fetch() would be controllable via an optional argument.