[EF Core] Use DB function to encrypt and decrypt data

 .NET Core   Entity Framework Core   PostgreSQL  

▌Introduction

---

In previous article, [PostgreSQL] pgcrypto - encryption and decryption, we created some encryption/decryption functions on PostgreSQL. We will integrate these DB functions to Entity Framework Core, so that we can use them to encrypt/decrypt data on runtime.

The concept can be applied to other RMDB, such as Microsoft SQL Server, Oracle, etc.

▌Environment

---

▋.NET Core 3.1

▋Entity Framework Core 3.1.0

▋PostgreSQL 11.6

▋Npgsql.EntityFrameworkCore.PostgreSQL 3.1.0

▌Implement

---

The sample code is on my Github: KarateJB\EF.Core.Sample.

▋DB scalar functions

Reference from [PostgreSQL] pgcrypto - encryption and decryption, I will create a EF migration for creating the DB functions to database. Notice that the migration won’t include creating asymmetric public/private keys, so make sure that the key pairs are ready before doing the DB migration.

First lets put the create-functions SQL into some SQL files,

PS. The SQLs can be found here. Make sure to set “Copy to Output Directory”.

Then we can create the MigrationBuilder extension which will load the SQLs and execute them.

▋MigrationBuilderExtensions.cs

public static partial class MigrationBuilderExtensions

    {

        private const string RootDirName = "Sqls";

        private static string datetimeDirName = string.Empty;

        /// <summary>

        /// Read SQL from file

        /// </summary>

        /// <param name="fileName">File name</param>

        /// <returns>SQL</returns>

        private static string ReadMigrationSql(string path)

        {

            FileInfo file = new FileInfo(path);

            string script = file.OpenText().ReadToEnd();

            return script;

        }

        /// <summary>

        /// Create DB functions

        /// </summary>

        /// <param name="migrationBuilder">MigrationBuilder</param>

        public static void CreatePgCryptoFunctions_20200108053432(this MigrationBuilder migrationBuilder)

        {

            datetimeDirName = "20200108053432";

            var path = string.Empty;

            var script = string.Empty;

            // Create Extension

            path = Path.Combine(AppContext.BaseDirectory, RootDirName, datetimeDirName, "01.create_extension.sql");

            script = ReadMigrationSql(path);

            migrationBuilder.Sql(script);

            // Create Schema and store secret

            path = Path.Combine(AppContext.BaseDirectory, RootDirName, datetimeDirName, "02.store_secret.sql");

            script = ReadMigrationSql(path);

            migrationBuilder.Sql(script);

            // Create functions

            path = Path.Combine(AppContext.BaseDirectory, RootDirName, datetimeDirName, "03.create_functions.sql");

            script = ReadMigrationSql(path);

            migrationBuilder.Sql(script);

        }

    }

Now we will create an EMPTY EF Migration by the following command:

$ cd src/EFCore.Ap

$ dotnet ef  --project ../EFCore.Dal --startup-project . migrations add CreateFunctions.

Put the create-method from MigrationBuilder extension to create the DB functions when updating database.

▋20200108053432_CreateFunctions.cs (EF migration file)

public partial class CreateFunctions : Migration

    {

        protected override void Up(MigrationBuilder migrationBuilder)

        {

            #region Custom migrate sqls

            migrationBuilder.CreatePgCryptoFunctions_20200108053432();

            #endregion

        }

        protected override void Down(MigrationBuilder migrationBuilder)

        {

        }

    }

Last step, run the following command to generate the DB functions on the database.

$ cd src/EFCore.Ap

$ dotnet ef  --project ../EFCore.Dal --startup-project . database update

Result:

▋EF Core: Value Conversions

From EF Core 2.1, we can use Value Conversions to bind logic when reading data from table’s column or writing data to table’s column. In our case, that will be:

1.  Decryption on Reading data

2.  Encryption on Writing data

Create an entity model (data table) named “Users”, which contains the following columns,

Column	Type	Description
Id	uuid	GUID
Name	varchar(48)
Password	text	Must be hashed
Phone	bytea	Must be encrypted
CardNo	bytea	Must be encrypted

▋User.cs

 [Table("Users")]

 public class User

 {

        [Key]

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

        public Guid Id { get; set; }

        [Column(TypeName = "varchar(48)")]

        public string Name  { get; set; }

        public string Password { get; set; }

        [Column(TypeName = "bytea")]

        public string Phone { get; set; }

        [Column(TypeName = "bytea")]

        public string CardNo { get; set; }

 }

