PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

https://www.purebasic.fr/english/viewtopic.php?t=73046

Page 1 of 2

Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Tue Jun 18, 2019 3:06 pm
by MarkOtt
With PureBasic 5.71 (Windows) it is now possible to have a fully encrypted SQLite database, if using the new PB feature to specify an external SQLite-DLL in UseSQLiteDatabase().

SQLCipher from Zetetic is an open-source extension integrated in SQLite.dll with fully transparent 256-bit AES encryption. It has good performance with as little as 5-15% overhead for encryption and 100% of data in the database file is encrypted. Otherwise all features of SQLite can be used as normal.
https://www.zetetic.net/sqlcipher/
https://www.zetetic.net/sqlcipher/documentation/

There is only one downgrade: Zetetic does not offer compiled DLLs for free and doing it oneself from the free available source code is a bit tricky.
http://www.jerryrw.com/howtocompile.php

Unfortunately Zetetic offers compiled libs only for a fee of US$ 499.

But there is another open source application "DB Browser for SQLite" which contains the premade replacement for "sqlite.dll" called "sqlcipher.dll" (current considered SQLite version is 3.27.2.0).
https://sqlitebrowser.org/dl/

Here is an easy and well working example (derived from the PB example "database.pb"):

Code: Select all

UseSQLiteDatabase("sqlcipher.dll")    ; Use own DLL  (at least PureBasic 5.71 beta 1 needed)

Procedure CheckDatabaseUpdate(Database, Query$)
  Result = DatabaseUpdate(Database, Query$)
  If Result = 0
    Debug DatabaseError()
  EndIf
  ProcedureReturn Result
EndProcedure

DatabaseFile$ = "DatabaseAES.sqlite"

If CreateFile(0, DatabaseFile$)
  CloseFile(0)
  
  ; create ciphered database
  If OpenDatabase(0, DatabaseFile$, "", "")
    
    CheckDatabaseUpdate(0, "PRAGMA key = 'This is my pass phrase'")  ; initiate encryption
    
    CheckDatabaseUpdate(0, "CREATE TABLE food (name CHAR(50), weight INT)")
    CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('apple', '10')")
    CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('pear', '5')")
    CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('banana', '20')")
    
    CloseDatabase(0)
  Else
    Debug "Can't open database !"
  EndIf
  
  ; read ciphered database
  If OpenDatabase(0, DatabaseFile$, "", "")
    
    CheckDatabaseUpdate(0, "PRAGMA key = 'This is my pass phrase'")  ; initiate decryption
    
    If DatabaseQuery(0, "SELECT count(*) FROM sqlite_master")        ; check for valid key
      
      If DatabaseQuery(0, "SELECT * FROM food WHERE weight > 7")
        While NextDatabaseRow(0)
          Debug GetDatabaseString(0, 0)
        Wend
        FinishDatabaseQuery(0)
      EndIf
      
    Else   
      Debug "Can't decrypt database !"
    EndIf
    
    CloseDatabase(0)
  Else
    Debug "Can't open database !"
  EndIf
  
Else
  Debug "Can't create the database file !"
EndIf
Best regards
Markus

Re: Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Tue Jun 18, 2019 6:50 pm
by MarkOtt
What is the best way to pass a password-phrase to the SQL PRAGMA statement, that the password is hidden in the program as good as possible?

CheckDatabaseUpdate(0, "PRAGMA key = 'This is my pass phrase'")

Any good ideas?

Re: Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Wed Jun 19, 2019 7:45 am
by dige
Great Markus! Thx for sharing!! :D

Re: Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Wed Jun 19, 2019 12:58 pm
by infratec
MarkOtt wrote:What is the best way to pass a password-phrase to the SQL PRAGMA statement, that the password is hidden in the program as good as possible?

CheckDatabaseUpdate(0, "PRAGMA key = 'This is my pass phrase'")
You can use a variable Password$ and decode the password at runtime.

Code: Select all

AESDecoder(?CryptedPassword, @Password$, xxx, *Key ...)
CheckDatabaseUpdate(0, "PRAGMA key = '" + Password$ + "'")
And use a datasection for the crypted password maybe via includeBinary.

Re: Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Thu Jun 20, 2019 8:59 am
by MarkOtt
Thank you very much for your hint, infratec.

Then I have 3 options:
- Including the password in the executable: everyone having the program can access the db.
- Deriving the password from a personal license code: only this user can access the db.
- Always asking for a password for db creation and editing (password changing is also easily possible by "PRAGMA rekey") .

Or do I overlook something else here?

Re: Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Fri Jun 21, 2019 5:28 am
by AAT
You can use a USB flash drive with a binary file as a password (key).

