I'm trying to decide on what format I should save data in, and hopefully this question will help me come to a decision. The program I'm writing uses 'databases' in which word=meaning (not a dictionary, that was just an example). I don't really want people to be able to manually edit these 'databases' without my program easily, but at the same time I'm looking for interoperability between programming langauges (namely C#, PHP, Java and Objective C)

At the minute, the main contenders are SQLite, XML and SQL Server CE (although PHP doesn't have support for Server CE, but that doesn't have to be too much of an issue). Really, my question is, should I use encryption because I have read that it is often just as easy to decrypt? And if you support encryption, how do you deal with the encryption key - do I just generate a long, complicated string?

Or is binary enough? Nothing in the database is too critical, however I would like to have an isEditable 'key' so my program knows whether to allow edits or not. And I'd also like to use my own file extension.

  • 6
    Does it add business value to spend time implementing this data hiding? – Daenyth Jul 23 '13 at 18:53
  • 4
    @Andy: My gut says YAGNI. How much time have you spent creating your tool for this data? Do you think someone else will really spend that much time reverse engineering it? – Daenyth Jul 23 '13 at 19:08
  • 1
    And @Daenyth, perhaps you may have another point. I'm just thinking of the future, and if my application gets bigger I might need it. Though that starts a second 'debate': is a bad thing having other software use your file format - Microsoft allow it? – Andy Jul 23 '13 at 19:29
  • 3
    @Andy: You "might" need it, which logically implies that you 1) currently don't need it and 2) might NOT need it in the future, meaning that this is wasted work. – Daenyth Jul 23 '13 at 19:32
  • 3
    @Andy, it is defintely worth thinking about why you don't want other people being able to use your data. If your reason is simply that you want them to use your tool, you might actually detract from that possibility by minimizing the interoperability of your application. A lot of applications become popular because the use of an open data format (or an API, either works in this case) that makes their functionality easy to extend and build off of. – Dan Albert Jul 23 '13 at 19:34

I'd say no, you should not encode them for multiple reasons

  • Having them more open is less work for you. Does your business need to spend the extra work hiding it?
  • Having it encrypted creates lock-in. Lock-in is not a selling feature
  • If your app decrypts it, that means that the encryption can be cracked by examining your app, so you've just done work for little gain - as soon as one person cracks it, anyone can repeat it.
  • Having it open empowers your users to do things with the data you did not foresee and did not add to your app - this means that you've given them extra utility.
  • Open data will make future partnerships easier because a partner will not need to write handing logic.
  • Open data does not mean that competing tools using that data will magically appear - competitors still need to write their own software, which is likely not as good a proposition as just using yours - how much effort will someone want to go to just to NOT use your software?

This is by no means a complete list, just what's off the top of my head.

  • You raise a lot of valid points here and they help make my decision a bit easier. Not implementing encryption is most likely in the best interest of me, as well as the programs users, because it allows for greater flexibility and I do suppose there's any great reason for the 'database' to be encrypted on reflection. The question still remains though, SQLite or XML - what do you think? – Andy Jul 23 '13 at 19:58
  • @Andy: That's a different question than this one and will be dictated by how you use, store, and transport your data. – Daenyth Jul 23 '13 at 23:06
  • I cannot upvote this answer enough. Especially point #2, lock-in. I'll add one additional item - files get corrupted. It doesnt happen often, but if you have a large user base someone will have the problem. Non-encrypted files are then easier to recover. – GrandmasterB Jul 24 '13 at 18:03

One really important fact that people always overlook about encryption is that it does absolutely nothing to ensure the integrity of your data. Encryption solves the problem of confidentiality; it prevents unauthorized users from reading the data. Encryption will make it harder for someone to modify the data in a useful way, but it isn't solving the right problem.

If you're worried about other programs changing your data, what you actually need is a trusted fingerprint of your data. A common way to do this is to generate a hash of the database when your program modifies it and then sign that hash with a private key. When you load the database, compute the hash, compare it to the signed hash, and verify that the signature is valid.

