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.

  • Is the FoxPro database encrypted? If it is not, then you are in legal trouble already if you are in the US. Before beginning this project please get some formal training on the requirements of HIPAA.
    – HLGEM
    Commented Feb 21, 2013 at 22:00
  • I'm not in the US. I am UK-based, and doing this project for an Australian pharmacy. From what I understand, they get their clients to sign an opt-in form for this service - and only those users' data gets uploaded onto the web server. Also, the software is running on the same LAN as the pharmacy software - there is no moving the database about on the internet. Commented Feb 21, 2013 at 22:02
  • 1
    @MattJadczak - You do understand that by using VisualFox Pro the 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.
    – Ramhound
    Commented Feb 22, 2013 at 12:42
  • @Ramhound I don't make the pharmacy/point of sale system, it's an industry standard one that happens to use this format as its storage format. And I'm actually reading it using an x64 executable on an x64 system, so I'm not sure what you mean. The pharmacy's system stores the records in an unencrypted DBF file - I've no control over that. My software only reads that file. I should have been clearer - my question is whether or not I should send already encrypted data through the HTTPS connection, that the server cannot decrypt without the user's password. Commented Feb 22, 2013 at 17:59
  • @MattJadczak - There is not a single version of VisualFox Pro that supports 64-bit processe. This fact is well documented.
    – Ramhound
    Commented Feb 22, 2013 at 18:37

2 Answers 2


I hope this isn't too blunt, but the task you are undertaking here is extremely difficult, and the odds of you getting it right are slim. Security flaws are most of the time caused by mistakes in implementation, not in the underlying technologies. In order to make a system like the one you've described secure, you have to use the correct tools and the correct methodology and account for all of the edge cases or the security of the entire system will be compromised.

That's not really a helpful answer though, is it? When you are building a system like you are building the question you should be asking shouldn't be "How do I do this?" It should instead be "What is the way I can do this that relies the least on myself?" The answer to that question is to use tried and tested systems wherever possible, and to roll your own solutions only as a last resort.

To answer your first point about encryption, it doesn't make sense to worry too much about securing a key in memory of the server. If an attacker has enough access to a machine to read your keys out of memory, you are totally and completely hosed and any solutions that you have coded up aren't going to help much any way. In other words, favor securing data at rest and data that is moving over the internet, since that is where most attacks are going to occur.

As far as storing the data goes, I don't see any reason why asymmetric crypto needs to be involved here. I would use something like PBKDF2 to derive a key directly from the user's password, then encrypt the data and store the encrypted blob in a database. I would recommend a database over a flat file because managing a folder full of flat files is tedious at the best of times. Databases may not show any solid benifits in speed or security over flat files, but they come with many other features such as pooled connections and they also make backing up data much easier than flat files. Use the simplest system you can to minimize your attack surface, and use thoroughly tested open source tools whenever possible. If you can find a way to use GPG for the encryption and key derivation part of things, I would recommend it.

As far as transfer goes, I believe that you are thinking about things the wrong way. Don't do any encryption client side. Browser javascript is not suitable for cryptography, as explained in this article. So long as you make sure that you use TLS/SSL for all connections to your site, you shouldn't need to worry about transmitting data unencrypted. For an example of why it is hard to do client side encryption, do some googling about the security of MegaUpload's successor, MEGA.

Finally, I wouldn't trust any one dude you get an answer from on the internet, including myself. I would do a lot of research about this sort of thing before committing to a solution. Also, I might recommend asking this question over at the IT Security Stack Exchange.

-- EDIT --

Somehow, I totally missed the fact that there are three parts to your system, the client (browser), the server (database), and the connector that imports data from the VisualFox Database. This actually makes the whole system a lot more complex, because there are essentially three parties that need to share a secret, instead of two. What I would recommend is not to encrypt the data based on the users password, but to instead encrypt it based on some server password. I'm having a little bit of trouble thinking of a good way to describe this process, so I'll give you an example workflow instead.

