Setting Postgresql schema with hibernate parameters

Question

I have a multi-tenant application using schemas to separate tenants. My connection pool does not support multi-tenant out-of-the-box (Dropwizard) - so I am trying to set the tenant's schema manually on retrieval. However, I am getting an SQL syntax error.

Code:

NativeQuery fpq = this.getSessionFactory()
                          .getCurrentSession()
                          .createNativeQuery("SET schema=?");
fpq.setParameter(1, tenantSchema);
fpq.executeUpdate();

Error:

DEBUG org.hibernate.SQL: SET schema=?
TRACE org.hibernate.type.descriptor.sql.BasicBinder: binding parameter [1] as [VARCHAR] - [myapplication_client1]
WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper: SQL Error: 0, SQLState: 42601
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper: ERROR: syntax error at or near "$1"
  Position: 12

I've also tried "SET search_path=?" and "SET search_path TO ?" with the same result. This seems pretty straight forward, what am I doing wrong? The tenantSchema variable is generated using an untrustworthy string, so it is important to build the parameters correctly and not manually concat the SQL.

Update: Stepping through the debugger, I have verified Hibernate is correctly converting the statement to SET schema 'myapplication_client1' - but it still fails, I don't know why. It definitely has something to do with setParameter since the query runs fine if I hard-code it.

you should be able to modify your sql to include the schema e.g. select colA from myschema.mytable; — Scary Wombat
– Scary Wombat, Commented Apr 13, 2017 at 2:59
@ScaryWombat unfortunately that is not an option. The tenant setting logic has to be in a single class. It is important every query only operates within its own tenant and relying on every statement to manually set the schema opens up too large a risk — lightswitch05
– lightswitch05, Commented Apr 13, 2017 at 3:05

user330315 · Accepted Answer · 2017-04-13 05:54:31Z

You can't pass an identifier as a parameter in a prepared statement. The schema name is an identifier just like a table name and you can't do select * from ? either.

You have to concatenate the parameter to your query:

NativeQuery fpq = this.getSessionFactory()
                          .getCurrentSession()
                          .createNativeQuery("SET schema=" + tenantSchema);
fpq.executeUpdate();

As this is a parameter from an "untrusted" source, you will need to verify that it's correct before doing that. A simple way would be to retrieve all schemas using DatabaseMetaData.getSchemas() and validate that the parameter is one of them.

Unrelated:

Hibernate is correctly converting the statement to SET schema 'myapplication_client1'

No, Hibernate is not converting anything. This is just the way the Postgres JDBC driver implements the PreparedStatement.toString() method. It does not mean it's the query sent to the database. The toString() method is only there for debugging purposes as a convenience (e.g. for logging purposes)

Collectives™ on Stack Overflow

Setting Postgresql schema with hibernate parameters

Code:

Error:

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Code:

Error:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related