How to encrypt DB using wxSQLite3? Topic is solved

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
MJaoune
Earned a small fee
Earned a small fee
Posts: 22
Joined: Tue Aug 20, 2019 7:37 pm

How to encrypt DB using wxSQLite3?

Post by MJaoune » Sat Aug 31, 2019 7:43 pm

I am encrypting (AES256) the database file using this code:

Code: Select all

wxSQLite3Database *db = new wxSQLite3Database();
db->Open("mydb.db");
		
wxSQLite3CipherAes256 crypt;
crypt.InitializeFromGlobalDefault();
		
db->ReKey(crypt, wxString("My Key"));
		
db->Close();
And I am trying to open it using this code:

Code: Select all

wxSQLite3Database *db = new wxSQLite3Database();

wxSQLite3CipherAes256 crypt;
crypt.InitializeFromGlobalDefault();

db->Open("mydb.db", crypt, wxString("My Key"), WXSQLITE_OPEN_READONLY);
Problem is that I am getting this exception message when trying to open:
file is not a database[26]: file is not a database

utelle
Moderator
Moderator
Posts: 897
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: How to encrypt DB using wxSQLite3?

Post by utelle » Sat Aug 31, 2019 10:33 pm

In principle, your code should work. However, from your code snippets several things don't become clear:
  1. Are you encrypting an existing database or creating an empty database on which you apply encryption in the first step?
  2. Is the first step (applying the Rekey method) part of the same application as the second step (opening the encrypted database)?
  3. Did you verify that the first step did not fail and that the database file accessed in step 1 is indeed the same as in step 2? That is, that the same folder is accessed.
The most likely cause for failure in step 2 is that you are accessing a database file in a different folder which is still a plain database (or was encrypted with a different encryption scheme).

Additionally, please tell which version of wxSQLite3 and wxWidgets you are using.

MJaoune
Earned a small fee
Earned a small fee
Posts: 22
Joined: Tue Aug 20, 2019 7:37 pm

Re: How to encrypt DB using wxSQLite3?

Post by MJaoune » Sun Sep 01, 2019 1:32 am

utelle wrote:
Sat Aug 31, 2019 10:33 pm
In principle, your code should work. However, from your code snippets several things don't become clear:
  1. Are you encrypting an existing database or creating an empty database on which you apply encryption in the first step?
  2. Is the first step (applying the Rekey method) part of the same application as the second step (opening the encrypted database)?
  3. Did you verify that the first step did not fail and that the database file accessed in step 1 is indeed the same as in step 2? That is, that the same folder is accessed.
The most likely cause for failure in step 2 is that you are accessing a database file in a different folder which is still a plain database (or was encrypted with a different encryption scheme).

Additionally, please tell which version of wxSQLite3 and wxWidgets you are using.
Hello,

Thanks for replying.

1- Existing.
2- No, separate, but same system.
3- Yes probably.

I am compiling wxSQLite3 4.4.5 from source. I guess the problem might be because I am not enabling codecs when building the library? I didn't change anything before building, just downloaded the source and compiled it with ./configure and make.

utelle
Moderator
Moderator
Posts: 897
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: How to encrypt DB using wxSQLite3?

Post by utelle » Sun Sep 01, 2019 7:40 am

MJaoune wrote:
Sun Sep 01, 2019 1:32 am
1- Existing.
How big is the database file? Encrypting a large database can be a time-consuming operation, and the encrypted database can only be accessed after the rekey operation has completed. While the rekey operation is running, you must not access the database file from any other database connection.
MJaoune wrote:
Sun Sep 01, 2019 1:32 am
2- No, separate, but same system.
What do you do with the rekeyed database file? Do you copy it to a different folder for processing by the other application? If the answer is no, how do you know when to rekey/encrypt a plain database file?
MJaoune wrote:
Sun Sep 01, 2019 1:32 am
3- Yes probably.
You can verify whether the rekey operation succeeded by inspecting the first 16 bytes of the database file. If the first 16 bytes read "SQLite format 3" it is usually a plain, unencrypted database file. Under Linux you would do this for example with the hexdump command:

Code: Select all

