prepared statements v text queries

[aerosuidae]

I know some folks here love prepared statements and others swear by manually constructed text queries A number of posts lately have got me thinking about the issue, so I though't I'd fan the flames a bit

For the record: Personally, I use both query types depending on the situation and I have no real religious affiliation either way.

Reasons to use prepared statements

* PS can actually be much faster than normal queries, if you execute the PS more than once, because:

• After the first execution, the database does not have to parse the query text again.
• The application never has to escape data.

That said, I've seen between -50% and +200% speed change on real world applications moving from text queries to PS. It is very traffic dependent and there is no simple answer.

The point about escaping data is also very nebulous: Unless you're writing very large blocks of text to the database, escaping strings is typically a non-issue. Most libraries have put a lot of effort into making it cheap, and odds-on it is not the bottleneck.

* PS protect against against most forms of SQL injection, because the programmer does not need to remember to escape stuff.

* Some people also think PS look nicer, which sounds like fluff (!) but in fact is important because it decreases their code maintenance costs.

* PS can also be easier to maintain because you are enforcing encapsulation/factorisation of your queries in one place, rather than having the same query scattered throughout your source code (ie, for the same reasons OO code is often cheaper to maintain than [insert-other-coding-method-here]).

Reasons not to use prepared statements

* An extra network round trip is required between application and database if the PS is a once off query and not reused.

* Some SQL statements can not be prepared, so you may have to mix and match PS and text queries in your source code anyway! This is database and client library specific.

* Some environments do not implement server side prepared statements; some just emulate them on the client library side and end up sending normal text queries over the network anyway. Sometimes you can configure this behavior. Do check.

* MySQL specific: Not all PS can use the query cache (for internal reasons), even in MySQL 5.1. If your application makes good use of the query cache with normal text queries, you might end up seeing a slow down moving to PS, and potentially increased memory usage by the database. "It depends. Test it." is the answer here

Finally, an entirely personal note

I think the decision between PS and text queries is less important than many think. A well designed application will somehow encapsulate database access behind an API or a Class so that 90% of the application source code should not have queries in it anyway, just calls to the backend... Just my $0.02!

Any thoughts?

[dsheroh]

Overall, a solid comparison.  But, of course, I have a couple things to add in favor of prepared statements...

Reasons to use prepared statements

The point about escaping data is also very nebulous: Unless you're writing very large blocks of text to the database, escaping strings is typically a non-issue. Most libraries have put a lot of effort into making it cheap, and odds-on it is not the bottleneck.

Although I did raise the possibility in another thread that escaping may become a performance hit in some cases, my real beef with escaping has nothing to do with execution speed.  It's that you not only have to remember to do it, but you also have to remember which data has been escaped already and which data has not.  Using SQL placeholders eliminates the need to think about those details without imposing any additional requirements on the programmer.

* PS protect against against most forms of SQL injection, because the programmer does not need to remember to escape stuff.

"Most"?  SQL placeholders and prepared statements send the structure of the query (the statement) and the data (placeholder values) to the database engine via completely separate channels and at completely different times (the "extra round trip to the database" that some complain about).  This means that, barring severe bugs in the database library, there is absolutely no way that the database engine could be tricked into interpreting data as an SQL command.  This technique makes SQL injection (theoretically) impossible; if you're aware of any cases where SQL injection attacks have been successfully made using data passed via an SQL placeholder, I would be extremely interested to hear about them.

Reasons not to use prepared statements

* An extra network round trip is required between application and database if the PS is a once off query and not reused.

This is something of a red herring, IMO.  If your load is heavy enough for this to be relevant, then you should be running in a persistent environment (assuming web apps here, the cost of starting up a new instance of the app to handle each incoming request is far greater than the cost of a few extra round trips to the database) and, in a persistent environment, prepared queries can be reused from one request to the next.  If each process is allowed to handle 1,000 requests before being killed off, then even a query used by only 1% of requests will be prepared once and executed (on average) 10 times.

* Some SQL statements can not be prepared, so you may have to mix and match PS and text queries in your source code anyway! This is database and client library specific.

Yes...  This is a sad fact of life.

I think the decision between PS and text queries is less important than many think. A well designed application will somehow encapsulate database access behind an API or a Class so that 90% of the application source code should not have queries in it anyway, just calls to the backend...

Very good point and one which is often overlooked.

[aerosuidae]

It's that you not only have to remember to do it, but you also have to remember which data has been escaped already and which data has not.

A good point, certainly, though I would argue it can be avoided with proper application design And yes, the choice to use PS counts as proper app design, just not the only one. Another is properly done ORM, for example.

"Most"?  SQL placeholders and prepared statements send the structure of the query (the statement) and the data (placeholder values) to the database engine via completely separate channels and at completely different times (the "extra round trip to the database" that some complain about).  This means that, barring severe bugs in the database library, there is absolutely no way that the database engine could be tricked into interpreting data as an SQL command.  This technique makes SQL injection (theoretically) impossible; if you're aware of any cases where SQL injection attacks have been successfully made using data passed via an SQL placeholder, I would be extremely interested to hear about them.

Good idea to bring up the theory! Yes, I say "most".

You're quite correct about the theory. But in practice, the PS bugs you mention do pop up from time to time, whether it be a server side problem or character set issues in client libraries or changes in expected behavior during upgrade or [insert-weirdness-here]. And, SQL injection can cause problems even when it doesn't successfully penetrate through to the database layer and execute, simply by triggering errors the app may not be ready to handle.

Such bugs are not a reason not to use PS, but they are a reason to remain vigilant and not assume that the simple use of PS solves the issue for ever. Obviously similar bugs in manual string escaping could pop up too and require the same vigilance, so this is an apples v oranges argument

* An extra network round trip is required between application and database if the PS is a once off query and not reused.

This is something of a red herring, IMO.  If your load is heavy enough for this to be relevant, then you should be running in a persistent environment (assuming web apps here, the cost of starting up a new instance of the app to handle each incoming request is far greater than the cost of a few extra round trips to the database) and, in a persistent environment, prepared queries can be reused from one request to the next.  If each process is allowed to handle 1,000 requests before being killed off, then even a query used by only 1% of requests will be prepared once and executed (on average) 10 times.

Your persistent environment is reusing the PS by only preparing it once per N executions (which is good and proper). I was referring to the specific case of preparing then executing the PS every time, because someone arbitrarily made the choice to use PS across the board without considering the environment or how the app will scale up.

The really nasty thing about server load problems is that they often exceed some resource limit and cause things to stop abruptly and catastrophically, rather than gradually slowing down and giving you plenty of polite warning. Odds are the probelm won't even be the nice predictable thing like system load or network bandwidth either, but something silly like a process running out of file handles because the extra round trips doubled the required handles per second.

I don't sound jaded and cynical, do I ? 

[aerosuidae]

Btw, hopefully you all follow xkcd:

http://xkcd.com/327/