Restrict executeQuery from data manipulation in java for oracle

Question

I am developing query browser in java where I want to restrict users from manipulating data. So am using executeQuery as I searched many question and all are answered that executeQuery is used to select and does not allow data manipulation as this on link.
Its working just fine with MySql but when its comes to Oracle am getting the error

ORA 00900: invalid SQL statement

but the real problem is, in database its updating the record. I am getting the same error for update, delete, insert and drop but all commands 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 and its working absolutely fine in MySql but not 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 can I restrict users from data manipulation without applying checks on query string and the reason behind this behavior of Oracle.

no I want to allow only date query statements such as select or desc so am using `stmt.executeQuery()` but its allowing data manipulation queries also in oracle — DnA, Jan 06 '17 at 16:00
You're getting ORA-00900 *and* the new record is being inserted? Also, why not create a new user that only has select privileges on the data you want exposed, and have your Java code connect as that user? If the account cannot perform inserts, updates or deletes on any of your tables then you'll have less to worry about. — Alex Poole, Jan 06 '17 at 17:06
@Alex No! my remain application allows data manipulation only query browser tab is having this restriction — DnA, Jan 06 '17 at 17:09
It could still connect as a different user to the rest of the application. — Alex Poole, Jan 06 '17 at 17:10
And more over am also wondering that if MySQL not allowing then why this happening in Oracle — DnA, Jan 06 '17 at 17:11
[The docs](https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeQuery(java.lang.String)) just say the argument is "an SQL statement to be sent to the database, typically a static SQL SELECT statement". It doesn't have to be a query. If you need a result set back you have to use `executeQuery()` (or bind out parms etc); you can't send a query with `executeUpdate()`; while `execute()` is more flexible. It isn't doing anything it isn't supposed to. — Alex Poole, Jan 06 '17 at 17:19
@Alex you might be right. I can simply put check in query string before passing it to executeQuery instead of creating new user but I'm more interested in knowing the reason behind such behavior — DnA, Jan 06 '17 at 17:29
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/132523/discussion-between-amol-solanke-and-alex-poole). — DnA, Jan 06 '17 at 17:52

DnA · Accepted Answer · 2017-01-15T07:33:19.667

Issue is solved by checking whether query string start with select or not but still I didn't understood the reason why oracle behaving like this. Answer with valid reason is open.

    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();
        if(query.startsWith("select"))
            query_set = stmt.executeQuery(query);
        else
            System.out.println("Only select query allowed");
    } catch(Exception e) {
        e.printStackTrace();
    }

You might want to check that this prevents DDL commands like `drop table ...` from running, since DDL in Oracle always auto-commits. — Jon Heller, Jan 14 '17 at 18:37
@JonHeller Yes it was allowing drop and delete so modified my answer — DnA, Jan 15 '17 at 07:35

score 0 · Answer 2 · answered Jan 16 '17 at 01:06

There are probably at least two better ways to do this. There should be a JDBC method that restricts the type of statements that can run. And it should be possible to have a separate read-only user execute commands.

But if it's really necessary to perform custom statement classification then I recommend you use my open source project PLSQL_LEXER. Oracle SQL is orders of magnitude more complex than a language like Java. Trying to classify it with a few simple string functions will create many incorrect results.

Install PLSQL_LEXER and create this custom function:

create or replace function get_statement_type(p_code clob) return varchar2 is
    v_category varchar2(4000);
    v_statement_type varchar2(4000);
    v_command_name varchar2(4000);
    v_command_type number;
    v_lex_sqlcode number;
    v_lex_sqlerrm varchar2(4000);
begin
    statement_classifier.classify(
        p_tokens => plsql_lexer.lex(p_code),
        p_category => v_category,
        p_statement_type => v_statement_type,
        p_command_name => v_command_name,
        p_command_type => v_command_type,
        p_lex_sqlcode => v_lex_sqlcode,
        p_lex_sqlerrm => v_lex_sqlerrm
    );

    return v_statement_type;
end;
/

Then it can be called to return the statement type for each command. As the examples below demonstrate there are many cases where a SELECT statement will not begin with "SELECT".

select
    get_statement_type('sElEcT * from dual') select1,
    get_statement_type('with cte as (select 1 from dual) select * from cte;') select2,
    get_statement_type('/* comment */ select * from dual') select3,
    get_statement_type('  select 1 whitespace_at_beginning from dual') select4,
    get_statement_type('(((((select 1 parentheses from dual)))))') select5,
    get_statement_type('insert into ...') insert1,
    get_statement_type('alter session set ...') alter_session1
from dual;
/

Results:

SELECT1   SELECT2   SELECT3   SELECT4   SELECT5   INSERT1   ALTER_SESSION1
-------   -------   -------   -------   -------   -------   --------------
SELECT    SELECT    SELECT    SELECT    SELECT    INSERT    ALTER SESSION

Restrict executeQuery from data manipulation in java for oracle

2 Answers2