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 — Amol Solanke
– Amol Solanke, Commented Jan 6, 2017 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
– Alex Poole, Commented Jan 6, 2017 at 17:06
@Alex No! my remain application allows data manipulation only query browser tab is having this restriction — Amol Solanke
– Amol Solanke, Commented Jan 6, 2017 at 17:09
It could still connect as a different user to the rest of the application. — Alex Poole
– Alex Poole, Commented Jan 6, 2017 at 17:10
And more over am also wondering that if MySQL not allowing then why this happening in Oracle — Amol Solanke
– Amol Solanke, Commented Jan 6, 2017 at 17:11

Amol Solanke · Accepted Answer · 2017-01-15 07:33:19Z

1

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();
    }

edited Jan 15, 2017 at 7:33

answered Jan 14, 2017 at 9:08

Amol Solanke

7853 gold badges11 silver badges33 bronze badges

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

2 Comments

Jon Heller Over a year ago

You might want to check that this prevents DDL commands like drop table ... from running, since DDL in Oracle always auto-commits.

Amol Solanke Over a year ago

@JonHeller Yes it was allowing drop and delete so modified my answer

Jon Heller · Accepted Answer · 2017-01-16 01:06:54Z

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

Collectives™ on Stack Overflow

Restrict executeQuery from data manipulation in java for oracle

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related