0

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.

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

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.

| improve this answer | | | | |
  • 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 '17 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 '17 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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