Column Encryption

[upswing]

Hi,

I need help encrypting a single column in a database table. I would like to be able to use within a linq query a property that will contain the decrypted value of a column stored in a database table.


Here is what I have done ...

I have extended a partial class named Login generated by the entity and added a property called UnencryptedPassword

public partial class Login
{
public String UnencryptedPassword
{
get
{
return Cryptography.DecryptTableColumn(this.Password);
}

set
{
this.Password= Cryptography.EncryptTableColumn(value);
}
}
}

Since the datatable does not contain a table property named UnencryptedPassword I have not specified a mapping.. (//[Devart.Data.Linq.Mapping.Column(Name = @"""UnencryptedPassword """)] )

Because I do not specify a mapping the following query compiles but generates an exception when executing..
"Cannot translate a member that is not mapped." ..


int RecordsCount = (from c in context.Logins
where ((c.UnencryptedPassword == Password) && (c.UserName== UserName))
select c).Count();

Since I do not want to use a for loop, What do I need to do to create the proper linq query syntax?

Thank you

[upswing]

I have tried the following as well

public static Expression<Func<Login, string>> DecryptLoginPasswordColumn
{
get { return p => Cryptography.DecryptTableColumn(p.Password); }
}

and

// What mapping here?
public String UnencryptedPassword
{
get
{
return Login.DecryptLoginPasswordColumn.Compile()(this);
}

set
{
this.Password = Cryptography.EncryptTableColumn(value);
}
}

What is the mapping that I need to specify since the property does not have a column in the table?

[MariiaI]

"Cannot translate a member that is not mapped." ..

This is an expected error. Generally, unmapped properties cannot be used in server-side LINQ queries, as LinqConnect cannot translate unmapped properties into SQL. Of course, you can use this property at the client side after the entities are loaded.

I need help encrypting a single column in a database table. I would like to be able to use within a linq query a property that will contain the decrypted value of a column stored in a database table.

Unfortunately, it is impossible to implement such scenario so that all operations are performed both on the client and server sides.

There are two ways for implementation of your scenario:
1) Make all the necessary operations on the client side
- first you should retrieve all data from the necessary database table, e.g. via the ToList() method
- then filter data using the necessary conditions with the decrypted values
- perform other operations according to your scenario, etc.

2) Make all the necessary operations on the server side. For example, you can try using updatable views, stored procedures, etc..
Some useful information: http://www.postgresql.org/docs/9.3/stat ... eview.html.