Adapter / v.2.0 / SQL Server 2005

Hi,

In an application I have to encrypt a field in the database. I have to use the SQL Server 05 internal functions decryptByPassPhrase and encryptByPassPhrase because other applications access the db in another way (not via LLBLGen). So I can't do the encryption in the business layer.

The encryption functions expect a passphrase that needs to be passed by the application.

I have no idea how I can solve that with LLBLGen. Any hints? Sure I could use a stored procedure to load and save the data. But as there are no other stored procedures in the database I don't like that solution.

Thanks for your help!

Cheers.

I have to use the SQL Server 05 internal functions decryptByPassPhrase and encryptByPassPhrase

Please try using DBFunctionCalls. (ref: LLBLGen Pro manual "Using the generated code -> Calling a database function")

Hi,

Great! I didn't know that feature. That's what I'm looking for. I think it should work to read the data.

How can I write the data back to the db and encrypt it there? Any hints or ideas?

Thanks!

A DBFunction call can be used as an Expression, knowing that you may check the Expressions in entity updates section in the manual, at the following path: "Using the generated code -> Field expressions and aggregates"

Oh yes, was a silly question, sorry. That should work!

Thanks a lot!

Hi,

In the meantime I implemented what I asked above. It seems to work pretty code. Maybe others can use my implementation as sample good. Here it is: (Data structure: Table EmployeeSalary with the fields Id, EmployeeId and EncryptedNetSalary)

Decrypting data

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);

Encrypting data

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

adapter.UpdateEntitiesDirectly(employeeSalary, null);

LLBLGen generates the following SQL statement to encrypt the data:

exec sp_executesql 
N'UPDATE [dbo].[EmployeeSalary] SET 
[encryptedNetSalary]=encryptByPassPhrase(@LO6c85ac7f1, @LOa0b51cf82)',

N'@LO6c85ac7f1 nvarchar(7),
@LOa0b51cf82 nvarchar(9)',
@LO6c85ac7f1=N'password',
@LOa0b51cf82=N'textToEncrypt'

Reading the data on SQL Server

SELECT *, netsalary = convert(nvarchar(10), decryptbypassphrase(N'password', encryptedNetSalary))

Writing the data on SQL Server

INSERT INTO EmployeeSalary (employeeId, encryptedNetSalary) Values (100557, EncryptByPassPhrase(N'password', N'my salary'))

Always use NVARCHAR for all operations on SQL Server. I had some problems with that because for the encryption functions it matters if you use nvarchar or varchar.

Have fun!

Cheers.