Now we will add Value Conversions: PropertyBuilder.HasConversion Method, on the columns that must be hashed or encrypted.

// Parameters:

//   convertToProviderExpression:

//     An expression to convert objects when writing data to the store.

//

//   convertFromProviderExpression:

//     An expression to convert objects when reading data from the store.

public virtual PropertyBuilder<TProperty> HasConversion<TProvider>(

  [NotNullAttribute] Expression<Func<TProperty, TProvider>> convertToProviderExpression, 

  [NotNullAttribute] Expression<Func<TProvider, TProperty>> convertFromProviderExpression);

       

Notice that the Value Conversions does not do the converting on SQL execution but BEFORE/AFTER the SQL execution!! In other words, the SQL will be as same as the original SQL before we adapt the Value Conversions methodology.

 

 

▋MyDbContext.cs

 public class MyDbContext : DbContext

 {

        private readonly DbContextOptions<MyDbContext> options = null;

        public MyDbContext(

            DbContextOptions<MyDbContext> options) : base(options)

        {

            this.options = options;

        }

        public DbSet<User> Users { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)

        {

            // Hash Password

            modelBuilder.Entity<User>().Property(p => p.Password).HasConversion(

                val => this.HashIt(val),

                val => val);

            // Encrypt/Decrypt Phone

            modelBuilder.Entity<User>().Property(p => p.Phone).HasConversion(

                val => this.EncryptMe(val),

                val => this.DecryptMe(val));

            // Encrypt/Decrypt CardNo

            modelBuilder.Entity<User>().Property(p => p.CardNo).HasConversion(

                val => this.EncryptMe(val),

                val => this.DecryptMe(val));

        }

 }

Lets get things done by implementing the HashIt, EncryptMe, DecryptMe methods.

I will take HashIt for example.

1. Inject the configured TOptions object for getting connection string later.

 public class Startup

 {

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.

        public void ConfigureServices(IServiceCollection services)

        {

            services.Configure<AppSettings>(this.Configuration);

            // … Skip

        }

 }

AppSettings is a class to store the connection string,

public class AppSettings

{

     public ConnectionStringOptions ConnectionStrings { get; set; }

}

public class ConnectionStringOptions

{

      public string DB { get; set; }

}

2. Implement the HashIt method on MyDbContext.cs: (Only show the updated code)

public class MyDbContext : DbContext

{

        private readonly DbContextOptions<MyDbContext> options = null;

        private readonly AppSettings appSettings = null;

        public MyDbContext(

            DbContextOptions<MyDbContext> options,

             IOptions<AppSettings> configuration) : base(options)

        {

            this.options = options;

            this.appSettings = configuration.Value;

        }

        private string HashIt(string text)

        {

            var connectionString = this.appSettings.ConnectionStrings.DB;

            var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();

            optionsBuilder.UseNpgsql(connectionString);

            using (var dbContext = new MyDbContext(optionsBuilder.Options))

            using (var command = dbContext.Database.GetDbConnection().CreateCommand())

            {

                command.CommandType = CommandType.StoredProcedure;

                command.CommandText = "my_hash";

                command.Parameters.Add(new Npgsql.NpgsqlParameter("t", NpgsqlTypes.NpgsqlDbType.Text)

                { Value = text });

                if (command.Connection.State == ConnectionState.Closed)

                {

                    command.Connection.Open();

                }

                var hashed = (string)command.ExecuteScalar();

                return hashed;

            }

        }

}

Important!! If we disable Database connection pooling, the above codes will take MUCH TIME on CREATING THE DB CONNECTION when the Query/Execution SQL includes lots of records!!  This will result in bad performance. In this case, we can create a DB connection pool(factory) to keep the connections without closing them immediately.

 

The codes for EncryptMe, DecryptMe methods are almost as same as HashIt, you only have to change the DB function name.

See the codes on Github.

Finally, lets add some seed data and do a new migrations.

public class MyDbContext : DbContext

{

        protected override void OnModelCreating(ModelBuilder modelBuilder)

