12

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?

Helmut
  • 1,377
  • 1
  • 15
  • 25
  • 2
    Why is "older" in quotes? It *is* older, and it's deprecated too. – Mark Byers Jun 30 '12 at 21:07
  • To answer, simply use `PDO` or `mysqli` not `mysql` and your observations are correct :) – Blaster Jun 30 '12 at 21:10
  • 1
    Convenience. It's all about the f-ing convenience. Using prepared statements saves you the tedious and manual escaping part. It's a side effect, but pretty much eliminates SQL injections. You still will concatenate dynamic queries at some point, so ignoring escaping and context isn't an option. But prepared statements cover the majority of cases. -- Whether it's faster or not depends on your current setup and usage, only a profiler can tell, not a meme. – mario Jun 30 '12 at 21:11
  • 1) PDO is both faster and slower, depending on what you are doing and (more importantly) whether you know how to use it properly. 2) Prepared statement do prevent SQL injection, but they are not limited to PDO, they are a part of MySQL itself. Again, they only do the job if you know how to use them properly, and that's a small part of what they do. They *can* make your code a lot faster in some circumstances, they can also make it slower - mainly because of more round trips to the database. – DaveRandom Jun 30 '12 at 21:12
  • @MarkByers: ok, thanks! you are right, I removed the quotes ;) – Helmut Jun 30 '12 at 21:14
  • @DaveRandom: so, what is the best way to go if you look at security? Is it really that complex, are there no simple steps like "only use prepared queries" or sth? what can pdo do in case of security that mysql cannot do? I've read `mysql_real_escape_string()` is not enough to prevent SQL injections, but you should rather use the quote method of pdo? – Helmut Jun 30 '12 at 21:17
  • 2
    I have been known to use `PDO::quote()`, it depends what your doing. I think the best way for you to understand this is to understand what prepared statements actually *do* - they send the query to the database server for parsing and preparation, then when you call `execute()` they send the data that will be used for the placeholders and retrieve the results. So for a statement that you run only once, this is more database traffic and it will (very very slightly) slow things down... – DaveRandom Jun 30 '12 at 21:22
  • 1
    ...but (a common example) for many `UPDATE`s that use slightly different data, it can speed things up considerably because the query is parsed and the execution plan designed only once, the only variant is the data used during the execution. – DaveRandom Jun 30 '12 at 21:23
  • 1
    PDO::quote() not only escapes the string, but it also quotes it. On the other side, mysql_real_escape_string() will only escape the string. Prepared statements are always recommended over PDO::quote() and mysql_real_escape_string(). – abhshkdz Jun 30 '12 at 21:40

2 Answers2

20

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);

PDO vs MySQL

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

Community
  • 1
  • 1
abhshkdz
  • 6,335
  • 1
  • 21
  • 31
  • 1
    `While both PDO and MySQL are quite fast`, are you comparing PDO to `mysqli_*` (as in your example code) or `mysql_*`? Also note that PDO may also work with [binding values](http://php.net/manual/en/pdostatement.bindvalue.php), if you need/want to bind those before calling the `execute()`. – Fabrício Matté Jun 30 '12 at 21:29
  • The other main advantages of PDO of MySQLi are the ability to use named placeholders and not having to deal with MySQLi's STUPID interface where you are *required* to `bind_param()`/`bind_result()` and you can't just pass/retrieve arrays, resulting in horrible functions to create explicitly referenced arrays and nasty unreadable `call_user_func_array()` constructs – DaveRandom Jun 30 '12 at 21:29
  • "PDO supports many different drivers like CUBRID, MS SQL Server, Firebird/Interbase, IBM, MySQL, and so on" - true but since their SQL dialects differ, all DDL or non-trivial DML will have to take this into account. So no, PDO is not a cross-database silver bullet. – MaxSem Jun 30 '12 at 22:37
  • thank you Abhishek, that's an awesome answer! Thank you also for the practical examples to compare! wonderful! – Helmut Jul 01 '12 at 14:11
0

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.

Mark Eirich
  • 10,016
  • 2
  • 25
  • 27