Encrypting Decrypting Data via Entity Framework using separate class exposing encrypted/decrypted values: Linq statements fails

Question

In addition to my earlier question on entity framework. My purpose is to encrypt the field before saving to DB and Decrypt the fields before reading from DB.I have class (User.cs) generated from table User(having UserName property(to be encrypted) and I created a separate class SecureUser with UserName property that isencrypted/decrypted as suggested earlier. But I am not sure how to map this new SecureUser class to DB and not the previous POCO class User. My Linq queries fail when I replace UserName with UserName from SecureUser class. I tried doing the same thing with partial classes , same thing happens. Any suggestions will be greatly appreciated !

[Table("Users")]
public class User
{

    #region database table column mapped fields
    [Key]
    [Required]
    public Int32 UserID { set; get; }

    [Required]
    [MaxLength(50)]
    public String UserName { set; get; }

    [Required]
    public Int32 CustID { set; get; }

//created the separate class as
public class SecureUser // UserViewModel
{
    // 
    private readonly User _user;

   public SecureUser(User user)
    {
        _user = user;
    }

    public string UserName
    {
        get { return Decrypt(_user.UserName); }
        set { _user.UserName = Encrypt(value); }
    }

}

Answer 1

I am a big fan of any solution that depends on Attributes.

Imagine you have an Entity class with one or more Properties you want to store Encrypted in the DB.Just add [Encrypted] attribute.

[Encrypted] 
public string EncryptedProperty { get; set; }

Now create a custom DbContext that does Encryption/Decryption on the fly for you:

public class MyDB : IdentityDbContext<User>
{  
    //DBSet properties go here

    public MyDB()
    {
        ((IObjectContextAdapter)this).ObjectContext.ObjectMaterialized += new ObjectMaterializedEventHandler(ObjectMaterialized);
    }

    #region Encryption

    public override int SaveChanges()
    {
        var contextAdapter = ((IObjectContextAdapter)this);

        contextAdapter.ObjectContext.DetectChanges(); //force this. Sometimes entity state needs a handle jiggle

        var pendingEntities = contextAdapter.ObjectContext.ObjectStateManager
            .GetObjectStateEntries(EntityState.Added | EntityState.Modified)
            .Where(en => !en.IsRelationship).ToList();

        foreach (var entry in pendingEntities) //Encrypt all pending changes
            EncryptEntity(entry.Entity);

        int result = base.SaveChanges();

        foreach (var entry in pendingEntities) //Decrypt updated entities for continued use
            DecryptEntity(entry.Entity);

        return result;
    }

    public override async Task<int> SaveChangesAsync(System.Threading.CancellationToken cancellationToken)
    {
        var contextAdapter = ((IObjectContextAdapter)this);

        contextAdapter.ObjectContext.DetectChanges(); //force this. Sometimes entity state needs a handle jiggle

        var pendingEntities = contextAdapter.ObjectContext.ObjectStateManager
            .GetObjectStateEntries(EntityState.Added | EntityState.Modified)
            .Where(en => !en.IsRelationship).ToList();

        foreach (var entry in pendingEntities) //Encrypt all pending changes
            EncryptEntity(entry.Entity);

        var result = await base.SaveChangesAsync(cancellationToken);

        foreach (var entry in pendingEntities) //Decrypt updated entities for continued use
            DecryptEntity(entry.Entity);

        return result;
    }

    void ObjectMaterialized(object sender, ObjectMaterializedEventArgs e)
    {
        DecryptEntity(e.Entity);
    }

    private void EncryptEntity(object entity)
    {
        //Get all the properties that are encryptable and encrypt them
        var encryptedProperties = entity.GetType().GetProperties()
            .Where(p => p.GetCustomAttributes(typeof(Encrypted), true).Any(a => p.PropertyType == typeof(String)));
        foreach (var property in encryptedProperties)
        {
            string value = property.GetValue(entity) as string;
            if (!String.IsNullOrEmpty(value))
            {
                string encryptedValue = EncryptionService.Encrypt(value);
                property.SetValue(entity, encryptedValue);
            }
        }
    }

    private void DecryptEntity(object entity)
    {
        //Get all the properties that are encryptable and decyrpt them
        var encryptedProperties = entity.GetType().GetProperties()
            .Where(p => p.GetCustomAttributes(typeof(Encrypted), true).Any(a => p.PropertyType == typeof(String)));

        foreach (var property in encryptedProperties)
        {
            string encryptedValue = property.GetValue(entity) as string;
            if (!String.IsNullOrEmpty(encryptedValue))
            {
                string value = EncryptionService.Decrypt(encryptedValue);
                this.Entry(entity).Property(property.Name).OriginalValue = value;
                this.Entry(entity).Property(property.Name).IsModified = false;
            }
        }
    }

    #endregion Encryption
}

Then add a class

class Encrypted : Attribute
{

}

Source: https://gist.github.com/albertbori/e95860644e69c1572441

Answer 2

You can also use the free Crypteron CipherDb library that works transparently with encrypted data. There is also a feature to allow for searchable encryption if that's something you'll be using in your LINQ queries.

You can annotate the data model with [Secure] or name a property to something like Secure_SocialSecurityNumber (the Secure_ is the key part) and CipherDb automatically performs data encryption, tamper protection, secure key storage, secure key distribution, caching, key roll overs, ACLs and more. Alternatively, with the CipherObject adaptor you can just use myObject.Seal() to encrypt the object.

You can find the sample apps on GitHub at https://github.com/crypteron/crypteron-sample-apps. You can also use Crypteron to protect streams, files, objects, message queues, noSQL etc.

Disclaimer: I work there and we do have a free community edition which anyone can use.

Answer 3

On adding the user object to the dbcontext, you should set UserName=Encrypt(model.UserName) where model is the object returned by the user for adding it

// before adding the model to the dbcontext
model.UserName=Encrypt(mode.UserName);
db.Users.Add(model);
db.SaveChanges();

[Table("Users")]
public class User
{
    #region database table column mapped fields
    [Key]
    [Required]
    public Int32 UserID { set; get; }

    [Required]
    [MaxLength(50)]
    public String UserName { set; get; } // this field will be encrypted field

    [Required]
    public Int32 CustID { set; get; }

    [NotMapped]
    public string DecryptedUserName
    {
        get { return Decrypt(UserName); }  // this you can display it to the user
    }
}

to use the NotMapped, you should add this line in the using statements

using System.ComponentModel.DataAnnotations.Schema;

hope this will help you

Answer 4

You can't use SecureUser's UserName property in a LINQ to Entities query because it's calling the Decrypt method, which LINQ has no idea on how to translate that into SQL. You should probably just create a [NotMapped] property on your regular User object, and that should do it. Like so:

[Table("Users")]
public class User
{
  #region database table column mapped fields
  [Key]
  [Required]
  public Int32 UserID { set; get; }

  [Required]
  [MaxLength(50)]
  public String UserName { set; get; }

  [Required]
  public Int32 CustID { set; get; }

  [NotMapped]
  public string DecryptedUserName
  {
    get { return Decrypt(this.UserName); }
    set { this.UserName = Encrypt(value); }
  }
}

If that doesn't work, you'll have to do the decryption after the objects have come back from the database inside of a LINQ to Objects query. Something like this:

var users = db.Users.Where(u => u.CustID == someID); //LINQ to Entities
var decryptedUserNames = users.ToList().Select(u => Decrypt(u.UserName)); //LINQ to Objects