Querying and ordering results of a database in grails using transient fields

Question

I'm trying to display paged data out of a grails domain object. For example: I have a domain object Employee with the properties firstName and lastName which are transient, and when invoking their setter/getter methods they encrypt/decrypt the data. The data is saved in the database in encrypted binary format, thus not sortable by those fields. And yet again, not sortable by transient ones either, as noted in: http://www.grails.org/GSP+Tag+-+sortableColumn .

So now I'm trying to find a way to use the transients in a way similar to:

Employee.withCriteria( max: 10, offset: 30 ){
    order 'lastName', 'asc'
    order 'firstName', 'asc'
} 

The class is:

class Employee {

byte[] encryptedFirstName
byte[] encryptedLastName

static transients = [
    'firstName',
    'lastName'
]


String getFirstName(){
    decrypt("encryptedFirstName")
}

void setFirstName(String item){
    encrypt("encryptedFirstName",item)      
}

String getLastName(){
    decrypt("encryptedLastName")
}

void setLastName(String item){
    encrypt("encryptedLastName",item)       
}

}

Answer 1

That can't work due to the way GORM/hibernate criteria are executed. Those order directives are translated into SQL and can operate only on the non-transient fields, since it's happening at the database tier.

Your choices are:

1. Load the results of the query into memory and do sorting and pagination yourself with the unencrypted values.
2. Use the encryption capabilities of your database and a custom query (e.g. "select * from employee order by AES_DECRYPT(lastName, key)"). Beware, this will put a lot of extra load on your database.
3. Store something in unencrypted form that can be used for sorting. Example: the first few letters of the lastName. However, this leaks some of the information you're trying to keep secure.