Määntig,17 Auguscht, 2020
Both MySQL and MariaDB provide a native way to encrypt and decrypt data using the AES algorithm. I am told by experienced programmers that is not a good idea to store password this way (unless they are hashed) because the decryption key might appear in the logs or in transit but just in case here is how is done:
Let’s assume we have a table with admins for a website. We can now encrypt the password field directly in the database:
INSERT INTO admins (id, user, pw) VALUES (NULL, 'admin',
AES_ENCRYPT('user_password','encryption_key'));
It is pretty straightforward: the password record will be encrypted with an encryption key. The AES encrypted record is stored in binary format so in my table the password column type is varbinary(150).
Now to decrypt your password you need to provide the encryption key you used to add the data (without the key you won’t be able to decrypt your data for the encrypted field/row so don’t lose it!)
SELECT *, AES_DECRYPT(password, 'encryption_key') AS decrypted FROM admins WHERE user = 'admin'
This will return the decrypted password (or data). The only usage I could think of is probably not very secure but probably still better than storing passwords in clear in the database 🤔.
I think this can be be useful and since I didn’t find many recent articles with examples working on MariaDB and MySQL I wanted to share the easiest way to use it!
UPDATE
the function is meant to be used to decrypt sensitive data but not passwords.
back home