PDO vs. MySQLi

pdo-vs-mysqli
How are you connecting to your database?

When accessing a database in PHP, we have two choices: MySQLi and PDO. Lets compare them.

Connection

It is very simple to connect to a database with either one of these:

API Support

PDO and MySQLi both offer an object-oriented API, but MySQLi also offers a procedural API.

Database Support

The advantage of PDO is its database driver support. PDO supports 12 different drivers as opposed to MySQLi, which supports MySQL only. The databases PDO supports are listed below:

  1. Cubrid
  2. FreeTDS / Microsoft SQL Server / Sybase
  3. Firebird
  4. IBM DB2
  5. IBM Informix Dynamic Server
  6. MySQL 3.x/4.x/5.x
  7. Oracle Call Interface
  8. ODBC v3 (IBM DB2, unixODBC and win32 ODBC)
  9. PostgreSQL
  10. SQLite 3 and SQLite 2
  11. Microsoft SQL Server / SQL Azure
  12. 4D

*PDO Drivers list can be found at php.net

To list of all the drivers that PDO currently supports, you can use the following code:

If database support isn’t a big concern, then PDO and MySQLi are essentially equivalent.

Named Parameters

Another important feature that PDO has is binding parameters; this is easier than using the numeric binding.


The PDO way:

The MySQLi way:

While the question mark parameter binding might seem shorter and easier, it isn’t as flexible as named parameters, unless you want to always keep track of the parameter order.

It is unfortunate MySQLi doesn’t support named parameters.

Object Mapping

PDO and MySQLi both can map results to objects. This comes in handy if you don’t want to use a custom database abstraction layer, but still want ORM-like behavior.

Example:

Without object mapping, we would need to fill each field’s value before you can use the info() method.

This allows us to pre define these properties before the object is constructed.

Example:

Security

PDO and MySQLi both provide SQL injection security, as long as you use them the way they were intended.

If a attacker tries to inject some malicious SQL through the ‘username’ HTTP query parameter $_GET[‘username’]:

If you don’t escape this, it will be included in the query – deleting all rows from the members table.

Both PDO and MySQLi support multiple queries.

PDO::quote() not only escapes the string, but it also quotes it. mysqli_real_escape_string() will only escape the string; you will need to apply the quotes manually.

Its recommended that you always use prepared statements with bound queries instead of PDO::quote() and mysqli_real_escape_string().

Conclusion

PDO definitely wins with support of twelve different database drivers and named parameters. For security, both of them are safe as long as the developer uses them the way they are supposed to be used. If you are using MySQLi, perhaps it’s time to start using PDO.

I hope you find this article useful.

Barrett

Tagged with:

View all contributions by

Website: http://rrpowered.com

Related Articles

Advertisement

Stay connected with us

Add us on Google+

Follow us on Facebook

Follow us on Twitter

Get updates via RSS

Get email updates

FEEDBACK0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">