hexdump -n 16 -C databasefile.db
Also inspect the database file that your application tries to open as an encrypted database in the same way.
MJaoune wrote:
Sun Sep 01, 2019 1:32 am
I am compiling wxSQLite3 4.4.5 from source.
Ok, so you are using the latest release.
MJaoune wrote:
Sun Sep 01, 2019 1:32 am
I guess the problem might be because I am not enabling codecs when building the library? I didn't change anything before building, just downloaded the source and compiled it with ./configure and make.
Codecs are enabled by default. That is, using configure and make as described under Installation for wxGTK will build wxSQLite3 with enabled encryption.

Either your application tries to access the plain database file (that is, wrong location) or it tries to access the file before the rekey operation completed.

MJaoune
Earned a small fee
Earned a small fee
Posts: 22
Joined: Tue Aug 20, 2019 7:37 pm

Re: How to encrypt DB using wxSQLite3?

Post by MJaoune » Sun Sep 01, 2019 9:47 am

utelle wrote:
Sun Sep 01, 2019 7:40 am
How big is the database file? Encrypting a large database can be a time-consuming operation, and the encrypted database can only be accessed after the rekey operation has completed. While the rekey operation is running, you must not access the database file from any other database connection.
A few hundred megabytes.
utelle wrote:
Sun Sep 01, 2019 7:40 am
What do you do with the rekeyed database file? Do you copy it to a different folder for processing by the other application? If the answer is no, how do you know when to rekey/encrypt a plain database file?
The encrypting application is only for encrypting, I wait for it to finish encryption then copy the db file somewhere else.
utelle wrote:
Sun Sep 01, 2019 7:40 am
You can verify whether the rekey operation succeeded by inspecting the first 16 bytes of the database file. If the first 16 bytes read "SQLite format 3" it is usually a plain, unencrypted database file. Under Linux you would do this for example with the hexdump command:

Code: Select all

hexdump -n 16 -C databasefile.db
Also inspect the database file that your application tries to open as an encrypted database in the same way.
Checking the output file with hexedit shows that the first 16 bytes are something random and not "SQLite format 3".
utelle wrote:
Sun Sep 01, 2019 7:40 am
Codecs are enabled by default. That is, using configure and make as described under Installation for wxGTK will build wxSQLite3 with enabled encryption.

Either your application tries to access the plain database file (that is, wrong location) or it tries to access the file before the rekey operation completed.
If I don't provide a wxSQLite3Cipher (That is wxSQLite3CipherAes256 in my case) to the Open function (i.e. using the "Open(const wxString &fileName, const wxString &key, int flags)" version of that function), the database opens, but not on all OS, as it throws an exception on them. So this is problem is reproduced if I provide a wxSQLite3Cipher to the Open function.


I have rebuilt wxSQLite3 from the beginning with providing --enable-codec to the codecs I need. Still same issue, if I don't provide the wxSQLite3Cipher to Open it works and opens the DB. If I do it wouldn't work.

utelle
Moderator
Moderator
Posts: 897
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: How to encrypt DB using wxSQLite3?

Post by utelle » Sun Sep 01, 2019 10:35 am

MJaoune wrote:
Sun Sep 01, 2019 9:47 am
utelle wrote:
Sun Sep 01, 2019 7:40 am
How big is the database file?
A few hundred megabytes.
utelle wrote:
Sun Sep 01, 2019 7:40 am
What do you do with the rekeyed database file? Do you copy it to a different folder for processing by the other application? If the answer is no, how do you know when to rekey/encrypt a plain database file?
The encrypting application is only for encrypting, I wait for it to finish encryption then copy the db file somewhere else.
Ok, sounds fine and should not impose any problems.
MJaoune wrote:
Sun Sep 01, 2019 9:47 am
utelle wrote:
Sun Sep 01, 2019 7:40 am
You can verify whether the rekey operation succeeded by inspecting the first 16 bytes of the database file. If the first 16 bytes read "SQLite format 3" it is usually a plain, unencrypted database file. Under Linux you would do this for example with the hexdump command:

Code: Select all

