1

I am developing a query browser in Java where I want to restrict users from manipulating data. So I am using executeQuery as I searched many questions and all are answered as executeQuery is used to select and does not allow data manipulation as this on link.

It's working just fine with MySQL but when its comes to Oracle I am getting the error

ORA 00900: invalid SQL statement

but the real problem is, in database the statement is updating the record. I am getting the same error for update, delete, insert and drop but all commands are manipulating the data in database.

Now I have the option to check whether my query string start with data manipulation keywords but I am trying to avoid that checking. It's working absolutely fine in MySSQL, so I amnot getting what is the issue with Oracle.

Below is the code sample:

Connection conn = null;
Statement stmt = null;
ResultSet query_set = null;
try {
    String query = "insert into users values(1,'name')";
    Class.forName ("oracleDriver"); //Oracle driver as our database is Oracle.
    conn = DriverManager.getConnection("oracleDbUrl", "dbUsername", "dbchecksum"); //login credentials to the database
    stmt = conn.createStatement();
    query_set = stmt.executeQuery(query);
} catch(Exception e) {
    e.printStackTrace();
}

Kindly suggest how I can restrict users from data manipulation without applying check on the query string and what is the reason behind this Oracle behavior.

3
  • Why not restrict it on server side ?
    – a1ex07
    Jan 6, 2017 at 17:43
  • I can do that but as I have already mentioned I want to understand the reason behind this
    – DnA
    Jan 6, 2017 at 17:47
  • Will it work if you just issue SET TRANSACTION READ ONLY statement ?
    – a1ex07
    Jan 6, 2017 at 18:52

1 Answer 1

2

The JDBC standard does not specify the executeQuery() method behaviour, apart from requiring it to throw an exception when the statement returns something other than a single result set. The Oracle driver seems to comply with this requirement. The method is intended to be used with queries, but nowhere in the the documentation is it stated that it should prevent you from using it with DML statements.

You can choose to handle this exception and roll back the transaction, if you want to prevent changes to your data. Obviously, you will have to disable autocommit for this to work.

The correct way to achieve what you want would be to grant only select privileges to the user ID your application connects with.

2
  • U r right about JDBC standard about executeQuery but Oracle giving error as I mentioned in question but it also executing the query n committing the result
    – DnA
    Jan 6, 2017 at 18:32
  • Oracle has every right to do so, because the standard does not say otherwise. Since you are catching the exception and not rolling back the transaction, the driver by default commits the transaction on connection close. If you re-throw a runtime exception, it might roll back the transaction, but in the interest of code quality and portability you should do the rollback yourself when you feel it is necessary.
    – mustaccio
    Jan 6, 2017 at 18:57

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.