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.

• JeanValjean: 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.

Answer 2

I have working code with

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