Should you use prepared statements for their escaping only?
mysql, php
Solution
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.
Problem
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?