DatabaseMetaData.getSchemas(catalog,schemaPattern) Does Not Handle Escapes in the Schema Pattern

[mbach979]

Driver version

12.4.0

SQL Server version

Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64)
Aug 7 2021 01:20:37
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Client Operating System

Windows 10 Enterprise 22H2 19045.3208

JAVA/JVM version

Amazon Corretto 11.0.17.8.1

Table schema

create schema matching_schema
create schema matchingAschema

Note that the test program will create and drop these test schemas.

Problem description

The DatabaseMetaData.getSchemas(catalog, schemaPattern) overload is not correctly handling the escape sequence for wildcards e.g. foo\_bar will not match the schema name foo_bar. Also validated that the DatabaseMetaData.getSearchEscapeString() is returning \ and therefore by the JDBC spec escapes should be supported for this parameter.

Attached sample java program illustrating issue.

Expected behavior

The DatabaseMetaData.getSchemas(catalog, schemaPattern) should support escaping wildcards in the schemaPattern parameter.

Actual behavior

Output of attached test program:

Driver Version: 12.4.0.0
Search String Escape: \
Unescaped Schema Pattern: matching_schema
	Unesacped Match: matching_schema
	Unesacped Match: matchingAschema
Escaped Schema Pattern: matching\_schema
FAILED: testMssqlDriver.SQLServerGetSchemasPatternTest.testEscapeSchemaPattern
java.lang.AssertionError: expected [1] but found [0]

Any other details that can be helpful

Problem seems to be that the SQLServerDatabaseMetaData.getSchemasInternal() is not adding ESCAPE clauses to the LIKE statements it generates for schema matching.

JDBC trace logs

Provide the JDBC driver trace logs. Instructions can be found here: https://docs.microsoft.com/sql/connect/jdbc/tracing-driver-operation
testMssqlDriver.zip
sql-server-driver-0.log.0.txt

[Jeffery-Wasty]

Hi @mbach979,

Thank you for the test code, we'll take a look into this issue and get back to you with our next steps.