Howto encrypt a existing sqlite database?

[T4r4ntul4]

Hi all,

I have a sqlite database with info in it, but if you open that with for example sqliteman you can read all data in the tables. I dont want that to happen.

Is there a way to encrypt a existing sqlite database?

Any help is very appreciated!

[skywalk]

Encrypt your data BEFORE storing, or pay for SQLite Encryption Extension.

[T4r4ntul4]

Hi,

sqlite SEE is not an option:

The cost of a perpetual source code license for SEE is US $2000

Are there any other solutions? like zipping the database with a password? and when starting the program unzip it and use it?
Or any other clever solutions?

[skywalk]

Once the db is unzipped, it will be accessible to anyone on the pc.
Better solution is to encrypt sensitive fields. Not the entire db.
Then the db can exist in the open.

[T4r4ntul4]

What is the best way to encrypt fields in the database?

When i use base64 it still can be decoded?

i actually know nothing about encryption. Can you give me a few pointers to do it the best way?

[skywalk]

Base64 is used to convert data into ascii character set for transmission.
Like you said, it is not encryption.
PB's cipher library has many options. You choose the severity you desire at the expense of size and speed.
There are many examples in the help.
Just keep in mind that these algorithms are known, so you have to obscure the data with your special "seed" that is NOT stored in the db.

[T4r4ntul4]

But wait, i need to encode it back. its info like emails, names, dates etc etc, so how to use an encryption with a seed?

[walbus]

Look here, its very simple

http://www.purebasic.fr/english/viewtop ... 27&t=66894

[skywalk]

2-way: Encrypt(*data) <--> Decrypt(*data), using AESxxx
1-way: HashFunc("Clear text") --> CRC32, MD5, SHA256, etc. No easy way to get Clear text.
You should store passwords in SHA256 form. They cannot be deciphered. Only recreated, if the user enters the correct original password.

To store individual fields as encrypted, you create a seed+dbPW+yourFieldData.
Then share the dbPW with the trusted user.
The field data is stored in the table as a blob.
Your queries are more complex, since they have to work on the extracted blob's decrypted value.

Maybe you are starting to understand why $2000 for the SQLite SEE extension is justified?

[walbus]

Look for the QAES demo code "Crypt a file blockwise in it self"
This encrypt your base file in one step, any sizes are available
It is recommended for using a pass phrase as password, or you can add a needed additional numerical counter for the crypter

The QAES Crypter overwrites the base directly in itself, no separate decrypted file is created.
The workload without any previous knowledge is probably no more than a few minutes.
The use of raw PB AES functions is very risky, as easily serious errors can occur.

[T4r4ntul4]

Hi walbus,

Your code is amazing. It works really fast.

My thinking was i had 2 options:

1. if i start my software i decrypt the files, use the software, and when i close software, it encrypt them again. (but if the user uses the software for 2 hours, database will be open for 2 hours)
or option 2:
2. let the files encrypted, and when only i need database access i decrypt them for that time (1 sec or less) and encrypt it right after that.

I went with option 2 because i think that is the safest.

But for some weird reason when i close the database file with closeDatabase(0), its still in use in windows, and so i cant encrypt it or decrypt it.

Do you (or anyone) have by chance a solution for that?

[walbus]

Hi T4r4ntul4
Your Way is right !
Looking here, I myself think we currently have a Close File Bug in Windows
http://www.purebasic.fr/english/viewtop ... =4&t=69932

If the problems persist for a longer period of time, you can also encrypt the contents of the database on the fly as needed.
That's the safest way to go !
It's no harder than encrypting the entire base.
See the Crypter demo codes, look for encrypt unicode strings.
You can easily encrypt strings.
You can handle the encrypted strings just like unencrypted strings.
You don't need a Base64 or anything else.
The QAES Crypter can encrypt strings without damaging them or changing the length.

[doctorized]

1-way: HashFunc("Clear text") --> CRC32, MD5, SHA256, etc. No easy way to get Clear text.
You should store passwords in SHA256 form. They cannot be deciphered. Only recreated, if the user enters the correct original password.

As there is SHA-2 and SHA-3, not only 256 but also 512 (as greater) natively supported from PB, is it safe to use them?