Archive

Archive for September, 2013

CodeFluent Entities (and CodeFluent Runtime Client) Zip File support

September 26, 2013 Leave a comment

The CodeFluent.Runtime.dll (provided with the CodeFluent Entities commercial product) and the CodeFluent.Runtime.Client.dll (provided as a totally FREE nuget package) both contain a very useful utility class: ZipFile.

We already talked about it in a previous post: https://blog.codefluententities.com/2012/08/24/exploring-the-codefluent-runtime-zipfile/

The big advantage of this class is it uses an unmanaged implementation of the ZIP compression algorithm. It consumes less CPU and less memory. The implementation, written in C/C++, is located in a native DLL called CodeFluent.Runtime.Compression.dll. It’s provided in 32 and 64-bit version, and both versions have the same name.

If you use the commercial CodeFluent Entities product, this 32-bit version DLL is located in the %programfiles(x86)%\SoftFluent\CodeFluent\Modeler directory, and the 64-bit version DLL is located in the %programfiles(x86)%\SoftFluent\CodeFluent\x64 directory.

If you use the Nuget package, both versions are shipped each in a x86 or x64 directory in the package.

There are few problems with this:

  1. Since the CodeFluent.Runtime.Compression.dll is a native DLL, it must be available in the standard DLL search path. The standard way of shipping it was to copy the DLL aside the .NET .exe you were developing.
  2. The problem with this is you can’t write easily a .NET .exe built as “Any CPU” that uses the ZipFile class, because both versions of the DLL have the same name. So you have to build and ship two versions (32 and 64-bit) of your .NET exe just to overcome this, and you must install them in separate directories, even if the rest of your .EXE works just fine in “Any CPU” mode…

To solve these issues, starting with CodeFluent version 760, the new ZipFile class has changed the way it finds the CodeFluent.Runtime.Compression.dll dll. The algorithm is now the following:

CodeFluent ZipFile Compression

So, existing installations should not be affected by this, however our new recommendation is:

  • If you’re writing applications that will run on machine that have the CodeFluent Entities commercial product installed (build 760 or higher), then… do nothing. The ZipFile class will find what it needs automatically.
  • If you’re writing applications that will be deployed on production machines where nothing special is installed, then don’t ship CodeFluent.Runtime.Compression.dll aside your .exe anymore but rename it as CodeFluent.Runtime.Compression.x86.dll and CodeFluent.Runtime.Compression.x64.dll and ship both aside your .exe. If you specifically compile your .exe as X86 or X64, you can put only the one required. If it’s “Any CPU”, copy both.

Happy zippin’ !

The R&D team.

Writing a custom CodeFluent Entities aspect to encrypt/decrypt columns values at runtime

September 25, 2013 Leave a comment

Today, we will demonstrate how to automatically change the SQL code generated during the build process in order to encrypt and decrypt the values stored in the database columns. This is the answer to a very interesting question that was posted to stackoverflow recently: How to manage Encrypt* and Decrypt* TSQL functions on an entity property?

Let’s consider this model:

model

A card number is a sensible piece of information, so you should encrypt it before saving it in the database. Obviously, You should also be able to read it back and decrypt it.

