PostgreSQL: PreparedStatement to execute set schema command

Question

I need to execute in a Java application a SET schema ? statement, where the placeholder is replaced by a value provided at runtime by the caller.

I assumed I could use PreparedStatement to overcome potential SQL injection, but if I try to execute a prepared statement I get a PSQLException with message

ERROR: syntax error at or near "$1"  Position: 18

If I do execute the SQL by just concatenating the schema name by doing:

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

  } catch(Exception){}
}

Then everything works as expected.

Clearly, this happens with all the latest releases of PostgreSQL

Why I cannot use a PreparedStatement and what should I do instead to prevent SQL injection here?

Answer 1

You can't pass identifiers as parameters. It's similar to select * from ?

However, you can use a function to set the search path:

public void executeSetSchema(Connection con, String schema) {
  try (
    PreparedStatement pstmt = con.prepareStatement("select set_config('search_path', ?, false)");
  ) {
     pstmt.setString(1, schema);
     stmt.execute();
  } catch(Exception) {
  }
}

I moved the prepareStatement() call into a try-with-resources so that the statement is closed properly.

Answer 2

I have working code with

     Statement stmt = conn.createStatement();
     stmt.execute("set search_path to '" + schema + "'");