0

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?

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.

  • Yeah, mine example was to make it easy-to-read. I do use Apache DBUtils.closeQuietly for every statement and connection. Lemme try your solution, but now I do see the problem, thanks. – JeanValjean Nov 8 '18 at 13:44

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.