Should you use prepared statements for their escaping only? [closed]

Question

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?

Answer 1

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.

Answer 2

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.