Statement.executeQuery() and SQL injection

Question

We have internal web based tool, that allows arbitrary SQL queries to database. Access to the the tool is limited. I am more worried about mistakes or accidents than someone intentionally tampering data or attacks.

The queries are ultimately executed by Statement.executeQuery and results are returned. I tried few test runs and it seems like executeQuery, as documentation suggests, fails on any other call than select.

Are there any other SQL statements / combinations that can trick executeQuery call to cuase changes in database (insert/update/delete/drop etc.). I tried few SQL injection examples available on the web and it failed in every case.

Answer 1

SQL injection attacks are possible when the query arguments are concatenated to the query template, therefore allowing a rogue attacker to inject a malicious code.

If your Statement queries don't take any parameter, the client has no way to inject a malicious SQL routine. Whenever you have parameterized queries, you should use PreparedStatement instead.

As for statement restriction, you should have the DBA provide you a database user account that can only execute SELECT and DML statements on the application schema only. DROP and TRUNCATE privileges shouldn't be allowed to the application user account.

If you use dynamic schema upgrade (e.g. FleywayDB), you can use a separate database account and a separate DataSource for that specific case.

This way, you will also protect you against data corruptions due to application developers mistakes.