I am new to LLBL Gen Pro and would like to use it in a project I am working on. However, one of the capabilities I have not seen supported with in other ORM's is the ability to handle column level encryption. At the moment my project access the database via stored procedures interface and ideally I would like to move towards a situation where the stored procedures are used ONLY to create, update and delete. Nothing more.

I figure I should be able to do this in LLBLGenPro if there is a way to always call a stored procedure before and after a query is executed. That would give me the following option:

Since my project uses keys and not a password to handle encryption on the column level. We could create views and map entities to theses views, where the encrypted columns would be decrypted. Since the stored procedure to open the key is executed before the query is run the query will return the decrypted value. When the query has generated its results it would then close the key and everything would be fine.

With that said, my question is, Can a developer using LLBLGenPro have a stored procedure called before and after all queries are executed?

Hi there..

Just to be sure... Could that encryp/decrypt behavior you are doing to be moved to code-side. You could use TypeConverters to decrypt (when fetching) and encrypt (when saving) transparently, couldn't you?

And for the SP's, I think you could do that. It's not built-in but you could include the calls (SPs) and the saves in a transaction. Or you can generalize this, extending the entity code to make sure these SPs are called (ref).

There's also the problem of specifying the DecryptByKey() function call on the entity field. So you can IMHO indeed better use a typeconverter which encrypts/decrypts using AES or other encryption, the value read from / written to the db. This is totally transparent for you as a developer and inside the DB you simply have the encrypted data. As the encryption/decryption done in the typeconverter (which you write, it's very simply see the SDK and the example sourcecode for a type converter to get started) is also key based, you should be able to decrypt the data inside the db with the same public key part using a proc to support other apps on the same database.

this also frees you from using procs for inserts/updates/deletes, and also you don't have to call procs before/after.

moving the encryption to the app side would be just enough work to make it unfeasible for the project. That said I would never again do it in the database if I had to do it all over again.

In Order to get this project to use LLBLGenPro I will definitely need to support database side encryption on the column level using stored procedures to open and close the key used for encrypting and decrypting data. And stored procedures for creating updating and deleting entities. If there was a way to do this then I could map the entities with encrypted content to views whose select statement would have embedded the function call to decryptbykey.

daelmo wrote:

Hi there..

Just to be sure... Could that encryp/decrypt behavior you are doing to be moved to code-side. You could use TypeConverters to decrypt (when fetching) and encrypt (when saving) transparently, couldn't you?

And for the SP's, I think you could do that. It's not built-in but you could include the calls (SPs) and the saves in a transaction. Or you can generalize this, extending the entity code to make sure these SPs are called (ref).

As a complete noob to using ORM's, I am confused by your last paragraph. I generated some code for our projects database to play around with and browsing through it I have not the foggiest where to put a call to a stored procedure to make sure that the call to open the encryption key always happens before the generated SQL query used to populate the Entity.

A Simplified example of what I need to happen is as follows

1. open connection to database
2. call stored procedure to open key
3. run query
4. call stored procedure to close key
5. close connection to database

