Part 3: Query the newly encrypted data

In the previous part, we:

• Added and applied migrations
• Encrypted the sensitive data

Now we’re going query the encrypted data.

In this part we will:

1. Update our dataset configuration to read from encrypted fields
2. Create some user records to query
3. Query those encrypted records

1. Update dataset configuration

The configuration is currently set to plaintext-duplicate mode on each field.

In this mode, the plaintext field’s will be read from and written to.

The encrypted field’s will be written to.

Update the mode on each field in the dataset.yml to encrypted-duplicate mode.

In this mode, the plaintext field will be written to.

The encrypted field’s will be read from and written to.

Push this configration to CipherStash:

stash upload-config --file dataset.yml --client-id <the client-id from previous step> --client-key <the client-key from previous step>

2. Create some user records

Open your Rails console:

rails console

Create a patient:

Patient.create(full_name: "Grace Hopper", email: "grace@hopper.example", dob: Date.parse("9 December 1906"))

In psql, verify that the data is encrypted;

SELECT __full_name_encrypted, __full_name_match, __full_name_ore FROM users LIMIT 5;

3. Query those encrypted records via PSQL

Now back in the Rails console, to find that new record by email address:

Patient.where(email: "grace@hopper.example")

This will return a result that looks like this:

Patient Load (0.5ms)  SELECT "users".* FROM "users" WHERE "users"."email" = ?  [["email", "grace@hopper.example"]]
=>
[#<User:0x0000000119cd47d0
  id: 1,
  full_name: "Grace Hopper",
  email: "grace@hopper.example",
  created_at: Wed, 15 Feb 2023 22:37:08.134554000 UTC +00:00,
  updated_at: Wed, 15 Feb 2022 22:37:08.134554000 UTC +00:00]

To order users alphabetically by name, do:

Patient.order(:full_name)

3. Query those encrypted records via the UI

Start your Rails server:

rails s

Go to the patients dashboard.

Create a patient:

• Click on new patient
• Complete patient details
• Click on Create Patient

Use the filters on the side to perform queries.

4. Viewing logs of encryptions and decryptions

To view the logs showing encryptions and decryptions of data, go to your workspace folder ~/.cipherstash/<your workspace id>.

Run:

tail -F decryptions.log

That’s it. All your queries will work with no modification. Your data is encrypted, and the CipherStash driver transparently rewrites all your queries.

Most importantly, all of the queries and decryptions are logged, so you can cryptographically prove when your sensitive data is accessed.