makssent opened a new issue, #40243: URL: https://github.com/apache/shardingsphere/issues/40243
### 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 store the text that a client sends on a connection whose character set ([`isc_dpb_lc_ctype`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/jrd.cpp#L7093-L7095)) is [`NONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/IntlManager.cpp#L106) or [`SJIS_0208`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/IntlManager.cpp#L115) as the Firebird server does. ### Actual behavior Both statements succeed, but the proxy stores other bytes of the text in the database. The same statements were executed directly on the Firebird server and through the proxy, and the bytes of NAME were read directly from the server with [`CAST`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L8567-L8570) to the character set [`OCTETS`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/IntlManager.cpp#L107): | Statement | Firebird server | Proxy | | --- | --- | --- | | `INSERT INTO T_PLAIN (ID, NAME) VALUES (8901, CAST('Ж' AS VARCHAR(5) CHARACTER SET WIN1251))` on a connection with [`encoding`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jaybird/props/PropertyNames.java#L55)`=NONE` | Success, NAME is stored as `d0a0e28093`, the bytes `D0 96` that Jaybird sent for `Ж`, read as two [`WIN1251`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/IntlManager.cpp#L144) characters | Success, NAME is stored as `d096`, the letter `Ж` in UTF-8 | | `INSERT INTO T_PLAIN (ID, NAME) VALUES (8903, '日本語')` on a connection with [`encoding`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jaybird/props/PropertyNames.java#L55)`=SJIS_0208` | Success, NAME is stored as `e697a5e69cace8aa9e`, the text `日本語` in UTF-8 | Success, NAME is stored as `efbfbdefbfbdefbfbd7befbfbdefbfbd`, replacement characters U+FFFD and `{` instead of the text | So through the proxy an application on a [`NONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/IntlManager.cpp#L106) or [`SJIS_0208`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/IntlManager.cpp#L115) connection gets other text in the database than through the Firebird server, and no error tells it that something went wrong. ### Reason analysis - [`FirebirdAuthenticationEngine#parseAttachCharset`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/authentication/FirebirdAuthenticationEngine.java#L103-L112) maps the connection character set ([`isc_dpb_lc_ctype`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/jrd.cpp#L7093-L7095)) to a Java charset with [`FirebirdCharacterSets#findCharacterSet`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/backend/firebird/handler/admin/executor/variable/charset/FirebirdCharacterSets.java#L119-L123). [`FirebirdPrepareStatementPacket`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/com mand/query/statement/prepare/FirebirdPrepareStatementPacket.java#L59) reads the SQL text with [`FirebirdPacketPayload#readString`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/payload/FirebirdPacketPayload.java#L231-L233), which decodes it with this charset, and the proxy passes it to the backend as a Java string. - [`FirebirdCharacterSets.NONE`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/backend/firebird/handler/admin/executor/variable/charset/FirebirdCharacterSets.java#L70) is `Charset.defaultCharset()`, so the proxy decodes and transliterates the bytes of a [`NONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/IntlManager.cpp#L106) connection, while the Firebird server [copies them as they are](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/intl.cpp#L704-L725). - [`FirebirdCharacterSets.SJIS_0208`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/backend/firebird/handler/admin/executor/variable/charset/FirebirdCharacterSets.java#L73) is `ISO-2022-JP`, while [`SJIS_0208`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/IntlManager.cpp#L115) in the Firebird server is [Shift-JIS](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/intl/charsets.h#L33). ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 6.0.7. #### Steps 1. Create the table on the Firebird server in a database with the default character set UTF8: ```sql CREATE TABLE T_PLAIN (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(20)); ``` 2. 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> username: <user> password: <password> rules: - !SINGLE tables: - "*.*" ``` 3. 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 All cases on Jaybird 6.0.7, one method per case. The comment above each method 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.SQLException; import java.sql.Statement; import java.util.HexFormat; import java.util.Properties; public class ConnectionCharsetMapping { // The proxy; put the URL of the Firebird server here to compare static final String URL = "jdbc:firebirdsql://<proxy host>:<proxy port>/firebird_db"; static final String USER = "root"; static final String PASSWORD = "root"; // The Firebird server, to read the rows directly static final String SERVER_URL = "jdbc:firebirdsql://<host>/<database file>"; static final String SERVER_USER = "<user>"; static final String SERVER_PASSWORD = "<password>"; public static void main(final String[] args) throws Exception { executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 8901 AND 8903"); noneConnection(); sjisConnection(); executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 8901 AND 8903"); } // Firebird server: "ID = 8901: success, NAME is stored as d0a0e28093"; // proxy: "ID = 8901: success, NAME is stored as d096" static void noneConnection() throws SQLException { // On a NONE connection Jaybird encodes the text with the JVM default charset, UTF-8, // so it sends the letter as the bytes D0 96 insert("NONE", 8901, "INSERT INTO T_PLAIN (ID, NAME) VALUES (8901, CAST('Ж' AS VARCHAR(5) CHARACTER SET WIN1251))"); } // Firebird server: "ID = 8903: success, NAME is stored as e697a5e69cace8aa9e"; // proxy: "ID = 8903: success, NAME is stored as efbfbdefbfbdefbfbd7befbfbdefbfbd" static void sjisConnection() throws SQLException { // Jaybird sends the text in Shift-JIS insert("SJIS_0208", 8903, "INSERT INTO T_PLAIN (ID, NAME) VALUES (8903, '日本語')"); } // Executes the statement on a connection with the given connection character set // and reads the stored bytes directly on the Firebird server static void insert(final String encoding, final int id, final String sql) throws SQLException { Properties properties = new Properties(); properties.setProperty("user", USER); properties.setProperty("password", PASSWORD); properties.setProperty("wireCrypt", "DISABLED"); properties.setProperty("encoding", encoding); String result; try (Connection connection = DriverManager.getConnection(URL, properties); Statement statement = connection.createStatement()) { statement.executeUpdate(sql); result = "success"; } catch (final SQLException ex) { result = ex.getMessage(); } System.out.println("ID = " + id + ": " + result + ", NAME is stored as " + storedBytes(id)); } // Reads the bytes of NAME directly on the Firebird server static String storedBytes(final int id) throws SQLException { try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD); ResultSet resultSet = connection.createStatement().executeQuery( "SELECT CAST(NAME AS VARCHAR(80) CHARACTER SET OCTETS) FROM T_PLAIN WHERE ID = " + id)) { return resultSet.next() ? HexFormat.of().formatHex(resultSet.getBytes(1)) : "nothing, the row is lost"; } } static void executeOnServer(final String sql) throws SQLException { try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD)) { connection.createStatement().execute(sql); } } } ``` -- 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]
