Encrypted columns with Entity Framework

views:

answers:

Encrypted columns with Entity Framework

Anyone figured out a good way to pull encrypted values from db through entity framework 4?

I got a MySql db with some columns encrypted with des_encrypt and need to be able to get those values as easy as possible, and also of course, update and insert them.

I think it's quite strange there doesn't seem to be in built support for this in EF. Even our own built ORM-system have support for this. We just add a comment "encrypted" for each field thats encrypted and the ORM tool will add des_decrypt(column) and des_encrypt(column) in the queries.

Anyone?

IMO you should encrypt before putting it into the database and store it as binary data. Then you can easily get the byte[] with EF.

EDIT: What if you used a stored procedure to do all the des_encrypt and des_decrypt as well as the selects/inserts/deletes for you. Then EF will still do the mapping for you?

TheCloudlessSky 2010-07-21 13:19:36

Seems like a good solution and I would probably go with that if I created a new database.The thing is, the database is pretty large and is used by tons of projects so it would be a huge job to go over the code and change this.

Andreas 2010-07-22 06:53:10

@Andreas - See my edit above.

TheCloudlessSky 2010-07-22 12:02:59

Thanks for the suggestion.This looked like a really good idea and I tried it out.unfortunately if I want to be able to do LINQ queries on all of my decrypted data from the table, the stored procedure must first execute. This takes forever since it's 250 000+ rows with 5 columns each to be decrypted.So doing this: context.AllMembers().Where(x=>x.MemberId == 1) will take too long.Sure i could do a SP that takes an argument of memberid, but what if i want to search on e.g. firstname with LINQ?Maybe im missing something important here...

Andreas 2010-07-23 08:46:29

@Andreas - From what I understand ... `MemberId` is encrypted? Shouldn't you only need to decrypt columns that are encrypted (like a password... even though you should use a one-way hash)? If `MemberId` is *not* encrypted, you don't need to decrypt it to do a 'SELECT'. Is anyone else using this table? Why don't you decrypt it all and create a new table for the non-encrypted data?

TheCloudlessSky 2010-07-23 11:49:03

No, everything BUT the memberid is encrypted (it's the primary key). We decrypt things like social security number, names and addresses because it's sensitive data for us. The password is hashed, so no problem there.This table is used alot, but we really don't want another version of it which is decrypted since that would work against thea very idea of having the things encrypted. Sorry for my late answer, vacation... :)

Andreas 2010-08-16 06:46:03

ansaurus

tags:

views:

answers:

Encrypted columns with Entity Framework

related questions