CODEWITHSUNDEEP
entity-frameworkentity-framework-6
onefootswill
onefootswill

Reputation: 4087

EF Code First Model with Properties that Hold Encrypted Data

I've got some encrypted code in a table in the database which I am maintaining. This is circa 2012, so no "Always On" encryption. 3 columns contain encrypted data.

If I reverse engineer an EF domain, the Model which is created for that table contains properties for those columns which have a type byte[]. This is to be expected, as the columns are varbinary. So, it looks like this: 

class Person 
{
  public byte[] FirstName { get; set; } // FirstName
}

Is there an elegant way to do some kind of EF mapping/configuration such that the FirstName class has a type of string and that it decrypts automagically by the framework? I realize I can just instantiate a Person object using sql, but it would be nice to offload this processing to the framework.

I've seen one of two solutions around where people are basically using a sql query for every property. They decorate the property with an Encrypt attribute and iterate the properties of every property. But with a sql query for every property for every object in a list - that does not exactly scale.

Has anyone "solved" this issue before?

Note: to retrieve the data, you first need to send a sql statement akin to: 

OPEN SYMMETRIC KEY SomeKey DECRYPTION BY CERTIFICATE SomeCertificate

Thanks

Upvotes: 0

Views: 680

Answers (1)

onefootswill
onefootswill

Reputation: 4087

In this answer I'm going to set out the things you need to do to deal with encrypted columns in EF. So, the columns in question will have a type of VARBINARY(MAX). Lets say you table looks something like this:

CREATE TABLE dbo.Person
(
    SomeId int NOT NULL,
    CreatedByUserId uniqueidentifier NULL,
    CreatedUtcDate datetimeoffset(7) NULL,
    Rowversion timestamp NULL,
    FirstName varbinary(MAX) NULL,
    LastName varbinary(MAX) NULL
)

Step 1 - Create a View which returns the decrypted columns. The view should basically be identical to your table, but for the columns which hold encrypted data, it should return the decrypted data. It would looks something like this:

CREATE VIEW [dbo].[v_Person]
AS
SELECT [SomeId]
      ,[CreatedByUserId]
      ,[CreatedUtcDate]
      ,[RowVersion]
      ,CONVERT(NVARCHAR(50),DECRYPTBYKEY([FirstName])) [FirstName]
      ,CONVERT(NVARCHAR(50),DECRYPTBYKEY([LastName])) [LastName]
FROM [dbo].[Person]

Step 2 - Create your domain model Person class with string as the relevant property type, not byte[] (note the select statement in the View above where we have cast the decrypted columns to NVARCHAR).

public class Person 
{
    public int SomeId { get; set; }
    public string FirstName { get; set; } // string, not binary
    public string LastName { get; set; } // string, not binary
    public Guid CreatedByUserId { get; set; } 
    public DateTime CreatedUtcDate { get; set; }      
    public int SomeForeignKeyId { get; set; }
}

Step 3 - We need to set up a mapping for that Domain class. (The solution I am setting out here is for EF6. I am aware that EF Core does not support separate mapping files yet, so this would need to be done in the OnModelCreating event of you DbContext). Create a mapping class for you domain object which looks like this: 

public class PersonMap : EntityTypeConfiguration<Person>
{
    public PersonConfiguration(string schema)
    {
        ToTable("v_Person", schema); // note we map to the View
        HasKey(x => x.SomeId);  

        // ... other properties elided for brevity

        Property(x => x.FirstName)
            .HasColumnName(@"FirstName")
            .HasColumnType("nvarchar")
            .IsOptional()
            .HasMaxLength(50);
        Property(x => x.LastName)
            .HasColumnName(@"LastName")
            .HasColumnType("nvarchar")
            .IsOptional()
            .HasMaxLength(50);

        // Foreign keys
        HasRequired(a => a.LogbookEntry)
            .WithOptional(b => b.Person)
            .WillCascadeOnDelete(false);

        MapToStoredProcedures(p =>
            p.Insert(i => i.HasName("Insert_Person"))
                .Update(u => u.HasName("Update_Person"))
                .Delete(d => d.HasName("Delete_Person")));

    }
}

Note how we mapped to the view, v_Person, and not the raw table.
Also note the call to MapToStoredProcedures, which I explain next.

Step 4 - The last step is to create some stored procedures for your Insert, Update and Deletes. When you invoke SaveChanges, these will be invoked by EF and the relevant stored proc will be invoked depending on which EntityState the entity has. I won't set out all 3, but an example of the Update stored proc might look something like: 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Update_Person]
    @SomeId INT,
    @CreatedByUserId UNIQUEIDENTIFIER,
    @CreatedUtcDate DATETIME,
    @RowVersion_Original timestamp,
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50) = NULL   
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @CertKey NVARCHAR(7) = 'CertKey';

    UPDATE PersonDetail
        SET             
            FirstName = ENCRYPTBYKEY(KEY_GUID(@CertKey), @FirstName),
            LastName = ENCRYPTBYKEY(KEY_GUID(@CertKey), @LastName)          
    WHERE SomeId = @SomeId

    SELECT SomeId, RowVersion
    FROM PersonDetail
    WHERE SomeId = @SomeId
END

Feel free to comment if you have done it a better way.
Cheers

Upvotes: 2

Related Questions