hexdump -n 16 -C databasefile.db
Also inspect the database file that your application tries to open as an encrypted database in the same way.
Checking the output file with hexedit shows that the first 16 bytes are something random and not "SQLite format 3".
Ok, so the database file has been encrypted. And if your code snippet showed the code you were actually using, the cipher should be AES256CBC.
MJaoune wrote:
Sun Sep 01, 2019 9:47 am
If I don't provide a wxSQLite3Cipher (That is wxSQLite3CipherAes256 in my case) to the Open function (i.e. using the "Open(const wxString &fileName, const wxString &key, int flags)" version of that function), the database opens, but not on all OS, as it throws an exception on them. So this is problem is reproduced if I provide a wxSQLite3Cipher to the Open function.
I'm not sure whether I fully understand what exactly you are doing. If I understand you correctly you call the wxSQLite3 Open method without explicitly passing a wxSQLite3Cipher object, but with giving the encryption key. And that works on some systems, but on others not.

With wxSQLite3 version 4.0.0 the default cipher was changed to ChaCha20. If only a key is passed to the Open method wxSQLite3 will try to open the database with the default cipher, that is, ChaCha20 for wxSQLite3 version 4.0.0 and up. And this can't work, because you encrypted the database with the cipher AES256CBC.

Could it be that some of your applications are still using a prior version of wxSQLite3? And for those opening the database works?

Of course you have to make sure that the right cipher is used. That is, either you have to provide a matching wxSQLite3Cipher object for the Open method or you have to compile wxSQLite3 with your preferred cipher as default, AES256CBC in your case.

MJaoune
Earned a small fee
Earned a small fee
Posts: 22
Joined: Tue Aug 20, 2019 7:37 pm

Re: How to encrypt DB using wxSQLite3?

Post by MJaoune » Sun Sep 01, 2019 10:40 am

utelle wrote:
Sun Sep 01, 2019 10:35 am
I'm not sure whether I fully understand what exactly you are doing. If I understand you correctly you call the wxSQLite3 Open method without explicitly passing a wxSQLite3Cipher object, but with giving the encryption key. And that works on some systems, but on others not.
Exactly.
utelle wrote:
Sun Sep 01, 2019 10:35 am
With wxSQLite3 version 4.0.0 the default cipher was changed to ChaCha20. If only a key is passed to the Open method wxSQLite3 will try to open the database with the default cipher, that is, ChaCha20 for wxSQLite3 version 4.0.0 and up. And this can't work, because you encrypted the database with the cipher AES256CBC.
Tried doing the same with ChaCha20 (Replaced AES256 with ChaCha20 for enc and dec) a few minutes ago, I get the following:
SQL logic error[1]: unsupported file format
utelle wrote:
Sun Sep 01, 2019 10:35 am
Could it be that some of your applications are still using a prior version of wxSQLite3? And for those opening the database works?
Built from source for all OS, v4.4.5.
utelle wrote:
Sun Sep 01, 2019 10:35 am
Of course you have to make sure that the right cipher is used. That is, either you have to provide a matching wxSQLite3Cipher object for the Open method or you have to compile wxSQLite3 with your preferred cipher as default, AES256CBC in your case.
But the default shouldn't matter if I am using:
Open (const wxString &fileName, const wxSQLite3Cipher &cipher, const wxString &key, int flags)
And for encryption:
ReKey (const wxSQLite3Cipher &cipher, const wxString &newKey)

utelle
Moderator
Moderator
Posts: 897
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: How to encrypt DB using wxSQLite3?

Post by utelle » Sun Sep 01, 2019 11:15 am

MJaoune wrote:
Sun Sep 01, 2019 10:40 am
utelle wrote:
Sun Sep 01, 2019 10:35 am
I'm not sure whether I fully understand what exactly you are doing. If I understand you correctly you call the wxSQLite3 Open method without explicitly passing a wxSQLite3Cipher object, but with giving the encryption key. And that works on some systems, but on others not.
Exactly.
Well, this strongly indicates that you have different versions in use. The big question is, which versions? Could it be that you have some systems where really old wxSQLite3 versions are in use? If you happen to have versions older than version 3.1.0 (May 2014) in use and if you don't have the chance to upgrade those applications then you will have to use the legacy mode of the encryption scheme.
MJaoune wrote:
Sun Sep 01, 2019 10:40 am
utelle wrote:
Sun Sep 01, 2019 10:35 am
With wxSQLite3 version 4.0.0 the default cipher was changed to ChaCha20. If only a key is passed to the Open method wxSQLite3 will try to open the database with the default cipher, that is, ChaCha20 for wxSQLite3 version 4.0.0 and up. And this can't work, because you encrypted the database with the cipher AES256CBC.
Tried doing the same with ChaCha20 (Replaced AES256 with ChaCha20 for enc and dec) a few minutes ago, I get the following:
SQL logic error[1]: unsupported file format
This is a bit strange, because usually you should get the error message that the file is not a database. On which platform do you get this error message?
MJaoune wrote:
Sun Sep 01, 2019 10:40 am
utelle wrote:
Sun Sep 01, 2019 10:35 am
Could it be that some of your applications are still using a prior version of wxSQLite3? And for those opening the database works?
Built from source for all OS, v4.4.5.
And did you make sure that your applications really link against the new wxSQLite3 library? Do you use a static or shared wxSQLite3 library?