Of course, with CodeFluent Entities, you can do it in the Business Object Model Layer generated BOM (C# or VB.NET) using OnAddSaveParameters and OnAfterReadRecord rules, but this post will demonstrate how it can be done directly in the database layer!

Microsoft SQL Server 2005 and higher provides two new useful functions: ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE. These functions allow you to encrypt or decrypt data with a pass phrase. For example:

ENCRYPTBYPASSPHRASE(‘my super secret key’, ‘1234-5678-9012-3456-7890’) -- will write 0x01000000FFB251B13ADE1344597535490BDD7ABB4A5094CF24C211A63FFDD465052795A9 in the database

So all we need to do is to call ENCRYPTBYPASSPHRASE during saving (INSERT or UPDATE statements), and DECRYPTBYPASSPHRASE during loading (SELECT statements). A code such as this one for instance:

INSERT INTO [Test] ([Test].[Test_CardNumber])
   VALUES (ENCRYPTBYPASSPHRASE(@PassPhrase, @Test_CardNumber))

SELECT [Test].[Test_Id], CONVERT(nvarchar, DECRYPTBYPASSPHRASE(@PassPhrase, Test_CardNumber)) AS [Test_CardNumber]
   FROM   [Test]
   WHERE  [Test].[Test_Id] = @Id

Moreover, you’ll have to change the column type from string to varbinary to match the ENCRYPTBYPASSPHRASE return type.

In the CodeFluent Entities context, you’ll have to add the PassPhrase parameter to the stored procedure parameters, and to the BOM generated code.

Some theory

Before anything is actually generated, CodeFluent Entities parses the model and transforms it into a complete memory representation which contains Entities, Properties, Methods, Tables, Columns, Procedures, etc. The inference engine that does this transformation is using a pipeline that’s divided into steps. CodeFluent Entities Aspects can be introduced at any step, and are able to modify the model currently in memory, therefore influencing the next steps.

Here are the main steps of the inference pipeline:

pipeline

The most important thing to note here is the fact that each step processing uses what has been created in memory during the previous steps. So for example, if you add a property to an entity early enough during inference, this property will be used to create a column automatically, all standard methods will use this property, procedures – based on methods – will use this column automatically, and so on.

At the final stage, generators (a.k.a. ‘producers’ in CodeFluent Entities terminology) will transform this in-memory model into real code, files, etc.

You can read more about the inference pipeline at http://www.softfluent.com/documentation/Aspects_Overview.html

Enough theory… Let’s do it!

To make it short, an aspect is simply a .NET class that implements the CodeFluent.Model.IProjectTemplate interface (located in CodeFluent.Model.dll).

public interface IProjectTemplate
{
    XmlDocument Run(IDictionary context);
}

You’ll find some information about this interface in previous posts https://blog.codefluententities.com/2012/07/27/codefluent-entities-writing-a-custom-aspect/

An aspect usually declares a specific XML namespace it will use for its specific XML attributes that will be store alongside CodeFluent Entities ones. These attributes should also be declared. It’s not mandatory, but it’s cool if you want to use them directly in the graphical editor. To each descriptor will correspond a property grid line in the Visual Studio standard property grid.

public class EncryptAspect : IProjectTemplate
{
    public static readonly XmlDocument Descriptor;
    public const string Namespace = "http://www.softfluent.com/aspects/samples/crypt"; // this is my custom XML namespace
    private const string PassPhraseToken = "PassPhrase";
    public Project Project { get; set; }

    static EncryptAspect()
    {
        Descriptor = new XmlDocument();
        Descriptor.LoadXml(
        @"<cf:project xmlns:cf='http://www.softfluent.com/codefluent/2005/1' defaultNamespace='MyAspect'>
            <cf:pattern name='Encrypt Aspect' namespaceUri='" + Namespace + @"' preferredPrefix='ca' step='Start'>
                <cf:message class='_doc'> CodeFluent Sample Encrypt Aspect Version 1.0.0.1 - 2013/09/20 This aspect modifies Save and Load* procedures in order to call Sql Server         ENCRYPTBYPASSPHRASE / DECRYPTBYPASSPHRASE functions.</cf:message>
                <cf:descriptor name='encrypt'
                    typeName='boolean'
                    category='Encrypt Aspect'
                    targets='Property'
                    defaultValue='false'
                    displayName='Encrypt the property'
                    description='Determines if the property must be encrypted when saving to the database.' />
            </cf:pattern>
        </cf:project>");
    }
}

When the aspect runs, it needs to be notified whenever a property is added to an entity, anywhere in the model, in order to check whether it should be encrypted. If it should be encrypted, the entity should be modified accordingly.

The aspect should also be able to modify stored procedures code, once they are are generated. The step after stored procedures inference is ‘Categories’, so we need to handle this inference pipeline step as well:

public XmlDocument Run(IDictionary context)
{
    if (context == null || !context.Contains("Project"))
    {
        // we are probably called for meta data inspection, so we send back the descriptor xml<br />
        return Descriptor;
    }

    // the dictionary contains at least these two entries
    Project = (Project)context["Project"];

    // hook on new base entities, and hook on new properties
    Project.Entities.ListChanged += (sender, e) =>
    {
        if (e.ListChangedType != ListChangedType.ItemAdded)
            return;

        var entity = Project.Entities[e.NewIndex];
        if (!entity.IsPersistent)
            return;

        if (!entity.IsProjectDerived)
        {
            entity.Properties.ListChanged += OnPropertiesListChanged;
        }
    };

    Project.StepChanging += (sender, e) =>
    {
        if (e.Step != ImportStep.Categories)
            return;

        foreach (var procedure in Project.Database.Procedures.Where(procedure => procedure.Parameters[PassPhraseToken] != null))
        {
            UpdateProcedure(procedure);
        }
    };

    // we have no specific Xml to send back, but aspect description
    return Descriptor;
}

We have designed our aspect so it considers a property should be encrypted if the XML attribute “encrypt” (in the aspect XML namespace) is set to ‘true’ and if the property is persistent (e.g. available in the persistence layer). CodeFluent Entities provides methods to read XML file attributes easily. In this example, if the attribute “encrypt” is not defined or if its value is not convertible to a boolean value, the function will return false.

private static bool MustEncrypt(Property property)
{
    return property != null && property.IsPersistent && property.GetAttributeValue("encrypt", Namespace, false);
}

Now the OnPropertiesListChanged method applies the necessary changes whenever a new property is added:

  1. Check whether it must be encrypted
    private void OnPropertiesListChanged(object sender, ListChangedEventArgs e)
    {
        if (e.ListChangedType != ListChangedType.ItemAdded)
            return;
    
        var property = ((PropertyCollection)sender)[e.NewIndex];
        if (!MustEncrypt(property))
            return;
        ...
    }
  2. Change its persistence type to Binary
    property.DbType = DbType.Binary;
    property.MaxLength = 8000;
    
  3. Add an ambient parameter “PassPhrase” to the entity. This parameter will be used for all methods without explicitly declaring it on each one. The ambient parameter will automatically be inferred as a standard parameter for stored procedures, but it will get its value from a static property or method in the BOM. In this example it will get its value from a static parameterless arbitrarily named “GetPassPhrase” method, described further down the document. Its ambient expression (the expression to use in the WHERE part of the stored procedures) must be also set. Since this parameter is not really used as a filter clause in this example, let’s simply set it to “(1=1)” which is equivalent to a “NOP” in a WHERE SQL clause (i.e: WHERE (([Test].[Test_Id] = @Id) AND (1 = 1)))
    var passPhraseParameter = new MethodParameter
        {
            Name = PassPhraseToken,
            ClrFullTypeName = "string",
            Nullable = Nullable.False,
            Options = MethodParameterOptions.Ambient |
                        MethodParameterOptions.Inherits |
                        MethodParameterOptions.UsedForLoad |
                        MethodParameterOptions.UsedForSearch |
                        MethodParameterOptions.UsedForCount |
                        MethodParameterOptions.UsedForRaw |
                        MethodParameterOptions.UsedForSave,
            ModelName = "[" + Project.DefaultNamespace + ".PassPhrase.GetPassPhrase()]", // Note the brackets here. It means that code should not be verified by CodeFluent Entities; otherwise an existing property of the current entity is expected.
            AmbientExpression = "(1=1)"
        };
    
    property.Entity.AmbientParameters.Add(passPhraseParameter);
    

Ok, we applied the required changes to the future BOM, and now we need to update stored procedures before they get generated.

CodeFluent Entities creates an in-memory Abstract Syntax Tree (AST) to represent stored procedures. This AST is independent from the target database type, and can be modified during inference as well.

To update the in-memory stored procedures AST, you can visit (using a visitor pattern) this tree and modify it when needed. We will use literal expressions (ProcedureExpressionStatement.CreateLiteral(“Sql code”)) to create our ENCRYPT/DECRYPT Sql function calls. In this case, the generated code won’t be of course platform independent anymore. This aspect should be adapted if we wanted to use it on an Oracle, MySQL or PostgreSql database.

private static void UpdateProcedure(Procedure procedure)
{
    procedure.Parameters[PassPhraseToken].DefaultValue = null; // This means the passphrase must be provided, and cannot be null
    if (procedure.ProcedureType == ProcedureType.SaveEntity)
    {
        procedure.Body.Visit(s =>
        {
            var statement = s as ProcedureSetStatement;
            if (statement == null || statement.LeftExpression == null || statement.RightExpression == null || !MustEncrypt(statement.LeftExpression.RefColumn))
                return;

            string parameterName = statement.RightExpression.Parameter.Name;
            statement.RightExpression.Literal = ProcedureExpressionStatement.CreateLiteral(string.Format("ENCRYPTBYPASSPHRASE(@{0}, @{1})", PassPhraseToken, parameterName));
            statement.RightExpression.Parameter = null;

            // Column is of type varbinary but parameter must be of type string
            var parameter = procedure.Parameters[parameterName];
            if (parameter != null)
            {
                parameter.DbType = DbType.String;
            }
        });
        return;
    }
  
    procedure.Body.Visit(s =>
    {
        var statement = s as ProcedureSetStatement;
        if (statement == null || statement.LeftExpression == null || !MustEncrypt(statement.LeftExpression.RefColumn))
            return;

        statement.As = new ProcedureExpressionStatement(statement, ProcedureExpressionStatement.CreateLiteral(statement.LeftExpression.RefColumn.Column.Name));
        statement.LeftExpression.Literal = ProcedureExpressionStatement.CreateLiteral(string.Format("CONVERT(nvarchar, DECRYPTBYPASSPHRASE(@{0}, {1}))", PassPhraseToken, statement.LeftExpression.RefColumn.Column.Name));
        statement.LeftExpression.RefColumn = null;
    });
}

That’s it, the aspect is finished! But we want to use it in Visual Studio now…

Integrate the aspect in the visual modeler

To integrate your aspect, add a reference to the class library project that contains the aspect (it can be in the same solution):

integrate_aspect_in_modeler1

integrate_aspect_in_modeler2

Use the reference context menu to add an aspect (compiled) from this reference.

integrate_aspect_in_modeler3

The following dialog box will display what aspects are available in the compiled project, and what are the descriptors for the selected aspect:

integrate_aspect_in_modeler4

To use the aspect, a developer has to select the concept targeted by a given descriptor (Entity, Property, Method, etc.) and use the “Aspects and Producers Properties” tab in the Visual Studio standard property grid:

integrate_aspect_in_modeler5

Now you can build your model, add the logic to get the pass phrase, and enjoy 🙂

public static class PassPhrase
{
    public static string GetPassPhrase()
    {
        return "hello world";
    }
}

class Program
{
    static void Main(string[] args)
    {
        DemoEncrypt entity = new DemoEncrypt();
        entity.CardNumber = "0123-4567-8901-2346-5678";
        entity.Save();
        entity.Reload(CodeFluentReloadOptions.Default);
        Console.WriteLine(entity.Trace());
    }
}

The full source code is available here: DemoEncrypt.zip

Conclusion

With the power of CodeFluent Entities and approximately 180 lines of C# code, we have added the possibility to add database encryption to the columns of our choice and the tables of our choice. This aspect is 100% reusable across all our projects. Can you do this without CodeFluent Entities?

Cheers,
Gerald Barré