Now that the matter of theory is out of the way, it is worth noting that, in practice, encrypting your data might well be good enough for this kind of thing. Any blind modifications to encrypted structured data will likely corrupt the database, so that will prevent users from modifying the database using anything other than your application. If you also want to make sure no one else can read your data (it wasn't clear in the question), then you're probably safe to leave it at encryption and be done with it.

  • This decision has got really hard now because I've got two somewhat conflicting answers. As you said, encryption could still be a good option. However, that brings in the question of what I do with the encryption key (i.e how is it generated) and also what method I use, which is likely to make the program a lot more complicated, and it could put users off using the application. I suppose as well, if I don't add encryption now I'll still have the option for future versions of my program to support encrypted databases as well. – Andy Jul 23 '13 at 19:50
  • 1
    @Andy Encryption is hard. It's much harder when the encryption needs to be done from a user's machine and not your server. Including the key used for encryption leaves it vulnerable. Even if you scramble the key and reassemble it at run time, tools like passe-partout can still extract them from the program's memory. There is an entire industry dedicated to making this work (it's called DRM), and it's far from perfect. Is your application something that can be made into a web app? This problem is much easier solved if decryption happens on your server. – Dan Albert Jul 23 '13 at 20:03
  • I can see that encryption is hard now, and unfortunately it would have to be done offline, as this is one of the key features of the software - the ability to work without an internet connection. So I think, as people have been saying, implementing encryption would be a lot of work for not much gain (if any). – Andy Jul 23 '13 at 20:11

Do you want to limit tampering or limit the view of the data? The potential solution for each would require slightly different implementations.

Tampering you would use hashes. As a real world example, one application I worked on we would compute the hashes of strings and/or files. When users downloaded the file, we checked the hash. When user opened the file, we checked the hash. This is because our client didn't want end users tampering with the files (jpg, etc.) This prevented users from tampering with the file, because if they did, the hashes didn't match and they got an error. Any file/string can be hashed, and if your don't want user tampering with strings or files without issue, store a hash of that data along with the string/file. Passwords do not need to be encrypted, the solution would be to salt the password and then hash it. Then the data store and application has no knowledge of user passwords.

Encryption would be used to hide information such as credit card and/or personal data. Legal and regulatory requirements should define what needs to be encrypted. If the application is storing sensitive data, then one fo the ways to prevent access is via encryption. You can also limit access by defining ACLs etc. but if you only want your user(s) to view the data, then encryption is your best option.

Also, only implement if required. Going back to the example above, originally we did have any tamper resistence in the application. Users downloaded files and opened and viewed locally. Only after we had a requirement from a customer that users couldn't tamper with files then we implemented a hashing check. When in design we considered two options, limiting access on the file system to the files that were downloaded, or using a hash. We choose a hash because we were already calculating it on upload so it was relatively easy to add a check before the user could view it.

  • Passwords do not need to be encrypted, store a password hash and then compute the hash of what the user typed and compare if valid. rather, select a non-optimized function such as bcrypt or scrypt, salt the password and then hash it. – rath Jul 23 '13 at 22:02
  • In the ideal world, I'd like to limit both tampering and viewing the data but I know realise in the real world, it probably isn't worth the effort. Hashing would be good, but the file will change and be updated online so I don't think this would work. When I update the file, I could also download the new hash I suppose but then you have the problem of keeping that safe. With regards to encryption, there will be no sensitive information stored - I was only inquiring because I wanted users to use my software to manage the files, but now I see that encryption may be too hard to implement – Andy Jul 24 '13 at 11:15
  • 1
    @Andy - If users are allowed to update the file, you could implement versioning. Updates would be a new file being uploaded with a new hash. But you are correct, the hash would have to updated. Also computing hashes on files does take some time. For small files not an issue, but if you have very large files it will take a second or two. – Jon Raynor Jul 24 '13 at 14:39
  • 1
    @Rath - Good point, I was too brief in my original answer, I will update. Thx. – Jon Raynor Jul 24 '13 at 14:39
  • @JonRaynor As I say, the only problem with updated the hash is that it would have to be stored somewhere, and depending on where it's stored and how the hash is created it could be easy just to change it. The question is, is it better to deter users using a method that could so easily be hacked, or just leave it open anyway and not bother deterring users. – Andy Jul 24 '13 at 14:45

