Encrypt MySQL Data in Web Apps

G5 Cyber Security

2 weeks ago

TL;DR

This guide shows you how to encrypt data stored in your MySQL database when building a web application. We’ll cover using AES encryption, generating keys securely, and integrating it with PHP for common use cases.

1. Understanding Encryption Options

We’ll focus on Advanced Encryption Standard (AES) as it’s widely supported and considered secure. There are different modes of operation; we’ll use CBC (Cipher Block Chaining) for its good balance of security and performance.

2. Generating an Encryption Key

Important: Never hardcode your encryption key directly into your code! This is a major security risk. Here’s how to generate one securely:

Using OpenSSL (command line):

openssl rand -base64 32

This creates a random 32-byte key, which is suitable for AES-256 encryption. Store this key securely – ideally in an environment variable or a dedicated secrets management system.

Storing the Key: Use environment variables to store your encryption key. This keeps it separate from your code repository and makes configuration easier.

3. MySQL Configuration

MySQL 5.7.9+ has built-in AES functions, but for older versions or more control, you can use a User Defined Function (UDF). We’ll demonstrate using the built-in functions:

Ensure AES Support: Check if your MySQL version supports AES encryption.

4. PHP Implementation

Here’s how to encrypt and decrypt data in PHP:

4.1 Encryption Function

This function takes the data to encrypt and the encryption key as input. It generates a random Initialization Vector (IV), encrypts the data using AES-256-CBC, and returns the base64 encoded ciphertext along with the IV separated by a pipe symbol (|).

4.2 Decryption Function

This function takes the base64 encoded encrypted data and the encryption key as input. It decodes the data, splits it into ciphertext and IV, and decrypts the ciphertext using AES-256-CBC.

5. Integrating with Your Web Application

Database Interaction: Encrypt sensitive data before storing it in the database and decrypt it immediately after retrieving it.
Example (Storing a password):
Example (Retrieving a password):

6. Security Considerations

Key Management: Protect your encryption key at all costs! Use strong access controls and consider a dedicated secrets management solution.
IV Storage: Store the IV along with the ciphertext. It’s crucial for decryption.
Salted Hashing: For passwords, always use salted hashing (e.g., bcrypt or Argon2) in addition to encryption. Encryption alone is not sufficient for password security.
Regular Key Rotation: Periodically change your encryption key to minimize the impact of a potential compromise.