Hi,

Using LLBLGen 4.2.

We have written an LLBLGen type converter that does application-side encrypting/decrypting of data (of an "employee" table) before it's set/get to/from the database (our application can run either on Oracle or SQL Server as the database).

We have the data get encrypted in several columns of the "employee" table. For example, the data encrypted is first-name, last-name, social-security-number, etc. We also have large binary documents (e.g. .pdfs) stored (in a child table) in the database as varchar(max)/blob fields for each employee.

The way we control if the encryption/decryption type converters actually do the encryption/decryption is for the type converter code to look for the encryption key specified in the web.config (the type converter looks a the .config and skips encrypting/decrypting if there is no key...meaning the database is not encrypted).

To convert the whole database from not-encrypted, to encrypted, we have an off-line console utility that will crawl that database employee table (and child tables) records and encrypt the employee records one by one.

This all works fine on our development machines and smaller test databases.

However, when we get to a database the size of the production database (million employees), we realize it takes several days for this utility to encrypt all records (probably mostly because of large binary data fields). The problem is that we can't take the system off-line for many days to do a complete database encrytion.

An approach we're considering is to have the web application run on both encrypted and unencrypted employees (which the utility is encrypting the employees in the background). We'd like to be able to run the web-application in this mixed encrypted/unencrypted mode (until a couple days later when all records are encrypted): The determinination of whether the type converter encryptes/decrypts based on the encryption flag in the "employee" entity/record.

Instead of the current, where the type-converter checks the .config if it should encrypt/decrypt, we'd need the type converter to convert based on the "encrypted" flag in the "employee" record.

One approach we came up with would be to possibly have the "employee" entity have both a FirstName and a FirstNameEncrypted field: FirstName would not have a type converter. FirstNameEncrypted would be mapped to use the encrytion type converter. Both entity fields would be mapped to the database FirstName field in the employee table. In the code, whenever we get/set FirstName, we'd have to access the correct entity field based on if the EmployeeEntity.EncryptionFlag.

It does mean making a number of duplicate-except-use-encryption-type-converter fields for the entity...which adds come code complexity. Maybe there's a better way the type converter itself can know if the "employee" entity has it's "is encrypted" flag set?

Any thoughts would be appreciated.

Thanks!

If you already have the flag to know whether or not to encrypt/decrypt, then I don't know why you need two fields FirstName and FistNameEncrypted. Based on the encrypted flag you should know determine the real FirstName value and return it inside the typeConverter.

Also, the flag could be un-encrypted always, right?

Additionally, if you use adapter, you could create a copy of the project, and specify no type converters on the fields, then generate code for that project as well (in a separate folder). Give it a different rootnamespace. The entities are the same, but the DataAccessAdapter isn't: the persistence info now contains no references to the type converters.

In your code you use the DataAccessAdapter class from the generated code which uses typeconverters if you need to encrypt /decrypt something, otherwise you use the DataAccessAdapter class of the other project. DataAccessAdapter implements IDataAccessAdapter, so you don't have to change much code.

But I think David's suggestion is better: simply decide inside the typeconverter whether you need to encrypt/decrypt or not.

Thanks for the suggestion. Within the type converter sounds like the better idea.

Suggestion of how I can get the EmployeeEntity.IsEncrypted field within the type converter...so I can use it to determine if to encrypt or not?

Here's the type converter code:

public class EncryptionTypeConverter : TypeConverter
{
  string initialVector = "xxxxxxxxxxxx";

  public EncryptionTypeConverter()
  {
  }

  public override bool CanConvertFrom(ITypeDescriptorContext context, Type sourceType)
  {
    switch (sourceType.FullName)
    {           
        case "System.String":
            return true;
        default:
            return false;
    }
  }

  public override bool CanConvertTo(ITypeDescriptorContext context, Type destinationType)
  {
    switch (destinationType.FullName)
    {
        case "System.String":
            return true;
        default:
            return false;
    }
  }

  //Encrypt
  public override object ConvertTo(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value, Type destinationType)
  {
    String plainText = Convert.ToString(value, System.Globalization.CultureInfo.InvariantCulture);
    String keyValue = xxx
    String encryptionMode = xxx

    if (value == null || ...)
    {
      return value;
    }       

    return Encrypt(plainText, keyValue, initialVector);
  }

It's not possible to obtain entity fields inside the type converter, it works solely based on the value it receives. But your solution you describe isn't bad:

greenstone wrote:

Hi, One approach we came up with would be to possibly have the "employee" entity have both a FirstName and a FirstNameEncrypted field: FirstName would not have a type converter. FirstNameEncrypted would be mapped to use the encrytion type converter. Both entity fields would be mapped to the database FirstName field in the employee table. In the code, whenever we get/set FirstName, we'd have to access the correct entity field based on if the EmployeeEntity.EncryptionFlag.

Do it this way: you add 2 fields in the entity: FirstNameNormal and FirstNameEncrypted. Do as you describe: map them both to the same db field, where FirstNameEncrypted uses the type converter. Then add in a partial class of EmployeeEntity a new property, called 'FirstName', which looks something like:

public string FirstName
{
    get { return this.IsEncrypted ? this.FirstNameEncrypted : this.FirstNameNormal; }
    set { this.FirstNameEncrypted = value;}
}

Your code works with FirstName. It's key that with 2 fields mapped to the same table field, you set only one of them, inserts will otherwise fail (as 2 fields end up in the field name list), as both entity fields are set to a value. This property will take care of that.

When everything is encrypted, you simply remove the FirstNameNormal field and rename the FirstNameEncrypted to FirstName, and remove the partial class and regenerate the code and your code will work as before.

I assume your encryptor is a T-SQL script so you don't have to pull the data out of the DB?

Another approach, which uses the type converter is to check whether the string value to convert (the value from the DB) is in base64 format: an encrypted value is in base64 format (as encrypted data is binary data, to store that in a string form you need e.g. base64). Testing that should be rather simple. To limit false positives you could also check for length (as the encrypted value is likely padded to a given length)

Thanks Otis for the approaches!

I assume your encryptor is a T-SQL script so you don't have to pull the data out of the DB?

I forgot to include the reverse direction in the code sample, so yes, we do have to pull the data out of the DB.

Using the approach with the added property 'FirstName' that determines which field to use (FirstNameNormal or FirstNameEncrypted), what would we do for things like:

includeFieldsList.Add(EmployeeFields.FirstName)

Would we have to change it to include FirstNameNormal and FirstNameEncrypted? Or is there a way to add a custom "field" called FirstName that would include both?

Would we have to change it to include FirstNameNormal and FirstNameEncrypted? Or is there a way to add a custom "field" called FirstName that would include both?

Both should be included.