0

Using an Oracle Database through a Java application, I need to execute a SET CURRENT SCHEMA statement using a PreparedStatement to overcome potential SQL injection.

So, I cannot do

public void executeSetCurrentSchema(Connection con, String schema){
  try{
     Statement stmt = con.createStatement();
     stmt.execute("SET CURRENT SCHEMA " + schema);

  } catch(Exception){}
}

but instead I'd like to use a prepared statement, that won't accept a "SET" command, instead I can do a "SELECT setCurrentSchema(" + schema + ")" if such a function would exists.

Is there any function I can issue using a SELECT to set the schema?

  • Please provide an example of what you are trying to do. – Joe W Feb 15 at 12:39
  • Not possible. The schema name is an identifier and you can't pass identifiers as parameters. You could use "SET CURRENT SCHEMA \"" + schema.toUpperCase() + '"') instead if you know you never create user names that require quoting (and thus would be case-sensitive) – a_horse_with_no_name Feb 15 at 12:52
  • @a_horse_with_no_name I saw you suggesting to use execute "ALTER SESSION SET current_schema ?". What's wrong with that one? – JeanValjean Feb 15 at 13:02
  • That does the same thing (and is subject to the same restrictions when run as a prepared statement) – a_horse_with_no_name Feb 15 at 13:09
2

Always use the DBMS_ASSERT package when dealing with variable Database Objects that are used within Dynamic SQL statements.

Example

create or replace
procedure owner_of_proc.setCurrentSchema( schema_name in varchar2 )
as
  asserted_schema_name  varchar2(30 byte); -- set to 128 BYTE for 12c+ extended names
  sql_text              varchar2(200);
begin

  asserted_schema_name := DBMS_ASSERT.SCHEMA_NAME( schema_name );

  sql_text := 'ALTER SESSION SET CURRENT_SCHEMA = "' || asserted_schema_name || '"';

  -- always have the ability to log the generated SQL text for Debugging purposes
  -- logger.debug( 'SQL =' || chr(10) || sql_text );

  execute immediate sql_text;
end;
/

Your Java code would be:

public void executeSetCurrentSchema(Connection con, String schema){
  try{
     Statement stmt = con.createStatement();

     // I think this is how you use BIND variables in Java
     stmt.execute("begin owner_of_proc.setCurrentSchema( ? ) end;", schema);

  } catch(Exception e){
    /* Please fill this out
       It is the Java equivalent of WHEN OTHERS THEN NULL;

       You need to catch (and handle) "ORA-44001: Invalid schema name"
     */
  }
}
  • I asked if there is any function, not if I can create one. I do see your point, however +1 – JeanValjean Feb 15 at 15:31

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.