pgjdbc/pgjdbc — pull request #207 by sehrope (2014-10-14 17:11:21+00:00)
Title: Escape search_path in Connection.setSchema(...)

The `Connection.setSchema(...)` method does not escape the schema name before executing `SET search_path TO ...`. It just wraps it in single quotes as a literal.

This works fine to handle whitespace in a schema name (ex: "`foo bar`") but it does not work if there is a single single-quote (`'`) in the name. For example it will fail with a schema named `foo'bar`.

This patch replaces the single quoted schema name in the query string with an escaped identifier. I've also added a couple tests with funky schema names.

While testing this patch out, I noticed that `Connection.getSchema()` and `Connection.setSchema(...)` don't play nice with each other. Specifically, `setSchema(...)` allows for unescaped input (*which it then escapes/quotes*) but `getSchema()` returns back quoted/escaped text.

This means that the following would not work:

// Cache the old schema:
String oldSchema = conn.getSchema();
conn.setSchema(newSchema);
// .. do some work in newSchema
// Revert back to oldSchema - this won't work as we'd end up double escaping the schema name:
conn.setSchema(oldSchema);

You can see this first hand in the additional schema tests I added. The `assertEquals(...)` comparisons test against the escaped values of the funky schema names rather than the unescaped values. Ex: `\"schema '5\"` vs `schema '5`

I don't think that's correct. `getSchema()` should unescape it's output on the Java side before returning it back. That would be a breaking change for anyone expecting the old behavior so I haven't added it to this patch yet.

Thoughts?

PS: For the sake of discussion, let's put aside how silly it would be to have schemas with spaces or single quotes...