Is it worth using prepared statements on Android?

When it comes to SQL queries, there are two ways you can execute them from your program, whether it targets Android or not. One is ad hoc - create an SQL statement that finds your data (or manipulates it), with all the parameters of the actual query clauses already embedded in it. For example:

SELECT * FROM someTable WHERE a=5 AND b='CCC' AND c IN (1, 2, 3)

This is simple and straightforward. However, this approach has a few disadvantages, as it is well known by the development community. The most obvious and most dangerous is security, or the lack of such. Ff query parameters come from user input, carelessly building the query directly out of them might be very unwise. There is even a special name for the attack vector exploiting this approach - SQL injection.

In addition, if the same query is executed time and time again, but with different parameters, the plan executor of the relevant database engine will have to build the same execution repeatedly. This is not very efficient, especially when the query is relatively large and complex. Wouldn’t it be nice if we could optimize the query once and then reuse it?

This is exactly what prepared statements are for. The idea is simple - create a statement, where query parameters are replaced by special placeholders, usually database and API-specific - this will be phase one. Phase two - reuse the query, supplying appropriate new values on each invocation. For example, our original query can be rewritten as follows:

SELECT * FROM someTable WHERE a=? AND b=? AND c IN (?, ?, ?)

When the time comes to actually execute this statement, we supply the actual values for the parameters, represented by question marks. The engine/API handles the values correctly and safely, even if they happen to contain bits of SQL embedded with malicious intent. The execution plan is also prepared already, so we will get our results faster.

This sounds so good in theory, that recently I decided to test it in practice in an Android project of mine. There I have a certain number of relatively complicated queries executing over and over again. So, I rewrote the code using prepared statements. Total execution time went from 53 seconds to … 56 seconds. Hmm, assuming there are no bugs in the code, this doesn’t look too encouraging.