PDO - real facts and best practice?

Since now I've been using the older mysql instead of PDO and I've seen many recommendations why to switch to PDO, however also many different facts (also here on SO), e.g.:

  • stating PDO is slightly faster/a little bit slower
  • saying PDO helps prevent SQL-injections, but only if you use prepared queries
  • and also saying using prepared queries is bad, as it is damn slow

So, what is actually true? Especially, what are the best practices when using PDO and both speed and security matter a lot - how to best protect yourself from SQL injections while still having fast queries?

Answers


Database Support

The core advantage of PDO over MySQL is in its database driver support. PDO supports many different drivers like CUBRID, MS SQL Server, Firebird/Interbase, IBM, MySQL, and so on.

Security

Both libraries provide SQL injection security, as long as the developer uses them the way they were intended. It is recommended that prepared statements are used with bound queries.

// PDO, prepared statement
$pdo->prepare('SELECT * FROM users WHERE username = :username');
$pdo->execute(array(':username' => $_GET['username']));

// mysqli, prepared statements
$query = $mysqli->prepare('SELECT * FROM users WHERE username = ?');
$query->bind_param('s', $_GET['username']);
$query->execute();
Speed

While both PDO and MySQL are quite fast, MySQL performs insignificantly faster in benchmarks – ~2.5% for non-prepared statements, and ~6.5% for prepared ones.

Named Parameters

Just like @DaveRandom pointed out, this is another feature that PDO has, and it is considerably easier than than the horrible numeric binding.

$params = array(':username' => 'test', ':email' => $mail, ':last_login' => time() - 3600);

$pdo->prepare('
SELECT * FROM users
WHERE username = :username
AND email = :email
AND last_login > :last_login');

$pdo->execute($params);

Few links for further reference MySQL vs PDO (Stackoverflow) Why you should be using PDO for database access (net.tutsplus.com)


In most cases, development speed (how long it takes to write the software) is much more important than minute improvements to performance.

I recommend using PDO, and using it with prepared queries. Unless you are Twitter or Google it is highly unlikely you will be even the slightest bit aware of any performance difference.


Need Your Help

Pagination not working AngularJS 1.4 with ui-bootstrap

angularjs pagination angular-ui-bootstrap

Here's what I currently have (I've also tried numerous things from other SO answers and tutorials to no avail):

Zazzle.com Image Zoom effect using jQuery

jquery image jquery-plugins

Is there a jQuery plugin that mimics the Zazzle.com photo zoom effect (you can view the effect here: http://www.zazzle.com/awards+tshirts). The closest I've found is the jQZoom plugin which opens ...