Order by / Sort with encrypted field

Hello everyone,

I'm facing a challenge that maybe some of you already encountered.

Basically I am saving some sensitive information on the database in an encrypted manner but now I have the need to present the decrypted data sorted by alphabetic order.


A more concrete example is as follows:

  1. We have a column of names which are encrypted on the database;
  2. We are fetching 20 names at a time and decrypting them and when we reach the bottom of the screen we will fetch another 20 names and decrypt the new names to present;
  3. Problem: the list needs to be ordered by Name in an ascending manner.


Since we are fecthing only 20 at a time because decrypting info is computationally heavy and the user would have to wait a long time if we decrypt every record, how may we have a way of knowing the proper order with all the names and then present the list of names with the right order.

One idea would be to decrypt every name and then do the ordering but this solution is not feasible in a timely manner so it can't be considered.

Another would be to have an extra column serving as index but every time that we would add a new record we would have to decrypt everything to find out the right order - also not to be considered.

What would you suggest to solve this problem?

Best regards,
Rodrigo Marques

Hi Rodrigo,

Maybe the second option is doable, if you trigger a timer to actually do the recalculation of the sort order column, so as to not make your user wait for it.  It wouldn´ t have to decrypt everything, it could decrypt one record at a time and zoom in on the right spot in the ordering, by comparing new value to decrypted value.  You would have to do something like leaving gaps in your ordering so as to not have to update every record behind the inserted or changed one.  I´ m not even sure that you would need a timer if you are smart about finding the right spot and leaving enough gaps, but that depends on how many rows will be in your table. 

Your UI could do 2 fetches, a list of the new/changed records that are not in the right spot of the ordering, and also a paginated list, and do the fitting in of first in second.  As soon as timer has done it´ s work, first list will be empty, and pagination will work on it´ s own.

Dorine


The easier option would be to verify with your business user if they really need this column to be encrypted and if they really need to be able to sort on it.  A no on either of these questions will save you a lot of headache.   

mvp_badge
MVP

Hi Rodrigo,

You could try the solution with the extra indexed entity attribute and on create or update of your record use a SOUNDEX function on the unencrypted data. 

The SOUNDEX function generates a number value of your text and are normally used in phonetic matching.

This solution avoids the need to scan and update your entity records when adding/updating records.

There is already Forge component available that implements a SOUNDEX function, that you can use or be inspired from to create your own SOUNDEX function.

https://www.outsystems.com/forge/component-overview/4266/soundex 

Regards,

Daniel

Possibly, fetch 2 lists: 1 'full list' (if feasable) and 1 'first 20 records' (F20) list, assuming asynchronous call. While the F20 is used for display, the first list ("source list"), can be decrypted and sorted (by the same criteria as F20 list) after fetching. When the user expands the F20 list, you can listAppend the next 20 elements to the F20 list from the source list.

This should mitigate time.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.