How to search encrypted value in Yajra datatables of laravel?

[atulnaik2008]

I am using Yajra datatable, But I am getting problem to search encrypted value. My name,email,phone_no is stored in encrypted format. How can we match the searched value with encrypted value which is stored in the database? I am not getting the response.My code is as below.

$contacts_array = DB::table(DB::raw("(SELECT c.*,cp.title as company, cp.logo as logo,co.connections FROM app_contact1 as c LEFT JOIN app_company as cp ON c.company_id=cp.company_id LEFT JOIN (SELECT count(connection_id) as connections, contact_id FROM app_connection WHERE organization_id=? AND contact_id !='' GROUP BY contact_id) as co ON co.contact_id=c.contact_id WHERE c.organization_id=? AND NULLIF(c.archived, ' ') IS NULL AND ( (c.privacy = 0 OR c.privacy IS NULL) OR (c.privacy = 1 AND (c.created_by='".$my_user."' OR FIND_IN_SET('".$my_user."',c.stakeholders))) ) ORDER BY c.contact_id desc) as contacts"))->setBindings([$session_org_id,$session_org_id]);

 Datatables::of($contacts_array)
   ->filterColumn('c.c.name', function ($contacts_array, $search_value) {
            $query->where($query->qualifyColumn('c.name'), \EncryptHelper::instance()->en_de_crypt($search_value,'e'));
        });

[Echecivuole]

@yajra I think you have also considered the case for which search has to work with Laravel Encrypted values. How to allow your searchbar to find encrypted values or to decrypted them before to search?

[schonhoff]

That is not a package problem at all. Encrypted DB values are stored as string. You can only match those strings with the same encrypted string or you need to get all data from the column you want to search and search them in memory. Another approach would be to get a hashed value of the column value in another column and search for the hash (Hashes can be handled in some database drivers and their queries). But you can't use "like" with it because it needs to be an exact match.

In memory approach:

->filterColumn('user', function (Builder $query, $keyword)
	$userIDs = DB::table('users')
		->select([
			'users.id as user_id',
			'users.name',
			'users.deleted_at',
		])
		->whereNull('users.deleted_at')
		->get()
		->filter(function (User $user) use ($keyword) {
			return isset($user->name) && stristr($user->name, $keyword);
		})
		->pluck('user_id')
		->toArray();

	$query->whereHas('user', function (Builder $query) use ($userIDs) {
		$query->whereIn('users.id', $userIDs);
	});
});

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.