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.