makssent opened a new issue, #40290: URL: https://github.com/apache/shardingsphere/issues/40290
### Before reporting a bug - [x] I have searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - [x] I have read the [ShardingSphere documentation](https://shardingsphere.apache.org/document/current/en/overview). ### ShardingSphere version master, commit [`01446d4fad4`](https://github.com/apache/shardingsphere/commit/01446d4fad48450e7fc3eb3b3631b25d6bf411df) ### ShardingSphere project ShardingSphere-Proxy ### Expected behavior The proxy should accept strings in alternative quotes ([`Q'{...}'`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/Parser.cpp#L716-L762)) as the Firebird server does. ### Actual behavior The proxy rejects the string in alternative quotes with a syntax error. The same statement was executed directly on the Firebird server and through the proxy: | Steps | Firebird server | Proxy | | --- | --- | --- | | `SELECT Q'{it's}' FROM RDB$DATABASE` | One row: it's | Error [`isc_dsql_error`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L250) "Dynamic SQL Error; You have an error in your SQL syntax: SELECT Q'{it's}' FROM RDB$DATABASE, null" | So through the proxy an application cannot use strings in alternative quotes, for example to write a string with apostrophes without doubling them. ### Reason analysis - The rule [`STRING_`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/Literals.g4#L27-L29) of the Firebird lexer of ShardingSphere has no alternative quotes ([`Q'{...}'`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/Parser.cpp#L716-L762)), so the proxy rejects the statement with a syntax error. - [`FirebirdStatementVisitor#visitStringLiterals`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/FirebirdStatementVisitor.java#L166-L168) would also have to remove the quotes of such a string, because [`StringLiteralValue`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/value/literal/impl/StringLiteralValue.java#L33-L35) removes only one character on each side. ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 6.0.7. #### Steps 1. Start ShardingSphere-Proxy with this configuration: ```yaml # global.yaml authority: users: - user: root@% password: root privilege: type: ALL_PERMITTED props: proxy-frontend-database-protocol-type: Firebird ``` ```yaml # database-firebird.yaml databaseName: firebird_db dataSources: ds_0: url: jdbc:firebirdsql://<host>/<database file>?encoding=UTF8 username: <user> password: <password> rules: - !SINGLE tables: - "*.*" ``` 2. Execute the statements from the table under "Actual behavior" through the proxy and directly on the Firebird server, and compare the results. "Example code" runs all of them. ### Example code The statements from the table on Jaybird 6.0.7. The comment above each call shows what it prints against the Firebird server and through the proxy. ```java import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; import java.util.List; public class AlternativeQuotes { // The proxy; put the URL, user and password of the Firebird server here to compare static final String URL = "jdbc:firebirdsql://<proxy host>:<proxy port>/firebird_db?encoding=UTF8&wireCrypt=DISABLED"; static final String USER = "root"; static final String PASSWORD = "root"; public static void main(final String[] args) throws SQLException { try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { // Firebird server: "SELECT Q'{it's}' FROM RDB$DATABASE -> CONSTANT CHAR(4): it's"; // proxy: "SELECT Q'{it's}' FROM RDB$DATABASE -> error: Dynamic SQL Error; You have an error in your SQL syntax: SELECT Q'{it's}' FROM RDB$DATABASE, null [SQLState:42000, ISC error code:335544569]" execute(connection, "SELECT Q'{it's}' FROM RDB$DATABASE"); } } // Executes the statement and prints the described columns with the rows, the update count or the error static void execute(final Connection connection, final String sql) { try (Statement statement = connection.createStatement()) { if (!statement.execute(sql)) { System.out.println(sql + " -> update count " + statement.getUpdateCount()); return; } try (ResultSet resultSet = statement.getResultSet()) { ResultSetMetaData metaData = resultSet.getMetaData(); List<String> columns = new ArrayList<>(); for (int i = 1; i <= metaData.getColumnCount(); i++) { int type = metaData.getColumnType(i); boolean text = Types.CHAR == type || Types.VARCHAR == type; columns.add(metaData.getColumnLabel(i) + " " + metaData.getColumnTypeName(i) + (text ? "(" + metaData.getPrecision(i) + ")" : "")); } List<String> rows = new ArrayList<>(); while (resultSet.next()) { List<String> values = new ArrayList<>(); for (int i = 1; i <= metaData.getColumnCount(); i++) { values.add(String.valueOf(resultSet.getObject(i))); } rows.add(String.join(", ", values)); } System.out.println(sql + " -> " + String.join(", ", columns) + ": " + (rows.isEmpty() ? "no rows" : String.join("; ", rows))); } } catch (final SQLException ex) { System.out.println(sql + " -> error: " + ex.getMessage()); } } } ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
