MySQL Data Encryption via AES_ENCRYPT() and decryption via a Java program
This post briefly documents the process of encrypting data in a table using the MySQL native AES_ENCRYPT() function.
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
- MySQL - Encryption
- MySQL - Decryption
- Java - Decryption
Minimum Software Requirements
- MySQL
- MySQL Workbench (Optional)
- Java
Sample Project
aes-mysql-java is the sample Spring Boot web application i've used to illustrate the idea.
Noticed an issue with this Sample Project? Open an issue or a PR on GitHub!
Dependencies
This implementation has a dependency on flyway-mysql, mysql-connector-java, lombok dependencies. The maven/gradle dependencies of the same are mentioned below.
Basic Usage
User table is created by Flyway. Refer to /src/main/resources/data/mysql/migrations
secret key: dsfdfdsfsdfdfsfs
Reference: https://www.javainuse.com/bytesize - Online Text(String) Size Calculator Tool (In Bytes)
MySQL
INSERT INTO user(first_name, last_name, address) VALUES (to_base64(AES_ENCRYPT('Jane','dsfdfdsfsdfdfsfs')) ,'Doe','UK');
SELECT AES_DECRYPT(from_base64(first_name) , 'dsfdfdsfsdfdfsfs'), last_name from user;
Reference
SHOW VARIABLES LIKE '%encryption%';
SET block_encryption_mode = 'aes-128-ecb';
Application Execution
Build and run the application via an IDE or by issuing the following command:
mvnw spring-boot:run
