5

I see a lot of people saying you should always use prepared statements for database queries. However, the PHP docs say:

Every prepared statement occupies server resources. Statements should be closed explicitly immediately after use. If not done explicitly, the statement will be closed when the statement handle is freed by PHP.

Using a prepared statement is not always the most efficient way of executing a statement. A prepared statement executed only once causes more client-server round-trips than a non-prepared statement.

From http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

Given the above, if you're only going to use a query once, isn't it better not to use prepared statements?

texelate
  • 2,157
  • 3
  • 19
  • 30
  • I think that completely depends on the query itself. If it contains any user-supplied data, then you definitely should use prepared statements. – Jeff Lambert May 03 '13 at 18:46
  • This is an odd question - and would probably solicit debate - which is why I vote to close. Personally, I would never stop using prepared statements just because it makes me feel that my code is more secure ( from sql injections). – Repox May 03 '13 at 18:48
  • 1
    They're advocated for convenience, and security as by-product. Performance is an entirely different (and arguably negligible) property; with PDOs emulated prepared statements irrelevant. Bound parameters were invented for compiled languages btw, just recently readvocated for scripting languages because they are overall less effort than string interpolation and manual babysitting. – mario May 03 '13 at 18:51

4 Answers4

5

The difference considered to be negligible.

Nevertheless, one have to distinguish native prepared statements from the general idea of a prepared statement.

The former is just a form of running queries supported by most of DBMS, explained here. Its usage can be questioned.
The latter is a general idea of substituting actual data with a placeholder, implying further processing of the substituted data. It is widely used in programming, a well-known printf() function is an example. And this latter approach have to be ALWAYS used to run a query against a database, no matter if it is backed by native prepared statements or not. Because:

  • prepared statement makes proper formatting (or handling) inevitable.
  • prepared statement does proper formatting (or handling) in the only proper place - right before query execution, not somewhere else, so, our safety won't rely on such unreliable sources like
    • some PHP 'magic' feature which rather spoils the data than make it safe.
    • good will of one (or several) programmers, who can decide to format (or not to format) our variable somewhere in the program flow. That's the point of great importance.
  • prepared statement affects the very value that is going into query, but not the source variable, which remains intact and can be used in the further code (to be sent via email or shown on-screen).
  • prepared statement can make application code dramatically shorter, doing all the formatting behind the scenes (*only if driver permits).

So, even if you consider not using native prepared statements (which is quite okay), you have to always create your queries using placeholders instead of the actual data. For this purpose you can use PDO, which works exactly as described above - by default it just emulate prepares, means regular SQL query being created out prepared query and data, and then run against database.

However, PDO lacks support for many important data types, such as identifier or an array - thus it makes you unable to always use placeholders and thus makes an injection quite possible. Luckily, safeMysql has placeholders for the every data type and allows you to run queries safely.

Community
  • 1
  • 1
Your Common Sense
  • 152,517
  • 33
  • 193
  • 313
1

Unless you can benchmark this and prove that they're having a measurable drag on performance, as in something that's more than at least 10-15% slower, there's no reason to fuss about this. Even then, that is a small price to pay for almost absolute certainty on data integrity, presuming you use placeholders exclusively.

Prepared statements, even if used only once, are easy to implement correctly and very hard to get wrong if you're disciplined about never using string interpolation to inject data.

Anyone who considers themselves a professional programmer will have to periodically audit their application to be sure there's no SQL injection bugs in it. If it's not blatantly obvious that something's escaped, it cannot be presumed to be escaped, so you must investigate.

Placeholders also make it less likely you'll mistakenly mixing up columns and their associated values. The named parameters feature of PDO is great at avoiding this.

tadman
  • 194,930
  • 21
  • 217
  • 240
1

Preparing a statement yields a query plan suitable for the variables. It then remains available for multiple use.

There are gotchas associated with them, btw. Consider this statement:

select * from posts order by post_date limit 10 offset ?;

If you prepare that, you'll get no index scan. Because, for all the planner knows, you want the last couple of rows and it's out of the question to go through your millions posts one by one as you go through the index.

If you run that directly with the parameter, you'll get an index scan for small offsets, and no index scan past a threshold for the same reason the prepared statement didn't use an index.

In that light, considering that most apps only run queries once, you can usually stick to emulated prepares. See PDO::ATTR_EMULATE_PREPARES in particular.

Denis de Bernardy
  • 67,991
  • 12
  • 114
  • 140
0

SQL injection is the reason why prepared statements are preferred way. If your query is constant then there is no reason to use prepared statements. If you are sure that your query will be safe even if you construct it by string concatenation then it is ok to skip prepared statements.

Constants are OK.

$sql = "SELECT * FROM foobar";

If there is no chance that $id variable will contain any other type of data than int then this is ok.

$sql = "SELECT * FROM users WHERE id=".$id;

Usually it is easy to fail to ensure that the data contained in variable is the right type so prepared statements are more secure way of constructing the query.

oikku
  • 502
  • 2
  • 7