About sqlcipher DB size limitations

#1

Hello sqlciphers ^.^

I am really glad i finally made it work inside a c++ application but now i am facing a small error in database i allready had inside the application i plan to use sqlcipher.

It is about 1.4 Mb size this database after much use of it and the encryption doesnt work. It doesnt encrypt the data allready exists in that database and i think it has to do with the size of its data.
A new database or a small KB database are encrypted succesfully.
As PRAGMA definitions while i created the big sized database i used secure_delete = true, encoding = UTF8 and journal_mode = OFF

What i want to ask is this ?

  1. Is there a way to encrypt an allready big size database ?
  2. Will the encryption have problems after long time use of database that will become bigger and bigger in size after years inside an application ? (will it encrypt and decrypt without problems?)

Thank you in advance

#2

It is about 1.4 Mb size this database after much use of it and the
encryption doesnt work. It doesnt encrypt the data allready exists in
that database

See How to encrypt a plaintext SQLite database to use SQLCipher (and avoid “file is encrypted or is not a database” errors)

SQLCipher should report an error if you attempt to use sqlite3_key or
PRAGMA key on an encrypted database. Did you not see the error? Is it not
clear enough?

I think 1.4 MB is not a very big SQLite database. Web data storage is at
least 2MB for LocalStorage, at least 5MB for Web SQL or IndexedDB as
described at https://www.html5rocks.com/en/tutorials/offline/quota-research/.
SQLite limits described at http://www.sqlite.org/limits.html are much
higher (over 100 TB).

Will the encryption have problems after long time use of database that
will become bigger and bigger in size after years inside an application ?
(will it encrypt and decrypt without problems?)

My understanding is that SQLCipher should be able to handle many gigabytes
and even multiple terabytes by design. I leave it up to the SQLCipher team
to provide some further details.

#3

Unfortunately the sqlite3_key doesnt produce any errors in the big database and it passes through as it is correclty encrypted. Then i check always the database if it is encrypted by opening it in a text as a first step and it is not encrypted. Then i try to login but it cant login.
It is like somehow it has encrypted the database though the database is unencrypted and all data are readable and i can`t retrieve any information from the database cause it thinks it is encrypted but it is not.

This happens only to the big size database, small ones or even creating a new one from the start it works fine from the same application.
All databases i test is from the same application so it has something to do with the size of it as it seems.
It may be a memory issue also, i don`t know cause i only have 4GB ram (if this is an issue)

I will try the first link though cause it was plaintext and it is the first time that i use sqlcipher to encypt it plus it has many tables, deleted datas, exiting data, etc etc.

----- UPDATE ------
I manage to encrypt the big size database from the shell of sqlcipher but it is not working cause i cant login (the initials of login in to the application is inside the encrypted database) i use the same KEY in the shell to encrypt and the same in the application i use the db to open it and login but it doesnt accept the correct initials.
If i create a new login account creating the db by the app and encrypted it works fine.
My main problem is just for personal reasons that i need to reuse the data i allready have inside the encrypted data and also be sure after this that i can ‘rekey’ the database whenever is needed.

#4

Hi @Nocs

We are happy to hear you have made some progress from the previous thread! I’d like to touch base on a couple of your comments below:

SQLCipher does require some additional steps in order to encrypt an existing plain-text SQLite database beyond opening the database, keying it, etc. @brodybits has already included a link to a detailed post by @sjlombardo that covers the migration process from a plain-text SQLite database to an encrypted SQLCipher database. Also, since you may be new to SQLCipher, you might considering reviewing some of our public documentation here, we detail the SQLCipher specific API here.

Very little occurs within SQLCipher when calling sqlite3_key, in fact no decryption occurs once that function has been invoked as it is deferred until a SQL statement is invoked. We recommend folks invoke the following query after keying a database to verify that no errors occur, the sqlite_master table is guaranteed to exist even if your application schema is not in place.

SELECT COUNT(*) FROM sqlite_master;

Can you try those approaches and let us know if the behavior within your application changes? Thanks!

#5

@developernotes

I did all the tries i could by following the link to encrypt from shell and it does but i cant login to the encrypted database anymore. If i encrypt from the application the big db, after the sqlite3_key i use your function to check but it doesnt execute the function and returns the error "databasee disk image is malformed’ and any other try to connect again says “database is encrypted or is not a database”

