Bug #102067 Selecting JSON with binary data produces broken JSON string
Submitted: 24 Dec 2020 11:49 Modified: 19 Oct 12:29
Reporter: John WhyIsThisSoComplicated Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:5.7+, 5.7.32, 8 OS:Any
Assigned to: CPU Architecture:Any

[24 Dec 2020 11:49] John WhyIsThisSoComplicated
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.
[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 12:29] Knut Anders Hatlen
This looks like bug#90503, which was fixed in MySQL 8.0.13.