MySQL PHP PDO prepared statements - performance issues vs security

Question

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

Answer 1

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...

Answer 2

My question is: Which cases are reasonable for using prepared statements?

All of them. The community is openly-opposed to the usage of mysql_* functions.

Note: Suggested alternatives

Use of this extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API for more information.

Alternatives to this function include:

• mysqli_connect()
• PDO::__construct()

source

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

You're trading in a Geo for a Jaguar and you're complaining that you don't like the Jaguar because you don't always use the seat-heaters. You don't have to be consistently using every function of a library to mean it's good.

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?

If you are using this function to build SQL statements, you are strongly recommended to use PDO::prepare() to prepare SQL statements with bound parameters instead of using PDO::quote() to interpolate user input into an SQL statement. Prepared statements with bound parameters are not only more portable, more convenient, immune to SQL injection, but are often much faster to execute than interpolated queries, as both the server and client side can cache a compiled form of the query. source