If i use the shell`s created encrypted new database to the app it says “database is encrypted or is not a database”

I use the same password a test password as a start in both shell and when i open or use sqlite3_key option

The way i use the password is after i open the database like this :
std::string PassK = “test”;
const void DBKey = PassK.c_str();
int dbcp = sqlite3_key(dbhandle, DBKey, sizeof(DBKey));
It doesn`t return any error
and after that i run
SELECT COUNT() FROM sqlite_master;
and it returns the errors above
(only to the big size database - for all other is working fine with no errors)

What could cause this issue ?

#6

I think the problem in the big size database is the password.
I can encrypt the plaintext database to an encrypted one from the shell and use the shell to check that is working fine.
But if i use the same password inside my application it doesn`t allow me to login, it says “database is encrypted or is not a database”

So i think is the way i use my password in the sqlite3_key() function.
Also i tried to remove this function and use in the sqlite definition the PRAGMA KEY=test or ‘test’ but again it seems that it doesn`t decrypts the database.

#7

Hello @Nocs

You are calling sqlite3_key with incorrect 3rd parameter value. You need to pass the length of the key, not what sizeof reports for DBKey. Try using strlen in-place of sizeof and let us know if this resolves your issue.

#8

@developernotes

Thank you, yes i was having problem with that as it seems, here is the correct :
std::string PassK = “test”;
int dbcp = sqlite3_key(dbhandle, PassK.c_str(), strlen(PassK.c_str()));
BUT it will not work if you close the program and reopen it cause i was using PRAGMA journal_mode=OFF, when i removed that yes this is the correct way without any errors.

Now some things i noticed and may be needed and you can confirm or not too.
If i use PRAGMA journal_mode=OFF it has problem reading with the key the same database that may created by the sqlcipher.
what i did was to create a new encrypted database with the above function
It does encrypts it and i was able to use it BUT if i close the application and re open it
the same key is not recognized if i use PRAGMA journal_mode=OFF, if i remove this definition it works as it should.

Another thing i noticed by playing around is.
Indeed brobybits is correct about the first link.
no matter what i ve tried an allready plaintext db cannot be used from within a program and be encrypted for the first time but it has to be encrypted first with the sqlcipher-shell.exe and then with the correct password you can use it inside the application you want.

For the moment everything seems to be working fine.

#9

Hi @Nocs

Are you setting the journal_mode before or after you key the database? If the former, try applying it after you key the database and see if your application behavior changes. Also, does your application need the journal mode set to off, instead of the default of delete?

There is likely an issue with either your build of the library, or the process you are performing to import the database. Please feel free to post a sample code example of the code you are invoking when trying to convert a plain-text SQLite database to an encrypted SQLCipher database to verify there aren’t any visible issues.

#10

I am terrible sorry, you are correct.
I was using PRAGMA definitions in my script as soon as i open the db and then i was setting the password so it had some issues as it seems.
Now i used first the password and then the PRAGMA definitions and it works as it should :slight_smile:

I am about to check that tomorrow as i am still playing around and repost back

Your help is much appreciated

#11

I couldn`t achieve it but it is ok cause it was only needed for personal reasons (to use the data of databases i allready have) so since i manage to make it work even with shell it is ok.
i ve made some tests while playing around for the applications needs and all things are working perfect.
I created, used and rekeyed a database, fixed some issues around the journal and all seems good.

Thanks for the time and help and the license ofcourse.
By the way
i will use your license from the link and for the third parties that sqlcipher is depending on should i use them too as your license for each one of them or i could use the same way you mention the third parties by just having links and mentioning about them ?

#12

Hi @Nocs

We are happy to hear you were able to make successful progress on your project!

You will need to disclose both the SQLCipher license and any third-party libraries used in your SQLCipher build (e.g., OpenSSL).

#13

i get it now, so since i use only sqlcipher and openssl library i need to enclose your license and openssl from their webpage.
For all others that i dont use such as Android, System.Data.SQLite, Mono etc…
I need to get each license from their website too or just mentioning as third parties for sqlcipher the way you do at your website here ?

#14

Hello @Nocs - if you are not including some of the other components, then you would not need to disclose their usage.

#15

@sjlombardo i only use OpenSSL and SqlCipher, i am not so expert on such things so i will just disclose the sqlcipher and openssl, if anything else is needed due to use of sqlcipher plz post back.
Thanks you