In my previous article, I looked into the process of enabling MySQL keyring encryption, which is a complex topic, so I divided it into multiple articles. We can use keyring encryption to encrypt the physical files of the database, but what if we also want to encrypt the data stored in our tables? Fortunately, MySQL allows us to store data in an encrypted form, which we will discuss in this article.
Previous Article: Encrypting Percona MySQL Keyring
OS Version: "Ubuntu 20.04.3 LTS"
Database: 8.0.30-22 Percona Server (GPL)
Let's create a testing database and table.
CREATE database test;
use test;
Create table encrypted_data (id int AUTO_INCREMENT , col1 varbinary(200));
insert into encrypted_data (col1) values (AES_ENCRYPT('This is credit card 1', '123'));
insert into encrypted_data (col2) values (AES_ENCRYPT('This is credit card 2', '1234'));
For the column in which the data will be stored in an encrypted form, the data type should be VARBINARY, because AES_ENCRYPT() encrypts a string and returns a binary string. Otherwise, it will prompt an error. (Please find below)
The last digit used in AES_ENCRYPT is the key i.e., the password for the value. We will need this in case of decryption. Note that we deliberately change the password while inserting the 2nd row.
Now, let's see while selecting the table.
Let’s try decrypting the data.
As the datatype is VARBINARY, we can’t decrypt the data by just using the decrypt functions. We will convert the type to char and use the decrypt function to find the actual value.
select id, cast(AES_DECRYPT(col1, '123') as CHAR) from encrypted_data;
Note we only get the text form of row 1, why? Because we passed the key (123) which was used to insert row 1, if we pass (1234) then we will see NULL in the first row and text in the 2nd row. If we don’t convert the datatype, we will get the result below.
Issues in this function: If somehow the value of a column is the same, then we will get the same encrypted form when the password is the same as well. In our case most probably the password will be the same. See below that the highlighted rows' values are the same. To overcome this, we can use unique constraints on the column. Then it will prompt an error before inserting the same values.
To Conclude:
Encryption and security are both intricate and crucial topics. When looking into column-level encryption in MySQL, you have a few possibilities. The most straightforward option would be to use the integrated encryption functions in MySQL. If you want more control, you could manage the whole encryption and decryption process in your application.
