Email addresses in the database in encrypted form in PHP, how to implement?

Question

PHP

Good day to all!
I've been developing in PHP for quite some time now, but I've worked very little with cryptography. And this is the task before me.
There is a `users` table in the MySQL database. The table has fields: `uid` - user id and `email` - user's e-mail address.
I need to write php (using only common php modules like Mcrypt) code that:

• stores all email addresses in encrypted form
  
• it should be possible to search for users by the domain of their email addresses
  
• you cannot use additional fields or other database tables
  

To be honest, I don’t even know how to approach this task. Please advise how this can be implemented.

Answer 1

additional fields cannot be used

This is a very cruel condition, it forces you to make difficult decisions that can turn into problems in the future. But if you really want adventure, then you can go this way:
1. Initially, parse the email into a username and domain.
2. Encrypt these parts with some block cipher so that the lengths of the resulting strings do not correlate with the initial content. Here there are restrictions on the length of the initial username and domain, well, in real life, the length of the email is still limited by something.
3. Write lines like this to the database:

sprintf("%s_%s", base64_encode($encripted_domain), base64_encode($encripted_username));

4. Carry out a selection with filtering by domain as follows:

sprintf("SELECT * FROM table WHERE email LIKE \"%s_%%\" ", base64_encode($encripted_domain));

5. Restore the original email like this:

list($domain_base64, $username_base64) = explode("_", $encripted_email_from_db);
$decripted_email = sprintf("%[email protected]%s", decript_function(base64_decode($username_base64)), decript_function(base64_decode($username_base64)));

Here decript_function is a custom decryption function.

Answer 2

https://dev.mysql.com/doc/refman/5.5/en/encryption... for example, you can encrypt inside the database.
Something like - INSERT INTO table VALUES (AES_ENCRYPT (email))
Select - SELECT AES_DECRYPT (email)
But for searching by domain, it is better to create a separate column.
Well, or you get a mega-long search, like WHERE AES_DECRYPT (email) LIKE '%domain'

Answer 3

Interesting suggestion, thanks. Can you be a little more specific?