AES_DECRYPT Requires Cast? I'm trying to use the AES_ENCRYPT and AES_DECRYPT functions. I've found an answer to my initial question which was how to decrypt the encrypted string, https://stackoverflow.com/questions/16556375/how-to-use-aes-encrypt-and-aes-decrypt-in-mysql. I'm not sure why the char(50) is needed though, nor what to do since my string can be longer than 50 characters. Should I use char(65535)?

This returns the encrypted string as expected:

SELECT 'test' as original, 
AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))) as encrypted, 
CAST(AES_DECRYPT(AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))), UNHEX(SHA2('This Is Cool',512))) as char(50)) as decrypted




this does not:

SELECT 'test' as original, 
AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))) as encrypted, 
AES_DECRYPT(AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))), UNHEX(SHA2('This Is Cool',512))) as decrypted
To resume the situation for people who don’t want to follow the link to the original question on SO: the OP is querying a Mysql DB via an unspecified interface which (sensibly) refuses to show the values stored in VARBINARY fields with encrypted data, and (rather dumb-mindedly) still shows a BLOB label for the value returned by calling AES_DECRYPT on them. The answer given on SO was to cast those results as CHAR(50), which from a DB perspective makes little sense, but in this way the interface shows the results as text and everybody is happy. 

The question here is: if I have to use CHAR(50), how can I handle longer values?  

My answer is: you don’t say which interface you are using (your screenshots are not enough for me to recognize it), but I’d bet that if you cast as CHAR instead of CHAR(50) it still works and you have no size limits that you should care of. So:

SELECT 'test' AS original, 
        AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))) AS encrypted, 
        CAST(AES_DECRYPT(AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))), 
                                             UNHEX(SHA2('This Is Cool',512))) 
             AS CHAR) AS decrypted;


However, this is an interface question, not a DB engine question, and it is rather strange. I suspect the interface settings have been changed from the ones used when asking the first question, because now the strings which the interface thinks are binary are automatically displayed as HEX. Giving the first query to a commandline interface does not produce the results shown here. To get them from a commandline interface I have to write the second field as HEX(AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512)))); similarly, the supposedly wrong result of the second query is simply HEX('test') instead of 'test' because the interface does not know it is actually readable text. Casting it to CHAR should work.

Please be aware that all those conversions the interface is forcing you to perform are not needed when data is exchanged between different parts of your application.