RaigorJiang opened a new issue, #39203:
URL: https://github.com/apache/shardingsphere/issues/39203
## Bug Report
### Which version of ShardingSphere did you use?
5.5.4-SNAPSHOT 6621687546488d654546b2c8a9f8bfd66cd12dfc
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
For a MySQL server-side prepared statement, the signedness used to decode a
`COM_STMT_EXECUTE` parameter must come from the parameter type metadata sent by
the client.
For example, when a client calls:
```java
preparedStatement.setInt(1, -1);
// UPDATE t_unsigned_passthrough SET int_payload = ? WHERE id = ?
```
the parameter is encoded as:
- type: `MYSQL_TYPE_LONG`
- client unsigned flag: `0x00`
- value bytes: `FF FF FF FF`
ShardingSphere-Proxy should decode the value as `-1` and forward it to the
backend. MySQL should then reject the assignment to an `INT UNSIGNED` column
with an out-of-range error, consistent with a direct MySQL connection.
### Actual behavior
ShardingSphere-Proxy uses the target column's `UNSIGNED` metadata, collected
during `COM_STMT_PREPARE`, to decode the execute parameter.
As a result, the same payload is decoded as `4294967295` instead of `-1`:
```text
UPDATE t_unsigned_passthrough SET int_payload = ? WHERE id = ? :::
[4294967295, 106]
```
Because `4294967295` is a valid value for `INT UNSIGNED`, MySQL accepts the
statement. The original client value `-1` is silently changed by Proxy.
This differs from both direct MySQL behavior and the behavior for `BIGINT
UNSIGNED` with `setLong(-1L)`, where the value remains `-1` and MySQL correctly
returns an out-of-range error.
### Minimal reproduction
```sql
CREATE TABLE t_unsigned_passthrough (
id INT PRIMARY KEY,
int_payload INT UNSIGNED,
bigint_payload BIGINT UNSIGNED
);
INSERT INTO t_unsigned_passthrough (id, int_payload, bigint_payload)
VALUES (106, 0, 0);
```
```java
try (PreparedStatement preparedStatement = connection.prepareStatement(
"UPDATE t_unsigned_passthrough SET int_payload = ? WHERE id = ?")) {
preparedStatement.setInt(1, -1);
preparedStatement.setInt(2, 106);
preparedStatement.executeUpdate();
}
```
Expected result:
```text
Data truncation: Out of range value for column 'int_payload' at row 1
```
Actual result through ShardingSphere-Proxy:
```text
The update succeeds and int_payload becomes 4294967295.
```
### Root cause analysis
The MySQL `COM_STMT_EXECUTE` protocol defines each parameter type with a
type code and a flag byte. The highest bit of the flag byte (`0x80`) indicates
whether the client parameter value is unsigned:
- MySQL protocol reference:
https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_com_stmt_execute.html
The current implementation already reads this client flag into
`MySQLPreparedStatementParameterType`, but
`MySQLComStmtExecutePacket#readParameters` does not use it when decoding
numeric values.
Instead, it uses `MySQLColumnDefinitionFlag.UNSIGNED` from the target column
metadata generated during `COM_STMT_PREPARE`.
These two metadata sources have different responsibilities:
- The client parameter unsigned flag describes how the bytes in
`COM_STMT_EXECUTE` are encoded.
- The target column `UNSIGNED` attribute describes the valid range enforced
by MySQL after the value is decoded.
Using target-column metadata to decode the client payload can change the
value before it reaches MySQL.
### Proposed fix
1. Decode numeric parameter values using
`MySQLPreparedStatementParameterType#getUnsignedFlag()`.
2. Treat the parameter as unsigned only when `(unsignedFlag & 0x80) == 0x80`.
3. Remove `parameterColumnDefinitionFlags` from
`MySQLServerPreparedStatement`, because it should not be used by
execute-parameter numeric decoding.
4. Keep `parameterColumnTypes`, because they are still required to preserve
raw bytes for string-family parameters targeting BLOB columns.
5. Add protocol unit tests covering the same `FF FF FF FF` payload with:
- client unsigned flag `0x00` -> `-1`
- client unsigned flag `0x80` -> `4294967295L`
### Relationship to the previous BLOB fix
This issue was identified while adding non-BLOB regression coverage after
the MySQL BLOB prepared-statement fix in
https://github.com/apache/shardingsphere/pull/39072.
Both issues occur while Proxy reads `COM_STMT_EXECUTE` parameters, but they
require different metadata:
- BLOB protection needs target column type metadata to distinguish binary
and text handling.
- Numeric signedness decoding must use the client parameter flag from the
execute packet.
### Impact
This is a data correctness issue in MySQL Proxy prepared statements:
- A negative `int` value can be silently converted into a large positive
unsigned value.
- The backend database cannot reject the invalid assignment because Proxy
has already changed the value.
- The issue affects MySQL binary-protocol prepared statement execution when
a signed client numeric parameter targets an unsigned column.
--
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]
