How can I search encrypted data in Laravel using like operator

Question

How can I search data in Laravel using like operator, I have used

encrypt($searchValue); 

OR

Crypt::encryptString($searchValue)

But both return full encrypted data but I need searchable data using like operator, In that case, the first name is the encrypted format when is search normal text it returns null

User::where('first_name', 'like', '%' . 'abc' . '%')->get();
//it's return null

When I user

//searchValue is like only 'ab'
User::where('first_name', 'like', '%' . Crypt::encryptString($searchValue) . '%')->get();
//it's also return null 

Answer 1

I came across this issue too.

My solution is to use Laravel Scout to build a search index.

Depending on the security policies of the encrypted data (and the search driver you're using), this solution may not be suitable.

I am using the Laravel TNT Search Driver which stores the index on my server.

<?php

namespace App\Models;

use Illuminate\Support\Facades\Crypt;
use Laravel\Scout\Searchable;

class User extends Model
{
    use Searchable;

    public function toSearchableArray(): array
    {
        return [
            'id' => $this->id,
            'email' => $this->getDecryptedEmail()
        ];
    }

    public function getDecryptedEmail(): string
    {
        return Crypt::decryptString($this->email);
    }
}

Then in your controller, or wherever you want to implement the search function:

...
public function search(Request $request)
{
    return User::search($request->input('email'))->get();
}
...

If you need to do an exact match, you can use the filter method on the search results. Doing the search first should provide better performance rather than filtering large sets of data.

You can also place constraints on the search function if you need to, for example:

public function search(Request $request)
{
    $constraints = User::where('is_active', '=', 1);

    $filtered = User::search($request->input('email'))
                ->constrain($constraints)
                ->get()
                ->filter(function ($user) use ($request) {
                    return $user->getDecryptedEmail() === $request->input('email');
                });
}

Answer 2

Crypt::encryptString('abc') will output a slightly different string each time you call it. For that reason, you can't do something like:

User::where('first_name', 'LIKE', '%abc%')->get();
# OR
User::where('first_name', 'LIKE', '%' . Crypt::encryptString('abc') . '%')->get();

If you're encrypting the string and saving it to the database, the point is that you can't see it as plain-text, and by extension, can't search against it.

Sidenote, your first case %abc% might return a result, if another encrypted string has that sequence, but it will not be a reliable way to search for the un-encrypted value of abc, so you can't rely on that.