Search columns with encryption

[LaMance22]

Hi, i have encryption field in database. I want to search these values in table.

    protected $casts = [
        'name_field_in_db' => 'encrypted',
    ];

I try remove "$casts" on model and add code to table but not working.

        Column::make(__('Name Column'))
        ->label(function($row) {
            return Crypt::decryptString($row->name_field_in_db);
        })
        ->searchable(function(Builder $builder, $term) {
            $test = Crypt::encryptString($term);
            $builder->orWhere('name_field_in_db', 'like', $test);
        }),

Please help me in solving this problem. Thanks and best regards.

[lrljoe]

You can't do a like against an encrypted value, as it won't be "like" it at all due to how encryption works.

Your best bet would probably be tags, look at Spatie's solution.

What's the use case. Perhaps an argument for storing part of it unencrypted?

[LaMance22]

Hi, thanks for your answer. Tags spatie? maybe you meant "Spatie Ciphersweet" ? There is a field in the employee's data that I would like to be encrypted (in the database) so that in the event of access by the wrong person to, for example, a file with a database backup copy, this information would be encrypted there. In the application, this field is decrypted, but it cannot be searched in the table.

[lrljoe]

In that instance, you're going to be somewhat limited sadly!

Depending on the size of your data, you could of course retrieve ALL models, decrypt the values, and store them in a collection in memory, and search against that collection, but that's really not a nice way of doing it, alternatively you could make use of a redis cache with a very short lifespan to help you with this.

Another alternative would be to split the first-X digits, and store them in a separate field, and then only return once someone has searched for at least X digits (e.g. 4).
For example, given a 12-digit "sensitive ID number", you split the first 4 digits off, encrypt them separately in a field in your database, then you could encrypt the person's search, and return all rows that have those first 4 digits.

There are other mechanisms for protecting sensitive data if your concern is backup copies etc (read up on Transparent Data Encryption) which is an alternative.