RaigorJiang opened a new issue, #39071:
URL: https://github.com/apache/shardingsphere/issues/39071
## Bug Report
### Which version of ShardingSphere did you use?
5.5.4-SNAPSHOT 94f02c4b8ab3815ad58cb8d046f0c096b835b18c
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy.
### Expected behavior
When a MySQL client writes binary data to a `BLOB` / `TINYBLOB` /
`MEDIUMBLOB` / `LONGBLOB` column through ShardingSphere-Proxy with server-side
prepared statements, Proxy should preserve the original bytes exactly.
For example, if the client writes the following bytes to a `LONGBLOB` column:
```text
AC ED 00 05 73 72 00 13 6A 61 76 61 78 81 D2 1D 99 C7 61 9D 00 FF
```
the physical MySQL table should store the same bytes:
```text
ACED0005737200136A6176617881D21D99C7619D00FF
```
Proxy should not decode BLOB parameters as UTF-8 strings.
### Actual behavior
When the client writes binary data to a `LONGBLOB` column through MySQL
Proxy with server-side prepared statements, some non-UTF-8 bytes are converted
to the UTF-8 replacement character `U+FFFD.`
For example, bytes such as `AC, ED, 81, D2, 99, C7, 9D`, and `FF` may be
written as:
```text
EF BF BD
```
This corrupts binary data irreversibly. It may break Java serialized
objects, Flowable byte arrays, images, or any other binary payload stored in
BLOB columns.
### Reason analyze (If you can)
The issue happens in the MySQL `COM_STMT_EXECUTE` parameter reading path.
During investigation, diagnostic logs were temporarily added around
`MySQLComStmtExecutePacket#readParameters`. The reproduced execution showed the
following key facts for the BLOB parameter:
```text
COM_STMT_EXECUTE statementId=1, sql=INSERT INTO t_blob_passthrough(id,
payload) VALUES (?, ?), parameterTypes=[...], targetColumnTypes=[LONG, BLOB],
longDataIndexes=[]
COM_STMT_EXECUTE read parameter index=1, parameterType=VAR_STRING,
targetColumnType=BLOB, protocolValue=MySQLStringLenencBinaryProtocolValue
COM_STMT_EXECUTE parameter value index=1, valueType=java.lang.String
COM_STMT_EXECUTE statementId=1, parameterValueTypes=[java.lang.Integer,
java.lang.String]
```
This means:
- The target column was already recognized as `BLOB` during
`COM_STMT_PREPARE`.
- The client sent the parameter type as `VAR_STRING` in `COM_STMT_EXECUTE`.
- Proxy selected `MySQLStringLenencBinaryProtocolValue`.
- The parameter was read as `java.lang.String`, not `byte[]`.
`MySQLStringLenencBinaryProtocolValue` reads the value via
`payload.readStringLenenc()`, which decodes bytes using the connection charset.
For invalid UTF-8 binary bytes, Java replaces them with `U+FFFD`. When the
value is later sent to the backend database, `U+FFFD` becomes `EF BF BD,`
causing BLOB data corruption.
The problematic flow is:
```text
COM_STMT_EXECUTE binary parameter bytes
-> parameterType = VAR_STRING / VARCHAR
-> MySQLStringLenencBinaryProtocolValue
-> readStringLenenc()
-> Java String with U+FFFD replacement characters
-> backend write
-> corrupted BLOB bytes
```
The fix should use the prepared target column type before reading the value.
If the client parameter type is a string-like length-encoded type such as
`STRING`, `VAR_STRING`, or `VARCHAR`, but the prepared target column type is
one of `TINY_BLOB`, `BLOB`, `MEDIUM_BLOB`, or `LONG_BLOB`, Proxy should read
the value with `readStringLenencByBytes()` and preserve it as `byte[]`.
It should not globally change all `VAR_STRING / VARCHAR` parameters to
bytes, because normal character columns should still be read as `String`.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
1. Prepare a physical MySQL database and table:
```sql
CREATE DATABASE business_db;
USE business_db;
DROP TABLE IF EXISTS t_blob_passthrough;
CREATE TABLE t_blob_passthrough (
id INT NOT NULL PRIMARY KEY,
payload LONGBLOB NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
```
2. Configure ShardingSphere-Proxy with a single MySQL storage unit.
```yaml
databaseName: encrypt_db
dataSources:
ds_0:
url:
jdbc:mysql://127.0.0.1:3306/business_db?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&characterEncoding=utf-8
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SINGLE
tables:
- "*.*"
```
3. Start ShardingSphere-Proxy on port 3307.
4. Use a MySQL JDBC client with server-side prepared statements enabled:
```text
jdbc:mysql://127.0.0.1:3307/encrypt_db?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&characterEncoding=UTF-8&useServerPrepStmts=true&cachePrepStmts=false
```
5. Write binary bytes through PreparedStatement#setBytes.
6. Query the physical table directly:
```sql
SELECT HEX(payload), LENGTH(payload) FROM t_blob_passthrough WHERE id = 1;
```
7. Compare the stored bytes with the original bytes.
Expected:
```text
ACED0005737200136A6176617881D21D99C7619D00FF
```
Actual:
The stored value contains EFBFBD replacement bytes, for example:
```text
EFBFBDEFBFBD00057372...
```
###
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public final class LongblobReproduce {
private static final String BACKEND =
"jdbc:mysql://127.0.0.1:3306/business_db?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&characterEncoding=UTF-8";
private static final String PROXY =
"jdbc:mysql://127.0.0.1:3307/encrypt_db?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&characterEncoding=UTF-8&useServerPrepStmts=true&cachePrepStmts=false";
private static final byte[] RAW = new byte[] {
(byte) 0xAC, (byte) 0xED, 0x00, 0x05, 0x73, 0x72, 0x00, 0x13,
0x6A, 0x61, 0x76, 0x61, 0x78, (byte) 0x81, (byte) 0xD2, 0x1D,
(byte) 0x99, (byte) 0xC7, 0x61, (byte) 0x9D, 0x00, (byte) 0xFF
};
public static void main(final String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
try (Connection backend = DriverManager.getConnection(BACKEND,
"root", "xxx");
Connection proxy = DriverManager.getConnection(PROXY, "root",
"xxx")) {
backend.createStatement().executeUpdate("TRUNCATE TABLE
t_blob_passthrough");
try (PreparedStatement preparedStatement =
proxy.prepareStatement("INSERT INTO t_blob_passthrough(id, payload) VALUES (?,
?)")) {
preparedStatement.setInt(1, 1);
preparedStatement.setBytes(2, RAW);
preparedStatement.executeUpdate();
}
try (ResultSet resultSet =
backend.createStatement().executeQuery("SELECT HEX(payload), LENGTH(payload)
FROM t_blob_passthrough WHERE id = 1")) {
resultSet.next();
System.out.println("expected = " + hex(RAW));
System.out.println("actual = " + resultSet.getString(1));
System.out.println("length = " + resultSet.getInt(2));
}
}
}
private static String hex(final byte[] bytes) {
StringBuilder result = new StringBuilder();
for (byte each : bytes) {
result.append(String.format("%02X", each & 0xFF));
}
return result.toString();
}
}
```
--
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]
