Encrypting SQLite Database file in iPhone OS

Question

Any SQLite database on the iPhone is simply a file bundled with the application. It is relatively simple for anyone to extract this file and query it.

What are your suggestions for encrypting either the file or the data stored within the database.

Edit: The App is a game that will be played against other users. Information about a users relative strengths and weaknesses will be stored in the DB. I don't want a user to be able to jail-break the phone up their reputation/power etc then win the tournament/league etc (NB: Trying to be vague as the idea is under NDA).

I don't need military encryption, I just don't want to store things in plain text.

Edit 2: A little more clarification, my main goals are

1. Make it non-trivial to hack sensitive data
2. Have a simple way to discover if data has been altered (some kind of checksum)

Answer 1

How likely do you think it is that your normal user will be doing this? I assume you're going through the app store, which means that everything is signed/encrypted before getting on to the user's device. They would have to jailbreak their device to get access to your database.

What sort of data are you storing such that it needs encryption? If it contains passwords that the user entered, then you don't really need to encrypt them; the user will not need to find out their own password. If it's generic BLOB data that you only want the user to access through the application, it could be as simple as storing an encrypted blob using the security API.

If it's the whole database you want secured, then you'd still want to use the security api, but on the whole file instead, and decrypt the file as necessary before opening it. The issue here is that if the application closes without cleanup, you're left with a decrypted file.

You may want to take a look at memory-resident databases, or temporary databases which you can create either using a template db or a hard-coded schema in the program (take a look at the documentation for sqlite3_open). The data could be decrypted, inserted into the temporary database, then delete the decrypted database. Do it in the opposite direction when closing the connection.

Edit:

You can cook up your own encryption scheme I'm sure with just a very simple security system by XOR-ing the data with a value stored in the app, and store a hash somewhere else to make sure it doesn't change, or something.

Answer 2

There are at least two easier approaches here (both complimentary) that avoid encrypting values or in-memory databases:

#1 - ipa crack detection

Avoid the technical (and legal) hassle of encrypting the database and/or the contents and just determine if the app is pirated and disable the network/scoring/ranking aspects of the game. See the following for more details:

http://thwart-ipa-cracks.blogspot.com/2008/11/detection.html

#2 - data integrity verification

Alternatively store a HMAC/salted hash of the important columns in each row when saving your data (and in your initial sqlite db). When loading each row, verify the data against the HMAC/hash and if verification fails act accordingly.

Neither approach will force you to fill out the encryption export forms required by Apple/US government.

Score submission

Don't forget you'll need to do something similar for the actual score submissions to protect against values coming from something other than your app. You can see an implementation of this in the cocos2d-iphone and cocoslive frameworks at http://code.google.com/p/cocos2d-iphone/ and http://code.google.com/p/cocoslive/

Response to comments

There is no solution here that will 100% prevent data tampering. If that is a requirement, the client needs to be view only and all state and logic must be calculated on a trusted server. Depending on the application, extra anti-cheat mechanisms will be required on the client.

There are a number of books on developing massively-multiplayer games that discuss these issues.

Having a hash with a known secret in the code is likely a reasonable approach (at least, when considering the type of applications that generally exist on the App Store).

Answer 3

You cannot trust the client, period. If your standalone app can decrypt it, so will they. Either put the data on a server or don't bother, as the number of people who actually crack it to enhance stats will be minuscule, and they should probably be rewarded for the effort anyway!

Put a string in the database saying "please don't cheat".

Answer 4

Ignoring the philosophical and export issues, I'd suggest that you'd be better off encrypting the data in the table directly.

You need to obfuscate the decryption key(s) in your code. Typically, this means breaking them into pieces and encoding the strings in hex and using functions to assemble the pieces of the key together.

For the algorithm, I'd use a trusted implementation of AES for whatever language you're using. Maybe this one for C#:

http://msdn.microsoft.com/en-us/magazine/cc164055.aspx

Finally, you need to be aware of the limitations of the approach. Namely, the decryption key is a weak link, it will be available in memory at run-time in clear text. (At a minimum) It has to be so that you can use it. The implementation of your encryption scheme is another weakness--any flaws there are flaws in your code too. As several other people have pointed out your client-server communications are suspect too.

You should remember that your executable can be examined in a hex editor where cleartext strings will leap out of the random junk that is your compiled code. And that many languages (like C# for example) can be reverse-compiled and all that will be missing are the comments.

All that said, encrypting your data will raise the bar for cheating a bit. How much depends on how careful you are; but even so a determined adversary will still break your encryption and cheat. Furthermore, they will probably write a tool to make it easy if your game is popular; leaving you with an arms-race scenario at that point.

---

Regarding a checksum value, you can compute a checksum based on the sum of the values in a row assuming that you have enough numeric values in your database to do so. Or, for an bunch of boolean values you can store them in a varbinary field and use the bitwise exclusive operator ^ to compare them--you should end up with 0s.

For example,

for numeric columns,

2|3|5|7| with a checksum column | 17 |

for booleans,

0|1|0|1| with a checksum column | 0101 |

If you do this, you can even add a summary row at the end that sums your checksums. Although this can be problematic if you are constantly adding new records. You can also convert strings to their ANSI/UNICODE components and sum these too.

Then when you want to check the checksum simple do a select like so:

Select * FROM OrigTable right outer join (select pk, (col1 + col2 + col3) as OnTheFlyChecksum, PreComputedChecksum from OrigTable) OT on OrigTable.pk = OT.pk where OT.OnTheFlyChecksum = OT.PreComputedChecksum

Answer 5

It appears to be simplest to sync all tournament results to all iPhones in the tournament. You can do it during every game: before a game, if the databases of two phones contradict each other, the warning is shown.

If the User A falsifies the result if his game with User B, this result will propagate until B eventually sees it with the warning that A's data don't match with his phone. He then can go and beat up explain to A that his behavior isn't right, just the way it is in real life if somebody cheats.

When you compute the final tournament results, show the warning, name names, and throw out all games with contradictory results. This takes away the incentive to cheat.

As said before, encryption won't solve the problem since you can't trust the client. Even if your average person can't use disassembler, all it takes is one motivated person and whatever encryption you have will be broken.

Answer 6

Like Kendall said, including the key on the device is basically asking to get cracked. However, there are folks who have their reasons for obfuscating data with a key on-device. If you're determined to do it, you might consider using SQLCipher for your implementation. It's a build of SQLite that provides transparent, page-level encryption of the entire DB. There's a tutorial over on Mobile Orchard for using it in iPhone apps.

Answer 7

Yet, if on windows platform, you also can select SQLiteEncrypt to satisfy your needs.SQLiteEncrypt extends sqlite encryption support, but you can treat it as original sqlite3 c library.