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?

Solution 1

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?

Solution 2

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!

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

Solution 4

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

Solution 5

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 :)

View more solutions

Comments

[Andreas]

Seems like a good solution and I would probably go with that if I created a new database. The thing is, the database is pretty large and is used by tons of projects so it would be a huge job to go over the code and change this.

[Andreas]

Thanks for the suggestion. This looked like a really good idea and I tried it out. unfortunately if I want to be able to do LINQ queries on all of my decrypted data from the table, the stored procedure must first execute. This takes forever since it's 250 000+ rows with 5 columns each to be decrypted. So doing this: context.AllMembers().Where(x=>x.MemberId == 1) will take too long. Sure i could do a SP that takes an argument of memberid, but what if i want to search on e.g. firstname with LINQ? Maybe im missing something important here...

[TheCloudlessSky]

@Andreas - From what I understand ... MemberId is encrypted? Shouldn't you only need to decrypt columns that are encrypted (like a password... even though you should use a one-way hash)? If MemberId is not encrypted, you don't need to decrypt it to do a 'SELECT'. Is anyone else using this table? Why don't you decrypt it all and create a new table for the non-encrypted data?

[Andreas]

No, everything BUT the memberid is encrypted (it's the primary key). We decrypt things like social security number, names and addresses because it's sensitive data for us. The password is hashed, so no problem there. This table is used alot, but we really don't want another version of it which is decrypted since that would work against thea very idea of having the things encrypted. Sorry for my late answer, vacation... :)

[Joe Enos]

Old question and answer, but just be aware that if you try to do this, it will only work if you use a fixed Init Vector, which is not recommended, since it would potentially allow an attacker to learn about the data. A random IV with each encryption should be used, which would mean you would get a different value every time you encrypt something.

[mac10688]

I give you a vote for effort. But I disagree with your approach a bit. I don't want my database to be decrypting values. I can store all that information in my application and not have to make two trips to the database.

[Parth Shah]

@mac10688 I agree with you on the performance hit associated with multiple trips to database. In general, writing dedicated stored procedures for CUD operations and linking them to the model via entity framework is more efficient than having stored procedures like encrypt/decrypt. But I wanted to demonstrate a method of achieving encryption/decryption using keys managed by the database instead of the application.

[Parth Shah]

I am providing a link to how to connect CUD stored procedures to CUD operations by the entity framework in case someone is curious on how to achieve this: msdn.microsoft.com/en-us/data/jj593489. I know links are generally frowned upon but since this one is to the MSDN Entity Framework Documentation site, I hope people won't mind it.

[Dov Miller]

Excelent informative helpful detailed and useful including the links in the answer and comment! Thank you very much!

[Dov Miller]

You wrote that this is for a single column/row but not for an entire table/class. How should I go about this if I have encript/decript all or most columns in a table?

[Sam]

I like this solution as the key is actually stored inside the database. However, is there any benefit of doing this? I mean, is it really better to store the key inside the database instead of inside the application? I can only see a disadvantage (eg. make a round trip to the database). If there is a benefit, I will change my application to use this method as I really like the idea that the key is inside the database.

[Leandro Bardelli]

but is only free for 3 months.

[Leandro Bardelli]

The data can be sniffed in the middleway to the store procedure. This is not a secure way.