Encrypt/Decrypt a string via web API code and store encrypted value in SQL database?

Question

I need to be HIPAA compliant and those requirements indicate AES256 encryption will be sufficient for storing of sensitive data (first name, last name, SSN, ID, DOB, Phone, VIN, etc.).

I'm leaning towards encryption thru application code rather than using MS SQL or MySQL built in support for encrypted fields. Avoid the SQL signed certificates process, setting MASTER KEY, etc.

I researched AES256 encryption using .NET 6 and most warned to not use AES - CBC, ECB, EFB or CTS ... in fact even Microsoft's own documentation suggest NOT using CBC but also provide an Aes class code sample that makes no practical sense found here? Encrypt then Decrypt within the same execution scope ... that would almost never happen in the real world where encrypted data is saved then retrieved and decrypted at some later date.

There is obviously going to be a performance hit when searching and/or reporting on (wild cards) any data that is encrypted, I'll have to iterate thru it before returning the data to requesting source (I have a threaded multi-core strategy here).

What's the "current" best practices method for encrypting/decrypting field level data in a SQL database using application code? Will I need to allocate a field to hold a random value for each field I want to encrypt in addition to global application key?

If AES .NET 6 crypto libraries are to be avoided, what are my other options?

Do not do your own crypto: you will make errors. Those errors may get you sued. Use built-in crypto from a reputable package, then you can sue the package makers. Very small errors can make crypto insecure. — rossum
– rossum, Commented May 12, 2022 at 14:32

mehmetcantas · Accepted Answer · 2022-05-11 23:35:44Z

1

Did you check this question? Entity Framework with Sql Server Column Level Encryption I think this might be useful if you working with EF Core

You can use attributes for this.

1 - Create an attribute :

[AttributeUsage(AttributeTargets.All)]
public class EncryptData: System.Attribute
{
   public EncryptData()
   {
   }
}

2 - Use it wherever you want :

public class Person 
{
    [EncryptData]
    public string Name {get;set;}
}

3 - Use it on insert :

public void Insert(T entity)
{
 // your code

EncryptFields(entity,_context);
 dbContext.SaveChanges();
}

`

public virtual T EncryptFields(T entity, MyDbContext dbContext)
    {
        MetadataTypeAttribute[] metadataTypes = entity.GetType().GetCustomAttributes(true).OfType<MetadataTypeAttribute>().ToArray();
        foreach (MetadataTypeAttribute metadata in metadataTypes)
        {
            PropertyInfo[] properties = metadata.MetadataClassType.GetProperties();              
            foreach (PropertyInfo pi in properties)
            {             
                if (Attribute.IsDefined(pi, typeof(DB.PartialEntites.EncryptData)))
                {
                    int id = ((EncryptData)pi.GetCustomAttributes(true)[0]).id;
                    
dbContext.Entry(entity).Property(pi.Name).CurrentValue =  // YOUR ENCRYPTION ALGORITHM;
                }
            }
        }
        return entity;
    }

edited May 11, 2022 at 23:35

answered May 11, 2022 at 22:08

mehmetcantas

113 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rob Over a year ago

Thanks for the link, but those link(s) are letting SQL server do the encrypt/decrypt. I wanted to avoid that (due to various project requirements out of my control) and do the encryption/decryption in my web API application level NOT reliant on EF (that underneath just generates the SQL statements to work with encryption/decryption). SQL MASTER KEY works fine and moves with the database (backup/restore), BUT what I want is viable application code to do the encrypt/decrypt and so far everything seems to be AES based and deemed "risky".

mehmetcantas Over a year ago

I edit my answer. I think this should work for you. Also you decrypt all data back when you are select. You can use query interceptors. This way you can decrypt all data in your project

Rob Over a year ago

Thank you for the response, but not what I'm really looking for ... I was hoping for an good encryption/decryption method/class for .NET 6 that didn't involve saving random values to my database in addition to a static secret key. For example if I used System.Security.Cryptography with AesGcm I would need to save 2 separate (or 1 concatenated) Nonce, and tag values per encrypted field. Trying to avoid that, but it doesn't seem like I'll be able to do that.

Jeremy Lakeman Over a year ago

If you are going to limit yourself to processing data client side, what about moving all the secret columns to a single type with a value converter to encrypt a utf8 json string?

Rob Over a year ago

You lost me there Jeremy? web API so this is all server side and back end database.

Collectives™ on Stack Overflow

Encrypt/Decrypt a string via web API code and store encrypted value in SQL database?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related