How to Securely Store Passwords: MySQL AES vs. Application‑Level Encryption

Why Encrypt Data in Databases?

Data encryption and decryption are crucial in security; storing passwords as ciphertext in a database prevents attackers from easily stealing user privacy.

1. MySQL Built‑in Encryption Functions

Use AES_ENCRYPT and AES_DECRYPT functions directly in SQL.

Pros: Encryption is provided by the database, no extra code needed.

Cons: Requires rewriting SQL statements and lacks easy integration with ORM frameworks.

Example

# Insert encrypted data
INSERT INTO userdata(username,password,encryptedpassword)
VALUES ('magedu','mypasswd',AES_ENCRYPT('mypasswd','mykey'));

# Retrieve and decrypt
SELECT username,password,AES_DECRYPT(encryptedpassword,'mykey')
FROM userdata;

2. Application‑Level Encryption

Encrypt data before storing and decrypt after retrieving in the application code.

Pros: Works with any supported database system and offers high flexibility to change algorithms and keys.

Cons: Requires updating model definitions and implementing custom encryption/decryption logic.

Example 1: Base64 (Python)

# Using base64
s1 = base64.encodestring('hello world')
s2 = base64.decodestring(s1)
print s1, s2  # aGVsbG8gd29ybGQ= hello world

Example 2: AES (Python Crypto)

from Crypto.Cipher import AES
obj = AES.new('key123', AES.MODE_CBC, 'This is an IV456')
message = "magedu.com"
ciphertext = obj.encrypt(message)
obj2 = AES.new('key123', AES.MODE_CBC, 'This is an IV456')
plain = obj2.decrypt(ciphertext)  # 'magedu.com'

3. Summary

The first approach relies on SQL statements, which can conflict with ORM usage, while the second approach offers more control and portability at the cost of additional code and model changes.