If shared, maybe the wrong library version is referenced at runtime. Otherwise it is hard to explain that it works on some platforms, but not on all.
MJaoune wrote:
Sun Sep 01, 2019 10:40 am
utelle wrote:
Sun Sep 01, 2019 10:35 am
Of course you have to make sure that the right cipher is used. That is, either you have to provide a matching wxSQLite3Cipher object for the Open method or you have to compile wxSQLite3 with your preferred cipher as default, AES256CBC in your case.
But the default shouldn't matter if I am using:
Open (const wxString &fileName, const wxSQLite3Cipher &cipher, const wxString &key, int flags)
And for encryption:
ReKey (const wxSQLite3Cipher &cipher, const wxString &newKey)
Sure, the default doesn't matter if you select a cipher scheme explicitly on opening or rekeying a database.

However, I still have the impression that you have a version mixup somehow. For which platforms do you build your applications?

MJaoune
Earned a small fee
Earned a small fee
Posts: 22
Joined: Tue Aug 20, 2019 7:37 pm

Re: How to encrypt DB using wxSQLite3?

Post by MJaoune » Sun Sep 01, 2019 11:44 am

utelle wrote:
Sun Sep 01, 2019 11:15 am
However, I still have the impression that you have a version mixup somehow. For which platforms do you build your applications?
Linux using wxGTK 3.0.4 (GTK3), wxSQLite3 4.4.5 (Shared). And Windows 7, wxMSW 3.1.2 (VC9), wxSQLite3 4.4.5 (Static).

But the problem exists in the same platform, as I copy the encrypted DB files to another application in the same platform. The problem that is platform dependent is when I do not provide the cipher I used to Open(), it works in Linux, but not in Windows XP.

I have never used wxSQLite3 prior to v4.4.5 (As I knew about it lately).

utelle
Moderator
Moderator
Posts: 897
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: How to encrypt DB using wxSQLite3?

Post by utelle » Sun Sep 01, 2019 12:27 pm

MJaoune wrote:
Sun Sep 01, 2019 11:44 am
Linux using wxGTK 3.0.4 (GTK3), wxSQLite3 4.4.5 (Shared). And Windows 7, wxMSW 3.1.2 (VC9), wxSQLite3 4.4.5 (Static).

But the problem exists in the same platform, as I copy the encrypted DB files to another application in the same platform. The problem that is platform dependent is when I do not provide the cipher I used to Open(), it works in Linux, but not in Windows XP.
If the application, you use to primarily encrypt the database (using Rekey method), passes a wxSQLite3Cipher object to the Rekey method to select a certain cipher, you need to do that for the other applications that expect an encrypted database, too, unless you compiled with a matching default cipher.

Is there a reason why you don't use the default cipher, ChaCha20 that is?

Are you sure you use the same passphrase for rekeying and opening the database? If the passphrase contains non-ASCII characters you might have encoding differences. Just wild guessing, though. In fact, wxSQLite3 is available and in use for many years, and I never experienced such difficulties as you describe, and have never heard from other developers experiencing such problems.

Without seeing the actual code used for accessing the database it is almost impossible to further investigate your problem as I'm running out of ideas.
MJaoune wrote:
Sun Sep 01, 2019 11:44 am
I have never used wxSQLite3 prior to v4.4.5 (As I knew about it lately).
Ok. I had to ask, because it seemed to be the most plausible explanation.

MJaoune
Earned a small fee
Earned a small fee
Posts: 22
Joined: Tue Aug 20, 2019 7:37 pm

Re: How to encrypt DB using wxSQLite3?

