Grails: How to search a key word in encrypted column and get rows

Question

I am working on an grails application where it can save SMS or email in the database. These messages are saved in an encrypted format.

I am trying to get rows from the table by using keyword search criteria. As this is encrypted, I am not able to find a query which can search with a keyword.

I have tried adding the transients to domain class and decrypt it. Here is the code:

in domain class:

 static transients = ['decrypted']

 String getDecrypted() {
     return DESCodec.decryptText(message)
 }

and

 def m2 = Messages.findAll {-1 == it.getDecrypted().indexOf("xyz")};

which did not work.

Can someone tell me if there is a way to get rows by searching using a keyword in the encrypted column?

Any help really appreciated.

Answer 1

May I suggest you read this Using transient property in findBy or listOrderBy methods.

Basically a transient object becomes available when you have an object to hand. So writing hql/hibernate queries will not really work. You may have better luck doing something like:

def m2 = Messages.findAll {-1 == DESCodec.decryptText(it.message).contains("xyz")}; 

Unsure on above for sure, something you need to play around

Here is what you are trying to do

def listing = Messages.executeQuery("from Messages" ,[:],[max:-1])
def found = listing.findAll {-1 == it.decrypted.contains("xyz")};

or manually :

  def listing = Messages.executeQuery("select new map(id as id, message as message) from Messages" ,[:],[max:-1])
    def found = listing.findAll {-1 == DESCodec.decryptText(it.message).contains("xyz")};