How to decrypt all rows from mysql db

Question

Hopefully not an already answered question, but I want to be able to select everything while decrypting it similar to just doing select * from like below:

$sql = $conn->prepare("SELECT AES_DECRYPT(*, UNHEX('...");
$sql->execute(array("test2", "test2"));

But this doesn't seem to work, creates a syntax error. Is there any other way?

Answer 1

I want to be able to select everything while decrypting it similar to just doing select * from

You can't do that. AES_DECRYPT(), and sister function AES_ENCRYPT(), operates on strings, not on records.

You do need to repeat the function call for each and every column you want to decrypt:

select
    aes_decrypt(col1, ...) as decrypted_col1,
    aes_decrypt(col2, ...) as decrypted_col2,
    ...
from mytable