What Am I doing worng? Decrypting an AES on MySQL using AES_DECRYPT

Question

I managed to encrypt a password using AES_ENCRYPT.

SELECT
acc.id,
acc.user_id,
acc.type,
acc.account,
acc.`password`,
acc.`status`
FROM
acc

Returns this:

id      user_id   type          account         password                   status    
16      4         Main          xjdkdndn        xU+WHHT9Eidt8J+xoNMbKw==   0    

The password is obviously encrypted. I'm trying to decrypt it using this:

SELECT AES_DECRYPT(password,'fawefwefwaef4waegdf235twe4rsgrdtgw54ershtfg')
FROM acc;

fawefwefwaef4waegdf235twe4rsgrdtgw54ershtfg is the encryption key.

However it always returns a null value.

What am I doing wrong?

Forgot to add, tha password is fkdkfjkddk

EDIT:

Guys I upgraded my mysql to the 5.6 version, and now this is what is being returned:

That looks like something that could be aes encoded, but then I use AES_DECRYPT, it still returns a NULL value.

Any guesses?

Answer 1

Assuming that your encryption key is correct, this will select the password field from user id of 16 then try to decrypt it and pass it back to you as a UTF8 encoded string.

SELECT password, CONVERT(AES_DECRYPT(FROM_BASE64(password), 'fawefwefwaef4waegdf235twe4rsgrdtgw54ershtfg') USING utf8) AS `password` FROM `acc` WHERE `id` = 16

Answer 2

Try using AS, for example:

SELECT AES_DECRYPT(acc.password,'fawefwefwaef4waegdf235twe4rsgrdtgw54ershtfg') AS my_decrypted_password FROM acc;

Additional: The above code is what I am using and it works great, but adamS is correct you do want to store the password as Blob or TinyBlob. This is probably why you are returning null, because nothing is stored and what you are seeing is NULL encrypted, not your password, i.e., what you have displayed above as xU+WHHT9Eidt8J+xoNMbKw== is just NULL, it is not your password. Change to Blob or Tinyblob and you should be good.

Answer 3

I installed this: http://wi-fizzle.com/downloads/base64.sql on my database, and now it's working!

Thanks for the help guys.

Answer 4

The encrypted password you have is in base64 and I suspect you are not converting from base64 when you are decrypting. You will need to convert from base64. This is how it should work:

SELECT to_base64(aes_encrypt('fkdkfjkddk','fawefwefwaef4waegdf235twe4rsgrdtgw54ershtfg'));

Returns: pS9sEA/o6XAaUYud631R2g==

SELECT aes_decrypt(from_base64('pS9sEA/o6XAaUYud631R2g=='),'fawefwefwaef4waegdf235twe4rsgrdtgw54ershtfg');

Returns: fkdkfjkddk

Answer 5

For me issue was fixed when I used VARCHAR(200) instead of TEXT to store my encrypted string.

Here is my working solution :

Encrypt (store my column as encrypted string)

UPDATE `table` 
SET `column_name` = TO_BASE64(
    AES_ENCRYPT(
        'data', 
        SHA2(
            CONCAT(
                'my app salt',
                'my very good encryption key / passphrase'
            ),
            512
        )
    )
);

Decrypt (read my table)

SELECT *, 
AES_DECRYPT(
    FROM_BASE64(`column_name`), 
    SHA2(
        CONCAT(
            'my app salt',
            'my very good encryption key / passphrase'
        ),
        512
    )
) AS `column_name`
FROM `table`