Request for encrypted values ​​using LINQ

Question

I have a custom class that has a username that I need to store in an encrypted DB

public abstract class User { public virtual int Id { get; protected set; } public virtual string Username { get { return _encryptionProvider.Decrypt(SecuredUsername); } protected set { SecuredUsername = _encryptionProvider.Encrypt(value); } } [Obsolete("Use the 'Username' property -- this property is only to be used by NHibernate")] protected virtual string SecuredUsername { get; set; } } 

I mapped the User object as shown below:

 public class UserMapping : ClassMap<User> { public UserBaseMapping() { Id(user => user.Id).GeneratedBy.HiLo("100"); Map(Reveal.Member<UserBase>("SecuredUsername")).Unique(); } } 

It worked fine until I had to write a LINQ statement.

 User user = _session.QueryOver<User>().Where(x => x.Username == "Hamza").SingleOrDefault(); 

The problem here is that when LINQ translates the statement above into SQL, it becomes something like this: Select * from [dbo]. [User] Where is the username, for example "Hamza"

And as you can see, the table does not have a column named username, but protectedusername and contains an encrypted value. Someone can help me solve this problem, I need to be able to query LINQ.

Answer 1

You can use your own type to encrypt your username (instead of doing encryption in the User class, doing encryption in a custom way) see http://nhforge.org/blogs/nhibernate/archive/2009/02/22/encrypting -password-or-other-strings-in-nhibernate.aspx

When you request a request, you can only request exact matches, but you can make a request, for example:

 User user = _session.QueryOver<User>() .Where(x => x.Username == "Hamza") .SingleOrDefault(); 

If you want to match case-to-case, you can convert the value to upper / lower case in a custom type.

If you want to make LIKE queries, you will need to look at some other type of indexing - for example. Lucene.NET and NHibernate.Search

Answer 2

How about this:

 User user = _session.QueryOver<User>().Where(x => x.securedusername == _encryptionProvider("Hamza")).SingleOrDefault(); 

Edited: Your request has problems that I mentioned in the comment above. One of the options: (This can be done only if the usernames are unique, otherwise there may be a conflict) 1. You need to come up with an index that is unique for each string value. for EX:

 SAM = xyz(110111101) SAMI = xyzk(110111101001) 

this index must be inserted for each client in the database.

then you can go like this:

 `User user = _session.QueryOver<User>().Where(x => x.Index.Contains(IndexGenerator("Hamza"))).SingleOrDefault();//I do not know weather this Contains method exist in nhibernate`. But there should be. so find that :) 

But this request will give some unnecessary values , but it can be further filtered using the actual username after decryption in encoding. It can get exact search results.

You can use this IndexGenerator as your _encryptionProvider if it is cryptic.

Answer 3

I have never used NHibernate, but the problem is that the Username definition is in the model and not in the database.

You basically have two options (remember that I have never used NHibernate, but other entities / ORMs) and maybe the third one depending on the implementation of IQueryable in NHibernate.

Load the entire table into memory (if it is small, and you often execute this query, which may be useful because I assume NHibernate has some smart caching?):

 User user = _session.QueryOver<User>().ToList().FirstOrDefault(x => x.Username == "Hamza"); 

The second checks the encrypted string provided by @Diode:

 // Resolve string, since we are using LINQ2SQSL in some form var encName = _encryptionProvider.Encrypt("Hamza"); User user = _session.QueryOver<User>().FirstOrDefault(x => x.SecuredUsername == encName); 

If you can use a specific instance of IQueryable for packaging purposes, this will be one solution. See http://msdn.microsoft.com/en-us/library/bb351562.aspx for a description of IQueryable.

But basically, encryption should occur on the server, that is, not in the database, and this limits your options.

EDIT: Searching for all users matching "Hamz *" we need to load into memory and check there:

 var users = _session.QueryOver<User>().ToList().Where(x => x.Username.StartsWith("Hamz"));