I don't know how true any of these statements are anymore. My greatest concern in choosing a MySQL interface is preventing SQL Injection. The second concern is performance.

My application currently uses procedural MySQLi (without prepared statements), and utilizes the query cache quite a bit. It will rarely re-use prepared statements in a single request. I started the move to PDO for the named parameters and security of prepared statements.

I'm using MySQL 5.1.61 and PHP 5.3.2

Should I leave PDO::ATTR_EMULATE_PREPARES enabled or not? Is there a way to have both the performance of the query cache and the security of prepared statements?

Honestly? Just keep using MySQLi. If it's already working using prepared statements under that, PDO is basically a pointless layer of abstraction. EDIT: PDO is really useful for green field applications where you're not sure what database is going into the back-end.
–
jmkeyesApr 12 '12 at 1:59

1

Sorry, my question was unclear before. I've edited it. The application does not use prepared statements in MySQLi at the moment; just mysqli_run_query(). From what I've read, MySQLi prepared statements also bypass the query cache.
–
AndrewApr 12 '12 at 14:13

3 Answers
3

MySQL >= 5.1.17 (or >= 5.1.21 for the PREPARE and EXECUTE statements) can use prepared statements in the query cache. So your version of MySQL+PHP can use prepared statements with the query cache. However, make careful note of the caveats for caching query results in the MySQL documentation. There are many kinds of queries which cannot be cached or which are useless even though they are cached. In my experience the query cache isn't often a very big win anyway. Queries and schemas need special construction to make maximum use of the cache. Often application-level caching ends up being necessary anyway in the long run.

Native prepares doesn't make any difference for security. The pseudo-prepared statements will still escape query parameter values, it will just be done in the PDO library with strings instead of on the MySQL server using the binary protocol. In other words, the same PDO code will be equally vulnerable (or not-vulnerable) to injection attacks regardless of your EMULATE_PREPARES setting. The only difference is where the parameter replacement occurs--with EMULATE_PREPARES, it occurs in the PDO library; without EMULATE_PREPARES, it occurs on the MySQL server.

Without EMULATE_PREPARES you may get syntax errors at prepare-time rather than at execute-time; with EMULATE_PREPARES you will only get syntax errors at execution time because PDO doesn't have a query to give to MySQL until execution time. Note that this affects the code you will write! Especially if you are using PDO::ERRMODE_EXCEPTION!

An additional consideration:

There is a fixed cost for a prepare() (using native prepared statements), so a prepare();execute() with native prepared statements may be a little slower than issuing a plain textual query using emulated prepared statements. On many database systems the query plan for a prepare() is cached as well and may be shared with multiple connections, but I don't think MySQL does this. So if you do not reuse your prepared statement object for multiple queries your overall execution may be slower.

As a final recommendation, I think with older versions of MySQL+PHP, you should emulate prepared statements, but with your very recent versions you should turn emulation off.

After writing a few apps that use PDO, I've made a PDO connection function which has what I think are the best settings. You should probably use something like this or tweak to your preferred settings:

Re #2: surely values that MySQL receives as parameters (to native prepared statements) do not get parsed for SQL at all? So the risk of injection must be lower than using PDO's prepare emulation, where any flaw in escaping (e.g. the historical issues mysql_real_escape_string had with multi-byte characters) would still leave one open to injection attacks?
–
eggyalMay 4 '12 at 20:56

2

@eggyal, you are making assumptions about how prepared statements are implemented. PDO may have a bug in its emulated prepares escaping, but MySQL might have bugs too. AFAIK, no problems have been discovered with emulated prepares which could cause parameter literals to pass through unescaped.
–
Francis AvilaMay 4 '12 at 21:39

2

Awesome answer, but I have a question: If you turn EMULATION off, won't the execution be slower ? PHP would have to send the prepared statement to MySQL for validation and only then send the parameters. So, if you use the prepared statement 5 times, PHP will talk to MySQL 6 times (instead of 5). Won't this make it slower ? Besides, I think there's a greater chance that PDO could have bugs in the validation process, rather than MySQL...
–
Radu MurzeaApr 8 '13 at 12:14

4

Note the points made in this answer re prepared statement emulation using mysql_real_escape_string under the hood and the consequent vulnerabilities that can arise (in very particular edge cases).
–
eggyalJun 12 '13 at 13:22

2

+1 Good answer! But for the record, if you use native prepare, parameters are never escaped or combined into the SQL query even on the MySQL server side. By the time you execute and supply parameters, the query has been parsed and transformed into internal data structures in MySQL. Read this blog by a MySQL optimizer engineer that explains this process: guilhembichot.blogspot.com/2014/05/… I'm not saying this means native prepare is better, insofar as we trust the PDO code to do escaping correctly (which I do).
–
Bill KarwinJul 9 '14 at 23:06

I ditched MySQLi for PDO for the prepared named statements and the better API.

However, to be balanced, PDO performs negligibly slower than MySQLi, but it's something to bear in mind. I knew this when I made the choice, and decided that a better API and using the industry standard was more important than using a negligibly faster library that ties you to a particular engine. FWIW I think the PHP team is also looking favourably at PDO over MySQLi for the future too.

Yes. That depends on the nature of the query, rather than the platform specifics.
–
Will MorganMay 7 '12 at 22:31

I read that to mean "The query result is cached unless something else prevents it from being cacheable," which - from what I had read until then - included prepared statements. However, thanks to Francis Avila's answer, I know that that is no longer true for my version of MySQL.
–
AndrewMay 14 '12 at 19:21

It's an interesting point. I guess emulation postpones server-side parsing to the execution phase. While it isn't a big deal (wrong SQL will eventually fail) it's cleaner to let prepare do the job it's supposed to. (Additionally, I've always assumed that the client-side parameter parser will necessarily have bugs of its own.)
–
Álvaro G. VicarioJul 15 '14 at 10:54

1

IDK if you're interested, but here's a little writeup on some other spurious behavior I noticed with PDO that lead me down this rabbit hole to begin with. Seems the handling of multiple queries is lacking.
–
quickshiftinJul 15 '14 at 16:44

I just looked at some migrations libraries on GitHub... What do you know, this one pretty much does the exact same thing as my blog post.
–
quickshiftinJul 15 '14 at 20:13