Entity Framework has encrypted columns.

c# encryption entity entity-framework mysql

Question

Has anybody found a reliable method using entity framework 4 to get encrypted values from a database?

I need to be able to obtain the values from a MySql database that has certain columns that have been des encrypted as easily as feasible, as well as update and insert them naturally.

I find it odd that EF doesn't seem to have built-in support for this. Even the ORM system we designed has support for this. For each encrypted field, we just add the comment "encrypted," and the ORM tool will automatically add des decrypt(column) and des encrypt(column) to the queries.

Anyone?

1
9
7/21/2010 12:16:35 PM

Accepted Answer

I believe that before should be encrypted before being stored in a database and should be stored as binary data. then you may obtain the easybyte[] using EF

EDIT: What if you carried out everything using a stored procedure?des_encrypt and des_decrypt Moreover, theselects/inserts/deletes with you. If so, will EF still carry out the mapping for you?

2
7/22/2010 12:02:46 PM

Popular Answer

This is an illustration of how @TheCloudlessSky's solution was put into practice. I believed it would be useful for anybody who was unsure how to go about putting it into practice.

Because I was using an existing database, the fundamental model class was produced for me automatically.

User.cs produced automatically

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

I made a custom User.cs file. (Note that the auto-generated User.cs was defined as portion of class, so it is in the same namespace and there were no compiler issues. Additionally, due to a file name issue, my personal User.cs and the automatically created User.cs cannot be in the same folder.)

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

Now, if I were to obtain User from my DbContext, I would see both the attributes specified in my upgraded class and those defined in the automatically created class.

Here is a version of my UserRepository.cs implementation:

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

You may be asking how or where I got MyDbContext.Decrypt() at this point.

NOT automatically produced for you. However, you may import this saved method into the Model.Context.cs file that was created automatically. (How to: Import a Stored Procedure (Entity Data Model Tools) at http://msdn.microsoft.com/en-us/library/vstudio/bb896231(v=vs.100).aspx in the official EntityFramework article) provides excellent documentation for this procedure.)

In case you're unsure of how the final product should seem, this is what my Model automatically created. 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 
    }
}

My Decrypt Stored Procedure appears as follows:

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

Factors Considering Performance

After implementing the suggestion made by @TheCloudlessSky, I'd like to briefly highlight a few performance-related concerns.

1) Rather than making only one trip to the database each time you obtain a user object, you now make two. First trip is for item retrieval; the second is for SSN decryption. If you don't take precautions, this might affect your performance.

Do NOT automatically decode encrypted fields! In the aforementioned example, I decrypted SSN when collecting the user object. I merely did it to serve as an example. Every every time a user is obtained, consider if you truly need their SSN. Choose sluggish decryption over eager decryption whenever feasible!

2) Even though I haven't shown it, every time you create or change a user object, there will also be two journeys to the database. First trip is for SSN encryption; the second is for item insertion. Again, if you're not cautious, this might affect your performance.

Recommendation: Be mindful of the performance impact, but don't use a different technique to encrypt and save SSN. Keep everything together in one operation to avoid forgetting to save it. The advice for creating/updating is thus the exact reverse of that for retrieving: choose eager encryption over lazy encryption!



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow