Jul 28, 2020 3:55 AM

Masking real email addresses in MySQL/MariaDB

Masking real email addresses in MySQL/MariaDB

In this article I will explain how to mask real email addresses in MySQL/MariaDB. This can be used to hide user information in the database. You can keep the domain email address or hide it entirely. We will use the UUID() function to generate a unique email address. Let's take a look at an example of how to mask email addresses without hiding the domain.

First, create a simple `emails` table:

--
-- Table structure for table `emails`
--
CREATE TABLE IF NOT EXISTS `emails` (
	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`email` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`id`)
);

--
-- Dumping data for table `emails`
--
INSERT INTO `emails` (`email`)
VALUES
	('example@gmail.com'),
	('example2@yahoo.com'),
	('example3@msn.com'),
	('example4@outlook.com'),
	('example5@gmail.com');

Use the following query to update email addresses with unique values (without hiding the domain):

UPDATE `emails` SET `email` = CONCAT(LEFT(UUID(), 8), '@', SUBSTRING_INDEX(`email`, '@', -1));

Now let's check the result:

SELECT * FROM `emails`;

--
--	[output]
--
--	id	email
--	1	a46a97a5@gmail.com
--	2	a46a99a1@yahoo.com
--	3	a46a9a1f@msn.com
--	4	a46a9a6d@outlook.com
--	5	a46a9ab7@gmail.com

Also, consider an example of how to completely mask email addresses. Use the following query for this:

UPDATE `emails` SET `email` = CONCAT(LEFT(UUID(), 8), '@example.com');

Now let's check the result:

SELECT * FROM `emails`;

--
--	[output]
--
--	id	email
--	1	fd046e18@example.com
--	2	fd0477c8@example.com
--	3	fd047a25@example.com
--	4	fd047be6@example.com
--	5	fd047d17@example.com

Other articles