MySQL Data Encryption with AES_ENCRYPT() and Decryption in Java

MySQL Apr 25, 2022

In today's digital age, data security is paramount. Whether you're handling sensitive user information or protecting critical business data, encryption plays a crucial role in safeguarding information from unauthorized access. In this blog post, we'll explore how to encrypt data using MySQL's AES_ENCRYPT() function and subsequently decrypt it using Java.

Introduction

MySQL is an open-source relational database management system (RDBMS). A relational database organizes data into one or more data tables in which data types may be related to each other; these relations help structure the data. SQL is a language programmers use to create, modify and extract data from the relational database, as well as control user access to the database.

The AES (Advanced Encryption Standard), also known by its original name Rijndael, is a specification for the encryption of electronic data established by the U.S. National Institute of Standards and Technology in 2001.

Goals of This Tutorial

Encrypting Data: Utilize MySQL's AES_ENCRYPT() function to encrypt sensitive data before storing it in the database.
Decrypting Data: Retrieve and decrypt encrypted data using a Java application, ensuring secure data handling within your application.

Minimum Software Requirements

Before diving into the project, ensure you have the following prerequisites installed:

Java Development Kit (JDK)
Apache Maven
MySQL Database Server
MySQL Workbench (optional, for database management)

Sample Project Overview

To illustrate these concepts, we'll use a sample Spring Boot application named aes-mysql-java.

Noticed an issue with this Sample Project? Open an issue or a PR on GitHub!

Basic Usage

Assuming you have a user table:

CREATE TABLE user (
    first_name BLOB,
    last_name VARCHAR(255),
    address VARCHAR(255)
);

your_secret_key: dsfdfdsfsdfdfsfs

Reference: https://www.javainuse.com/bytesize - Online Text(String) Size Calculator Tool (In Bytes)

Replace your_secret_key with your encryption key for secure data storage.

Encrypting Data in MySQL

Let's start by encrypting data using MySQL's AES_ENCRYPT() function. Here’s an example of encrypting a user's first name before inserting it into the database:

INSERT INTO user(first_name, last_name, address) VALUES (to_base64(AES_ENCRYPT('Jane','your_secret_key'))  ,'Doe','UK');

Decrypting data in MySQL

To decrypt encrypted data stored in MySQL, use the following SQL query:

SELECT AES_DECRYPT(from_base64(first_name) , 'your_secret_key'), last_name from user;

This query retrieves the decrypted first_name and last_name from the user table, using the AES_DECRYPT() function along with the FROM_BASE64() function to decode and decrypt the encrypted data.

Additional MySQL Encryption Settings

For additional MySQL encryption settings, you can use the following SQL commands:

SHOW VARIABLES LIKE '%encryption%';

This command displays MySQL server variables related to encryption settings, providing insights into the current encryption configurations.

SET block_encryption_mode = 'aes-128-ecb';

This command sets the block encryption mode to 'aes-128-ecb', ensuring consistency in encryption practices across your MySQL databases.

Decrypting Data Using Java

Next, let's decrypt the encrypted data using a Java application. Below is a simplified Java code snippet that connects to the MySQL database, retrieves the encrypted data, and decrypts it locally:

Conclusion

Implementing AES encryption in MySQL provides a reliable method to secure sensitive data within your database. By leveraging AES_ENCRYPT() and AES_DECRYPT() functions, you can ensure data confidentiality while still maintaining query capabilities. Integrating this with a Java application allows for secure data retrieval and manipulation, enhancing overall data protection in your applications.