makssent opened a new issue, #40245: URL: https://github.com/apache/shardingsphere/issues/40245
### 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 execute an INSERT with a text parameter as the Firebird server does. If the bytes of the parameter are not valid in its character set, the proxy should reject the statement with error [`isc_malformed_string`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/cvt.cpp#L498-L502) and not change the value. ### Actual behavior The proxy replaces the bytes of a text parameter that are not valid UTF8 with U+FFFD and saves the row, while the Firebird server rejects the statement. The same statements were executed in one transaction directly on the Firebird server and through the proxy, and the rows were read directly from the server: | Steps | Firebird server | Proxy | | --- | --- | --- | | Turn auto-commit off, `INSERT INTO T_PLAIN (ID, NAME) VALUES (?, ?)` with ID = 8921 and NAME `before` set with [`setString`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBPreparedStatement.java#L346-L348) | Success | Success | | The same INSERT with ID = 8922 and NAME set with [`setBytes`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBPreparedStatement.java#L271-L273) to the bytes `ff 41`, which are not valid UTF8 | Error [`isc_malformed_string`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/cvt.cpp#L498-L502) "Dynamic SQL Error; SQL error code = -303; Malformed string" | Success | | `INSERT INTO T_PLAIN (ID, NAME) VALUES (8923, 'after')` | Success | Success | | Commit | Success | Success | | Read the rows with ID = 8921, 8922 and 8923 directly from the Firebird server, NAME as bytes (`CAST(NAME AS VARCHAR(20) CHARACTER SET OCTETS)`) | The rows with ID = 8921 and ID = 8923 are saved, the row with ID = 8922 is not saved | The rows with ID = 8921 and ID = 8923 are saved, the row with ID = 8922 is saved with NAME `ef bf bd 41` (U+FFFD and `A`) | So through the proxy an application saves text that differs from the bytes it sent and that the Firebird server does not accept. No error tells it that the value was changed. ### Reason analysis - [`FirebirdBinaryProtocolValueFactory`](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/FirebirdBinaryProtocolValueFactory.java#L55-L61) reads `VARYING` and `TEXT` parameters with [`FirebirdStringBinaryProtocolValue`](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/FirebirdStringBinaryProtocolValue.java#L25). - [`FirebirdStringBinaryProtocolValue#read`](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/FirebirdStringBinaryProtocolValue.java#L27-L30) calls [`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 the bytes with `ByteBuf.toString(charset)`. The decoder replaces malformed bytes with U+FFFD without an error, so the backend gets a valid string, and the Firebird server saves it. - The Firebird server copies an input parameter into the statement in [`DsqlDmlRequest::mapInOut`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/DsqlRequests.cpp#L938-L943). There [`CVT_move_common`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/common/cvt.cpp#L1980-L1984) checks the bytes with [`validateData`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/cvt.cpp#L498-L502), which raises [`isc_malformed_string`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/jrd/cvt.cpp#L498-L502) for bytes that are not valid [UTF8](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/common/IntlUtil.cpp#L410-L414), and [`MOVD_move`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/movd.cpp#L36-L49) adds "Dynamic SQL Error; SQL error code = -303" to the error. The proxy never sends the original bytes to the server, so this check does not run. - The open pull request #40211 makes [`FirebirdExecuteStatementPacket`](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/FirebirdExecuteStatementPacket.java#L77-L81) read a `VARCHAR` parameter in the character set `OCTETS` as bytes, but a text parameter in another character set, as in this case, is still decoded 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). ### 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 steps from the table under "Actual behavior" through the proxy and directly on the Firebird server, with the connection character set UTF8 (`encoding=UTF8`), 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.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.HexFormat; public class MalformedStringParameter { // 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 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>"; static final String INSERT = "INSERT INTO T_PLAIN (ID, NAME) VALUES (?, ?)"; public static void main(final String[] args) throws Exception { executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 8920 AND 8929"); // All steps in one transaction try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { connection.setAutoCommit(false); insertText(connection); insertMalformedBytes(connection); insertAfterError(connection); commit(connection); } printRow(8921); printRow(8922); printRow(8923); executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 8920 AND 8929"); } // Firebird server: "ID = 8921: success"; proxy: "ID = 8921: success" static void insertText(final Connection connection) throws SQLException { try (PreparedStatement statement = connection.prepareStatement(INSERT)) { statement.setInt(1, 8921); statement.setString(2, "before"); execute(statement, 8921); } } // Firebird server: "ID = 8922: Dynamic SQL Error; SQL error code = -303; Malformed string [SQLState:22000, ISC error code:335544849]"; // proxy: "ID = 8922: success" static void insertMalformedBytes(final Connection connection) throws SQLException { try (PreparedStatement statement = connection.prepareStatement(INSERT)) { statement.setInt(1, 8922); // Jaybird sends these bytes as they are; ff is not valid UTF8 statement.setBytes(2, new byte[]{(byte) 0xff, 0x41}); execute(statement, 8922); } } // Firebird server: "ID = 8923: success"; proxy: "ID = 8923: success" static void insertAfterError(final Connection connection) throws SQLException { try (Statement statement = connection.createStatement()) { statement.executeUpdate("INSERT INTO T_PLAIN (ID, NAME) VALUES (8923, 'after')"); System.out.println("ID = 8923: success"); } catch (final SQLException ex) { System.out.println("ID = 8923: " + ex.getMessage()); } } // Firebird server: "commit: success"; proxy: "commit: success" static void commit(final Connection connection) { try { connection.commit(); System.out.println("commit: success"); } catch (final SQLException ex) { System.out.println("commit: " + ex.getMessage()); } } static void execute(final PreparedStatement statement, final int id) { try { statement.executeUpdate(); System.out.println("ID = " + id + ": success"); } catch (final SQLException ex) { System.out.println("ID = " + id + ": " + ex.getMessage()); } } // Reads NAME of the row as bytes directly on the Firebird server. // Firebird server: "ID = 8921: NAME is 6265666f7265", "ID = 8922: the row is not saved", "ID = 8923: NAME is 6166746572"; // proxy: "ID = 8921: NAME is 6265666f7265", "ID = 8922: NAME is efbfbd41", "ID = 8923: NAME is 6166746572" static void printRow(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(20) CHARACTER SET OCTETS) FROM T_PLAIN WHERE ID = " + id)) { System.out.println("ID = " + id + ": " + (resultSet.next() ? "NAME is " + HexFormat.of().formatHex(resultSet.getBytes(1)) : "the row is not saved")); } } 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]
