Can't use AES_DECRYPT to decrypt data from database

[ghost]

Hi!

SELECT SUM(AES_DECRYPT(data_sum_original, '"test"'))
FROM debts
WHERE id_user = '346'

That returns 0 even if the key is correct. Also, SUM(AES_DECRYPT(data_sum_original, '')) (no key) returns 0 too, but SUM(AES_DECRYPT(data_sum_original, '"testingtestingalot"')) returns null. I have data in my database, which will return 5000 if I use plain text.

One more thing: is the encrypted strings (used with your class) in the image below correct?

If you go to https://www.browserling.com/tools/aes-encrypt the encryption will look like this: U2FsdGVkX18dFBLEGDTvlcz6Ykf0GvJcVbHMSFDXsRc=. If you want to decrypt that encrypted string, the password is test.

[annejan]

I can answer the last bit, the online tool uses a base64 representation, where our tool stores it in binary form.

The first bit is harder to answer, my first instinct tells me MySQL treats the decrypted data as a binary string with which it might not be able to do math operations like SUM.

After some google-ing it seems that you need to use some weird SQL logic here . .

SELECT SUM(CAST(CONV(HEX(AES_DECRYPT(data_sum_original, '"test"')),16,10) AS UNSIGNED INTEGER))
FROM debts
WHERE id_user = '346'

NB: I haven't tested this assumption in any way.

[ghost]

Thank you for your answer.

I'm using MariaDB, which gives me the following error message with the code you gave me.

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '( SELECT SUM(data_sum) FROM transactions WHERE id_user = '1' ' at line 32

I will ask Stack Overflow for this though, when I have more time :) But now I have more flesh on the bones. Many thanks once again!

[ghost]

The error message is now gone (I didn't have to do anything - it just somehow solved it self), but that SQL query you gave me, prints 18 446 744 073 709 551 616 which is far from correct! Only one row returns that value, since the other rows returns null.

Stack Overflow kind of laughed at my question, which is not the first time they do. It's not often I am asking questions there nowadays. I thought it would be helpful this time, but nope! Quite the opposite.

[ghost]

This issue has now been fixed!

I was using a 128 character long string. I tested with 99 character and it gave the same result. 64 characters worked like a charm!