Fetch encrypted data using where clause

Question

My entire database is encrypted except the primary key(id). I need to fetch email using $email variable.

$encrypted=registermodel::select('email')->where('email','=',$email)->get();

Answer 1

You cannot easily - its encrypted! You would have to fetch every record decrypt it and then compare the plaintext

This is the proper way to address the problem https://www.sitepoint.com/how-to-search-on-securely-encrypted-database-fields/

Below is my attempt using a CRC based index column

<?php

namespace App\ModelTraits;

use Illuminate\Support\Facades\Crypt;

/**
 * 
 */
trait EmailSigTrait
{

    public function setEmailAttribute($value)
    {
        $this->attributes['email'] = Crypt::encryptString($value);
        $this->attributes['emailsig'] = Self::crcemail($value);
    }

    public function getEmailAttribute()
    {
        if(!isset($this->attributes['email'])){
            return;
        }

        $value = $this->attributes['email'];

        if (empty($value)) {
            return;
        }

        return strtolower(Crypt::decryptString($value));
    }

    static function crcemail($email)
    {
        $email = strtolower($email);

        // anonymise the email
        $name = str_before($email,'@');

        $anon = substr($name, 0, 1) .
                substr($name, strlen($name)/2,1) .
                substr($name, -1) .
                '@' . str_after($email, '@');

        return sprintf(crc32(strToLower($anon)));
    }

    protected function findByEmailSig($email, $model)
    {
        $email = strtolower($email);

        $candidates = $model::where('emailsig', $model::crcemail($email))->get();

        foreach ($candidates as $candidate) {
            if (strtolower($candidate->email) == $email) {
                return $candidate;
            }
        }
        return false;
    }

}

Include this trait in a model that has the encrypted email address.

Add a text column for 'emailsig'

When you save the email field, a crc value is created for parts of the email address and the email is encrypted.

When retrieving the email it is decrypted

When finding the email, it calculates the CRC for the user to find then compares that with the CRC value stored for each email address. Because there could be more than one match (more than one email with the same CRC value) then it iterates over the possible choices until it finds the right email.

Depending on how you encrypt the email today, you may have to adapt to fit.