PDO - real facts and best practice? [closed]

Question

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?

Answer 1

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)