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
- Admin starts server.
- During start up, server code asks for a password.
- Server uses PBKDF2 to derive a key which is stored only in memory.
- Server spawns a thread that will poll the VirtualFox Pro server every X (days/hours/minutes) for updated data.
- Server enters loop awaiting requests from browser clients.
Updating database
- Main Server's child thread requests an update of data from the Virtual Fox Pro server.
- VirtualFox Pro server dumps a report containing data for client's with modified entries.
- VirtualFox Pro server opens secure connection to main server (ssh, sftp, etc) and transmits zipped data.
- 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
- Main server receives https request from client.
- 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.
- 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.
- 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.
According to MySQL, AES encryption (Advanced Encryption Standard) is the best method available for providing reversible encryption and decryption in SQL.
Formerly known as Rijndael, the AES_ENCRYPT and AES_DECRYPT functions are now built-in to MySQL so you can take user data, encrypt it with a salt, store it in your database, then extract it again later and decrypt it.
Define your salt
You'll need to apply a salt to the data that you encrypt. This is a special code that the encryption algorithm uses which works a bit like a key.
You'll need to provide the exact same key back to decrypt the data, and if an attacker should gain access to your database, they won't be able to decipher it without knowing the salt.
If you define your salt in PHP like this, you'll be able to pull the constant into your SQL statements more easily.
if(!define('SALT')) define('SALT','897sdn9j98u98jk');
To insert data into your MySQL database and encrypt the sensitive information, you'll need to issue a command like this, along with your salt.
INSERT INTO your_table (username,email,shoe size) VALUES ('$username', AES_ENCRYPT('$email','".SALT."'), AES_ENCRYPT('$shoesize','".SALT."'));
This will insert the username in plain text, as it's non-sensitive, but encrypt the user's email and shoesize, to prevent them from being viewed without access to the salt.
At some point, you're going to need to access some of the data you stored in its encrypted form, and you can do this very easily using the AES_DECRYPT function of MySQL and the same salt you used when you encrypted the data and inserted it.
SELECT username, AES_DECRYPT('email','".SALT."') AS email,
AES_DECRYPT('shoesize','".SALT."') AS shoesize FROM your_table WHERE username ='fred';
If you SELECT the encrypted data without running it through AES_DECRYPT or with the wrong or no salt, you'll get an ugly, unreadable string of odd characters. This means if an attacker manages to access your database, but does not have access to your server to view the salt, they won't be able to read any of the data you've stored. At least, not without going to great lengths to try and decrypt the data.
Updating encrypted records is very similar to insertion. Basically, you just apply the same salt and re-issue the AES_ENCRYPT command to re-encrypt the data again and lock it away safely.
UPDATE your_table SET email = AES_ENCRYPT('$email','".SALT."'), shoesize = AES_ENCRYPT('$shoesize','".SALT."') WHERE username= 'fred';
Searching encrypted data using both AES_ENCRYPT and AES_DECRYPT
Things get a little bit more complicated when you need to search for data that's encrypted and then display it in its unencrypted form.
Say you wanted to search for a user using their email address, but you'd encrypted that in the database. First, you'd need to encrypt the email address you want to search for with AES_ENCRYPT and your salt, and then you'd need to use AES_DECRYPT to ensure that MySQL decrypted it, returning it in a readable format.
You can achieve this, using code a bit like this:
SELECT user_username,
AES_DECRYPT(email,'".SALT."') AS email,
AES_DECRYPT(shoesize,'".SALT."') AS shoesize
FROM your_table WHERE
(email = AES_ENCRYPT('$q','".SALT."'));
For further informations, please see this link: http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html
Best Answer
Yes. You need two things:
modify your existing table structure so that all fields that you want encrypted are appropriately altered in size and type. For example a
VARCHAR(50)would becomeVARBINARY(64)in order to employ AES algorithm, that uses a code block of 128 bits (16 bytes) and would output blocks of multiples of 16 bytes.then you add a mapping table for each entity which you need to encrypt. For example you want to encrypt fields in tables
UserandReport, and want them independent; you add two tables User_Map and Report_Map. If Reports belong to Users as a one-to-many relationship, and access to User 12345 will therefore grant access to its Reports, you only need User_Map (any Report will use the secret key of its User). And so on.The mapping table holds three columns: Accessor, Accessed, and Key. Accessor identifies whoever is accessing the data (it could be a
user_idfor example). Accessed identifies the entity which is being accessed (user_idorreport_id). Finallykeyis a cryptographically strong random value associated with the Accessed resource instance at the moment of its creation, encrypted with Accessor's password.All data in the mapped resource is encrypted using that random value, which will never be disclosed and will never be changed; changing it, while possible, would be really awkward.
So if you have five Books in your table, each of those will have a random value of its own, that exists nowhere in the clear.
The operations you need to support are:
user_id = Accessor_id AND resource_id = XYZwill yield XYZ's encrypted key. Decrypt it with the password. The key thus obtained will allow decryption/reencryption of XYZ's data.Multiple Accessors can access the same Resource:
update Accessor's password: you need to decrypt all Key values that are encrypted with the old password and reencrypt them with the new one:
UPDATE Users_Map SET CKey = ENCRYPT(DECRYPT(CKey, :OldPassword), :NewPassword) WHERE Accessor_id = :MyId;
delete Accessor's credentials: just kill all tuples with that Accessor_id (note: if all Accessors with access to a given Accessed are eliminated, the Accessed's data will become unavailable. One user (e.g. root) should have access and not be deletable).
Some operations you might want to support:
access all data accessible to Accessor in a hierarchical way (i.e. you are Accessor's supervisor): that's tricky. You need to encrypt and maintain Accessor's password, which you can only do if Supervisor is logged in. To provide for this, you can supply a asymmetric encryption scheme whereby if user A is supervised by user B, then the t-uple (B_id, A_id, (A's private key encrypted with B's public key)) is available in the hierarchic database. This way, it is always possible for a supervisor to access his people's private keys, and they can update them without needing the supervisor's intervention.
granting rights to a Resource to a user also requires using asymmetric ciphers, because while user A is in possession of the resource instance's unique key, s/he can neither send it in the clear to user B (it would permanently disclose that resource instance's plaintext for everyone), nor can s/he encrypt it with B's password since it is known only to B. The solution is encrypt the instance key with user B's public key (which can be known), while the private key remains encrypted.
Since sending the private key might be difficult, being a couple of kilobytes, you could generate a public/private key pair and encrypt the private key symmetrically using a BCrypt hash of the user's password. When the user logs in, his private key becomes accessible to him or her, and can be used to unlock all asymmetrically encrypted information, if there is any (if you don't need grants and hierarchies, there might be no need).
Performances and limitations
The performance impact is not too large, if we consider that encrypted data are used mainly upon receipt or sending data to a user (which are slow operations in themselves). But this introduces the main limitation - encrypted data cannot be easily used in the system.
For example, straight search is either impossible or very slow: techniques for encrypted searching exist, but with this scheme, looking for records containing "Hello world" would require changing something like
to
Security
The database contains no plaintext keys or passwords, so capturing a database would not compromise data security. Standard caveats apply: user passwords must not be guessable and must be properly stored and checked (bcrypt is good for that). Accessing a user's password only allows accessing the data that user is granted read rights to. Possession of a currently valid password and physical access to database would allow recovering the accessible data not only in the present but in the future (provided physical access to the database is secured again).
Example: Eve steals a copy of the database. She is unable to read anything since she has no passwords. She manages to acquire Alice's password and is thus able to decrypt Bob's record, that Alice has access to. Alice changes her password, and updates Bob's record; after Dane is added to her people, Eve steals a fresh copy of the database. She can still read Bob's updated record (she has Bob's key from old database), but not Dane's (Dane's key is encrypted with Alice's new password, that Eve lacks).
To "re-secure" a record with N managers it is necessary to change its encryption key, which requires updating all N extant mapping records for that record; which requires access to all N passwords at the same time.