TeslaCN commented on issue #21627: URL: https://github.com/apache/shardingsphere/issues/21627#issuecomment-1316536204
There are 2 reasons that the binary value is damaged: 1. Proxy reads COM_QUERY SQL and convert it with `charset` (which usually `UTF-8` in Proxy). The byte[] to UTF-8 String conversion may damage values of literal prefixed with `_binary`. https://github.com/apache/shardingsphere/blob/e5c8dc7778f79d544935803a7093aee53ea91bf2/db-protocol/mysql/src/main/java/org/apache/shardingsphere/db/protocol/mysql/packet/command/query/text/query/MySQLComQueryPacket.java#L44 https://github.com/apache/shardingsphere/blob/e5c8dc7778f79d544935803a7093aee53ea91bf2/db-protocol/mysql/src/main/java/org/apache/shardingsphere/db/protocol/mysql/payload/MySQLPacketPayload.java#L436-L440 2. SQL String to bytes conversion in MySQL Connector/J damaged the _binary literal in SQL. This happened at ShardingSphere-Proxy sending SQL to MySQL server. `serverEncoding` is usually `utf8mb4` or `utf8`. https://github.com/mysql/mysql-connector-j/blob/ad86f36e100e104cd926c6b81c8cab9565750116/src/com/mysql/jdbc/Buffer.java#L670  To fix this issue, my idea is: 1. Implement `visitBlobValue` in `org.apache.shardingsphere.sql.parser.mysql.visitor.statement.impl.MySQLDMLStatementSQLVisitor` https://github.com/apache/shardingsphere/blob/e5c8dc7778f79d544935803a7093aee53ea91bf2/sql-parser/dialect/mysql/src/main/antlr4/imports/mysql/DMLStatement.g4#L103-L105 2. Parse SQL and check whether the SQL statement is allowed to prepare. `org.apache.shardingsphere.proxy.frontend.mysql.command.query.binary.prepare.MySQLComStmtPrepareChecker` 3. If the SQL is allowed to be prepared, extract _binary literal from SQL and rewrite _binary literal to placeholder `?`. Executing SQL as prepared statement. -- 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]
