All geek questions in one place

How to encrypt emails in mysql database but still be able to query them?

I want to store the email addresses of users in the MySQL database using encryption to ensure that they do not become public if the database is compromised. I believe that if I encrypt them with mysql AES_ENCRYPT() , then I cannot create an index in the INNODB table because I need to use the BLOB data type. If the table becomes very large, it will take a long time.

What is the best solution for protecting email addresses, but can you quickly request and save them as unique values ​​in a column?

+4
source share
3 answers

When a user logs into your site, use AES_ENCRYPT () to encrypt email.

 INSERT into users (email) VALUES (AES_ENCRYPT(' someemail@example.com ', 'aeskey'));

When you query your database, you can call the AES_DECRYPT () function as follows:

 SELECT AES_DECRYPT(email, 'aeskey') from users;
+9
source

If you use SHA-256 addresses or something similar, you can still index your tables, you can still quickly find addresses (when a user searches for example@example.com , you just enter the data and select the appropriate hashes in the tables).

ssh uses a very similar hash trick, (For more details, see the -H in this man page.)

+4
source

AES_DECRYPT (e-mail, secret key) and AES_ENCRYPT (e-mail, secret key) are the best solution,

I am not 100% sure because of the uniqueness after encryption, but the theory says that if a unique email should be unique

0
source

Source: https://habr.com/ru/post/1337630/

More articles:


All Articles