Best Practices with PreparedStatements; when to and when not to...

Question

I recently have began using prepared statements again in a web application, and I know that it is discouraged to use prepared statements for all the transactions. What I do not know is when it is best to use prepared statements or not.

I have read of when to use and not use them, but none of the examples really tell best practice of using them.

I am trying to figure out which database calls I should be using them for and which ones I should not.

For Example the MySQL website mentions it in "When to use prepared statements" on the following page Prepared Statements-MySQL

Answer 1

In order to prevent SQL Injection it is better to use prepared statements in Java

For more information: http://stackoverflow.com/questions/678400/sql-injections-with-prepared-statements

Answer 2

PreparedStatements have two major uses:

1. Preventing SQL injection attacks. This basically means automated sanitizing of inputs from external sources (web browser is external!) which are going to be saved to the database.
2. Batch processing. If you have a lot of data to enter into/modify in/remove from database at once, PreparedStatement can be used for that. In this case, PreparedStatement optimizes away most of the overhead of such operations and allows you to write fast database batch code.

Both of these reasons are a very compelling ones to justify using PreparedStatement almost always, however depending on how you're using the database you may hit a point where PreparedStatement won't allow you to do what you want.

As an example of such case, I've once written a tool which generated table names on the fly based on runtime properties of certain abstractions which meant that I had to be able to have SQL queries with mutable table names; you can't get those with PreparedStatement so I had to use raw Statements and some preprocessing trickery to get back to utilizing PreparedStatements for SQL injection protection.

Answer 3

The general thumb rule in deciding whether to go for a PreparedStatement or not is:

Use Prepared Statements, unless you have sufficient reason not to. Prepared Statements are compiled before execution therefore lending to better performance, and increased security against SQL injection as the database server takes care of the encoding of special characters.

Going by the article that you have referenced, the list of reasons where I believe Prepared Statements are less useful than normal queries or stored procedures are:

• One-time queries. If your application makes a single query to the database, and this is done infrequently compared to the other queries, it might not make sense to use a Prepared Statement in this case. The rationale is that the Prepared Statement must first be compiled and the 'compiled' form of the statement is cached for later use. For queries that are run infrequently, the compilation is an overhead. But still, it is preferable to use prepared statements, to avoid any SQL injection issues.
• Data-intensive operations. Sometimes Prepared Statements are not as effective as stored procedures, especially when a sequence of operations need to be performed in the same transaction. When you have a business process that requires multiple selects, updates and deletes to be executed against a variety of tables, stored procedures are often better than a bunch of prepared statements executed one after the other. This performance penalty can turn serious as several network trips are made for the execution of multiple statements, which is considerably reduced when invoking a stored procedure. This effect is more pronounced in query batching where several objects are created and destroyed in a short duration of time. This often tends to be a contentious issue between database administrators and application developers, as this is an edge-case; DBAs will believe that the batching of operations is better performed via SPs, while application developers believe that PreparedStatements can handle it (its usually better to have all logic in one tier). It eventually boils down to the application on whether using SPs is an advantage or not.
• Support for native database operations and types.. This might not hold good for MySQL, but in general the JDBC standard does not support all the operations supported by a database, and all the SQL/native/custom types supported by the database. This is more pronounced in the Oracle database (and possibly IBM DB2?), where programmers can create their own types, which require custom Java code to be written as the JDBC standard does not support User-Defined Types in the database. Similarly, other operations in the database need to not supported (as the MySQL document states) - one cannot create users (execute CREATE USER), modify user privileges (perform GRANT operations) etc. using a Prepared Statement. Stored procedures are better suited to this task, as they would have access to the native operation set of the database, either in a direct or indirect manner.