Hi,

Using LLBLGen 4.2.

We have written an LLBLGen type converter that does application-side encrypting/decrypting of data (of an "employee" table) before it's set/get to/from the database (our application can run either on Oracle or SQL Server as the database).

We have the data get encrypted in several columns of the "employee" table. For example, the data encrypted is first-name, last-name, social-security-number, etc.

We have a web page that needs to page through the list of all employees, sorted by last-name.

We are using the LLBLGen call that fetches data by database "page".

The problem is that, since the database "last-name" field is encrypted, we cannot use the database sorting on that field.

The database can have hundreds of thousands of employee records.

There are frequent inserts to the employee table, so insert performance is important. So the idea of updating all records with an "order" column value...when one employee is inserted, seems like a significant performance problem.

Thoughts on a good approach?

Thanks!

note: We have to do application-side encrption, instead of database managed encryption, because our customers want to eliminate the "easy access" people the database-admin rights have to automatically view all database data in unencrypted format.

If you can't order on the actual value, the only option is to order on something that mimics the value OR use an 'order' field indeed. The first option is risky, as you don't want a way to reverse engineer the original value back from the value used for ordering. This might help though: http://www.ece.umd.edu/~minwu/public_paper/Conf09/0901secureMMsearch_spie09.pdf (from discussion here: http://crypto.stackexchange.com/questions/2147/order-preserving-encryption-for-numeric-data-values)

The second option, an ordering, might help (just sort on the order) but as the paper linked above suggests, that might reveal information too (albeit small). This paper might help as well: http://rsrikant.com/papers/sigmod04.pdf (it's about numeric values, but it might lead to insights about what you need for alphanumeric values).

Thanks for the ideas. Working through the options...