I'm working on a piece of software to store and manage records for a youth group. They've found that a lot of the time they spend on record-keeping and admin is taken up by scanning through looking for various conditions, like "is older than X years and has Y qualification" or "has been a member for X years and has done Y event more than twice", or "has done events X, Y and Z and therefore needs award A". As a programmer, I recognised that this sort of thing could very easily be handled by a computer, thus saving them hours of work, so I'm building an application to take care of it.

Up until now, their records have been kept on paper, or in Excel spreadsheets, so it could be argued that I don't need to encrypt anything because they weren't encrypted before, but I don't feel entirely comfortable with writing a program that stores information on underage kids (ages 14-18, roughly) without having some way of controlling who can get at that information.

I've never really done encryption before, so I'm not really sure where to start here. I've implemented numerous password management schemes, so I'm familiar with some of the principles, but that uses nonces and one-way encryption methods like hashes, none of which can be used here because I need to be able to get the data back again. The best I can think of is to use a password (plus salt) in plaintext as the key to encrypt in some two-way scheme, but then only store the hash of that password to verify on login. The password would then be stored in memory while the program is running, though, and I'd like to reduce the amount of time where the password is anywhere other than the user's memory.

I understand that putting this data anywhere other than a server that I can control increases the risk, but I need this data to be accessible offline. Can anybody explain a solid way to store this data?

  • 1
    When dealing with cryptography, it's best to try to avoid writing anything directly yourself as much as possible, preferring to opt for pre-existing tools whenever you can. What language/environment are you using?
    – Kitsune
    Apr 20, 2013 at 15:09
  • The app is in C#, using WPF, and will be run on Windows 7 only (for now, at least). I'm looking at a variety of things to store the data in, ranging from local compact SQL databases to simple XML files. Apr 20, 2013 at 15:59
  • I agree with Kitsune.. Don't implement your own crypto. It's too easy to make mistakes, you're a novice, and there are too many high-quality and free implementations out there to justify DIY crypto. Your best bet is whole disk encryption. If you need free, look at True Crypt. Apr 21, 2013 at 12:38
  • Oh yeah - I know just enough crypto to know that I should NOT be implementing a system from scratch. I don't think full-disk encryption is an option here; the computers in question aren't part of the system I control. I'm just writing software which will be distributed to the various people who run the group, some of whom are volunteers, and all of whom are technophobes to some degree. I really can't distribute this software alongside an instruction to TrueCrypt their hard drives. Apr 21, 2013 at 20:41

2 Answers 2


You can resort to a MySQL database, to leverage its cryptography support.

Then you have two options (actually you can employ both):

1) Some data can be left in plaintext, but anonymized - i.e., there's no way of knowing who the data refer to. 2) Other data are encrypted, using a symmetric algorithm. The key to do so will be random, and encrypted using the user's password. This way, several users can access the same data

User    Password   CryptoKey
alice   <hash1>    <encrypt(pwd1, RANDOM)>
bob     <hash2>    <encrypt(pwd2, RANDOM)>

To authenticate, Alice sends along her password, which is checked with the stored hash. If it is valid, the RANDOM key is extracted and stored in the server's memory for the duration of the session.

Text and BLOB fields can be then encrypted (with or without salting). Short fields or fields with low cardinality are better salted, otherwise the contents can be easily guessed: for example a field that either contains 'Male' or 'Female', after unsalted encryption might contain 'e3290d6cd47a701168b962aa0c6b249e' or 'd87d344d4808900421c93de58dd7557a' - but that's still two values only, which is a potential vulnerability.

Changing Alice's password is no great deal; of course, if RANDOM gets exposed, that's a big problem. Communications with the front-end should go through SSL/TLS and contain RANDOM-decrypted (but SSL-encrypted) data, thereby protecting the security of RANDOM, which never leaves the server.

Needless to say, physical access to the server's memory will compromise everything, since it would be easy to perform a man-in-the-middle attack and trick a valid user into revealing his password, from which RANDOM can then be obtained and the data store decrypted.

