MySQL PHP PDO prepared statements - performance issues vs security
mysql, pdo, php, prepared-statement
Solution
I think this falls in the "premature optimization" category.
How significant is the overhead? Have you measured it? Does it affect your server performance at all?
Odds are it doesn't.
On the plus side, you have an undeniable gain in terms of security (which should be a major concern for any internet-based shop).
On the downside, you have the risk that it might affect performance. In the link you provided, it shows that poorly implemented PDO preparation results in slightly lower performance than non prepared statement in some circumstances. Performance difference on 5000 runs is 0.298 seconds.
Insignificant. Even more so when you realize that the "non prepared" queries are run without the input sanitizing routines that would be required to make them safe in a live environment. If you don't use the prepared queries, you need some form of input sanitizing to prevent SQL attacks, and depending on how it is done, you may need to massage back the result sets.
Bottom line, there is no significant performance issue, but there is a significant security benefit. Thus the official recommendation of using prepared statements.
In your question, you speak of "the common eshop". The "common eshop" will never have enough traffic to worry about the performance issue, if there is one. The security issue on the other end...
Problem
I am thinking of rewriting some open-source application for my purposes to PDO and transactions using InnoDB (mysql_query and MyISAM now). My question is: Which cases are reasonable for using prepared statements? Because everywhere I am reading is stated (even in many posts here) that I should use prepared statements every time and everywhere because of the 1. security and 2. performance. Even PHP manual recommends using prepared statements and not mentioning the escape-thing. You can't deny the security mechanism. But thinking it over and over it comes to mind that having to prepare the statement every time and then use it once.. It doesn't make sense. While having to insert 1000 times some variables in single statement, that makes sense but it is obvious. But this is not what common eshop or board is built upon. So how to overcome this? May I prepare my statements application-wide and to name them specifically? Can I prepare several different statements and to use them by name? Because this is the only reasonable solution I am thinking of (except the 1000x thing). I found there is this mysql_real_escape called $pdo->quote as well for the purpose of single query. Why not to use this? Why to bother with preparing? And what do you think of this excellent article? http://blog.ulf-wendel.de/2008/pdo_mysqlnd-prepared-statements-again/ Do you agree with the overhead caused by preparing the statements? Thanks