Preventing SQL Injection in JDBC without using Prepared Statements

Question

I am aware that using Prepared Statements is the best way to protect against SQL Injection (and syntax errors due to unescaped characters in unchecked input).

My current situation is that I am writing some Java code to move data from one third party application to another. The destination application uses a proprietary version of Sybase and so whilst I do have the JTDS JDBC driver PreparedStatement fails, as the driver uses temporary stored procedures which aren't supported in this particular flavour of the database. So I only have Statement to work with and I have no control over the user input as it is coming from another application.

There is this similar question but that is focused on fixing the problem where you have a parameter such as a table which cannot be handled via a Prepared Statement. My case is different and hopefully simpler, since I have straightforward SQL statements. I would like to know if there is a best practice for replicating something like the following without using PreparedStatement:

PreparedStatement statement = connection.prepareStatement("UPDATE mytable SET value=? WHERE id=?");
statement.setInt(1, getID());
statement.setString(2,userInput);
statement.executeUpdate();

So I guess the problem is how can I sanitise the user input reliably? I can try to do that myself from scratch but this seems like a bad idea as there is likely to be at least one edge case I'd miss, so I was hoping there was a library out there that would do that for me, but I haven't been able to find one so far.

Solution 1

The ESAPI library has procedures for escaping input for SQL and for developing your own db specific encoders if necessary.

Solution 2

Check out JTDS FAQ - I'm pretty confident that with a combination of properties prepareSQL and maxStatements you could go there (or "could have gone" as you probably completed that task years ago :-) )