makssent opened a new issue, #40284: URL: https://github.com/apache/shardingsphere/issues/40284
### 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 with the character set ([`isc_dpb_lc_ctype`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/jrd.cpp#L7093-L7095)) [`NONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/IntlManager.cpp#L106) as the Firebird server does, that is [copy its bytes as they are](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/intl.cpp#L704-L725). ### Actual behavior The statement succeeds, but the proxy stores other bytes of the text in the database. The data source of the proxy connects with `?encoding=UTF8`. The same statement was 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): | Steps | 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 | So through the proxy an application on a [`NONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/IntlManager.cpp#L106) 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 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). - The backend connection has the character set of the data source `url`, here UTF8, so the Firebird server gets the decoded text in UTF8 instead of the bytes of the client. All client connections share the connections of the data source, and a Firebird connection cannot change its character set after it is attached, so the Firebird frontend alone cannot send the bytes of a NONE client to the backend as they are. ### 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>?encoding=UTF8 username: <user> password: <password> rules: - !SINGLE tables: - "*.*" ``` 3. Execute the statement 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 Jaybird 6.0.7. The comment above the 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 NoneConnectionCharset { // 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"; 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 = 8901"); noneConnection(); executeOnServer("DELETE FROM T_PLAIN WHERE ID = 8901"); } // 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))"); } // 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]
