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?
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.
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();
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.
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.