Browsing through the generated code I do not see a place where I could place some code to ensure this type of semantic. As you can see this semantic only works well for a database using a single key to encrypt /decrypt all data contained within it. Which is an acceptable solution since using multiple keys would require much more work(I haven't used multiple keys before but I am pretty sure you cannot have multiple keys open at the same time). With that said, I think the ideal place to allow the user to implement such a semantic would be exactly when a connection to the database is opened and before the connection is closed. Can this be done? Is there another way to do this that would ensure the previously stated semantics?

Well I have been playing around with Linq2LLBL and I must say I am happy and disappointed at the same time. Mostly because I feel as if I am fighting against the code, which is generally a good sign of when you need to stop and take a look at what you are trying to do.

I have discovered that via specifying the DataAccessAdapter to a Linq Query I have the ability to run my stored procedures for opening and closing the key during a transaction (Just remember to Start the transaction otherwise there is no transaction to use). Now if my Entities were mapped to views and my views were coded to use the DecryptByKey function (DB Side) I would be good to go. Unfortunately that only gets you so far; Since I cannot find a way to save an entity via a stored procedure, short of writting one my self. You have no way of saving any changes made to the entities that are instantiated. Why you ask? Simple, if you try to save to the view which has a generated column it will cuss and do its best exorcist routine. Though if you can exclude the generated columns from the save then the LLBL Generated SQL should work.

Since views don't work as easily you might wonder what does. Well, since LLBL lets us use function mappings in linq queries you can use your DecryptByKey mapped function in where clauses and projections (fancy way of saying select); I also suspect you should be able to use it in joins too. The bad part about this is you end up having to either project onto an anonymous type or custom class, using the mapped function to decrypt. Assuming you only need a few columns at anyone time this shouldn't be too bad. Define a few Generic Tuple types to hold a defined number of fields and your off to the races(otherwise your stuck defining at random new classes to hold the projected data all the time). Though you could also select your LLBL defined entity in the cases where you don't need the encrypted fields and project onto an annoymous type or defined class when you do.

Personally I think LLBL must have a way to do this. Since you can apply a dbfunction to a field. Unfortunately linq does not take advantage of that information if I set the expressions to be applied in the constructor of the entity. That is the only place that it made sense to do that and it didn't work. I will keep monitoring this thread to see if the LLBL guys can give me any insight on what I could do to pull this off but since they have pretty much told all who have asked about DB encryption to do App side encryption I am not holding my breath.

P.S. While I understand that App Side encryption makes sense in some cases, It doesn't make sense in all. For instance if your database is or has the potential to be accessed by multiple applications not all under your control I see no reason why we should not put the encryption in the Database. Assuming your using Keys and not a password your data will be secure and you don't have to coordinate between multiple applications the keys used to do the encryption. I know you can place an app between the database and all other applications but then your putting yourself in the position of having to recreate SQL, at least for all the clients using the database.

birchoff wrote:

Personally I think LLBL must have a way to do this. Since you can apply a dbfunction to a field. Unfortunately linq does not take advantage of that information if I set the expressions to be applied in the constructor of the entity. That is the only place that it made sense to do that and it didn't work. I will keep monitoring this thread to see if the LLBL guys can give me any insight on what I could do to pull this off but since they have pretty much told all who have asked about DB encryption to do App side encryption I am not holding my breath.

The way you want to do db encryption/decryption is not supported because it needs deep customization of the query generated, per field. I.e.: instead of referring to a field in a query, it needs to be accessed through a decrypt function and has to be stored using an encrypt function, which needs parameters which have to be applied at query creation time. That kind of meta-data isn't stored in our system and I don't think we'll do that in an upcoming version as it's very likely other databases, if they support column encryption, require different data.

You also want to use CUD operations through procs. We don't support that either, because llblgen pro produces its own CUD queries and management logic (pk-fk syncs etc. )

P.S. While I understand that App Side encryption makes sense in some cases, It doesn't make sense in all. For instance if your database is or has the potential to be accessed by multiple applications not all under your control I see no reason why we should not put the encryption in the Database. Assuming your using Keys and not a password your data will be secure and you don't have to coordinate between multiple applications the keys used to do the encryption. I know you can place an app between the database and all other applications but then your putting yourself in the position of having to recreate SQL, at least for all the clients using the database.

AES encryption is AES encryption. If you encrypt data through a private key on the client and store that data in the database, you can decrypt it using a public key anywhere: in the database, in another client etc.

The problem is mainly: encryption requires the private key part of a keypair, and you don't want to store that part in every client. Though somewhere the data has to be encrypted/decrypted. If a proc is available in the db which decrypts data on the fly, an outside user can use that proc to decrypt data on the fly. This means that a typeconverter which consults a decrypt/encrypt service (locally) can solve that problem.

I.o.w.: it's about for whom is the encryption: for people using the application, or for people who force themselves to get access to the database. If it's the former, a proc callable through any interface is giving them what they want. If it's the latter, I think a type converter is as secure as a proc system.

Anyway, CUD operations in entities aren't mappable through procs, you have to make these calls yourself through action proc calls, OR by overriding methods in DataAccessAdapter and altering the queries generated. This is doable, but requires some work.

What you could do for the saves is to override OnSaveEntity() (for deletes, you don't need encryption/decryption as I don't expect you're encrypting PK's) and there, altering the query to prefix it with a call to the proc, and alter it with encryption calls (you get the full SQL query and the entity saved).

For selects, it's different: selects do decryption, and decryption can be done with a public key. You could store the public key part in the typeconverter (which thus does no encryption on save but performs decryption on load. A type converter is used both for convert from and convert to. Saves call ConvertTo (so convert the value to a binary value without encryption) and loads call ConvertFrom (so there decrypt the value to the real value using the public key).

This way, you have encryption using a private key inside the DB, no procs involved for CUD, and you have decryption using the public key outside the DB (but that doesn't matter) and because you use a type converter, you can create with linq projections anyway you like, as the type converter is still used. If you manually try to alter the query in OnSelect, it might not work, as the query might have a layout you won't expect due to custom projections by for example the linq query.

Btw, which O/R mappers did support encryption/decryption right out of the box ? I think most of them require you to write either an interceptor or other typeconverter-ekse class.

(edit) I see you opened another thread, so we're doing the discussion on 2 fronts now hehe . I gave another possible solution in the other thread. Please read my response above and then the other thread and let us know if we should look into changing the DQE a bit so it executes expressions in inserts. You still have to add the proc call.

I apologize for carrying on this conversation in two different threads. I wanted a specific answer to a question and felt that one required a different thread. I must also thank you for your explanation of the situation of how to get encryption to work in LLBL Gen Pro.

In response to your question of which ORM lets you do DB side encryption/decryption without having to do some extra work. The answer is NONE. which you already know. However I feel as if LLBL Gen Pro is the closest to making it work, at least for SQL Server 2005. In my project we are not using public/private key encryption since the decision was made to let the DB do all the encryption work and at the time they were using a stored proc interface they opted to use a symmetric key which is opened before use and closed after (Moving to a public/private key method for encryption is an option).

I do have a few questions in regards to your post, but before I ask them let me clarify something. Using stored procs for CUD was a compromise I thought was necessary after trying to get this to work under EF (I know trying to use v1 of an ms project is a bad idea). I changed my mind after seeing the following code in this thread (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=11220&HighLight=1)

Borrowed Code Block A

ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(EmployeeSalaryFields.Id, 0);
fields.DefineField(EmployeeSalaryFields.EmployeeId, 1);
fields.DefineField(EmployeeSalaryFields.EncryptedNetSalary, 2, "NetSalary");
fields[3].ExpressionToApply = new DbFunctionCall("DecryptByPassPhrase", new object[] { "password", EmployeeSalaryFields.EncryptedNetSalary });

DataTable results = new DataTable();
dataAdapter.FetchTypedList(fields, results, null);

byte[] bytes = (byte[])results.Rows[0]["NetSalary"];
string decryptedNetSalary = System.Text.Encoding.Unicode.GetString(bytes);

Borrowed Code Block B

EmployeeSalaryEntity employeeSalary = new EmployeeSalaryEntity();
employeeSalary.Fields[(int)EmployeeSalaryFieldIndex.EncryptedNetSalary].ExpressionToApply =
            new DbFunctionCall("encryptByPassPhrase", new object[] { "password", "textToEncrypt" });

adapter.UpdateEntitiesDirectly(employeeSalary, null);

I then made the leap of faith that if I could place the ExpressionToApply on the encrypted field before LLBL generated its query that it would do the write thing; for example, if I had a Person Table with the following columns

• 1. ID
• 2. User_Name
• 3. First_Name
• 4. Last_Name
• 5. SSN

and lets say Columns 4-5 are encrypted with a symmetrical key, on SQL Server 2k5 the following query is what I would write to retrieve all the people in the table

spOpenKey

SELECT 
           ID,
           DecryptByKey(FIRST_NAME) as FIRST_NAME,
           DecryptByKey(MIDDLE_NAME) as MIDDLE_NAME,
           DecryptByKey(LAST_NAME) as LAST_NAME,
           DecryptByKey(SSN) as SSN
FROM 
           Person

spCloseKey

Inserting a person into the table would be done with the following query(UPDATE would be derived similarly).

spOpenKey

INSERT INTO Person
   VALUES
        ( 
           EncryptByNamedKey(<first_name>),
           EncryptByNamedKey(<middle_name>),
           EncryptByNamedKey(<last_name>),
           EncryptByNamedKey(<ssn>)
        )

spCloseKey

where EncryptByNamedKey would be defined in the database as follows

CREATE FUNCTION EncryptByNamedByKey (@value varchar)
returns varbinary(128)
as
BEGIN
           return EncryptByKey(Key_GUID(<Key_Name>), value)
END

Lets say that the Select and Insert queries displayed directly above are the type of queries I would like to see LLBL Gen Pro generate when dealing with a table using Column Level encryption. To get LLBL Gen Pro to generate something close to the stated goal I would use a transaction to make sure spOpenKey and spCloseKey are called at the beginning and end respectively. As for the actual query, assuming we have a PersonEntity with fields matching the column names listed above. Then all I would need to do is find that magical spot to intercept the list of fields in the PersonEntity and do the following:

Func<object, DbFunctionCall> decryptByKey = 
     s => new DbFunctionCall("DecryptByKey", new[]{s});
Fields[(int)PersonFieldIndex.FirstName].ExpressionToApply = 
    decryptByKey(PersonFields.FirstName);
...
...
...
Fields[(int)PersonFieldIndex.Ssn].ExpressionToApply = 
    decryptByKey(PersonFields.Ssn);

Then when I do a classic LLBL Gen Query

using(DataAccessAdapter da = new DataAccessAdapter())
{
      da.StartTransaction(IsolationLevel.ReadCommitted, "TestTransaction");
      ActionProcedures.SpOpenKey(da);
      //Not sure if this is a valid (In particularly the call toe GetFactoryImpl.)
      //The point is basically to ask LLBL to get all the People in the database.
       var Persons = da.FetchEntityCollection(
                 new EntityCollection<PersonEntity>(GetFactoryImpl(typeof(Person))
            );
      ActionProcedures.SpCloseKey(da);
      foreach(var person in persons)
      {
            Console.WriteLine(System.Text.Encoding.ASCII.GetString(p.FirstName));
      }
}

Or a LINQ query

using(DataAccessAdapter da = new DataAccessAdapter())
{
      da.StartTransaction(IsolationLevel.ReadCommitted, "TestTransaction");
      ActionProcedures.SpOpenKey(da);
      var linqMetaData = new LinqMetaData(da);
      var query = from p in LinqMetaData.Person select p
      foreach(var p in query)
      {
            Console.WriteLine(System.Text.Encoding.ASCII.GetString(p.FirstName));
      }
      ActionProcedures.SpCloseKey(da);
}

the generated SQL should be very similar to the goal or ideal select query previously listed. The major problem with this code is that the designer will generate properties that expect a byte array since the fields in the tables are defined as varbinary(because they are encrypted). However in the classic LLBL query and LINQ query you will notice that I expect the values to still be in binary form. Since, DecryptByKey on Sql Server 2k5 returns a varbinary. however assuming we could we could do something about the designer generating code that assumed the queries can only return varbinary then we could define a function in the DB called DecryptVarCharByKey; where it is defined in the db as follows

CREATE FUNCTION DecryptVarCharByKey (@value varbinary(128), @fieldSize int)
returns varchar(@fieldSize) 
--not sure you can do this but you can get around that limitation.
as
BEGIN
           return convert(varchar(@fieldSize), DecryptByKey(value))
END

Then all you need to do is use this function in the DBFunctionCall definition and it would be responsible for decrypting the expected value. It just occurred to me that if a Type Converter (I have no experience with Type Converters and cannot find any in depth documentation on how to properly use and define them) could make a DB function call during its operation then it could be used to solve the issue of type mismatch between what the query returns and what the generated code expects.

As for inserts and updates we just need to apply a different expression to the encrypted fields

Func<object, DbFunctionCall> encryptByNamedKey = 
     s => new DbFunctionCall("EncryptByNamedKey", new[]{s});
Fields[(int)PersonFieldIndex.FirstName].ExpressionToApply = 
    encryptByNamedKey(PersonFields.FirstName);
...
...
...
Fields[(int)PersonFieldIndex.Ssn].ExpressionToApply = 
    encryptByNamedKey(PersonFields.Ssn);

And any Save of a modified or new entity should generate our previously defined ideal insert query

using(DataAccessAdapter da = new DataAccessAdapter())
{
      da.StartTransaction(IsolationLevel.ReadCommitted, "TestTransaction");
      ActionProcedures.SpOpenKey(da);
      //where p is either a new entity or a modified one
      da.SaveEntity(p)
      ActionProcedures.SpCloseKey(da);
    
}

The code and script samples above should illustrate what I believe is possible in LLBL gen pro and what could be possible. As you said in thread (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14930)

it's inserts which don't execute a function btw, updates do emit expressions (e.g. DbFunctionCalls) to the query. Inserts could obey this perhaps but it requires some changes in the DataAccessAdapter class and the SqlServer DQE (DynamicQueryEngine.cs, line 450-520). It might not be a big change, so we could look into altering the DQE a bit to make this work for you. (selects, updates already obey expressions set to fields. You need to set these expressions in an override to OnInitialized in a partial class of the entity).

Now on to my questions.

• Is all of what I outlined above possible in LLBL Gen Pro v2.6? If it is then where is the magical place for me to intercept fields so that I can attach expressions to them to get the required behavior? I know you said to use OnInitialized for the entity in question but I tried that by overloading the OnIntialized method like this

public void OnInitialized()
{
    Func<object, DbFunctionCall> decryptByKey = 
         s => new DbFunctionCall("DecryptByKey", new[]{s});
    Fields[(int)PersonFieldIndex.FirstName].ExpressionToApply = 
        decryptByKey(PersonFields.FirstName);
    ...
    ...
    ...
    Fields[(int)PersonFieldIndex.Ssn].ExpressionToApply = 
        decryptByKey(PersonFields.Ssn);
}

I break pointed the function and watched it get executed, unfortunately the trace that was displayed in my output window did not generate the call to DecryptByKey on the columns that had the expression applied to them. If I am doing something wrong please let me know. * If inserts don't utilize the expressions applied to a field and Type Converters are responsible for the bidirectional conversion of values to/from .NET Type System and the DB Type System. Then it makes sense to me that the Type converters should have the option to at least wrap the aforementioned field with a db function call that would yield the correct value, instead of doing the conversion in .NET? What do you think? * Where Can I find out more detailed information on how Type Converters work and how to implement my own?

Is all of what I outlined above possible in LLBL Gen Pro v2.6? If it is then where is the magical place for me to intercept fields so that I can attach expressions to them to get the required behavior? I know you said to use OnInitialized for the entity in question but I tried that by overloading the OnIntialized method like this Code:

public void OnInitialized() { Func<object, DbFunctionCall> decryptByKey = s => new DbFunctionCall("DecryptByKey", new[]{s}); Fields[(int)PersonFieldIndex.FirstName].ExpressionToApply = decryptByKey(PersonFields.FirstName); ... ... ... Fields[(int)PersonFieldIndex.Ssn].ExpressionToApply = decryptByKey(PersonFields.Ssn); }

I break pointed the function and watched it get executed, unfortunately the trace that was displayed in my output window did not generate the call to DecryptByKey on the columns that had the expression applied to them. If I am doing something wrong please let me know.

IMHO, the easiest thing to do is to use DynamicLists for fetching fields and then you may project the result into EntityCollections, dynamicLists let you set expressions to fields.

Earlier you mentioned: "moving the encryption to the app side would be just enough work to make it unfeasible for the project" IMHO, it is the least amount of work to do, rather than modifying the generated code or using DynamicLists everywhere.

If inserts don't utilize the expressions applied to a field and Type Converters are responsible for the bidirectional conversion of values to/from .NET Type System and the DB Type System. Then it makes sense to me that the Type converters should have the option to at least wrap the aforementioned field with a db function call that would yield the correct value, instead of doing the conversion in .NET? What do you think?

Type Converters transform values at client side after the query is issues (fetch), or before it is executed (Saves), and it has nothing to with the generated query.

Where Can I find out more detailed information on how Type Converters work and how to implement my own?

Please download the SDK package, which contains the SDK documentation, inwhich you can find more info about TypeConverters.

Additionally what Walaa said:

I break pointed the function and watched it get executed, unfortunately the trace that was displayed in my output window did not generate the call to DecryptByKey on the columns that had the expression applied to them. If I am doing something wrong please let me know.

Selects creates using the factory new fields, (through the factory created by the method CreateFactory()). The EntityFieldsFactory is the place where the fields are produced (the fieldprovider produces EntityField2 instances itself as it's faster to do it that way than calling the EntityFieldFactory methods individually and this routine is called a lot).

You could alter the template (entityFieldsFactoryAdapter.template in SharedTemplates\C#, bind your altered copy to the same templateID in a different templatebindings file and place it above the standard bindings in tab2 on the generator configuration dialog).

In the template, at line 35, you see a 'case' clause. What you want is alter the columns for the entities you're after. What you could do is (it's a partial class so you can place the method in another file) to call a method, e.g. AddExpressions(fieldsToReturn, relatedEntityType) and in that method, you add the functions.

However, while I was writing this, I realized that you need different functions for each operation, which I didn't realize yesterday when I wrote the reply. So there's not a single place where you can simply set the expression and be done with it, you need to know exactly for which operation you need the expressions (selects -> decrypt, update -> encrypt). So I think the best option is the type converter solution which is the ideal one: no field type misery, everything is encrypted and no hassle. For everything else, it's a non-ideal road as you have to add the different expressions per operation: insert/update need different expressions (encrypt) than fetches (decrypt). OnSaveEntity() can help with the saves, but for selects it's a different thing, as there's no routine which easily lets you add expressions at the last moment for fields which are about to be selected, as there are multiple ways to fetch data.

IMHO, the easiest thing to do is to use DynamicLists for fetching fields and then you may project the result into EntityCollections, dynamicLists let you set expressions to fields.

I checked out your documentation and I realize that the code the other guy used (borrowed Code Block A) was for generating a dynamic list. And I completely agree with you that doing that all across the app would be unacceptable. I shoulder to think of the kind of performance hit I would take for doing that anyway.

Type Converters transform values at client side after the query is issues (fetch), or before it is executed (Saves), and it has nothing to with the generated query.

As for type converters, while I completely understand that they should be used to process data AFTER it is retrieved from the database and BEFORE it is sent to the database. I was just proposing a possible feature be added. In a more abstract sense this database side encryption that we have been discussing here for a few days is really a type conversion issue. It just so happens that the database and not the App is in a much better position to actually handle the type conversion.

However, while I was writing this, I realized that you need different functions for each operation, which I didn't realize yesterday when I wrote the reply. So there's not a single place where you can simply set the expression and be done with it, you need to know exactly for which operation you need the expressions (selects -> decrypt, update -> encrypt). So I think the best option is the type converter solution which is the ideal one: no field type misery, everything is encrypted and no hassle. For everything else, it's a non-ideal road as you have to add the different expressions per operation: insert/update need different expressions (encrypt) than fetches (decrypt). OnSaveEntity() can help with the saves, but for selects it's a different thing, as there's no routine which easily lets you add expressions at the last moment for fields which are about to be selected, as there are multiple ways to fetch data.

potential feature request aside I played with LLBL a bit more on sunday night and implemented an Override to the Dynamic Query Engine. After studying its methods in the object view I figured it was as good a place as any to hook into. Since it defines the following methods

• CreateSelectDQ
• CreatePagingSelectDQ
• CreateInsertDQ
• CreateUpdateDQ
• CreateDeleteDQ

Since my playing was done with only selects I only overrode the CreateSelectDQ and CreatePagingSelectDQ. I was able to successfully apply a DbFunctionCall to one of the items in selectList(where selectList is type of IEntityFieldCore[]).Since the method also has access to the respective fields persistence information via the parameter fieldsPersistenceInfo (where fieldsPersistenceInfo is type of IFieldsPersistenceInfo[]). I can detect easily for which field and for which table and decide whether or not to apply a DbFunctionCall to decryptByKey. While this worked and generated the correct query with the field being wrapped in a DecrptByKey call. I have a few questions

• While I was successful in getting LLBL to generate the desired SQL Query I noticed that simply inserting the name of the field as a parameter to the DbFunctionCall would only work in the simple queries. It would definitely break in cases where I am querying two different tables and they both have columsn with the same name. What do I have to pass as a parameter to the DbFunctionCall constructor to get LLBL to generate a absolute reference to the correct column in the desired table?
• Do you think this is the best way, without changing the templates to make sure that the fields I want to wrap in DecrptByKey (And similarly EncryptByKey in the Create<Insert/Update>DQ methods) get the correct DbFunctionCall applied to them before query generation?
• If hooking into Create<statement_type>DQ methods to add a DbFunctionCall wont cause any unforeseen side effects. Can I assume that any other DbFunctionCalls applied in client code would be composable? For example:

Assume the following table is stored in a DB

• People

• ID

• FirstName
• LastName
• EmailAddress
• PhoneNumber

Where the FirstName field is encrypted. If I use the aforementioned method to make sure it is decrypted before use, that is add a DbFunctionCall to DecryptByKey in Either of the Create...DQ methods could I assume that if I applied a DbFunctionCall to Convert in a linq query or dynamic list; it would lead to code similar to the following would be generated.

SELECT 
    ID
    convert(nvarchar,DecryptByKey(FirstName)),
FROM
    People

I suspect this couldnt be the case if I merely do the following

Client Code

using(var da = new DataAccessADapter(da)
{
var md = new LinqMetaData(da)
var query =
    from person in md.People
    select new { ID= person.ID FirstName=DbFunctions.Convert("nvarchar", p.FirstName)}
}

Code that overrides CreateSelectDQ

protected override IRetreivalQuery CreateSelectDQ(IEntityFieldCore[] selectList, IFieldsPersistenceIfno[] fieldsPersistenceInfo,...)
{
    var FirstNameFieldIndex = 1;
    selectList[FirstNameFieldIndex].ExpressionToApply = decryptByKey(fieldsPersistenceInfo[FirstNameIndex].SourceColumnName);
    return base.CreateSelectDQ(selectList, fieldsPersistenceInfo, ...);
}

If this worked I suspect that the query generated would have something more like

SELECT 
    ID
    DecryptByKey(convert(nvarchar,(FirstName)),
FROM
    People

what do you think? If my intuition is correct how would I make sure that the expressions are applied in the correct order.

birchoff wrote:

Type Converters transform values at client side after the query is issues (fetch), or before it is executed (Saves), and it has nothing to with the generated query.

As for type converters, while I completely understand that they should be used to process data AFTER it is retrieved from the database and BEFORE it is sent to the database. I was just proposing a possible feature be added. In a more abstract sense this database side encryption that we have been discussing here for a few days is really a type conversion issue. It just so happens that the database and not the App is in a much better position to actually handle the type conversion.

Type converters are for converting from one .NET type to the other and back, so completely outside the DB. So: database --[raw data]--> ado.net provider --[.NET type X]--> Type converter --[.NET type y]---> entity field.

(and back).

So either, the encryption / decryption takes place on the 'raw data' before it's converted to a .NET type, or right after it's been converted to a .net type, though that has huge consequences, as one is done in the DB, and the other outside the DB.

So I think it's best to separate the concerns of converting type X to type Y and encryption/decryption, though for the 'easy way out' it can be easier to merge them together into the typeconverter.

However, while I was writing this, I realized that you need different functions for each operation, which I didn't realize yesterday when I wrote the reply. So there's not a single place where you can simply set the expression and be done with it, you need to know exactly for which operation you need the expressions (selects -> decrypt, update -> encrypt). So I think the best option is the type converter solution which is the ideal one: no field type misery, everything is encrypted and no hassle. For everything else, it's a non-ideal road as you have to add the different expressions per operation: insert/update need different expressions (encrypt) than fetches (decrypt). OnSaveEntity() can help with the saves, but for selects it's a different thing, as there's no routine which easily lets you add expressions at the last moment for fields which are about to be selected, as there are multiple ways to fetch data.

potential feature request aside I played with LLBL a bit more on sunday night and implemented an Override to the Dynamic Query Engine. After studying its methods in the object view I figured it was as good a place as any to hook into. Since it defines the following methods

• CreateSelectDQ
• CreatePagingSelectDQ
• CreateInsertDQ
• CreateUpdateDQ
• CreateDeleteDQ

Since my playing was done with only selects I only overrode the CreateSelectDQ and CreatePagingSelectDQ. I was able to successfully apply a DbFunctionCall to one of the items in selectList(where selectList is type of IEntityFieldCore[]).Since the method also has access to the respective fields persistence information via the parameter fieldsPersistenceInfo (where fieldsPersistenceInfo is type of IFieldsPersistenceInfo[]). I can detect easily for which field and for which table and decide whether or not to apply a DbFunctionCall to decryptByKey. While this worked and generated the correct query with the field being wrapped in a DecrptByKey call. I have a few questions

• While I was successful in getting LLBL to generate the desired SQL Query I noticed that simply inserting the name of the field as a parameter to the DbFunctionCall would only work in the simple queries. It would definitely break in cases where I am querying two different tables and they both have columsn with the same name. What do I have to pass as a parameter to the DbFunctionCall constructor to get LLBL to generate a absolute reference to the correct column in the desired table?

I think a clone of the the IEntityFieldCore object to refer to, which is also used inside the query. So the IEntityFieldCore object in the select list to decrypt. As decryption is only necessary for the data returned. The ideal routine is the CreatePagingSelectDQ to pre-process the select list, add the expressions there. Create a clone of the field, as otherwise it's diving into a stack overflow. Set the corresponding IFieldPersistenceInfo (with the table info etc.) in the corresponding index in dbFunctionCall.FieldPersistenceInfos. No clone needed for that object, it's readonly, shared data.

Do you think this is the best way, without changing the templates to make sure that the fields I want to wrap in DecrptByKey (And similarly EncryptByKey in the Create<Insert/Update>DQ methods) get the correct DbFunctionCall applied to them before query generation?

It's definitely a location in the code which is always used, no matter what. The CreatePagingSelectDQ is the best spot, as the CreateSelectDQ is also called for subqueries, which you might want to avoid.

If hooking into Create<statement_type>DQ methods to add a DbFunctionCall wont cause any unforeseen side effects. Can I assume that any other DbFunctionCalls applied in client code would be composable? For example:

Assume the following table is stored in a DB

• People

• ID

• FirstName
• LastName
• EmailAddress
• PhoneNumber

Where the FirstName field is encrypted. If I use the aforementioned method to make sure it is decrypted before use, that is add a DbFunctionCall to DecryptByKey in Either of the Create...DQ methods could I assume that if I applied a DbFunctionCall to Convert in a linq query or dynamic list; it would lead to code similar to the following would be generated.

SELECT 
    ID
    convert(nvarchar,DecryptByKey(FirstName)),
FROM
    People

I suspect this couldnt be the case if I merely do the following

Client Code

using(var da = new DataAccessADapter(da)
{
var md = new LinqMetaData(da)
var query =
    from person in md.People
    select new { ID= person.ID FirstName=DbFunctions.Convert("nvarchar", p.FirstName)}
}

Code that overrides CreateSelectDQ

protected override IRetreivalQuery CreateSelectDQ(IEntityFieldCore[] selectList, IFieldsPersistenceIfno[] fieldsPersistenceInfo,...)
{
    var FirstNameFieldIndex = 1;
    selectList[FirstNameFieldIndex].ExpressionToApply = decryptByKey(fieldsPersistenceInfo[FirstNameIndex].SourceColumnName);
    return base.CreateSelectDQ(selectList, fieldsPersistenceInfo, ...);
}

If this worked I suspect that the query generated would have something more like

SELECT 
    ID
    DecryptByKey(convert(nvarchar,(FirstName)),
FROM
    People

what do you think? If my intuition is correct how would I make sure that the expressions are applied in the correct order.

The DQE method to produce a select query is always called from whatever select method you use: linq, our own api etc. So in your DQE overriding code, you should check whether the field you want to decrypt has already an Expression object applied to it. If so, simply pass that expression as the parameter of the dbfunctioncall, if not, pass the clone of the field. That way the dbfunction call is performed either after the expression set (whatever it is, e.g. a function call you defined yourself) or if no expression was set, the right field was decrypted.

Another way to apply the dbfunction call to a field without an expression is to replace in the array the field with a new EntityField2() instance and by passing into the ctor the DBFunctionCall instance where you pass the original field object as the parameter (no clone needed). Be sure to specify the type and the name equal to the original field.

Hope this helps.

I think a clone of the the IEntityFieldCore object to refer to, which is also used inside the query. So the IEntityFieldCore object in the select list to decrypt. As decryption is only necessary for the data returned. The ideal routine is the CreatePagingSelectDQ to pre-process the select list, add the expressions there. Create a clone of the field, as otherwise it's diving into a stack overflow. Set the corresponding IFieldPersistenceInfo (with the table info etc.) in the corresponding index in dbFunctionCall.FieldPersistenceInfos. No clone needed for that object, it's read only, shared data.

Fantastic I got it to work. However I needed to make a few alterations to your recommendation and I have at least one more question. First to the alterations. Like I said in my previous post I overrode CreateSelectDQ and was able to get the DQE to generate the desired query. I followed your advice and did the following

Basic Query I have been using to test my code.

using (var da = new DataAccessAdapter())
    {
        var sb = new StringBuilder();
        da.StartTransaction(IsolationLevel.ReadCommitted, "Test");
        ActionProcedures.SpOpenKey(da);
        var md = new LinqMetaData(da);
        var q = from user in md.TbUser
                select user;

        foreach (var user in q)
        {
            sb.AppendFormat("USER_ID: {0}\n", user.UserId);
            sb.AppendFormat("FIRST_NAME: {0}\n", Encoding.ASCII.GetString(user.FirstName));
            sb.AppendFormat("MIDDLE_NAME: {0}\n", Encoding.ASCII.GetString(user.MiddleName));
            sb.Append("###################################\n");
        }
        ActionProcedures.SpCloseKey(da);
        Console.Write(sb);
    }

The override of CreatePagingSelectDQ

protected override IRetrievalQuery CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo,
                IDbConnection connectionToUse, IPredicate selectFilter, long maxNumberOfItemsToReturn, ISortExpression sortClauses,
                IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
            {
                func<object, dbfunctioncall> decryptbykey = s => new dbfunctioncall("decryptbykey", new[] { s });
                var clone = selectlist[38].clone();
                                
                var functioncall = decryptbykey(clone);
                functioncall.fieldpersistenceinfos[0] = fieldspersistenceinfo[38];
                selectlist[38].expressiontoapply = functioncall;
                
                var result =  base.CreatePagingSelectDQ(selectList, fieldsPersistenceInfo, connectionToUse, selectFilter,
                                                 maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates,
                                                 groupByClause, pageNumber, pageSize);
                return result;
            }

Unfortuantely that generated the following query

SELECT 
     [LPLA_1].[USER_ID] AS [UserId],
     ...
     ...
     DecryptByKey([DB].[dbo].[Users].[FIRST_NAME]) AS [FirstName],
     [LPLA_1].[EMAIL_ADDRESS] AS [EmailAddress],
     ...
     ...
     ...
 FROM 
    [DB].[dbo].[Users] [LPLA_1] 

For those following this thread that dont know why this is a proble, I will point you to the different method being used to reference the FIRST_NAME column in comparison to the others. On SQL Server 2005 that generates the following error

The multi-part identifier "DB.dbo.Users.FIRST_NAME" could not be bound.

I did some testing and realized that onces you declare a alias for a table you cannot use its absolute multi-part identifier to reference it, you must instead use the proper defined alias.

If I comment out the code that modifies the field in index 38 of selectList in the override method displayed above, the following SQL is generated

SELECT 
     [LPLA_1].[USER_ID] AS [UserId],
     ...
     ...
     [LPLA_1].[FIRST_NAME] AS [FirstName],
     [LPLA_1].[EMAIL_ADDRESS] AS [EmailAddress],
     ...
     ...
     ...
 FROM 
    [DB].[dbo].[Users] [LPLA_1] 

What I find even funnier is inspecting the parameter list before the call to the Base class implementation shows me that none of the fields ever have anything changed on them. The only place you can ever find reference to the alias used in the query is in relationsToWalk(of Type IRelationCollection). relationsToWalk carries a property named SelectListAlias, which just so happened to have the correct alias LPLA_1 stashed away in it. I tried some basic LLBL classic queries and while they worked well with the code implemented in the override displayed above the SQL that was generated did not alias the Users table (code displayed below)

Basic LLBL Classic Query

var users = new EntityCollection<Users>();
IRelationPredicateBucket relationPredicateBucket = null; //IRelationPredicateBucket relationPredicateBucket = new RelationPredicateBucket()
da.FetchEntityCollection(users, relationPredicateBucket);

Query Generated (_Using the same Override code displayed above_)

SELECT 
     [DB].[dbo].[Users].[USER_ID] AS [UserId],
     ...
     ...
     DecryptByKey([DB].[dbo].[Users].[FIRST_NAME]) AS [FirstName],
     [DB].[dbo].[Users].[EMAIL_ADDRESS] AS [EmailAddress],
     ...
     ...
     ...
 FROM 
    [DB].[dbo].[Users]

notice I tried it with and without a relationPredicateBucket defined and they both generated the same code. Since I couldn't find away to generate the same query as Linq using classic LLBL queries I decided to lay down my stubbornness and simply set the property ObjectAlias on the field equal to relationsToWalk.SelectListAlias.

protected override IRetrievalQuery CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo,
                IDbConnection connectionToUse, IPredicate selectFilter, long maxNumberOfItemsToReturn, ISortExpression sortClauses,
                IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
            {
                func<object, dbfunctioncall> decryptbykey = s => new dbfunctioncall("decryptbykey", new[] { s });
                var clone = selectlist[38].clone();
                [b][i][u]clone.ObjectAlias = relationsToWalk.SelectListAlias;[/u][/i][/b]
                
                var functioncall = decryptbykey(clone);
                functioncall.fieldpersistenceinfos[0] = fieldspersistenceinfo[38];
                selectlist[38].expressiontoapply = functioncall;
                
                var result =  base.CreatePagingSelectDQ(selectList, fieldsPersistenceInfo, connectionToUse, selectFilter,
                                                 maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates,
                                                 groupByClause, pageNumber, pageSize);
                return result;
            }

This finally generated the following valid TSQL code

SELECT 
     [LPLA_1].[USER_ID] AS [UserId],
     ...
     ...
     DecryptByKey([LPLA_1].[FIRST_NAME]) AS [FirstName],
     [LPLA_1].[EMAIL_ADDRESS] AS [EmailAddress],
     ...
     ...
     ...
 FROM 
    [DB].[dbo].[Users] [LPLA_1] 

While I may have won this war and can begin to see a way to make encryption work in LLBLGenPro with a few minor changes to the generated code. I have a few more questions to ask.

• How do I make sure that the field I am wrapping in a call to DecryptByKey/EncryptByKey has the correct alias assigned to it at all times? After running a few tests I discovered the following

• If you do a Linq query against a single table and do a projection on a few columns. The fields in the selectList in CreatePagingSelectDQ will have been assigned their correct alias in their respective ObjectAlias property.

• If you do a Linq query against multiple tables and project a few columns from each. The fields in the selectList in CreatePagingSelectDQ will have been assigned their correct alias in their respective ObjectAlias property.
• If you do a Linq query against a single table and return all the entities it contains . The fields in the selectList in CreatePagingSelectDQ will not have been assigned an alias in their respective ObjectAlias property. Instead the generated SQL will use the alias defined in relationsToWalk.SelectListAlias property.
• Of all the classic LLBL Gen queries I tried (simple ones nothing complicated as yet since I do not have a firm understanding of the framework.) None of them ever generated any alias in either the fields ObjectAlias property or the realtionsToWalk.SelectListAlias. Can you explain why these things are true and most importantly how do I know when to use the Alias in relationsToWalk.SelectListAlias and when not to use that alias?

birchoff wrote:

I think a clone of the the IEntityFieldCore object to refer to, which is also used inside the query. So the IEntityFieldCore object in the select list to decrypt. As decryption is only necessary for the data returned. The ideal routine is the CreatePagingSelectDQ to pre-process the select list, add the expressions there. Create a clone of the field, as otherwise it's diving into a stack overflow. Set the corresponding IFieldPersistenceInfo (with the table info etc.) in the corresponding index in dbFunctionCall.FieldPersistenceInfos. No clone needed for that object, it's read only, shared data.

Fantastic I got it to work. However I needed to make a few alterations to your recommendation and I have at least one more question. First to the alterations. Like I said in my previous post I overrode CreateSelectDQ and was able to get the DQE to generate the desired query.

Ok!

I'll explain some details here about aliasing. Our query API doesn't force people to specify aliases by default: calling FetchEntityCollection doesn't require you to specify an alias for example. Only when you use multiple times the same entity in a relation, you need to specify an alias. For entity fetches, it was also (before v2.6) not possible to specify an alias for the entities fetched.

For dynamic lists/typedlists, if an alias was used in the relations, the fields in the select list, the expressions etc., everything referencing the aliased entities had to do so with the alias specified (so fields in ResultsetFields have to have their ObjectAlias set if the entity they reference is aliased).

So in short: the query elements arriving at CreateSelectDQ have either an alias set in the entityfield objects (in the case of dynamic lists/typedlists) or don't need an alias.

For v2.6, with linq, this changed a bit, as Linq forces us to create derived tables sometimes and that forced us to implement a way to alias the entities being fetched (which wasn't possible before). We didn't want to add another overload of the fetch methods to specify an alias as that would bloat the api a lot. So instead we added the alias for the entities to fetch to the RelationCollection, as all other aliases are also in the relationcollection (on the relations specified there). This is the SelectListAlias you saw.

As our Api doesn't force aliases onto developers, it has to alias things by itself if necessary, e.g. when inheritance entities are used and for example two entities with the same supertype are joined: they need to alias the supertype table otherwise it's joined just once. This aliasing is done in the relationcollection as that object is in charge of emitting the FROM clause for situations where relations are specified.

When the projection is emitted in the CreateSelectDQ, the routine checks whether the field has an alias set on the field (e.g. in a dynamic list where the developer has specified the alias) or if the RelationCollection has the SelectListAlias set and/or there's no relation specified. If you look at the line 916 in SqlServer's DynamicQueryEngine.cs, you'll see that the call to the routine which emits the name of the field (so FULL name, including target (table name/viewname or alias)) gets the output of a call to DetermineObjectAliasToUseForSelectListField(IEntityFieldCore, IRelationCollection). That routine produces the alias for the target to use. This routine itself is pretty complex, but it's abstracted away from you, so you should just call that routine for the alias and you'll be fine. So set the alias IF the field doesn't have its ObjectAlias set already otherwise simply keep that alias (as it's set by the developer/linq provider).

huge cut

protected override IRetrievalQuery CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo,
                IDbConnection connectionToUse, IPredicate selectFilter, long maxNumberOfItemsToReturn, ISortExpression sortClauses,
                IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
            {
                func<object, dbfunctioncall> decryptbykey = s => new dbfunctioncall("decryptbykey", new[] { s });
                var clone = selectlist[38].clone();
                clone.ObjectAlias = relationsToWalk.SelectListAlias;
                
                var functioncall = decryptbykey(clone);
                functioncall.fieldpersistenceinfos[0] = fieldspersistenceinfo[38];
                selectlist[38].expressiontoapply = functioncall;
                
                var result =  base.CreatePagingSelectDQ(selectList, fieldsPersistenceInfo, connectionToUse, selectFilter,
                                                 maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates,
                                                 groupByClause, pageNumber, pageSize);
                return result;
            }

So if you change that code into:

protected override IRetrievalQuery CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo,
                IDbConnection connectionToUse, IPredicate selectFilter, long maxNumberOfItemsToReturn, ISortExpression sortClauses,
                IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
{
    func<object, dbfunctioncall> decryptbykey = s => new DbFunctionCall("decryptbykey", new[] { s });
    var clone = selectList[38].clone();
    if(string.IsNullOrEmpty(clone.ObjectAlias))
    {
        clone.ObjectAlias = this.DetermineObjectAliasToUseForSelectListField(clone,  relationsToWalk);
    }
    
    var functioncall = decryptbykey(clone);
    functioncall.fieldpersistenceinfos[0] = fieldspersistenceinfo[38];
    selectList[38].expressiontoapply = functioncall;
    
    var result =  base.CreatePagingSelectDQ(selectList, fieldsPersistenceInfo, connectionToUse, selectFilter,
                                     maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates,
                                     groupByClause, pageNumber, pageSize);
    return result;
}

the object alias should be created properly in all cases. Just be sure you set the alias on the field which is used in the DbFunctionCall, as the field the expression is defined on is ignored (as it's the placeholder for the expression)

While I may have won this war and can begin to see a way to make encryption work in LLBLGenPro with a few minor changes to the generated code. I have a few more questions to ask.

• How do I make sure that the field I am wrapping in a call to DecryptByKey/EncryptByKey has the correct alias assigned to it at all times? After running a few tests I discovered the following

• If you do a Linq query against a single table and do a projection on a few columns. The fields in the selectList in CreatePagingSelectDQ will have been assigned their correct alias in their respective ObjectAlias property.

• If you do a Linq query against multiple tables and project a few columns from each. The fields in the selectList in CreatePagingSelectDQ will have been assigned their correct alias in their respective ObjectAlias property.
• If you do a Linq query against a single table and return all the entities it contains . The fields in the selectList in CreatePagingSelectDQ will not have been assigned an alias in their respective ObjectAlias property. Instead the generated SQL will use the alias defined in relationsToWalk.SelectListAlias property.
• Of all the classic LLBL Gen queries I tried (simple ones nothing complicated as yet since I do not have a firm understanding of the framework.) None of them ever generated any alias in either the fields ObjectAlias property or the realtionsToWalk.SelectListAlias. Can you explain why these things are true and most importantly how do I know when to use the Alias in relationsToWalk.SelectListAlias and when not to use that alias?

I hope these questions are answered with my brief explanation of aliases at the start of this post .

Aliasing is a tough problem, especially when inheritance is involved and no aliases are specified. For example the Employee <- Clerk / Employee <-Manager inheritance hierarchy where I have a dynamic list with Clerk.Name and Manager.ManagesDepartmentId and no aliases (as both fields are from different entities). This requires aliasing 'behind the scenes', by generating artificial aliases. Furthermore, IF there's an alias specified, e.g. Manager is aliased "M", all tables manager is mapped on (through inheritance) normally would get the alias M, but that's not going to work, so they're sub-aliased. The fields, which refer to 'M' for example, then get the right table alias assigned to them based on the entity they're actualy defined in (e.g. a supertype). As you can see, this gets complex pretty quickly, and I have rewritten this system 2 times now, and it finally works OK in v2.6. The main problem is that the developer doesn't have to specify an alias.

Awesome fantasticness..... I now have what I think is a reliable way to decrypt on the DB side. Now all I need to do is put together the Encrypt part which wont be too hard. I hooked into the CreateSingleTargetInsertDQ method and realized I can search for the parameter of the field to be encrytped in the command text (located in IActionQuery, the value returned from CreateSingleTargeetInsertDQ) and replace it with EncryptByKey(<field_parameter_name>). The only thing left to do is implement a type converter that takes the binary emitted from the decrypted column and converts it to what ever type is need. Since I am working with the demo which doesn't seem to have the SDK to look at type converter stuff I am hoping I can do multiple type converters that take varbinary to any type I want.

The only question I have now is, can I derive a new fieldpersistenceinfo type where I can set a flag on it to say is encrypted. that would make the code to figure out which fields need reprocessing a lot more simple and sane.

birchoff wrote:

Awesome fantasticness..... I now have what I think is a reliable way to decrypt on the DB side. Now all I need to do is put together the Encrypt part which wont be too hard. I hooked into the CreateSingleTargetInsertDQ method and realized I can search for the parameter of the field to be encrytped in the command text (located in IActionQuery, the value returned from CreateSingleTargeetInsertDQ) and replace it with EncryptByKey(<field_parameter_name>). The only thing left to do is implement a type converter that takes the binary emitted from the decrypted column and converts it to what ever type is need. Since I am working with the demo which doesn't seem to have the SDK to look at type converter stuff I am hoping I can do multiple type converters that take varbinary to any type I want.

See: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=83293&ThreadID=14940 for an example of creating a type converter (it's a few lines of code as you can see. This type converter converts from Decimal typed values in the DB to Int32 in the entity and back.

The only question I have now is, can I derive a new fieldpersistenceinfo type where I can set a flag on it to say is encrypted. that would make the code to figure out which fields need reprocessing a lot more simple and sane.

I follow your thinking, but the fieldpersistenceinfo object is used as a static/readonly object, and is created inside the PersistenceInfoProviderbase when the application starts, so it's not really possible to change that to use a subclass for FieldPersistenceInfo.

What you could use perhaps is a lookup dictionary in your DQE subclass, based on tablename/fieldname.

yeah thats what my current implementation utilizes... a dictionary where the keys are the table names with encrypted keys and the value is a list of column names which need to be decrypted/encrypted.

Since this is so trivial to do at leas in the case of SQL Server 2005 using a Symmetric Key or Pass phrase. What other road blocks are there to get this actually supported in LLBL Gen Pro.

I can see a basic architecture where you simply annotate in the designer what tables and columns contain encrypted information. Then all the DQE needs to do is generate the correct db Function Call, returning varbinary from the DB. If the user wants they can use a type converter to go from varbinary to whatever type they need. Does this seem as easy as it looks for you to do Or am I suffering from a lack of understanding of the requirements and constraints of the code base.

P.S. For those following the thread if you use SQL Server 2005's PassPhrase for encryption/decryption duties you do not need to use a transaction to wrap your query with a call to stored procedures to open and close a key. Instead all you need to do is set expressions on your encrypted fields that call DecryptByPassPhrase(<pass_phrase>, <column_name>) (as for encrypting you do something similar you just call the encrypt version instead when inserting and updating). Check out the thread I referred to in the first paragraph of my 5th post on this thread. It has more info on using DecryptByPassPhrase/EncryptByPassPhrase.

birchoff wrote:

yeah thats what my current implementation utilizes... a dictionary where the keys are the table names with encrypted keys and the value is a list of column names which need to be decrypted/encrypted.

Since this is so trivial to do at leas in the case of SQL Server 2005 using a Symmetric Key or Pass phrase. What other road blocks are there to get this actually supported in LLBL Gen Pro.

I can see a basic architecture where you simply annotate in the designer what tables and columns contain encrypted information. Then all the DQE needs to do is generate the correct db Function Call, returning varbinary from the DB. If the user wants they can use a type converter to go from varbinary to whatever type they need. Does this seem as easy as it looks for you to do Or am I suffering from a lack of understanding of the requirements and constraints of the code base.

The current design of the DQE's is that they get data pushed in and act accordingly, and have no way of obtaining other data on their behalf, so there's no way a DQE can obtain for example annotation data for a given field if that data isn't passed to it.

The main thing is that the annotation data is something which is doable, (it's actually already implemented: custom properties inside the designer), but the meaning the data has inside the DQE is different. So what you really want is some kind of callback system which is pluggable into a DQE at runtime and which takes care of which fields are to be encrypted and how, or other things one want to execute at the last minute. I'll see if we can look into adding such a system to v3, but it depends if we have time for this as our plate is already very full..

Thanks for your feedback and time on this.