        {

            // ...skip

            // Seed

            modelBuilder.Entity<User>()

              .HasData(new User

              {

                  Id = new Guid("ce32bd49-7a7c-4bd1-b2b9-7e960ad86306"),

                  Name = "JB",

                  Password = "123456!@#$%^",

                  Phone = "0912345678",

                  CardNo = "XXXX-YYYY-1234-5678"

              });

            modelBuilder.Entity<User>()

              .HasData(new User

              {

                  Id = new Guid("3e177c71-f84e-4c23-9bf4-b322b350c8b2"),

                  Name = "Amy",

                  Password = "123456!@#$%^",

                  Phone = "0933333333",

                  CardNo = "1234-5678-ZZZZ-WWWW"

              });

        }

}

Then update database with the new EF migration…

$ cd src/EFCore.Ap

$ dotnet ef  --project ../EFCore.Dal --startup-project . migrations add CreateUsers

$ dotnet ef  --project ../EFCore.Dal --startup-project . database update

Check the Users table and see if the seed data’s “Password” is hashed, “Phone” and “CardNo” are encrypted like following.

I created an API and see if I can get the DECRYPTED “Phone” and “CardNo” without any other decryption logic.

[HttpGet("GetUser/{name}")]

public async Task<User> GetUser([FromRoute] string name)

{

      return this.dbcontext.Set<User>().FirstOrDefault(x => x.Name.Equals(name));

}

It works!

▋EF Core: Database scalar function mapping

From EF Core 2.0, we can enable mapping database scalar functions to method stubs so that they can be used in LINQ queries and translated to SQL.

Lets define the scalar-function-mapping in our DbContext,

▋MyDbContext.cs

Only have to map the DB function’s Name and Schema :-)

public class MyDbContext : DbContext

{

        #region Functions

        [DbFunction(Name = "my_hash", Schema = "public")]

        public static string DbHash(string t) => throw new NotImplementedException();

        [DbFunction(Name = "my_hash_match", Schema = "public")]

        public static bool DbHashMatch(string t, string hashed) => throw new NotImplementedException();

        [DbFunction(Name = "my_sym_encrypt", Schema = "public")]

        public static byte[] DbSymEncrypt(string t) => throw new NotImplementedException();

        [DbFunction(Name = "my_sym_decrypt", Schema = "public")]

        public static string DbSymDecrypt(byte[] t) => throw new NotImplementedException();

        #endregion

}

And we can use them in LINQ Query or Lambda extension. For example, I want to find the matched user with an input username and password:

var matchedUser = 

    this.dbcontext.Set<User>().FirstOrDefault(x => x.Name.Equals('JB') && MyDbContext.DbHashMatch('my_pwd', x.Password));

Or by LINQ:

var users =

   from x in this.dbcontext.Users

   where x => x.Name.Equals('JB') && MyDbContext.DbHashMatch('my_pwd', x.Password)

   select x;

var matchedUser = users.FirstOrDefault();

▋EF Core: Database scalar function mapping (Advanced)

Notice that if we do not want to encryption/decryption by Value Conversions, we can still use scalar function mapping to do the same thing.

For example, add a new property to the DAO, that will be encrypted manually later.

▋User.cs

[Table("Users")]

public class User

{

        // … skip

        

        [Column(TypeName = "bytea")]

        public byte[] Secret { get; set; } // This column wont apply Value Conversions

}

▋Get user with scalar function mapping

Now we can select the user as below,

this.dbcontext.Set<User>().Where(x => x.Name.Equals(name)).Select(x=>

                new ApiUserInfo

                 { 

                    Name = x.Name,

                    Phone = x.Phone,

                    CardNo = x.CardNo,

                    Secret = MyDbContext.DbSymDecrypt(x.Secret)

                }).FirstOrDefault();

▋Add/Update user with scalar function mapping

Since the scalar function mapping only works in LINQ or Lambda extension, here is the trick to add/update user,

await this.dbcontext.Set<User>().AddAsync(this.dbcontext.Set<User>().Select(x => new User

            {

                Name = user.Name,

                Password = user.Password,

                Phone = user.Phone,

                CardNo = user.CardNo,

                Secret = MyDbContext.DbSymEncrypt("some secret")

            }).First());

            await this.dbcontext.SaveChangesAsync();

▋Github

KarateJB\EF.Core.Sample

▌Reference

---

▋New features in EF Core 2.0

▋Value Conversions

▋Using value converter for custom encryption of field on Entity Framework Core 2.1