14

I've been reading a lot about prepared statements and in everything I've read, no one talks about the downsides of using them. Therefore, I'm wondering if there are any "there be dragons" spots that people tend to overlook?

Scott
  • 11,046
  • 10
  • 51
  • 83
  • Everything in IT has downsides, you just need to check if they are really worth worrying about in your specific problem. :) This link in SO http://stackoverflow.com/questions/535464/when-not-to-use-prepared-statements/537834 has some examples about prepared statements disadvantages. – GmonC Sep 01 '09 at 15:32

5 Answers5

8

Prepared statement is just a parsed and precompiled SQL statement which just waits for the bound variables to be provided to be executed.

Any executed statement becomes prepared sooner or later (it need to be parsed, optimized, compiled and then executed).

A prepared statement just reuses the results of parsing, optimization and compilation.

Usually database systems use some kind of optimization to save some time on query preparation even if you don't use prepared queries yourself.

Oracle, for instance, when parsing a query first checks the library cache, and if the same statement had already been parsed, it uses the cached execution plan instead.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 4
    I think this answer is missing a key problem with prepared statements, eg. that the query plan is optimized according to data statistics present at the time the query is prepared, and may not be optimal in the future, when the query is executed. So this IS a downside of a prepared statement. – egbokul Oct 18 '11 at 08:24
  • 1
    Some articles mentioning a similar downside: https://medium.com/@devinburnette/be-prepared-7768d1a111e1 --- and --- https://www.vividcortex.com/blog/2014/11/19/analyzing-prepared-statement-performance-with-vividcortex/ – Kalnode Jan 31 '18 at 23:23
3

If you use a statement only once, or if you automatically generate dynamic sql statements (and either properly escape everythin or know for certain your parameters have only safe characters) then you should not use prepared statements.

flybywire
  • 261,858
  • 191
  • 397
  • 503
  • 3
    Of course, this is extremely rare. I find it far easier to just have the prepared statement stuff on the server side do the input sanitization. – Powerlord Sep 01 '09 at 15:29
3

There is one other small issue with prepared statements vs dynamic sql, and that is that it can be harder to debug them. With dynamic sql, you can always just write out a problem query to a log file and run it directly on the server exactly as your program sees it. With prepared statements it can take a little more work to test your query with a specific set of parameters determined from crash data. But not that much more, and the extra security definitely justifies the cost.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
2

in some situations, the database engine might come up with an inferior query plan when using a prepared statement (because it can't make the right assumptions without having the actual bind values for a search).

see e.g. the "Notes" section at

http://www.postgresql.org/docs/current/static/sql-prepare.html

so it might be worth testing your queries with and without preparing statements to find out which is faster. ideally, you would then decide on a per-statement basis whether to use prepared statements or not, although not all ORMs will allow you to do that.

pmeidl
  • 221
  • 1
  • 3
  • 6
  • 2
    I've found that the best pattern is to use an all or nothing approach to prepared statements. The DBMS is smarter than most of us, and can figure out the best plan most of the time. Also I'm sure you know that different DBMSs will have different query plan strategies, so the link shouldn't be used as a catch-all for prepared statements. If you are using your DBMS for web work, the fastest and easiest way to defend against SQL injection attacks is to always use prepared statements. – bakoyaro Oct 18 '11 at 18:20
1

The only downside that I can think of is that they take up memory on the server. It's not much, but there are probably some edge cases where it would be a problem but I'm hard pressed to think of any.

Adam Ruth
  • 3,575
  • 1
  • 21
  • 20