CODEWITHSUNDEEP
javadatabaseoracle-databasejdbc
Amol Solanke
Amol Solanke

Reputation: 777

Restrict executeQuery from data manipulation in java for oracle

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.

Upvotes: 1

Views: 1039

Answers (2)

Jon Heller
Jon Heller

Reputation: 36807

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

Upvotes: 0

Amol Solanke
Amol Solanke

Reputation: 777

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

Upvotes: 1

Related Questions