I’m dealing with an app that needs a secure encryption for my first time. I have created a password, hashed it, and retrieved it successfully. Now I need to encrypt the DB the app will use. I see the LR instructions about DB.encrypt but I’m a little confused. If I hard code an encryption key in my code, isn’t that easily retrievable? But I can’t use the PW supplied by the user since I don’t know it.
How do most of you handle encrypting a DB?
If you hash a password, you can’t retrieve it, so that part is a little confusing.
I assume you’re using SQLite?
What is the data that needs to be encrypted, the initial data that you distribute or the data that the user enters? Because you’re right, if you include the password within the code, it will be retrievable. The best you can do is make it harder.
If you just need to encrypt the data the user enters, you can have them enter a password, store it to their keychain, or some other secure way on their drive, then use that to encrypt a copy of the raw data you distribute.
But it’s hard to advise without knowing more of what you’re doing.
Correct, Kem. I meant to type “retrieved the hash and compared it”. I am using that stored password to permit access to the app. Within the app the user will enter user names, passwords, etc into a DB. I would like to make that DB so it can’t be opened by another app. I guess if I’m going to use a PW the user stores in their keychain (this is X-plat. Is there an analog to “keychain” for Win?) then I can just use that password for access to the app and to encrypt the DB. I was hoping to use the techniques outlined in Paul’s Blog post on encryption.
Thanks for your reply.
It’s not perfect, but you can create and store a public/private key, then use that to encrypt and retrieve the password from a file. To get the data, someone would need the private key, the password file, and the database.
Thanks, Kem. I will read up on how to do that.
Hash the user’s input and compare that to the stored hash to determine if they match. It’s also a good idea to use a salt (extra data mixed in with the password) so that users who choose the same password won’t get identical hashes.
You can also use the hash value as an encryption key.
The Crypto.PBKDF2 hash method is specifically designed for securely hashing passwords (and generating keys from passwords.)
Yes, Andrew. That’s the part I have successfully completed.
You also have Bcrypt and Scrypt available through my M_Crypto project.
[quote=419175:@Kem Tekinay]You also have Bcrypt and Scrypt available through my M_Crypto project.
https://github.com/ktekinay/m_crypto[/quote]
Dont you have a class to obfuscate a string?, in this case roger wants to use the native Xojo encryption for SQLITE, and he is just worried about having the “password” on the compiled app
I posted a script that does that, yes.
@Roger Clary To the best of my knowledge, the ideal case is the following (with SQLite):
- The user enters its username and password which you hash together (let’s call it $HASH)
- You create a random long key for the DB (let’s call it $KEY) and initialize the DB with it
- You use a symmetrical cipher method to encrypt the $KEY which $HASH and write it on disk.
Now, whenever the user wants to access the database, you can ask for his/her username and password, compute the hash, decode the real key $KEY and use it to open the database. If it fails, then the username or password is wrong.
It also allows the user to change his/her password without re encrypting the whole DB. You just need to ask for his/her username, old password and new password, then:
- Decrypt the $KEY with the hash of username and old password
- Re-encrypt the $KEY with the hash of username and new password
- Store the result
Which symmetrical cipher would you recommend?
If you’d like the user to have to enter a password each time, I like @StphaneMons ’ suggestion and would add the following:
- The username may not be required in this case. If not, just eliminate it from the suggestions. Otherwise, uppercase (or lowercase) and trim the username before hashing to eliminate case issues.
- For $HASH, use PBKDF2 or (better) Scrypt with settings that require up to a second to compute.
- Store a random salt (use Crypto.GenerateRandomBytes) in the $KEY file that will be used when hashing.
- Generate 48 bits from the $HASH. The first 32 will be the encryption key, the last 16 bits the initial vector.
- Use AES-256 to encrypt the $KEY.
- When the user changes their password, be sure to replace the salt too.
Get the username (if needed) and password from the user, read the salt from the $KEY file, hash it, then attempt to decrypt.
If the user is going to enter the password, there is no need to store it, hash it, or encrypt it.
Just apply it on database open, and forget it… The database just needs it long enough to validate the connection
@Markus Winter As Kem proposed, I would go for AES-256
[quote=419277:@Dave S]If the user is going to enter the password, there is no need to store it, hash it, or encrypt it.
Just apply it on database open, and forget it… The database just needs it long enough to validate the connection[/quote]
Ok… what am I missing about this question? It seems that everyone is proposing overly complicated sollutions to a problem that doesn’t even need a solution
The suggestion, which may or may not fit the original problem, is for creating a pseudo username and password that can be changed without having to re-encrypt the database.
Also, it is a TERRIBLE ide to give the user the actual encryption password. He could open and edit the database or just extract the sensitive information in a third party app, rendering your “security” completely usesless.
Maybe a little late but here is a thought for data encryption but not passwords.
In a MySQL app I have I created two user defined functions using the built in functions of AES_ENCRYPT and AES_DECRYPT to encrypt and decrypt the data. I set a key in a MySQL variable after connecting to the DB then the functions use the key. I have some code in the startup that calculates the key based on some other data items. When I need to read or write a column from the DB that is encrypted I simply wrap the column name in the MySQL user function. If you have a multi user app that needs data segregated you could have different keys for each user and other users would get “gibberish” if they accessed the data with the wrong key.
The functions add some “extra stuff” (‘xyz’ and ‘ijklmnop’) to the key just to make it more obscure since some comes from the program code and some is embedded in the MySQL function. These do require a VARBINARY data type in MySQL columns that get encrypted. VARCHAR does not work.
Here are the two functions:
CREATE DEFINER=`mark`@`localhost` FUNCTION `dcrypt`(in_col VARBINARY(128)) RETURNS varchar(128) CHARSET latin1
DETERMINISTIC
BEGIN
RETURN AES_DECRYPT(in_col,concat('xyz', @xxkey, 'ijklmnop'));
END;CREATE DEFINER=`mark`@`localhost` FUNCTION `ncrypt`(in_col VARCHAR(128)) RETURNS varbinary(128)
DETERMINISTIC
BEGIN
RETURN AES_ENCRYPT(in_col,concat('xyz', @xxkey, 'ijklmnop'));
END;Reading data looks like this:
SELECT dcrypt(MyColName) as MyColName
FROM MyTable
WHERE MyKey = 'ABC'Writing data looks like this:
UPDATE MyTable SET
MyColName = ncrypt('Some New Value')
WHERE MyKey = 'ABC'I know this is potentially vulnerable maybe to hacking the memory of the DB server or other hacks but it is obscure enough for my purposes.
@Dave S It is true that you can use the plain user’s password but, as @Pedro Ivan Tellez Corella noted, it may be a terrible idea that the user can open the database on his/her own. I prefer some extra simple steps to prevent that.
@Pedro Ivan Tellez Corella You can add anything you want to the hash before encrypting the password so the user will never know the true DB password.