Post by MJaoune » Sun Sep 01, 2019 12:37 pm

utelle wrote:
Sun Sep 01, 2019 12:27 pm
If the application, you use to primarily encrypt the database (using Rekey method), passes a wxSQLite3Cipher object to the Rekey method to select a certain cipher, you need to do that for the other applications that expect an encrypted database, too.
That exact opposite is happening! When I provide the wxSQLite3Cipher object when encrypting, I have to NOT use it when using Open() for the decryption to work and I can open the database file successfully. Using the same cipher object values gives the error I mentioned.
utelle wrote:
Sun Sep 01, 2019 12:27 pm
Is there a reason why you don't use the default cipher, ChaCha20 that is?
No problem with ChaCha20, but I don't want incompatibility issues to happen later on, I want to be sure the right cipher is being used by providing the cipher object in ReKey() and Open().
utelle wrote:
Sun Sep 01, 2019 12:27 pm
Are you sure you use the same passphrase for rekeying and opening the database? If the passphrase contains non-ASCII characters you might have encoding differences. Just wild guessing, though. In fact, wxSQLite3 is available and in use for many years, and I never experienced such difficulties as you describe, and have never heard from other developers experiencing such problems.
I printed each byte in the key string in both applications (Encrypter and decrypter), same key byte by byte (Yes even the null terminator).
utelle wrote:
Sun Sep 01, 2019 12:27 pm
Without seeing the actual code used for accessing the database it is almost impossible to further investigate your problem as I'm running out of ideas.
The one I put in the main post, the only difference is that I start executing my needed queries after.

Tried using "SetLegacy(false);" in both applications, same problem.

Edit: Using "SetLegacy(true);" in both applications reports the old error message:
file is not a database[26]: file is not a database
Rather than: "SQL logic error[1]: unsupported file format"

MJaoune
Earned a small fee
Earned a small fee
Posts: 22
Joined: Tue Aug 20, 2019 7:37 pm

Re: How to encrypt DB using wxSQLite3?

Post by MJaoune » Sun Sep 01, 2019 1:21 pm

I have returned to the source code of wxSQLite3 and have found that the ReKey function is the same function with or without the cipher, it doesn't even use the cipher parameter:

Code: Select all

void wxSQLite3Database::ReKey(const wxSQLite3Cipher& cipher, const wxMemoryBuffer& newKey)
{
#if WXSQLITE3_HAVE_CODEC
  CheckDatabase();
  int rc = sqlite3_rekey(m_db->m_db, newKey.GetData(), (int) newKey.GetDataLen());
  if (rc != SQLITE_OK)
  {
    const char* localError = sqlite3_errmsg(m_db->m_db);
    throw wxSQLite3Exception(rc, wxString::FromUTF8(localError));
  }
#else
  wxUnusedVar(newKey);
  throw wxSQLite3Exception(WXSQLITE_ERROR, wxERRMSG_NOCODEC);
#endif
}
All other ReKey() functions call the function I mentioned above (They are wrappers for it).

So this is a glitch in your library, that ReKey() does not use the cipher provided for encryption. This explains why I need to not provide the cipher when using Open() for decryption to work, because encryption didn't make use of it.

utelle
Moderator
Moderator
Posts: 897
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: How to encrypt DB using wxSQLite3?

Post by utelle » Sun Sep 01, 2019 7:31 pm

MJaoune wrote:
Sun Sep 01, 2019 1:21 pm
[...]
So this is a glitch in your library, that ReKey() does not use the cipher provided for encryption. This explains why I need to not provide the cipher when using Open() for decryption to work, because encryption didn't make use of it.
Ouch ... this is indeed a bug in wxSQLite3. Interestingly it wasn't detected earlier by other users of the library. However, the Rekey method is typically used infrequently, because the typical use case is to create an encrypted database from scratch or to use the SQLite shell for this purpose.

Thanks for reporting.

MJaoune
Earned a small fee
Earned a small fee
Posts: 22
Joined: Tue Aug 20, 2019 7:37 pm

Re: How to encrypt DB using wxSQLite3?

Post by MJaoune » Mon Sep 02, 2019 11:54 am

Utelle has released a fixed and this problem is now fully solved in wxSQLite3 v4.4.6 and above.

Post Reply