If all you're looking for is to discourage external editing, while providing for interoperability among different implementation languages, consider simply compressing the file. It'll look like line noise if opened in a text editor, but if you use a standard compression algorithm you should be able to find (or write) a file compression library for most popular languages. You even get the benefit of saving storage space, if your data sets get large.

  • +1 Wow, what a neat idea. Would you recommend a format to compress inperticular? – Andy Jul 24 '13 at 17:49
  • LZ (Lempel-Ziv) compression is pretty widely used, so you should stand a good change of finding implementations in most languages. If you want to write it yourself, basic Huffman coding is pretty straightforward. – TMN Jul 24 '13 at 18:02
  • Thanks for your reply, however I meant which type of file would you recommend I compress (i.e XML, JSON, etc.)? – Andy Jul 24 '13 at 18:06
  • Also, it may sound like a strange question, but which is likely to be faster, encoding or compression? – Andy Jul 25 '13 at 11:37
  • Encoding is definitely faster, except if LZ doesn't want to create a dictionary first. The difference is going to be microseconds anyway. Now for the size... XML is likely to have a higher compression ratio (lots of repeated phrases) but is also likely to lead to a larger file (by a very small margin but larger nonetheless). That's just an estimate, you should run your own tests @Andy – rath Jul 25 '13 at 16:54

As I understand your question, you don't really care if someone modifies or looks at your database, it's just you'd rather they didn't.

Daenyth makes a good argument why you shouldn't do that but if you decide to go with it anyway, consider a few things:

  • Crypto is hard to get right

Even if you do encrypt your data with AES or what have you, the value of the data doesn't really call for encryption/protection. It may also make it harder to read the data in the future in a different language or even with a different library.

  • You are better off with obfuscation

which means a simple Base64 will do. Of course that will do nothing to verify the integrity of the data as Dan noted but implementing signatures is still too costly. As an alternative, you might add padding to your file to conform with a set of fixed checksums (for some checksum function). Much lighter than signatures but still quite expensive.

If in the future you need to have a trusted connection to a remote database, ask again when you implement it.

As for SQLite vs XML there is a question on StackOverflow on the same topic. Although closed it has some excellent answers.

Edit: If you are using Java you might want to consider zipping the file. You get both an illegible binary blob and a checksum. I'm sure C# also has a version of this but I'm not familiar with that language.

  • I think you understand correctly; I'm not all too sure myself but I've come to conclusion it's not the end of the world if someone see's or modifies my database without my software and it wouldn't be worth the effort to try and stop them. However, Base64 does sound like a good option. If I used it, would I just write to the file in any old format or could I use XML in Base64 if you know what I mean? – Andy Jul 24 '13 at 11:23
  • Base64 doesn't care about the underlying format of the data; you can save your XML data in a buffer, pass it through Base64 and dump it in the file. To read, do the reverse. Of course you can't use Base64 with SQLite because (if I'm not mistaken) SQLite uses its own procedures to read/write to a file (true for PHP anyway). You could get around this somehow but it's not worth the effort. – rath Jul 24 '13 at 11:27
  • Thanks for your reply. This does sound like a good solution to be because nearly all programming languages support Base64 and it will still enable other programs to access the content if needed, it will just deter casual users from manually modifying things. I'm not sure if I need SQLite anyway, because to me it looks like overkill and I can't see that there will be much difference in performance with files less than 1 MB. Can you see any advantages of using SQLite over XML? – Andy Jul 24 '13 at 11:40
  • Not for the scale of 100-200 values. Look at the question on SO I've linked in my answer, it has good answers to help you in your decision. Another plus is XML can be transfered as-is through the network even with a text-based protocol, ie. a RESTful architecture. – rath Jul 24 '13 at 11:47
  • Well, I'll have a lot more values than that, but looking at the question you linked to it's more about the size of the file rather than the amount of values. According to the question, it seems that for my purposes XML should be fine but I'll do a little bit more digging. – Andy Jul 24 '13 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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