I got closed on SO and told to post this here as it's about general application design as opposed to specific code.
I'm currently working on a project which involves the daily extraction of data (pharmacy records) from a VisualFox Pro database, and uploading some of it to a WordPress site, where clients of the pharmacy can securely view it. I would like some advice in terms of the general methodology of my software - I am able to code it, but need to know if I'm going the right way about it. I'm writing both the PC software (in C#/.NET 4.5) and the PHP WordPress plugin. (It doesn't matter much that it's in WordPress, the actual code for this will pretty much run separately).
Question 1: Encryption
The current process for encrypting the data server-side I plan to use is based on this article. Summarised, it advocates encrypting each separate user's data asymmetrically with their own public key, stored on the server. The private key to decrypt this data is then itself encrypted symmetrically using the user's password, and stored. This way, even if the database is stolen, the user's password hash needs to be broken, and even then the process needs to be repeated for every user's data.
The only weakness, pointed out by the author himself, and the main point of my question, is the fact that while the user is logged in, the decrypted key is stored in session storage. The way the article suggests to deal with it is to just limit the time the user is logged in. I thought a better solution would be to store that key in a short-lived secure cookie (of course the whole process is happening over HTTPS). That way, if the attacker has control of the user's computer and can read their cookies, they can probably just keylog their password and log in, no need to steal the database, while even if the attacker gains access to the server, they cannot decrypt the HTTPS traffic (or can they? I'm not sure.)
Should I use secure cookies or session storage to temporarily store the decrypted key?
Question 2: Storage
The second thing I still want to work out is how to store the data - this is more of an efficiency problem. Since every user has their own key for encryption, it follows that records for every user must be stored separately. I don't know if I should store a "block" of data for every user, containing encrypted JSON with an array of objects representing records, or whether I should store records in a table with the actual data structure, and encrypt each data field separately with the key.
I am leaning towards storing the data as one block - it seems to me to be more efficient to decrypt one big block of data at a time, than perhaps several thousands separate fields. Also, even if I stored the data in its proper structure, I still wouldn't be able to use MySQL's WHERE, ORDERBY etc, since the data would all be BLOBs.
Should I store the data as a big block per user, or separated into the different fields?
Question 3: Transfer
I extract the data from the DBF file, and essentially make a "diff", whereby I compare the current extracted data from the last day's data, and only upload the blocks of the users that have changed (I can't only upload the records, as I probably will end up storing the users' data in blocks). I also include "delete" instructions for users which have been deleted. This is as there are hundreds of thousands records in the database, totalling over 200mb, and the size increases every day.
My current plan is to write all this data to a JSON file, gzip it and upload it to the server. My question is, how do I do that while ensuring the security of the data? Naturally, the upload will happen over HTTPS, and I have an API password in place to only allow authorised uploads, but my main concern is how to protect the data if the server is compromised. I don't want the attacker to just grab the JSON file from the server while it's being processed. One idea I had was to get the server to send me a list of public keys for the users, and perform the encryption in my software, before the upload. It seems to me like that's the only way of protecting that data. I could encrypt the whole JSON file, perhaps with an API key or a special password, but that's moot if the attacker can just access the decrypted file as it's being processed on the server. Is that a good solution?
Should I encrypt the data individually client-side, or is there a way to securely transfer it to the server and encrypt it there?
Thanks in advance for any answers, I'd love to hear from someone who's dealt with problems like this before.
EDIT: I'm pretty decided on 1 and 2 - thanks @Morons. I'll use secure cookies and store data in blocks. My main question is now question 3, would love to get some input on that.
VisualFox Prothe company WILL NEVER be able to migrate to a x64 operating system right? As for the answer to question 3...Just use HTTPS all the time, the little performance decrease you will actually experience will be countered acted by the fact ALL your traffic to the client is secure. Of course there is also the fact you shouldn't be storing ANY PII in an unecrypted format EVER.