Preventing SQL injection without prepared statements (JDBC)

Question

I have a database log appender that inserts a variable number of log lines into the database every once in a while.

I'd like to create an SQL statement in a way that prevents SQL injection, but not using server-side prepared statements (because I have a variable number of rows in every select, caching them won't help but might hurt performance here).

I also like the convenience of prepared statments, and prefer them to string concatination. Is there something like a 'client side prepared statement' ?

Answer 1

Not sure if I understand your question correctly. Is there something in PreparedStatement that isn't fitting your needs?

I think that whether or not the statement is cached on the server side is an implementation detail of the database driver and the specific database you're using; if your query/statement changes over time than this should have no impact - the cached/compiled statements simply won't be used.

Answer 2

It sounds like you haven't benchmarked the simplest solution - prepared statements. You say that they "might hurt performance" but until you've tested it, you really won't know.

I would definitely test prepared statements first. Even if they do hamper performance slightly, until you've tested them you won't know whether you can still achieve the performance you require.

Why spend time trying to find alternative solutions when you haven't tried the most obvious one?

If you find that prepared statement execution plan caching is costly, you may well find there are DB-specific ways of tuning or disabling it.

Answer 3

what's wrong with using a regular prepared statement e.g. in the following pseudocode:

DatabaseConnection connection;
PreparedStatement insertStatement = ...;

    ...

connection.beginTransaction();
for (Item item : items)
{
   insertStatement.setParameter(1, item);
   insertStatement.execute();
}
connection.commitTransaction();

A smart database implementation will batch up several inserts into one communications exchange w/ the database server.

Answer 4

I can't think of a reason why you shouldn't use prepared statements. If you're running this on a J2EE server using connection pooling the server keeps your connections open, and the server caches your access/execution plans. It's not the data it caches!

If you're closing your connection every time, then you're probably not gaining any performance. But you still get the SQL injection prevention

Most java performance tuning books will tell you the same: Java performance tuning

Answer 5

Prepared Statements don't care about client or server side.

Use them and drop any SQL string concatenation. There is not a single reason to not use Prepared Statements.

Answer 6

First, Jon's answer that you should go with the most obvious solution until performance is measured to be a problem is certainly the right approach in general.

I don't think your performance concerns are misplaced. I have certainly seen precompiled complex statements fail dramatically on the performance scale (on MS-SQL 2000). The reason is the statement was so complex that it had several potential execution paths depending on the parameters, but the compilation locked one in for one set of parameters, and the next set of parameters were too slow, whereas a recompile would force a recalculation of the execution plan more appropriate for the different set of parameters.

But that concern is very far fetched until you see it in practice.

The underlying problem here is that parameter escaping is database specific, so unless the JDBC driver for your database is giving you something non-standard to do this (highly unlikely), you are going to have to have a different library or different escaping mechanism that is very specific to this one database.

From the wording of your question, it doesn't sound like your performance concerns have yet come to the point of meriting finding (or developing) such a solution.

It should also be noted that although JDBC drivers may not all behave this way, technically according to the spec the precompilation is supposed to be cached in the PreparedStatement object, and if you throw that away and get a new PreparedStatement every time, it should not actually be caching anything, so the whole issue may be mute and would need to be investigated for your specific JDBC driver.

From the spec:

A SQL statement with or without IN parameters can be pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.