Query on encrypted values using LINQ

Question

I have a user class that has username which I need to save in the DB encrypted

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 entity as 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 some LINQ statement.

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

The problem here is that when LINQ translates above statement to SQL it becomes something like this: Select * from [dbo].[User] Where Username like 'Hamza'

And as you might noticed there is no column called username in the table but securedusername and it contains the encrypted value Can anyone please help me solving this issue, I need to be able to query using LINQ.

First of all your code does not support for this search requirement. your code can only search the user if the whole user name is given. You have to look for something like [this] but this is not nhibernate. but this has to be something like this. But this is not nhibernate(http://stackoverflow.com/questions/835790/how-to-do-sql-like-in-linq) — diyoda_, Oct 22 '12 at 07:29

score 1 · Answer 1 · answered Oct 22 '12 at 15:11

1

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

When you query, you will only be able to query for exact matches, but you will be able to do a query like:

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

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

If you want to do LIKE searches, then you will need to look at some other type of indexing - eg. Lucene.NET and NHibernate.Search

answered Oct 22 '12 at 15:11

Martin Ernst

5,629
2
17
14

I did, but that same problem, my problem is how to get the encrypted values using LINQ not the mechanism of encrypting/decrypting the data using nhibernate – Hamza Oct 23 '12 at 13:08
Why do you need the encrypted values? Using Nhibernate the encrypted persistence of data becomes transparent to your usage of it (within reason), so you shouldn't need to access the encrypted data – Martin Ernst Oct 24 '12 at 14:18

diyoda_ · Answer 2 · 2012-10-22T07:50:23.690

0

How about this one:

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

Edited: Your query has issues as i have mentioned in the comment above. One option is, (This can be done only if the usernames are unique, otherwise there can be clash) 1. you have to come up with a index which is unique to each string value. for EX:

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

this index has to be inserted for each customer in the database.

then you can go for like:

`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 query will give some unnecessary values as well, But that can be further filtered using the actual username after decryption in the coding. That can get the precise search results.

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

edited Oct 22 '12 at 07:50

answered Oct 22 '12 at 06:51

diyoda_

5,274
8
57
89

I thought of that but that won't work in case of starts with or ends with or even search part of the string Also I don't want the LINQ to worry about encrypting and decrypting values – Hamza Oct 22 '12 at 06:59
Why do you want to encrypt the username and save it in the database. Intutively and normall its the password that is encrpted. – diyoda_ Oct 22 '12 at 07:05
But I do not know about your requirement, Thats why there are 2 things 1. username 2. password. I you do not wish LINQ to worry about encrypting and decrypting values. Then how are you going to insert using LINQ to the this User table. Still I can work with you to see for an answer to this but "starts with or ends with or even search part of the string" can you explain this quote in your comment. – diyoda_ Oct 22 '12 at 07:13
Thanks for your responses I have to encrypt username and first name and last name, the client insists on this – Hamza Oct 22 '12 at 07:17
I mean if I want to search for a part of the string let's say get all users that their username starts with "ham" now if I use your way then for sure _encryptionProvider("Ham") doesn't have any relation with _encryptionProvider("Hamza") so can't search for a part of the string – Hamza Oct 22 '12 at 07:19

flindeberg · Answer 3 · 2012-10-22T07:56:51.630

0

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

Basicly you have two choices (bear in mind that I've never actually used NHibernate but other entity frameworks/ORMs), and possibly a third depending on NHibernate's implementation of IQueryable.

Load the entire table into memory (if it is small and you do this query often that might be useful, because I guess NHibernate has some smart caching?):

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

The second is checking against the encrypted string, as 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 wrapping purposes, that would be one solution. Have a look at http://msdn.microsoft.com/en-us/library/bb351562.aspx for a description of IQueryable.

But basicly, the encryption has to happen on the server, ie not in the database, and that kinda limits your options.

EDIT: Search for all users matching "Hamz*", we have to load to memory and check there:

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

edited Oct 22 '12 at 07:56

answered Oct 22 '12 at 07:20

flindeberg

4,887
1
24
37

Thanks for your post, but the problem with Diode's solution is that if I want to search for a part of the string let's say get all users that their username starts with "ham" now if I use this way then for sure _encryptionProvider("Ham") doesn't have any relation with _encryptionProvider("Hamza") so can't search for a part of the string – Hamza Oct 22 '12 at 07:50
@Hamza I added an edit, if that is the functionality you want you have to load the tabel to memory and decrypt there. IE no quicky with going only to database with sql. – flindeberg Oct 22 '12 at 07:58
That would work if I have only one table with encrypted value, but my client needs me to encrypt data in about 5 tables and loading the table to memory and decrypt it fr each request will be very expensive regarding the performance – Hamza Oct 23 '12 at 13:07
Then there is no way around it since your decryption is application-layer and not persistance-layer specific. I'd suggest redesigning the application then, since there is no way you are going to get the performance you seem to require with the current design :) – flindeberg Oct 23 '12 at 13:16

Query on encrypted values using LINQ

3 Answers3