Fully encrypted SQLite DB with PB 5.71 and SQLCipher.dll

[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

[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?

[dige]

Great Markus! Thx for sharing!!

[infratec]

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.

[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?

[AAT]

You can use a USB flash drive with a binary file as a password (key).

[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?

[skywalk]

No, the SQLite database remains cross platform, but the tools required to decrypt are not.

[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

[LiK137]

Thanx Fred for including the feature and Markus for detailed Step By Step POW

[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?

[totorcalais]

I have same error on 5.73...

[loulou2522]

For me it works well. No problem I have Windows 10 family and purebasic 73 x86 i don't try on X64 version

[loulou2522]

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

[loulou2522]

How is-it possible to decrypt a databse crypt wit SQLChipher and purebasic ?
Thanks in advance