Encrypted columns with Entity Framework

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?

---

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!

---

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?

---

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...

---

You could go the DIY/roll-your own encryption security but every security expert will tell you to never, ever, do that. The hardest part of data security and encryption is actually not "AES" or some algorithm. It's key management. Sooner or later, you will face this beast and it's way harder.

Fortunately, there's a tool called Crypteron CipherDb that takes care of that. In fact it goes beyond entity framework encryption, also providing automatic tamper protection, secure key storage, secure key distribution, key roll-overs, key caching, access control lists and more. There is a free community edition and it only takes a few minutes to add to your app.

When integrating with Entity Framework, you just annotate the data model with [Secure] or name a property to something like Secure_SocialSecurityNumber (the Secure_ is the key part) and CipherDb takes care of the rest.

For example, your data model would be:

public class Patient
{
    public int Id {get; set;}

    [Secure]
    public string FullName {get; set;}

    [Secure]
    public string SocialSecurityNumber {get; set;}
}

And your web.config would be

<configuration>
  <configSections>
    <section 
        name="crypteronConfig" 
        type="Crypteron.CrypteronConfig, CipherCore, Version=2017, Culture=neutral, PublicKeyToken=e0287981ec67bb47" 
        requirePermission="false" />
  </configSections>

  <crypteronConfig>
    <myCrypteronAccount appSecret="Get_this_from_http://my.crypteron.com" />
  </crypteronConfig>
</configuration>

It's recommended to secure your web.config OR plug the Crypteron API key (AppSecret) programatically (documentation)

You can find the sample apps on GitHub at https://github.com/crypteron/crypteron-sample-apps. .

By the way, the free edition benefits from the commercial offerings, so in addition to the above, you can also secure streams, files, objects, message queues, NoSQL databases etc, all from one place.

Disclaimer: I work there and we do have a free community edition which anyone can use (and we don't make any money off). If you think it's cool, tell us, tell your friends. If you have a budget, get a commercial license. It helps us offer the free edition to everyone :)

---

For those of you Googling this question and looking for a simple way to decrypt a single column/row (not an entire table/class), using symmetric encryption and EF, you can do this one of two (simple) ways.

First way; create a stored procedure that does your decryption:

CREATE PROCEDURE [dbo].[Decrypt_Credential]
@User_Name varchar(50) = NULL
AS
BEGIN
OPEN SYMMETRIC KEY My_Key_01 DECRYPTION BY CERTIFICATE MyCertName;

SELECT CONVERT(varchar, DecryptByKey(Encrypted_Password)) FROM Application_Credentials WHERE User_Name = @User_Name;

CLOSE SYMMETRIC KEY My_Key_01; 
END;

... then call that stored procedure directly in code, retrieving the result as a string:

using (var context = new YourDatabaseContext())
        {
            var result = context.Database.SqlQuery<string>("Decrypt_Credential @user", new SqlParameter("user", TheUserName)).FirstOrDefault();
        }

The second way you can do so is via a database transaction that essentially does the same thing as the stored procedure. Note I am fully aware this example is not sql injection protection compliant, I was having some issues with parameterized queries so this was the example I got working. You will want to use parameterized queries if you go this route;

 using (var context = new YourDatabaseContext())
        {       
            using (var dbContextTransaction = context.Database.BeginTransaction())
            {
                try
                {
                    var sql = String.Format("OPEN SYMMETRIC KEY {0} DECRYPTION BY CERTIFICATE {1}", KeyName, CertName);
                    context.Database.ExecuteSqlCommand(sql);

                    sql = String.Format("SELECT CONVERT(varchar, DecryptByKey(Encrypted_Password)) FROM Application_Credentials WHERE User_Name = '{0}'", UserNameToDecryptCredsFor);
                    var result = context.Database.SqlQuery<string>(sql).FirstOrDefault();

                    sql = String.Format("CLOSE SYMMETRIC KEY {0}", KeyName);
                    context.Database.ExecuteSqlCommand(sql);
                }
                catch (Exception exp)
                {
                    var x = exp.ToString(); //do something with exception
                }
            }
        }