[possible degradation] UTF-8 string is now printed in 0x hex form in some cases. #1511

Activity

1. rolandwalker commented on Feb 5, 2026

   

   rolandwalker

   on Feb 5, 2026

   Contributor

   More actions

   Hi!

   Yes, this is intentional. In #1483, we fixed a bug in which some binary values displayed as text, and other binary values displayed as hex literals, depending on whether the values could be interpreted as UTF-8.

   Your expression, simplified as

   SELECT AES_DECRYPT(UNHEX(HEX(AES_ENCRYPT('foo@example.com', 'my_secret_key'))),'my_secret_key') AS foo;
   ┌──────────────────────────────────┐
   │ foo                              │
   ├──────────────────────────────────┤
   │ 0x666f6f406578616d706c652e636f6d │
   └──────────────────────────────────┘

   is indeed binary! We can check this using CHARSET()

   SELECT CHARSET(AES_DECRYPT(UNHEX(HEX(AES_ENCRYPT('foo@example.com', 'my_secret_key'))),'my_secret_key')) AS foo;
   ┌────────┐
   │ foo    │
   ├────────┤
   │ binary │
   └────────┘

   The docs for AES_DECRYPT():

   • https://dev.mysql.com/doc/refman/9.0/en/encryption-functions.html#function_aes-encrypt

   explain that

   AES_DECRYPT() decrypts the encrypted string crypt_str using the key string key_str, and returns the original (binary) string in hexadecimal format. (To obtain the string as plaintext, cast the result to CHAR. Alternatively, start the mysql client with --skip-binary-as-hex to cause all binary values to be displayed as text.)

   So, using the documented recommendation works:

   SELECT CAST(AES_DECRYPT(UNHEX(HEX(AES_ENCRYPT('foo@example.com', 'my_secret_key'))),'my_secret_key') AS CHAR) AS foo;
   ┌─────────────────┐
   │ foo             │
   ├─────────────────┤
   │ foo@example.com │
   └─────────────────┘

   Though I personally would be more explicit with CONVERT … USING:

    SELECT CONVERT(AES_DECRYPT(UNHEX(HEX(AES_ENCRYPT('foo@example.com', 'my_secret_key'))),'my_secret_key') USING utf8mb4) AS foo;
   ┌─────────────────┐
   │ foo             │
   ├─────────────────┤
   │ foo@example.com │
   └─────────────────┘

   Finally, the display of the original value as a hex literal is consistent with the vendor client.

   However, mycli could consider an option like --skip-binary-as-hex which restored the previous behavior, so long as it was not the default.

   👍React with 👍1Reacted by ynn

   😄React with 😄1Reacted by ynn
2. your-diary commented on Feb 7, 2026

   

   your-diary

   on Feb 7, 2026

   Author

   More actions

   @rolandwalker
   Thank you very much for the detailed explanation.
   I simply learned many things from your response.

   ---

   Finally, the display of the original value as a hex literal is consistent with the vendor client.

   I confirmed this after reading the documentation you quoted and that of --skip-binary-as-hex:

   # interactive session without `--skip-binary-as-hex`
   $ mysql
   
   mysql> SELECT AES_DECRYPT(UNHEX(HEX(AES_ENCRYPT('foo@example.com', 'my_secret_key'))),'my_secret_key') AS foo;
   +----------------------------------+
   | foo                              |
   +----------------------------------+
   | 0x666F6F406578616D706C652E636F6D |
   +----------------------------------+

   # interactive session with `--skip-binary-as-hex`
   $ mysql --skip-binary-as-hex
   
   mysql> SELECT AES_DECRYPT(UNHEX(HEX(AES_ENCRYPT('foo@example.com', 'my_secret_key'))),'my_secret_key') AS foo;
   +-----------------+
   | foo             |
   +-----------------+
   | foo@example.com |
   +-----------------+

   # non-interactive session
   $ echo "SELECT AES_DECRYPT(UNHEX(HEX(AES_ENCRYPT('foo@example.com', 'my_secret_key'))),'my_secret_key') AS foo;" | mysql
   
   foo
   foo@example.com

   ---

   However, mycli could consider an option like --skip-binary-as-hex which restored the previous behavior, so long as it was not the default.

   • Add binary_display configuration option #1512

   Thank you!

   I do want this because I have many existing SQL statements which use AES_DECRYPT() without CAST() or CONVERT(); rewriting all of them would be time-consuming.
3. rolandwalker

   closed this as completedin #1512on Feb 7, 2026
4. rolandwalker commented on Feb 7, 2026

   

   rolandwalker

   on Feb 7, 2026

   Contributor

   More actions

   binary_display option added in https://github.com/dbcli/mycli/releases/tag/v1.50.0 !

   👍React with 👍1Reacted by ynn

   😄React with 😄1Reacted by ynn

   🎉React with 🎉1Reacted by ynn