| Bug #102067 | Selecting JSON with binary data produces broken JSON string | ||
|---|---|---|---|
| Submitted: | 24 Dec 2020 11:49 | Modified: | 19 Oct 2021 12:29 |
| Reporter: | John WhyIsThisSoComplicated | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
| Version: | 5.7+, 5.7.32, 8 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[29 Dec 2020 10:38]
MySQL Verification Team
Hello! Thank you for the report. Observed the issue with 5.7.32. regards, Umesh
[29 Dec 2020 10:55]
John WhyIsThisSoComplicated
Yeah, it's also reproducible on the 8 branch.
[19 Oct 2021 12:29]
Knut Anders Hatlen
This looks like bug#90503, which was fixed in MySQL 8.0.13.
Description: When selecting JSON columns that contain binary values MySQL server produces JSON broken JSON string when it base64 encodes these values. How to repeat: Execute the following sample query: SELECT JSON_OBJECT('x', unhex(concat(sha2('test', 512), sha2('test', 512)))) It produces the following JSON string: {"x": "base64:type253:7iaw3Ur350mqGo7jwQrpkj9hiYB3Lkc/iBml1JQODbJ6wYX4oOHV+E+IvIh/1nsUNzLDBMxfqa2O b1f1ACio/+4msN1K9+dJqhqO48EK6ZI/YYmAdy5HP4gZpdSUDg2yesGF+KDh1fhPiLyIf9Z7FDcy wwTMX6mtjm9X9QAoqP8="} The base64 value of "x" includes raw new line control characters which is invalid JSON -- all control characters must be escaped. I understand that the base64 value for binary value in native JSON is produced using the same function as TO_BASE64() which inserts a new line every 76 characters. Suggested fix: Do not include new line control characters as it produces invalid JSON.