CipherStash
CipherStash Documentation

Query your first encrypted database column

In the previous steps, we have:

  • Added the ActiveStash gem to our Rails app.
  • Used ActiveRecord Encryption to encrypt sensitive fields on our User model
  • Discovered we can no longer perform arbitrary queries on that encrypted sensitive data

Now we’re going to use ActiveStash to query that encrypted data.

In this step we will:

  • Sign up for a free account and workspace with CipherStash.
  • Sign in to our account.
  • Update our model to declare which fields we want to be able to query.
  • Reindex our records into your CipherStash workspace.
  • Query encrypted records.

1. Signup:

$ rake active_stash:signup

You will be redirected to https://cipherstash.com/signup/rake to complete your signup.

  • Sign up with your GitHub account or email.
  • The confirmation page has the details of your workspace id.

ActiveStash Signup Confirmation page

2. Sign in:

Sign in using the workspace ID assigned to you in the previous step:

Using bash:

$ rake active_stash:login[YOURWORKSPACEID]

Using zsh:

$ rake active_stash:login\[WorkspaceId\]

3. Any model you use with ActiveStash::Search needs to have a stash_id column:

The stash_id column is used to link search results back to encrypted database records.

For example, to add a stash_id column to the database table for the User model, add the below migration:

$ rails g migration AddStashIdToUser stash_id:string:index
$ rails db:migrate

4. Add the ActiveStash::Search mixin to your user model:

# app/models/user.rb
class User < ApplicationRecord
  # ActiveStash search mixin
  include ActiveStash::Search

  # Previously added application-level encryption, by either ActiveRecord Encryption or Lockbox
  encrypts :name, :email, :suburb
end

5. Declare what fields are searchable:

Add stash_index followed by the fields.

# app/models/user.rb
class User < ApplicationRecord
  include ActiveStash::Search

  # Previously added application-level encryption, by either ActiveRecord Encryption or Lockbox
  encrypts :name, :email, :suburb

  # Fields that will be indexed into CipherStash
  stash_index do
    auto :name, :email, :suburb
  end
end

6. Create a collection:

This will create a users collection in CipherStash, where all user records are stored.

rake active_stash:collections:create

7. Reindex your existing encrypted database records into CipherStash:

$ rails active_stash:reindexall

If you want to just reindex one model, for example User, run:

$ rails active_stash:reindex[User]

You can also reindex in code:

User.reindex

Depending on how much data you have, reindexing may take a while but you only need to do it once.

ActiveStash will automatically index (and delete) data as it records are created, updated, and deleted.

8. Query records:

Open your Rails console:

$ rails c

Create a second user with the same name, but different email and suburb:

User.create(name: "Grace Hopper", email: "grace@test.com", suburb: "Melbourne")

To perform queries over your encrypted records, you can use the query method.

For example, to find a user by email address:

User.query(email: "grace@example.com")

This will return a result that looks like this:

User Load (0.5ms)  SELECT "users".* FROM "users" WHERE "users"."stash_id" = ?  [["stash_id", "bf65f1dd-7428-4263-a3c9-6ba524517cf7"]]
=> 
[#<User:0x0000000119cd47d0
  id: 1,
  name: "Grace Hopper",
  email: "grace@example.com",
  suburb: "Sydney",
  created_at: Wed, 24 Aug 2022 22:37:08.134554000 UTC +00:00,
  updated_at: Wed, 24 Aug 2022 22:37:08.134554000 UTC +00:00,
  stash_id: "bf65f1dd-7428-4263-a3c9-6ba524517cf7">]

This will return an ActiveStash::Relation which extends ActiveRecord::Relation so you can chain most methods as you normally would!

To constrain by multiple fields, include them in the hash:

User.query(name: "Grace Hopper", suburb: "Melbourne")

This will return a result that looks like this:

User Load (0.6ms)  SELECT "users".* FROM "users" WHERE "users"."stash_id" = ?  [["stash_id", "91b35648-0866-4514-81be-70e307c88a8f"]]
=>                                                              
[#<User:0x000000010c13f620                                      
  id: 2,                                                        
  name: "Grace Hopper",                                         
  email: "grace@test.com",                                      
  suburb: "Melbourne",                                          
  created_at: Wed, 24 Aug 2022 22:39:40.316922000 UTC +00:00,   
  updated_at: Wed, 24 Aug 2022 22:39:40.316922000 UTC +00:00,   
  stash_id: "91b35648-0866-4514-81be-70e307c88a8f">]        

To query by a name and order by suburb, do:

User.query(name: "Grace Hopper").order(:suburb)
User Load (0.5ms)  SELECT "users".* FROM "users" WHERE "users"."stash_id" IN (?, ?) AND "users"."stash_id" IN ('91b35648-0866-4514-81be-70e307c88a8f', 'bf65f1dd-7428-4263-a3c9-6ba524517cf7') ORDER BY CASE "users"."stash_id" WHEN '91b35648-0866-4514-81be-70e307c88a8f' THEN 1 WHEN 'bf65f1dd-7428-4263-a3c9-6ba524517cf7' THEN 2 ELSE 3 END ASC  [["stash_id", "91b35648-0866-4514-81be-70e307c88a8f"], ["stash_id", "bf65f1dd-7428-4263-a3c9-6ba524517cf7"]]
=>                                                                                      
[#<User:0x0000000119cc6c48                                                              
  id: 2,                                                                                
  name: "Grace Hopper",                                                                 
  email: "grace@test.com",                                                              
  suburb: "Melbourne",
  created_at: Wed, 24 Aug 2022 22:39:40.316922000 UTC +00:00,
  updated_at: Wed, 24 Aug 2022 22:39:40.316922000 UTC +00:00,
  stash_id: "91b35648-0866-4514-81be-70e307c88a8f">,
 #<User:0x0000000119cc6b08
  id: 1,
  name: "Grace Hopper",
  email: "grace@example.com",
  suburb: "Sydney",
  created_at: Wed, 24 Aug 2022 22:37:08.134554000 UTC +00:00,
  updated_at: Wed, 24 Aug 2022 22:37:08.134554000 UTC +00:00,
  stash_id: "bf65f1dd-7428-4263-a3c9-6ba524517cf7">]

Or to use limit and offset:

User.query(suburb: "Sydney").limit(10).offset(20)

This means that ActiveStash should work with pagination libraries like Kaminari.

You also, don’t have to provide any constraints at all and just use the encrypted indexes for ordering!

To order all records by email ascending and then suburb descending, do (note the call to query with no args first):

User.query.order(email: :asc, suburb: :desc)

For more information visit the ActiveStash Ruby docs.