When *not* to use prepared statements?

Solution 1:

Today's rule of software engineering: if it isn't going to do anything for you, don't use it.

Solution 2:

I think you want PDO::ATTR_EMULATE_PREPARES. That turns off native database prepared statements, but still allows query bindings to prevent sql injection and keep your sql tidy. From what I understand, PDO::MYSQL_ATTR_DIRECT_QUERY turns off query bindings completely.

Solution 3:

When not to use prepared statements? When you're only going to be running the statement once before the db connection goes away.

When not to use bound query parameters (which is really what most people use prepared statements to get)? I'm inclined to say "never" and I'd really like to say "never", but the reality is that most databases and some db abstraction layers have certain circumstances under which they won't allow you to bind parameters, so you're forced to not use them in those cases. Any other time, though, it will make your life simpler and your code more secure to use them.

I'm not familiar with PDO, but I'd bet it provides a mechanism for running parametrized queries with the values given in the same function call if you don't want to prepare, then run as a separate step. (e.g., Something like run_query("SELECT * FROM users WHERE id = ?", 1) or similar.)

Also, if you look under the hood, most db abstraction layers will prepare the query, then run it, even if you just tell it to execute a static SQL statement. So you're probably not saving a trip to the db by avoiding explicit prepares anyhow.

Solution 4:

Prepared statements are being used by thousands of people and are therefore well-tested (and thus one can infer they are reasonably secure). Your custom solution is only used by you.

The chance that your custom solution is insecure is pretty high. Use prepared statements. You have to maintain less code that way.

Solution 5:

The benefits of prepared statements are as follows:

• each query is only compiled once
• mysql will use a more efficient transport format to send data to the server

However, prepared statements only persist per connection. Unless you're using connection pooling, there would be no benefit if you're only doing one statement per page. Trivially simple queries would not benefit from the more efficient transport format, either.

Personally I wouldn't bother. The pseudo-prepared statements are likely to be useful for the safe variable quoting they presumably provide.