Escape '\' in sql causes DialectSQLParsingException

[krumbs8749]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

5.5.1

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere

Expected behavior

I am querying for "NAME_\\%_% "
it should output
Row: head_id=1, head_name=NAME_%_1
Row: head_id=2, head_name=NAME_%_2
Row: head_id=3, head_name=NAME_%_3
Row: head_id=4, head_name=NAME_%_4
Row: head_id=5, head_name=NAME_%_5

Actual behavior

but got this error. most likely due to ESCAPE '\'

org.apache.shardingsphere.infra.exception.dialect.exception.syntax.sql.DialectSQLParsingException: You have an error in your SQL syntax: SELECT th1_0.head_id, th1_0.head_name FROM mtsm_timeseries.ts_head th1_0 WHERE th1_0.head_name like ? ESCAPE '\' ORDER BY th1_0.head_id OFFSET ? ROWS FETCH FIRST ? ROWS ONLY, null
at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.getException(ShardingSphereSQLParserEngine.java:66)
at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse(ShardingSphereSQLParserEngine.java:59)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.parseSQL(ShardingSpherePreparedStatement.java:163)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.(ShardingSpherePreparedStatement.java:145)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.(ShardingSpherePreparedStatement.java:117)
at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:111)
at ShardingIntegrationTest.testQueryWithLikeAndPagination(ShardingIntegrationTest.java:115)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)

Reason analyze (If you can)

If i put single '\' then i will get this error:

java.sql.SQLDataException: ORA-01425: Escapezeichen muss eine Zeichenfolge der Länge 1 sein
Caused by: Error : 1425, Position : 95, Sql = SELECT th1_0.head_id, th1_0.head_name FROM TS_HEAD th1_0 WHERE th1_0.head_name like :1 ESCAPE '' ORDER BY th1_0.head_id OFFSET :2 ROWS FETCH FIRST :3 ROWS ONLY, OriginalSql = SELECT th1_0.head_id, th1_0.head_name FROM TS_HEAD th1_0 WHERE th1_0.head_name like ? ESCAPE '' ORDER BY th1_0.head_id OFFSET ? ROWS FETCH FIRST ? ROWS ONLY, Error Msg = ORA-01425: Escapezeichen muss eine Zeichenfolge der Länge 1 sein

if i put four '\\\\' then i will get this error:

java.sql.SQLDataException: ORA-01425: Escapezeichen muss eine Zeichenfolge der Länge 1 sein
Caused by: Error : 1425, Position : 95, Sql = SELECT th1_0.head_id, th1_0.head_name FROM TS_HEAD th1_0 WHERE th1_0.head_name like :1 ESCAPE '\' ORDER BY th1_0.head_id OFFSET :2 ROWS FETCH FIRST :3 ROWS ONLY, OriginalSql = SELECT th1_0.head_id, th1_0.head_name FROM TS_HEAD th1_0 WHERE th1_0.head_name like ? ESCAPE '\' ORDER BY th1_0.head_id OFFSET ? ROWS FETCH FIRST ? ROWS ONLY, Error Msg = ORA-01425: Escapezeichen muss eine Zeichenfolge der Länge 1 sein

Possible reason: When the ESCAPE '\\' is passed to the ShardingSphere parser, it receives it as '\' fails to parse correctly due to an illegal escape character—here it should instead be '\\'. However, testing with '\\\\' reveals that the ShardingSphere parser interprets it as '\\' and parses it correctly. After parsing, it forwards '\\' to Oracle, which results in the error ORA-0142

Testing with $ as escape character output the correct behaviour. Problenm here seems the backslashes nad the same error occurs with Postgres

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

sharding.yaml

mode:
  type: Standalone
  repository:
    type: JDBC

dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: oracle.jdbc.OracleDriver
    jdbcUrl: jdbc:oracle:thin:@localhost:1521/FREEPDB1
    username: system
    password: oracle
    maximumPoolSize: 10

  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: oracle.jdbc.OracleDriver
    jdbcUrl: jdbc:oracle:thin:@localhost:1522/FREEPDB1
    username: system
    password: oracle
    maximumPoolSize: 10

rules:
  - !SHARDING
    tables:
      TS_HEAD:
        actualDataNodes: ds_${0..1}.TS_HEAD
        databaseStrategy:
          standard:
            shardingColumn: head_id
            shardingAlgorithmName: head_mod
    shardingAlgorithms:
      head_mod:
        type: INLINE
        props:
          algorithm-expression: ds_${Math.abs(head_id.hashCode()) % 2}
props:
  sql-show: true
  check-table-metadata-enabled : true

sqlFederation:
  sqlFederationEnabled: true
  executionPlanCache:
    initialCapacity: 2000
    maximumSize: 65535

Example codes for reproduce this issue (such as a github link).

Test Class

class ShardingIntegrationTest {

    private static final String SHARDINGSPHERE_URL = "jdbc:shardingsphere:classpath:sharding.yaml";
    private static final String SHARDINGSPHERE_USERNAME = "system";
    private static final String SHARDINGSPHERE_PASSWORD = "oracle"
 @Test
    void testQueryWithLikeAndPagination() throws Exception {
        try (Connection connection = DriverManager.getConnection(SHARDINGSPHERE_URL, SHARDINGSPHERE_USERNAME, SHARDINGSPHERE_PASSWORD);
             PreparedStatement stmt = connection.prepareStatement(
                    "SELECT th1_0.head_id, th1_0.head_name " +
                             "FROM mtsm_timeseries.ts_head th1_0 " +
                             "WHERE th1_0.head_name like ? ESCAPE '\\' " +
                             "ORDER BY th1_0.head_id " +
                             "OFFSET ? ROWS FETCH FIRST ? ROWS ONLY")) {

            // Set query parameters
            stmt.setString(1, "NAME_\\%_%"); // Match all names starting with "NAME_"
            stmt.setInt(2, 0);          // Offset
            stmt.setInt(3, 10);         // Limit (fetch first 10 rows)

            try (ResultSet rs = stmt.executeQuery()) {
                int rowCount = 0;
                while (rs.next()) {
                    System.out.println("Row: head_id=" + rs.getInt("head_id") + ", head_name=" + rs.getString("head_name"));
                    rowCount++;
                }
                assertTrue(rowCount > 0, "No rows returned");
            }
        }
    }
}

[MeteorSkyOne]

In this case Java escapes '\' into '' instead of ShardingSphere parser. When entering prepareStatement method, the parameter value is "SELECT th1_0.head_id, th1_0.head_name FROM myuser.ts_head th1_0 WHERE th1_0.head_name like ? ESCAPE '' ", so it's not a ShardingSphere parser bug