Prevent SQL Injection without Prepared Statements (JDBC)

Question

I have an application for registering a database that inserts the number of log lines in the database into the database each time.

I would like to create an SQL statement in such a way as to prevent SQL injection, but not using statements prepared on the server (because I have a variable number of rows in each select, caching them will not help, but it can hurt performance here).

I also like the convenience of prepared records and prefer their string concatenation. Is there something like a "prepared client"?

Answer 1

It seems that you did not compare the simplest solution - prepared statements. You say that they "can hurt performance", but until you have tested it, you really won't know.

I would definitely test the prepared statements first. Even if they interfere with performance a bit until you have tested them, you won’t know if you can achieve the required performance.

Why waste time looking for alternative solutions if you have not tried the most obvious?

If you find that prepared execution of a caching execution plan is expensive, you may well find ways to configure or disable it for a specific database.

Answer 2

Not sure if I understood your question correctly. Is there something in PreparedStatement that doesn't fit your needs?

I think that regardless of whether the operator is cached on the server side, this is a detail of the implementation of the database driver and the specific database that you are using; if your request / statement changes over time than this should not have any effect - cached / compiled statements will simply not be used.

Answer 3

First, John replies that you have to go with the most obvious solution until performance is considered a problem, of course, is the right approach in general.

I don't think your performance problems are inappropriate. Of course, I saw that precompiled complex statements do not dramatically affect performance (on MS-SQL 2000). The reason is that the statement was so complex that it had several potential execution paths depending on the parameters, but the compilation was blocked for one set of parameters, and the next set of parameters was too slow, while recompiling would make the recalculation of the execution plan more suitable for a different set of parameters.

But this problem is very far, until you see it in practice.

The main problem here is that parameter escaping is a database specification, so if the JDBC driver for your database does not give you something non-standard for this (unlikely), you will have to have another library or another one that is very specific for this Database.

From the wording of your question, it doesn’t sound like your performance problems still go so far as to find a solution (or develop) such a solution.

It should also be noted that although JDBC drivers may not all behave this way, technically according to the specification it is assumed that the precompilation should be cached in the PreparedStatement object, and if you throw it away and get a new PreparedStatement every time, it should not actually cache anything, so the whole problem can be dumb and should be investigated for your specific JDBC driver.

From the specification:

An SQL statement with or without IN parameters can be precompiled and stored in the PreparedStatement object. Then this object can be used to efficiently execute this statement several times.

Answer 4

what is wrong with using a regular prepared statement, for example. in the following pseudo code:

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

The intelligent database implementation will contain several attachments in one data exchange with the database server.

Answer 5

I cannot think of a reason why you should not use prepared statements. If you use this on a J2EE server using the connection pool, the server keeps your connections open and the server caches your access / execution plans. This is not the data it caches!

If you close your connection every time, then you are probably not getting any performance. But you still get SQL injection prevention

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

Answer 6

Prepared statements do not care about the client or server side.

Use them and remove any concatenation of the SQL string. There is no reason not to use prepared statements.