2

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.

Improve this question
2
  • 2
    I'm not aware of any, but you could make sure that doesn't happen by connecting with a user that doesn't have any write access to the database. Commented Jun 24, 2015 at 4:46
  • If you can construct a query that performs an update and returns something (eg something like INSERT ... RETURNING or a block of procedural code as supported by some database), then executeQuery won't protect you. Also note that some JDBC drivers will give you an error after executing the query if it didn't produce a result set. Commented Jun 24, 2015 at 8:02

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

On the other hand: if your users are allowed to execute arbitrary queries, then you don't need SQL injection to execute malicious code.
The DBA must provide a limited privileges account to the application developer, and once the application is deployed the DataSource will use only that particular DB user credentials. From this point, the application client has no way to switch database users.
That is true, but just assuming that executeQuery itself will protect you (as suggested by the question) is not enough.
The PreparedStatement provides SQL injection protection, as opposed to plain Statement. The parameter validation acts before sending the query command to the database server.
But it won't protect you if your users can execute arbitrary queries (which is the topic of the question).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.