Querying eFPE encrypted columns

You can use a feature in Voltage SecureData called Embedded Format Preserving Encryption to encrypt your data. You choose to use eFPE when you define the format in your SecureData Appliance. This format is slightly different than the standard FPE format. It uses key rotation, and embeds identification information in the encrypted value. Due to these factors, calls to VoltageSecureProtect using an eFPE format may not result in the same encrypted value, depending on key rotation schedules and the identity of the caller. This feature makes querying columns encrypted using an eFPE format more challenging.

When you want to search a standard FPE encrypted column for a value, you can just encrypt the plain text with VoltageSecureProtect and use the output in your query. For example, suppose you want to search for the customers table for an entry with the social security number 559-32-0670. Then you could use the following query:

=> SELECT id, first_name, last_name FROM customers
       WHERE ssn = VoltageSecureProtect('559-32-0670' USING PARAMETERS
                                        format='ssn',
                                        config_dfs_path='voltage.conf');

  id  | first_name | last_name
------+------------+-----------
 5345 | Thane      | Ross
(1 row)

Querying a column that uses eFPE format encryption is not as simple, as you do not know which embedded key was used to encrypt the data. You could just use the VoltageSecureAccess function to decrypt the entire contents of the table column and search the result for the value you need. However, this is inefficient, as the SecureData function has to be called for every row of data in the table.

A better solution is to use the VoltageSecureProtectAllKeys function. This function is similar to VoltageSecureProtect. However, instead of returning a single encrypted value, it returns a table containing the value encrypted with each of the keys defined for the eFPE format.

=> SELECT VoltageSecureProtectAllKeys('376765616314013' USING PARAMETERS
                                      format='cc_num',
                                      config_dfs_path='/voltagesecure/conf')
          OVER ();

      data       |    protected
-----------------+-----------------
 376765616314013 | XMVMRU9RJVU4013
 376765616314013 | X5FD4KO1UEE4013
 376765616314013 | M7ZXTIQVCPB4013
 376765616314013 | UBOSC9K3EXZ4013
 376765616314013 | ZJ1C50C9L9R4013
(5 rows)

In the previous example, the cc_num column is encrypted using an eFPE format. The output from VoltageSecureProtectAllKeys shows that this eFPE format has 5 keys defined. The content of the protected column contains the same value encrypted with each of the keys.

To use this function in a query for a value in an eFPE column, use a JOIN on the table you are searching and the result table from VoltageSecureProtectAllKeys. The following example demonstrates querying the customers table to find all rows that have a cc_num value that matches the unencrypted credit card value 376765616314013.

=> SELECT id, first_name, last_name FROM customers3 u
          JOIN (SELECT VoltageSecureProtectAllKeys('376765616314013' USING PARAMETERS
                                                   format='cc_num',
                                                   config_dfs_path='/voltagesecure/conf')
          OVER ()) pak
          ON u.cc_num = pak.protected;

  id  | first_name | last_name
------+------------+-----------
 5345 | Thane      | Ross

(1 row)