makssent opened a new issue, #40247: URL: https://github.com/apache/shardingsphere/issues/40247
### 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 return the stored bytes of a [text BLOB with character set `NONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4882-L4894) as the Firebird server does. The Firebird server keeps character set `NONE` for such a column in the output message ([`GEN_port`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/gen.cpp#L168-L170)) and does not transliterate its BLOB ([`blb::open2`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/blb.cpp#L1528-L1542)), also when the connection encoding is UTF8. ### Actual behavior In the answer to [`op_get_segment`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5183-L5185) the proxy returns three U+FFFD characters in UTF-8 instead of the stored bytes of a [text BLOB with character set `NONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4882-L4894), when these bytes are not valid UTF-8. The row with ID = 8941 was inserted directly on the Firebird server with the bytes `cf f0 e8` in `TN` ([`setBytes`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBPreparedStatement.java#L257-L259)). The same statement was executed directly on the Firebird server and through the proxy, on a connection with the encoding UTF8: | Statement | Firebird server | Proxy | | --- | --- | --- | | `SELECT TN FROM T_RV WHERE ID = 8941`, then [`getBytes`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBResultSet.java#L499-L501) for `TN` | The bytes `cf f0 e8` | The bytes `ef bf bd ef bf bd ef bf bd`, three U+FFFD characters in UTF-8 | So through the proxy an application reads other data than stored in the database, and no error tells it that something went wrong. ### Reason analysis - [`StandardDatabaseProxyConnector#getRowData`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/connector/StandardDatabaseProxyConnector.java#L357-L364) takes each value of the backend row as `Object`, so [`JDBCStreamQueryResult#getValue`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/executor/src/main/java/org/apache/shardingsphere/infra/executor/sql/execute/result/query/impl/driver/jdbc/type/stream/JDBCStreamQueryResult.java#L127) reads it with [`getObject`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/FBResultSet.java#L529-L531) of the backend driver. For a text BLOB Jaybird returns a string ([`FBStringField#getObject`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/field/FBStringField.java#L78-L80)) and decodes the stored bytes with the encoding of t he backend connection ([`FBLongVarCharField#getString`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jdbc/field/FBLongVarCharField.java#L178-L183)), also for character set `NONE` ([`ConnectionEncodingFactory`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/encodings/ConnectionEncodingFactory.java#L54-L62)). With the encoding UTF8 the bytes that are not valid UTF-8 become U+FFFD. - [`FirebirdBlobBinaryProtocolValue#write`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/execute/protocol/FirebirdBlobBinaryProtocolValue.java#L118-L120) encodes this string with the charset of the client connection and keeps the result as the content of the BLOB. [`FirebirdOpenBlobCommandExecutor#execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/blob/FirebirdOpenBlobCommandExecutor.java#L59-L65) opens this content, and [`FirebirdGetBlobSegmentCommandExecutor#execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/comman d/query/blob/FirebirdGetBlobSegmentCommandExecutor.java#L63-L74) returns it in [`op_get_segment`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5183-L5185), so the stored bytes never reach the client. ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 5.0.13. #### Steps 1. Create the table on the Firebird server: ```sql CREATE TABLE T_RV (ID INTEGER NOT NULL PRIMARY KEY, TN BLOB SUB_TYPE TEXT CHARACTER SET NONE); ``` 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 steps from the table under "Actual behavior" through the proxy and directly on the Firebird server, and compare the results. The client and the data source of the proxy connect with the encoding UTF8 (`?encoding=UTF8` at the end of the data source `url`). "Example code" runs all of them. ### Example code All cases on Jaybird 5.0.13, 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.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.HexFormat; public class NoneTextBlobRead { // The proxy; put the URL 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"; // The Firebird server, to write the row 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_RV WHERE ID = 8941"); insertOnServer(); readBytes(); executeOnServer("DELETE FROM T_RV WHERE ID = 8941"); } // Firebird server: "TN: cf f0 e8"; proxy: "TN: ef bf bd ef bf bd ef bf bd" static void readBytes() throws SQLException { try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT TN FROM T_RV WHERE ID = 8941")) { System.out.println("TN: " + (resultSet.next() ? HexFormat.ofDelimiter(" ").formatHex(resultSet.getBytes(1)) : "no row")); } } // Writes the row with ID = 8941 directly on the Firebird server; the bytes cf f0 e8 are not valid UTF-8 static void insertOnServer() throws SQLException { try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD); PreparedStatement statement = connection.prepareStatement("INSERT INTO T_RV (ID, TN) VALUES (8941, ?)")) { statement.setBytes(1, new byte[]{(byte) 0xcf, (byte) 0xf0, (byte) 0xe8}); statement.executeUpdate(); } } 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]
