SNOW-1619625: Insufficient character unescaping

[bauer-at-work]

1. What version of JDBC driver are you using?
   latest from git

2. What operating system and processor architecture are you using?
   Fedora 40, amd64

3. What version of Java are you using?
   OpenJDK 64-Bit Server VM (Red_Hat-21.0.4.0.7-2) (build 21.0.4+7, mixed mode, sharing)

4. What did you do?

   Properties properties = new Properties();
   properties.put("db", "DEV");
   properties.put("schema", "\\\\_ESCAPED\\\\_UNDERSCORE");
   properties.put("CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX", "TRUE");
   
   ...
   
   connection.getMetaData().getSchemas(); // Returns a line for the precise schema
   connection.getMetaData().getTables(null, null, null, null); // Query fails

   Snowflake Query Monitor reveals the following queries being executed:

   show /* JDBC:DatabaseMetaData.getSchemas() */ schemas like 'ESCAPED\\_UNDERSCORE' in database "DEV";
   show /* JDBC:DatabaseMetaData.getTables() */ objects in schema "DEV"."ESCAPED\_UNDERSCORE";

5. What did you expect to see?

   The first SQL statement is expected and correct. Two backslashes are required for the sequence to be interpreted as an escaping of the underscore character.

   The second SQL statement is unexpected. Now that that metadata call results not in a LIKE operation but in a statement expecting a valid schema name, both backslash characters should have been 'unescaped'.

   The problem can be traced back to SnowflakeDatabaseMetaData::unescapeChars(...).
   The function is only removing one of the two backslahes.

6. Fix

   I had success with switching the order of statements as follows:

   private String unescapeChars(String escapedString) {
     String unescapedString = escapedString.replace("\\\\", "\\");
     unescapedString = unescapedString.replace("\\_", "_");
     unescapedString = unescapedString.replace("\\%", "%");
     unescapedString = escapeSqlQuotes(unescapedString);
     return unescapedString;
   }

   However, I might be missing other edge cases now.

[bauer-at-work]

also, THERE SHOULD BE A TEST CASE FOR unescapeChars!

[bauer-at-work]

Hi @sfc-gh-snow-drivers-warsaw-dl Hi, can you please give an estimate on when this can be fixed?
We @ Siemens are experiencing big slowdowns in integrating with our Snowflake instance.

[sfc-gh-dszmolka]

hi - the team is looking into this issue, which seems to be more complex than it might seem at the first glance. We would like to also take a holistic approach to the issue and rework today's (little bit inconsistent) behaviour . The full rewrite can be only expected in the next major version of the JDBC driver.

In the meantime, we're looking into making a workaround available which could be even implemented before the next major release. Please stay tuned and thank you for bearing with us - i'll keep this thread posted.

[sfc-gh-dprzybysz]

Hi @bauer-at-work
I prepared a PR with fix for schema lookup guarded by the session parameter ENABLE_EXACT_SCHEMA_SEARCH_ENABLED.
I think the correct approach should be to not escape schema in connection parameters and the metadata queries with CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=true should use the schema not as a wildcard.
You can take a look if you want.

[bauer-at-work]

Hi @sfc-gh-dprzybysz,
I thought exactly the same when coming back to this topic today -
Interpreting the underscores as wildcards constitutes undefined behavior.

When do you think the correct approach can be implemented?
With your current fix (workaround?), how do I need to specify my schema? Do I have to escape the underscores in the schema name still?

[sfc-gh-dprzybysz]

With the fix in PR the schema should be without any escaping applied when passed as connection parameter. When schema is test_bla%123 it should be passed as test_bla%123.

There are some flags and workarounds in fetching metadata functions - there are some plans to simplify the configuration and functions but probably it will result in behaviour changes so it won't become a default behaviour for current 3.x line