Query with dynamic schema without using string concatenation

Question

I have a system that uses a Oracle database, with a schema that is different from the application user. The schema name itself is not known in advance, so we can't just hardcode it. It's a system property.

Most of the data access is through Hibernate, which can specify the default schema on connection so this is not a problem in those cases.

However, there are a few places where plain SQL queries are used (using spring jdbcTemplate). So right now we have something that boils down to:

Map<String,Object> result = jdbcTemplate.queryForMap("SELECT A, B, C FROM "+schema+".TABLE WHERE blablablah");

And this, of course, is an open SQL injection vulnerability. We're planning security audits and this will be flagged for sure.

So the question is: How do I specify the schema on the query, be it with jdbcTemplate, another Sprint data access utility, or even plain jdbc?

Thank you, JGN

Do you really want to specify it for each statement - do you access objects from multiple schemas? Or could you set current_schema as each session is created, either in your code of via a login trigger that recognises your application user? — Alex Poole
– Alex Poole, Commented Mar 30, 2016 at 14:27
You could create Oracle synonyms for the database objects you reference. Then you wouldn't need to specify a schema in your SQL at all. — GriffeyDog
– GriffeyDog, Commented Mar 30, 2016 at 14:52
The problem is that the schema name is not known at coding time - hence, we'd have to use something like: update("ALTER SESSION SET_CURRENT_SCHEMA=?", schemaNameFromAppProperties); which I think doesn't work with Oracle, or something like this: update("ALTER SESSION SET CURRENT_SCHEMA=" + schemaNameFromAppProperties) which will get flagged again as SQL injection vulnerability in the scan. — JGN
– JGN, Commented Mar 30, 2016 at 14:52
The latter isn't really a problem for SQL injection though, as you'd control where the schema value comes from, e.g. a property file. Unless someone malicious can modify that, in which case you probably have bigger problems. A login trigger would hide it from your application completely, but you may prefer the control. — Alex Poole
– Alex Poole, Commented Mar 30, 2016 at 14:55
I know I control it, but the folks that control the static scan are not the most open to reason ;) — JGN
– JGN, Commented Mar 30, 2016 at 15:26

wero · Accepted Answer · 2016-03-30 14:44:29Z

3

You can use Connection.setSchema to specify the schema for a JDBC connection. This should be done before you create the Statement to execute a SQL command.

answered Mar 30, 2016 at 14:44

wero

33.1k3 gold badges63 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JGN Over a year ago

Accepting this answer as it covers the "even using plain jdbc" request. I'll try to propagate it into the jdbcTemplate (maybe by overloading the DataSource.getConnection method, I'm already using a custom DataSource), but at worst I can use plain JDBC with this suggestion. Thank you.

JGN Over a year ago

One caveat to this answer - Connection.setSchema(schema) was added on Java 1.7 - therefore, not all drivers will support it (for example, Oracle supports it only from version 12 of the drivers as far as I could find), and you will get weird NoSuchMethod errors at runtime.

Collectives™ on Stack Overflow

Query with dynamic schema without using string concatenation

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related