Server Side

  1. Admin starts server.
  2. During start up, server code asks for a password.
  3. Server uses PBKDF2 to derive a key which is stored only in memory.
  4. Server spawns a thread that will poll the VirtualFox Pro server every X (days/hours/minutes) for updated data.
  5. Server enters loop awaiting requests from browser clients.

Updating database

  1. Main Server's child thread requests an update of data from the Virtual Fox Pro server.
  2. VirtualFox Pro server dumps a report containing data for client's with modified entries.
  3. VirtualFox Pro server opens secure connection to main server (ssh, sftp, etc) and transmits zipped data.
  4. One by one, the main server uses the PBKDF2 derived key that is stored in memory to decrypt blobs stored in a database, update them with new data, reencrypt them, and store them back into the database. This process should all happen in-memory.

Browser client connects

  1. Main server receives https request from client.
  2. Main server uses some third party authentication framework to check clients credentials. This framework should use bcrypt to hash passwords and only store the hashes on the file system.
  3. If the authentication framework positively identifies a user, the main server will decrypt the user's blob using the PBKDF2 derived key in memory and send the data to the user.
  4. When the user's authentication cookie expires, the main server will stop using the PBKDF2 derived key to decrypt data, and will instead prompt the user to re-authenticate.

This model is more in line with how traditional websites work (which means that you can rely on third party, bug tested frameworks), but data is encrypted/decrypted in memory before touching the database. Ideally, you could use GPG or some other keystore for managing the encryption keys on the main server as well.

  • Maybe a bit of clarification about "client-side" - I have a C# application that once daily extracts appropriate data from the pharmacy/point of sale software and uploads it to the server - this isn't in the browser. That's where I think it would be best to do my encryption. There is no data provided by the user through the browser in this system - they can only view their records. My main concern about encrypting it server-side is that maybe I am hosed if they can access the server's memory, but a simple read-only access could grab the unencrypted json file being uploaded. Commented Feb 21, 2013 at 23:37
  • 1
    Whoops, I missed that part of the question when I read it the first time. I've updated my answer to include some more suggestions. Commented Feb 22, 2013 at 0:32

Honestly if you are are storing the data in encrypted blocks what do you need DB for? (You are right is saying that if you encrypt the entire record it can't be queried in any way)

I would encrypt the File locally in VFP, the upload it encrypted (using the users password), one file per user. I wouldn't even deal with Diffs, I would just upload the entire data set every day. (If data sizes permit)

The have the Word Press access and Decryption that file directly. (Think of the File as the Code Block)

I don't understand what you are trying to do with the cookies, are you going to time out the cookie while the user is logged in? Then the user will looses all access to his data while he is logged in..

  • Well, if I'll go with the block system, pulling the record out of DB by a primary key would be faster than searching a directory of thousands of files. As for the cookies, I'm thinking of re-setting the cookies on each page view, with a eg 10min expiry, so that after 10min inactivity a re-login is required. I need to store the decrypted key somewhere, else the user would have to enter their password on each page view. Commented Feb 21, 2013 at 22:17
  • To get the pk dosen't the user first have to click on the record he wants.. that means you need to first show him a record set... that means either displaying all the data or querying (reading) all the data.. that means decrypting all the data....
    – Morons
    Commented Feb 21, 2013 at 22:44
  • Yes, all the data for the same user... What I wanted was a MySQL table with user id, data block for every user. Commented Feb 21, 2013 at 22:46
  • 1
    Well, the main requirement is data security, speed comes second. The main "scaling factor" here will be user number - each user will only have 100-200 records, so decrypting that data block shouldn't be too bad. As for user number, that will hopefully scale with the MySQL, and if it goes over a few thousand, I guess I'll look at more efficient solutions then. Commented Feb 21, 2013 at 22:50
  • 2
    Al relational Dbs, mysql included a optimized to pull data by use of indexes.. I need to blog about this.. it comes up way too often
    – Morons
    Commented Feb 21, 2013 at 23:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.