Does using prepared statement to prevent SQL injection affect performance?

Question

Apparently the best way to prevent SQL injection is to use prepared statements. However prepared statements are designed for something else altogether:

In database management systems, a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency.
[...]
On the other hand, if a query is executed only once, server-side prepared statements can be slower because of the additional round-trip to the server. Implementation limitations may also lead to performance penalties: some versions of MySQL did not cache results of prepared queries, and some DBMSs such as PostgreSQL do not perform additional query optimization during execution.

I am guessing that prepared statements are primarily designed to be used inside tight loops in order to cut down the compile time of repeated statements. SQL injection prevention is just a bonus.

Assuming we are not using PDO or "emulated" prepared statements, does it make sense to use prepared statements for queries that are used only once on a page.

Answer 1

Just a personal thought..

Despite the possible performance penalties for SQL statements executed once per page I believe it makes sense.

Every application usually consist of combination of SQL statements. Some are executed just once. Some multiple times. One can usually not ignore SQL injection on some places though. Making it all parametrized throught the application keeps the code more consistent. Perhaps more elegant (e.g. if I consider apostrophes in strings - not having to escape them by doubling them etc.). Prepared statements take care of all of that. I have never measured the performance penalties for parametrized queries but I would probably never focus on them in case of any performance issues. If I have performance issues it usually means problem somwhere else (execution plan, right indexes, statistics etc.).

Answer 2

The answer certainly depends on what RDBMS you're using.

With web applications I would alsways rate security concerns higher than performance concerns, especially if the performance difference will probably be almost unmesurable.

That said, Oracle (and other RDBMS probably too) differentiates between soft and hard parses. When Oracle is asked to prepare a statement, it first calculates a hash value for the statement which is compared to a list of already prepared statements. If it is found in this list, Oracle knows that it has already parsed the statement and does not need to parse it again. This is referred to as a soft parse. If the statment is really new (so that is not found in this list), it has to hard-parse the statement. A hard parse is usually considered much more costly than a soft parse.

So, when your application issues a statement only once per page, the RDBMS will still see the same statement multiple times and will be able to soft-parse the statements from the 2nd onwards. This is even a huge benefit over the alternative where you use a different statement for each page request because in the alternative, each statement must be hard-parsed.

Answer 3

The following applies to SQL Server

It is possible for an individual prepared statement to perform worse than an ad hoc query. However, the overall performance should be better.

Prepared statement performing worse than an ad hoc query

Suppose we have a table containing records for the past 10 years and 10 possible statuses. Assume that the data is evenly distributed and we have these queries:

... WHERE date >= '2017-01-01' AND status = 1
... WHERE date >= '2009-01-01' AND status = 1
... WHERE date >= ? AND status = ?

For the first two queries (ad hoc) SQL Server could generate separate execution plans for each query e.g. using index on date column for the first query and using index on status column for second query.

For the third query (prepared) SQL Server will generate exactly one execution plan which could be best for most situations but not all. For example SQL Server might generate an execution plan that uses index on date column which would be efficient for recent dates but not past dates.

Ad hoc query performing worse than prepared statement

One of the problems with ad hoc queries is that they eventually lead to "plan cache bloat". When this happens, plans frequently get kicked out from the plan cache and recompiled when same query is executed again. Query compilation is an expensive process.

Observations

After converting my web application from ad hoc queries to prepared statements, I noticed a significant improvement in execution time. The plan cache bloat was eliminated and queries were not recompiled as often as they would with ad hoc queries saving precious CPU cycles and memory.