Re: Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Mon Jul 15, 2019 5:32 pm
by kpeters58
The Zetetic solutions does look interesting.

However, after reading the following paragraph in their docs:
SQLCipher does not implement its own encryption. Instead it uses the widely available encryption libraries like OpenSSL libcrypto, LibTomCrypt, and CommonCrypto for all cryptographic functions. The cryptography provider used depends on the platform and configuration options.

I wonder what effect this will have on SQLite cross-platform file format? Sounds as if that might break if one uses their DLL - which would be unacceptable to me in most use cases.

Has anyone tested that yet?

Re: Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Mon Jul 15, 2019 7:37 pm
by skywalk
No, the SQLite database remains cross platform, but the tools required to decrypt are not.

Re: Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Thu Jul 18, 2019 1:01 am
by vwidmer
The works for me in linux

Code: Select all

UseSQLiteDatabase("/usr/lib/libsqlcipher.so")    ; Use own DLL  (at least PureBasic 5.71 beta 1 needed)

Procedure CheckDatabaseUpdate(Database, Query$)
  Result = DatabaseUpdate(Database, Query$)
  If Result = 0
    Debug DatabaseError()
  EndIf
  ProcedureReturn Result
EndProcedure

DatabaseFile$ = "DatabaseAES.sqlite"

If CreateFile(0, DatabaseFile$)
  CloseFile(0)
 
  ; create ciphered database
  If OpenDatabase(0, DatabaseFile$, "", "")
   
    CheckDatabaseUpdate(0, "PRAGMA key = 'This is my pass phrase'")  ; initiate encryption
   
    CheckDatabaseUpdate(0, "CREATE TABLE food (name CHAR(50), weight INT)")
    CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('apple', '10')")
    CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('pear', '5')")
    CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('banana', '20')")
   
    CloseDatabase(0)
  Else
    Debug "Can't open database !"
  EndIf
 
  ; read ciphered database
  If OpenDatabase(0, DatabaseFile$, "", "")
   
    CheckDatabaseUpdate(0, "PRAGMA key = 'This is my pass phrase'")  ; initiate decryption
   
    If DatabaseQuery(0, "SELECT count(*) FROM sqlite_master")        ; check for valid key
     
      If DatabaseQuery(0, "SELECT * FROM food WHERE weight > 7")
        While NextDatabaseRow(0)
          Debug GetDatabaseString(0, 0)
        Wend
        FinishDatabaseQuery(0)
      EndIf
     
    Else   
      Debug "Can't decrypt database !"
    EndIf
   
    CloseDatabase(0)
  Else
    Debug "Can't open database !"
  EndIf
 
Else
  Debug "Can't create the database file !"
EndIf

Re: Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Tue Feb 25, 2020 2:37 pm
by LiK137
Thanx Fred for including the feature and Markus for detailed Step By Step POW

Re: Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Fri May 15, 2020 4:06 pm
by Yuri_D
Hello!

I'm not sure what's wrong but when I'm trying to use this solution I'm getting an error on OpenDatabase() command (PB v5.72):

[16:55:33] [ERROR] UseODBCDatabase(), UseSQLiteDatase() or UsePostgreSQLDatabase() need to be called before using database commands.

Did anybody try it on later PB releases?

Re: Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Tue Dec 15, 2020 12:21 am
by totorcalais
I have same error on 5.73...

Re: Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Fri Jan 01, 2021 5:58 am
by loulou2522
For me it works well. No problem I have Windows 10 family and purebasic 73 x86 i don't try on X64 version

Re: Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Mon Jan 04, 2021 3:32 pm
by loulou2522
infratec wrote:
MarkOtt wrote:What is the best way to pass a password-phrase to the SQL PRAGMA statement, that the password is hidden in the program as good as possible?

CheckDatabaseUpdate(0, "PRAGMA key = 'This is my pass phrase'")
You can use a variable Password$ and decode the password at runtime.

Code: Select all

AESDecoder(?CryptedPassword, @Password$, xxx, *Key ...)
CheckDatabaseUpdate(0, "PRAGMA key = '" + Password$ + "'")
And use a datasection for the crypted password maybe via includeBinary.
HI Infratec,
Can you explain me what runtime is ? and the utility to decode password in runtime ? Thanks in advance

Re: Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

Posted: Mon Jan 04, 2021 5:06 pm
by loulou2522
How is-it possible to decrypt a databse crypt wit SQLChipher and purebasic ?
Thanks in advance
All times are UTC+01:00
Page 1 of 2

Powered by phpBB® Forum Software © phpBB Limited