SQLite3 + encryption for WinCE (arm4i)

[vincococka]

Hi all,

let me share with you following compiled versions of SQLite for Windows CE 5/6 (arm4i).

Download URL:
  SQLite 3.9.2 (+ version with AES-128 enc.)
  MD5 checksums:
  db83043f404e866dbdf4b9c4554fb6ca sqlite3ce.dll
  8b27b589c91fb6eadf8fc5fb9e22098f  sqlite3ce-aes.dll


Built with following preprocessor macros:

Code: [Select]

_UNICODE
UNICODE
NO_TCL
SQLITE_DEFAULT_FOREIGN_KEYS=1
SQLITE_DEFAULT_MMAP_SIZE=2097152
SQLITE_DISABLE_LFS
SQLITE_OMIT_TCL_VARIABLE
SQLITE_OMIT_TRACE
SQLITE_HAS_CODEC
CODEC_TYPE=CODEC_TYPE_AES128
SQLITE_CORE;THREADSAFE=0
SQLITE_THREADSAFE=0
SQLITE_USER_AUTHENTICATION
SQLITE_ENABLE_EXTFUNC

Download URL:
  SQLite 3.11.1.0 (with AES-128 encryption & threadsafe=1)

Built with following preprocessor macros:

Code: [Select]

_UNICODE;UNICODE;NO_TCL;SQLITE_DEFAULT_FOREIGN_KEYS=1;SQLITE_DEFAULT_MMAP_SIZE=2097152;SQLITE_DISABLE_LFS;SQLITE_OMIT_TCL_VARIABLE;SQLITE_OMIT_TRACE;SQLITE_HAS_CODEC;CODEC_TYPE=CODEC_TYPE_AES128;SQLITE_CORE;THREADSAFE=1;SQLITE_THREADSAFE=1;SQLITE_USER_AUTHENTICATION;SQLITE_ENABLE_EXTFUNC

To encrypt database in FreePascal / Lazarus / Delphi you have to first initialize the database with new password via sqlite3_rekey. After encrypting it is enough to use sqlite3_open following with sqlite3_key call...

// Encryption support
//
function sqlite3_key(db: PSQLite3; const pKey: PAnsiChar; nKey: Integer): Integer; cdecl; external 'sqlite3ce-aes.dll';
function sqlite3_rekey(db: PSQLite3; const pKey: PAnsiChar; nKey: Integer): Integer; cdecl; external 'sqlite3ce-aes.dll';

Before opening database it is good to check first 6 bytes of file to compare them with "SQLite" - this means db is unencrypted so you dont have to call sqlite3_key after sqlite3_open.

Examples:

1, opening and encrypting DB file

Code: [Select]

sqlite3_open('test.db', db);
sqlite3_rekey(db, 'test', Length('test'));
// then use db as usual
2, opening already encrypted DB

Code: [Select]

sqlite3_open('test.db', db);
sqlite3_key(db, 'test', Length('test'));
// then use db as usual
3, decrypt database file

Code: [Select]

sqlite3_open('test.db', db);
sqlite3_key(db, 'test', Length('test'));
sqlite3_rekey(db, '', 0); // length 0 will decrypt database file
// then use db as usual

Regards,
  Jan

[bambamns]

Thank You 

First test shows positive results.

I'll put it in production very soon.

[vincococka]

Hi,

I`m glad that it is working for you.
I was in need of having current SQLite for on small project running on WinCE, so I decided to fire up Visual Studio and compile it for myself .

Just consider that it is compiled with O3 optimization and it was optimized for size (not for speed, because file has 750KB) + with following DEFINES:
  SQLITE_ENABLE_COLUMN_METADATA
  NO_TCL
  SQLITE_OMIT_TRACE

When new version comes out I`ll probably compile it again and put it here somewhere in "clouds" .

Greets,
  Vince

[bambamns]

This is great !

If you don't mind , it will be very helpful to compile another version with speed optimization, so we can test if there are noticeable differences in performance.

Any way - thx

[vincococka]

Hi,

I`ve uploaded freshly compiled build that has optimization preference set to speed (not size):
  http://www.filedropper.com/sqlite3-385-arm4i-speed

[vincococka]

Hi all,

I`ve uploaded new version for you.. Enjoy!

Compiled without COLUMN_METADATA and TRACE, optimization preference is for speed (not size).

[bambamns]

Thx,

Can you explain :

Compiled without COLUMN_METADATA and TRACE...

[vincococka]

Hi,

I`m really sorry for confusion.

Here are MACROS that I`ve set before compilation of release 3.8.6 (what a significant version )
NO_TCL
SQLITE_OMIT_TRACE
SQLITE_DEFAULT_FOREIGN_KEYS=1

V.

[vincococka]

Hi all,

here we go again with new release of SQLite library 3.8.7:

http://www.filedropper.com/sqlite3ce

[bambamns]

Nice,

Since you put :
SQLITE_DEFAULT_FOREIGN_KEYS=1

sqlite finally  take care for foreign keys.

Thx

[vincococka]

Hi,

yeah, referential integrity is a must.
Btw, does it work as expected?

Regards,
   V.

[bambamns]

Ye it works like it has to be.

[bambamns]

I've noticed some problems with this version so be careful when you use it.
It has some issue with unfinished transaction so the final result is started but not finished transaction and no access to database.

I'm back on production with 3.8.5 - 3.8.6 and 3.8.7 are on test.

[vincococka]

Hi all,

for those who are interested I`ve compiled current 3.8.7.1 version of SQLite for WinCE platform..
Enjoy!

[bambamns]

Nice - but where is the link ?