We are using SQL Encryption and decryption for a field, where the field (THB_File) is declared as varbinary(max) . The encryption is done using symmetric key. I have managed to get decrypted field information back and save encrypted information into the field in LLBLGen. Here are sql queries that I am seeking to achieve:
Insert encrypted data:
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
INSERT INTO Thumbnail(THB_ID, THB_File)
VALUES (2,ENCRYPTBYKEY(KEY_GUID(N'SymmetricKey1'), 'FileName'))
CLOSE SYMMETRIC KEY SymmetricKey1;
Fetch decrypted data:
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1
SELECT THB_ID, CONVERT(VARCHAR,DecryptByKey(THB_File)) as 'Decrypted Col2'
FROM dbo.Thumbnail
WHERE THB_ID = 1
Close SYMMETRIC KEY SymmetricKey1
Here are my LLBLGen implementations:
Insert encrypted data:
ThumbnailEntity e = new ThumbnailEntity();
e.Fields[(int)ThumbnailFieldIndex.ThbFile].ExpressionToApply =
new DbFunctionCall("EncryptByKey(KEY_GUID('SymmetricKey1'),convert(varchar,{0}))", new object[] { "fileName" });
UnitOfWork.AddForSave(e);
using (var adapter = new DataAccessAdapter(true))
{
adapter.ExecuteSQL("OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1");
UnitOfWork.Commit(adapter);
adapter.ExecuteSQL("CLOSE SYMMETRIC KEY SymmetricKey1");
}
Fetch Decrypted data:
var decryptedField = new EntityField2("DecryptedColumn", new DbFunctionCall("CAST({0} as varchar(200))",
new object[]
{
new DbFunctionCall("DecryptByKey", new object[] { ThumbnailFields.ThbFile })
}));
var queryFactory = new QueryFactory();
var queryGetCrewEventType = queryFactory.Thumbnail.Where(ThumbnailFields.ThbId == 1)
.Select(() => new Thumbnail()
{
Identifier = ThumbnailFields.ThbId.ToValue<string>(),
File = decryptedField.ToValue<string>(),
});
List<Thumbnail> response;
using (var adapter = new DataAccessAdapter(true))
{
adapter.ExecuteSQL("OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1");
response = adapter.FetchQuery(queryGetCrewEventType);
adapter.ExecuteSQL("CLOSE SYMMETRIC KEY SymmetricKey1");
adapter.CloseConnection();
adapter.Dispose();
}
As you can see that I have kept the DataAccessAdapter(true) and managed to use ExecuteSQL to Open and close symmetric keys.
I want to know if there is any better approach to achieve encryption and decryption using symmetric keys. I would also appreciate some pointers on making my LLBLGen implementation better.