0

For archival/historical purposes, after doing an UPDATE on a table in MySQL, I am storing a snapshot of the relevant fields in this table as JSON in a TEXT field of a changelog table. If a table has a varbinary field to securely store sensitive information, I am storing a string representation of the varbinary field by casting the varbinary field to CHAR because I cannot serialize binary data to JSON. This I have done as follows:

SELECT CAST(BinaryField AS CHAR) as CastedValue FROM table

A sample value from the binary field is: 0x774751ECAEC2D03703805E07AB0B8356 and casted value is: wGQ���7�^��V

The original value was stored in a varbinary field using the MySQL aes_encrypt function and a key.

When I try to decrypt this casted value using aes_decrypt, it is returning NULL:

SELECT cast(aes_decrypt('wGQ���7�^��V' ,'mykey') as char) as thedata ;

How can I get my original value back from the casted binary value?

RickInWestPalmBeach
  • 548
  • 6
  • 20

1 Answers1

0

Encrypted data is an array of 8-bit bytes with no encoding. In fact many bytes and byte sequences have no UTF-8 (or any meaningful) character representation.

Ultimatly when the data is decrypted it will be back to it's original encoding.

There is no "casting", it is strictly how you look at the bytes, and it is an encoding that gives bytes some meaning other than just 8-bits.

zaph
  • 111,848
  • 21
  • 189
  • 228
  • Casting or no casting, how can I retrieve the original value ? – RickInWestPalmBeach Mar 11 '16 at 17:13
  • See MySQL [Encryption and Compression Functions](https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html). Also SO [How to use AES_ENCRYPT and AES_DECRYPT in mysql](http://stackoverflow.com/questions/16556375/how-to-use-aes-encrypt-and-aes-decrypt-in-mysql). – zaph Mar 11 '16 at 17:51
  • I have done this Ad Nauseum. Nothing addresses decrypting a hard-coded encrypted value. – RickInWestPalmBeach Mar 11 '16 at 21:24