So, a bit of context before I start may be handy. I am developing a Java desktop application which will use an SQLite database to store/process data. The application will utilize authentication to access the program for editing data, adding/deleting data, etc. But I don't want an attacker to be able to pull the laptop's hard drive and access the data without first authenticating to Windows.

My question is, if I were to encrypt certain data before inserting into the SQLite database with say...AES256, is it possible to decrypt the data without the user having to insert a password AND without hardcoding the password in the application? I really think the overhead of the user having to remember 2 passwords (one for application authentication and one for database encryption/decryption) is infeasable because if they forget the encryption/decryption password, their data is gone..

Have any of you come across any solutions that you can share or suggest some ideas that may be of help?

  • Why do you want to encrypt the data in the database? The only reason I see to do that is if you want to protect the user data against the developers of the application (so yourself). – Gudradain Jul 17 '14 at 17:41
  • 1
    So, the reason for encrypting the database is to prevent access to the database from "the side" so, when the database file is not in use by the application, I dont want the ability that a hacker could open and read the file in PHP MyAdmin for example. – Anthony N Jul 18 '14 at 7:39
  • If the attacker can access your database, it probably means that your server is compromised. This then means that nothing on your server is safe. If you are expecting your server to be compromised, the only solution you have left is to encrypt/decrypt everything on the client side, like LastPass is doing for example. But really, most applications out there expect that the server is not compromised... – Gudradain Jul 18 '14 at 13:12
  • 3
    This solution is for a local DB, on a personal laptop. So I am working back from a physical security breach and the possibility that a user has not utilized windows authentication to access their profile or that their windows credentials are also compromised. The possibility is that access to the DB file is a likelihood. This is the reason I am investigating this, so as to ensure the confidentiality of the data in a worst case scenario. – Anthony N Jul 18 '14 at 16:24
  • "But really, most applications out there expect that the server is not compromised" I would counter that all sane applications expect that the server may inevitably be compromised. – Darren Ringer Dec 23 '16 at 14:05

When the user do the first login your software will ask for a new password. At this time the DB should be empty.

Now you have a UserPassword and an empty DB. So the software generate a new random DBdataKey that will be used to encrypt/decrypt the DB contents. To encrypt/decrypt use one of the many known algorithms, like AES or similar one.

Now you create 2 random salt string: PasswordSalt and DBdataKeySalt. Store PasswordSalt and DBdataKeySalt in the DB as a clear text.

Use a hash function like pbkdf2 (or something like SHA2 or better) with UserPassword and DBdataKeySalt to obtain a DBdataKeyEncryptionKey

Now: DBdataKey is the key you use to encrypt/decrypt the DB contents DBdataKeyEncryptionKey is the key you use to encrypt/decrypt the DBdataKey

Use a hash function like pbkdf2 (or something like SHA2 or better) with UserPassword and PasswordSalt to create HashedSaltedUserPassword, a salted-hashed version of the UserPassword, then use DBdataKey to encrypt HasedSaltedUserPassword before storing it in the DB.

From now on you can use DBdataKey to encrypt/decrypt the data to/from the DB.

Now, when the user opens the application, you ask him for the password, then use the inputed password with DBdataKeySalt (which is stored in cleartext) to regenerate the DBdataKeyEncryptionKey, and then use the obtained DBdataKeyEncryptionKey to decrypt the DBdataKey , and so you can use the decrypted DBdataKey to read the DB.

Now use the DBdataKey to decrypt the HashedSaltedUserPassword and compare it with the one you regenerated using the inputed password and the PasswordSalt (which is stored in cleartext) just to double-check that the login information is correct.

If the user inputs the wrong password, when you use the DBdataKeyEncryptionKey to decrypt the DBdataKey the decrypt function will give you a different value (garbage). Using that (wrong) value to decrypt the HashedSaltedUserPassword will result in more garbage values, which won't match the hash.

When the user changes the UserPassword then you only need to decrypt the DBdataKey with the old password and re-encrypt it with the new password, but the DBdataKey won't change, so you won't need to re-encrypt the db contents when the user changes its password.

Reference: I haven't invented anything, this is just a mix of two well known techniques:

  • How user-login & password salting/hashing is done
  • How on-the-fly disk encryption can be implemented (applied to DB encryption/decryption)
  • 2
    Even better: you also encrypt the “salted hash” (use something like pbkdf2) with the "DB key" so even bruteforcing the user password will be harder (if the user password is wrong, you will be decrypting with the wrong DB key, and the hash won't match). Plus replace all mentions of encrypt with the user password with a hash/hmac of the user password – Ángel Jul 24 '14 at 19:30
  • @Ángel thanks, I rewrote the answer to include your suggestion – Max Jul 24 '14 at 19:58
  • @Max and Ángel, Thank you very much! Extremely in-depth and well thought out solution, I really appreciate your help! – Anthony N Jul 25 '14 at 22:44
  • Some functionality is lost, likr in select * from table where col like '%string%', isn't it? – ott-- Mar 12 '16 at 20:41

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.