Optionally, you can encrypt also the connection to the database, but that seems a bit like overkill (they'll probably be local anyway) and still won't protect you against the server being exploited.

The data can be synchronized between a local and a remote MySQL database without knowing the password, either by downloading a database dump (which is viable if the data size is small; remember that encrypted data is not compressible) or by keeping a registry of updates, which is much more difficult (think 'Alice on her laptop modifies a record while Bob on his desktop deletes that same record, and Charlie inserts a new record with a new primary key...').

Frankly, the best solution I've found in these cases is to place everything on a reputable server with an appropriate backup plan, and have the clients connect to the server. It is usually actually faster and cheaper to supply unconnected clients with Internet connectivity than to design the database for offline usage, syncing, and conflict resolution. Read only copies could be handed out, of course. The remote server can usually be configured to send e-mails and run cron jobs, and inform the clients of any events (e.g. "Janie has earned the A2 Award").

By only exposing a REST interface, you can exploit several application frameworks, and also have a simpler and more easily testable server-side application (entry points and workflow paths are few by design and can be enumerated), which appreciably reduces the risk of a server exploit.

Another possibility

Given that the master database lives on a central server, and a copy of the same lives locally, you can keep the application and the database on an external, encrypted USB key. The database itself has no encryption; you could use SQLite locally. If using MySQL, you can run it via Portable Apps or something like it.

Then, the workflow would be: the user inserts the USB key, which requires a PIN or even a fingerprint . Once unlocked, he can run the application, which if necessary (i.e. MySQL, PostgreSQL,...) will activate the server and connect to it; or it will directly access the data (SQLite). When finished, the server (if any) is stopped, the key is ejected and the data on it is proof against any reasonable threat.

When back into connectivity range, the user again fires up the application and syncs it with the central server.

This solution has the advantage that security is a plug-in with a known cost. The users may decide not to pay that cost, but it is their decision, not yours.

  • I'm trying to find a way to keep it all on a server, because I know from experience that it'd sidestep a load of problems, mostly relating to concurrent access. Unfortunately, I don't think it's entirely possible; the main issue is when one of the group leaders takes their laptop to a meeting (the venue has no internet access) or to a trip, or on camp (which definitely has no internet access) and makes changes whilst there. It seems silly to require them to store it some other way and type it up later, so I need some way to store that data and sync it when there's a connection available. Apr 20, 2013 at 16:50
  • You can do that in two ways: one, the "server" database is set to "read only" for the trip, and the leader uploads the updated database at the end. This is easy, but raises several issues. Another way is to add three-four columns to each table to mark records that were locally updated, how, when, and by whom. Then a syncing utility can sort those records. You will have then to handle conflicts between two concurrent edits.
    – LSerni
    Apr 20, 2013 at 17:31
  • Keeping track of the user, time and nature of edits is no real problem; a "history" system is needed anyway, so when data is changed, the old one will be marked as "old" and the new values are inserted and marked as "current". The nature of the system keeps concurrent-edit resolution relatively simple to figure; my main problem is how to keep the local version of the data secure. Apr 21, 2013 at 20:44
  • Well, I've come up with another solution :-)
    – LSerni
    Apr 21, 2013 at 21:13

Encrypt the data with a strong algorithm like AES. Use pbkdf2 or bcrypt to derive the encryption key from a password.

You need a way to propagate and update the data, unless the solution is meant for a single computer only (If so, just stick with excel spreadsheets. why bother to write an application?). Have the client connect back to the server to update the database and/or download new data from the database. Have the client authenticate using certificates and secure the connection over SSL/TLS.

  • The data will be synchronised with a web-based server, although I haven't worked out exactly how yet. Even if it stayed on the one computer, there's still a reason to write the app - as I said to start, there's hours of work in manually comparing various bits of data to wee which kids have various combinations of factors that means they're eligible for things, or will require certain other bits of paperwork, and stuff. A program that could alert them immediately "Jimmy has gained X award" or "Jane needs form TN-19" would save them many hours that could be spent planning trips or whatever. Apr 20, 2013 at 16:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .