Encrypted columns with Entity Framework

Question

Anyone figured out a good way to pull encrypted values from db through entity framework 4?

I got a MySql db with some columns encrypted with des_encrypt and need to be able to get those values as easy as possible, and also of course, update and insert them.

I think it's quite strange there doesn't seem to be in built support for this in EF. Even our own built ORM-system have support for this. We just add a comment "encrypted" for each field thats encrypted and the ORM tool will add des_decrypt(column) and des_encrypt(column) in the queries.

Anyone?

Answer 1

This is an implementation example of the answer proposed by @TheCloudlessSky. I thought it will help out anyone who was wondering how to go about implementing it.

I was working with an existing database, so the basic model class was automatically generated for me.

Auto-generated User.cs:

namespace MyApp.Model 
{
    public partial class User
    {
        public int UserId { get; set; }
        public byte[] SSN { get; set; }
        ...
    }
}

I created my own User.cs. (Note it is in the same namespace as the auto generated User.cs and there were no compiler errors because the auto generated User.cs was declared as partial class! Also, my own User.cs cannot be in the same folder as auto-generated User.cs because of file name conflict!)

namespace MyApp.Model 
{
    public partial class User
    {
        public string DecryptedSSN { get; set; }
        ...
    }
}

Now whenever I were to retrieve User from my DbContext, I will see all properties defined in the auto-generated class as well as the ones defined in my enhanced class.

Here is an implementation of my UserRepository.cs:

namespace MyApp.Model
{
    public interface IUserRepository 
    {
        User Get(int userId);
        ...
    }

    public class UserRepository : IUserRepository
    {
        public User GetById(int userId)
        {
            using (var dataContext = MyDbContext())
            {
                var user = dataContext.Users.Find(u => u.UserId == userId);
                var decryptedSSNResult = dataContext.Decrypt(u.SSN);
                user.DecryptedSSN = decryptedSSNResult.FirstOrDefault();
                return user;
            }
        }
    }
}

Now you may be wondering how/where did I get MyDbContext.Decrypt() from?

This is NOT auto generated for you. However, you can import this stored procedure into your auto-generated Model.Context.cs file. (This process is very well documented in the official EntityFramework article: How to: Import a Stored Procedure (Entity Data Model Tools) at http://msdn.microsoft.com/en-us/library/vstudio/bb896231(v=vs.100).aspx)

Just in case you don't know what end result should look like, here is what was automatically generated in my Model.Context.cs:

namespace MyApp.Model
{
    // using statements found here

    public partial class MyDbContext : DbContext
    {
        public MyDbContext()
            : base("name = MyDbContext")
        { }

        public virtual ObjectResult<string> Decrypt(byte[] encryptedData)
        {
            var encryptedDataParameter = encryptedData != null ? 
                            new ObjectParameter("encryptedData", encryptedData) :
                            new ObjectParameter("encryptedData", typeof(byte[]));

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<string>("Decrypt", encryptedDataParameter);
        }

        // similar function for Encrypt 
    }
}

This is how my Decrypt Stored Procedure looks like:

CREATE PROCEDURE decrypt
    @encryptedData VARBINARY(8000)
AS
BEGIN
    OPEN SYMMETRIC KEY xxx_Key DECRYPTION BY CERTIFICATE xxx_Cert;

    SELECT CAST(DECRYPTIONBYKEY(@encryptedData) AS NVARCHAR(MAX)) AS data;

    CLOSE ALL SYMMETRIC KEYS;
END;
GO

---

Performance Considerations

Now that I have shown you an implementation of answer given by @TheCloudlessSky, I will like to quickly highlight some performance related points.

1) Each time your retrieve a user object, there are 2 trips being made to the database instead of 1. First trip for retrieving object; second trip for decrypting SSN. This can cause performance issues if you are not careful.

Recommendation: Do NOT auto decrypt encrypted fields! In my example shown above, I decrypted SSN when I was retrieving user object. I did that was for demonstration purposes only! Ask yourself if you really need SSN every single time User is retrieved. If possible, choose lazy decryption over eager decryption!

2) While I have not demonstrated this, every single time you create/update a user object, there will also be 2 trips being made to the database. First trip for encrypting SSN; second trip for inserting object. Again this can cause performance issues if you are not careful.

Recommendation: Be conscious about this performance hit but don't delegate encrypting and saving of SSN as a different method. Keep it all within one operation otherwise you may forget to save it altogether. So the recommendation for creating/updating is opposite of retrieving: choose eager encryption over lazy encryption!

Answer 2

IMO you should encrypt before putting it into the database and store it as binary data. Then you can easily get the byte[] with EF.

EDIT: What if you used a stored procedure to do all the des_encrypt and des_decrypt as well as the selects/inserts/deletes for you. Then EF will still do the mapping for you?

Answer 3

You can use AES Encryption (2 way encryption). When you need to query the db you can send the encrypted string that can represent the target value.

You can create an Extension to Decrypt the Entity.

MyTableEntitiesSet.Where(c=>c.MyField == MySeekValue.Encrypt()).First().Decrypt();

This can do a database query.

Be aware of data size, encrypted data is larger...