Oracle set schema in a PreparedStatement

oracle-12cschema

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?

Best Answer

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"
     */
  }
}
Related Question