Need help with Encrypting a Map with Cloak and Querying

Phoenix Forum Questions / Help
,
1

Hello there,
Happy Thanksgiving to everyone.

I am working on a project where the requirement is to encrypt all the data at rest. I am using Cloak for encrypting most of the fields. The schema fields looks like this

name Encrypted.Binary
email Encrypted.Binary
password Encrypted.Binary
profile_details Encrypted.Map

The profile_details map fields look like

department string
identification integer
building string

I am able to encrypt the map fine but I am running into an issue with querying the data. As the data is stored as a binary in the database, I am wondering how can i query the profile_details field?

Any pointers are welcome.

2

hey,

Something like Ecto.Query.API.fragment — Ecto v3.6.2
will be useful here I guess.
or maybe you can show us your query we can help you more than.

3

I don’t think this is possible without changing the schema because Cloak is using an application-level encryption. This means that the DB engine has no way of decrypting the data, which also means that the data gets decrypted only in your app, which means that the filtering needs to happen in the app itself.

One way to improve the performance of the querying would be to:

  • Flatten the schema (or put the profile details in its own table),
  • Encrypt the profile detail fields and add hashes for fields that you need to query on,
  • Query by the hash of the field,
  • Do the final comparison check in the app on the unencrypted data.

Something like this:

schema "profile_details" do
  field :department, Encrypted.Binary
  field :department_hash, Cloak.Ecto.SHA256
end

@hashed_fields [department_hash: :department]

def put_hash_fields(changeset) do
  @hashed_fields
  |> Enum.reduce(changeset, fn {hashed_field, unhashed_field}, changeset ->
    if value = get_field(changeset, unhashed_field) do
      # Cloak will calculate the hash for you here.
      put_change(changeset, hashed_field, value)
    else
      changeset
    end
  end)
end

query = from(
  profile_details in ProfileDetails,
  # ^ means the value will be casted to the type of the field,
  # which means that Cloak will hash the value for you.
  where: profile_details.departement_hash == ^value
)
1 Like
4

Thank you @stefanchrobot. That explains why I was struggling with querying the schema. I flattened the schema and it is working beautifully.

As always, the forum and community are of great help

2 Likes