Recent

Author Topic: SQLite3 + encryption for WinCE (arm4i)  (Read 24966 times)

vincococka

  • Full Member
  • ***
  • Posts: 101
SQLite3 + encryption for WinCE (arm4i)
« on: June 23, 2014, 08:59:29 pm »
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
« Last Edit: March 09, 2016, 12:20:03 am by vincococka »
---
  Guide me God and I`ll find you

bambamns

  • Full Member
  • ***
  • Posts: 223
Re: SQLite3 3.8.5 for WinCE (arm4i)
« Reply #1 on: June 24, 2014, 04:18:01 am »
Thank You  ;)

First test shows positive results.

I'll put it in production very soon.
Lazarus 1.8.4 + FPC 2.6.4 x86 (rebuild) and Lazarus 2.0, Windows 7 x64, unless otherwise specified

vincococka

  • Full Member
  • ***
  • Posts: 101
Re: SQLite3 3.8.5 for WinCE (arm4i)
« Reply #2 on: June 24, 2014, 01:19:42 pm »
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
---
  Guide me God and I`ll find you

bambamns

  • Full Member
  • ***
  • Posts: 223
Re: SQLite3 3.8.5 for WinCE (arm4i)
« Reply #3 on: June 25, 2014, 06:16:55 am »
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
Lazarus 1.8.4 + FPC 2.6.4 x86 (rebuild) and Lazarus 2.0, Windows 7 x64, unless otherwise specified

vincococka

  • Full Member
  • ***
  • Posts: 101
Re: SQLite3 3.8.5 for WinCE (arm4i)
« Reply #4 on: June 25, 2014, 10:23:32 am »
Hi,

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

vincococka

  • Full Member
  • ***
  • Posts: 101
Re: SQLite3 3.8.6 for WinCE (arm4i)
« Reply #5 on: August 17, 2014, 09:39:44 pm »
Hi all,

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

Compiled without COLUMN_METADATA and TRACE, optimization preference is for speed (not size).
« Last Edit: August 17, 2014, 09:41:27 pm by vincococka »
---
  Guide me God and I`ll find you

bambamns

  • Full Member
  • ***
  • Posts: 223
Re: SQLite3 3.8.6 for WinCE (arm4i)
« Reply #6 on: August 28, 2014, 04:07:15 am »
Thx,

Can you explain :
Quote
Compiled without COLUMN_METADATA and TRACE...
Lazarus 1.8.4 + FPC 2.6.4 x86 (rebuild) and Lazarus 2.0, Windows 7 x64, unless otherwise specified

vincococka

  • Full Member
  • ***
  • Posts: 101
Re: SQLite3 3.8.6 for WinCE (arm4i)
« Reply #7 on: August 29, 2014, 01:09:36 am »
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 :D)
NO_TCL
SQLITE_OMIT_TRACE
SQLITE_DEFAULT_FOREIGN_KEYS=1

V.
---
  Guide me God and I`ll find you

vincococka

  • Full Member
  • ***
  • Posts: 101
Re: SQLite3 3.8.7 for WinCE (arm4i)
« Reply #8 on: October 19, 2014, 11:05:08 pm »
Hi all,

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

http://www.filedropper.com/sqlite3ce
---
  Guide me God and I`ll find you

bambamns

  • Full Member
  • ***
  • Posts: 223
Re: SQLite3 3.8.7 for WinCE (arm4i)
« Reply #9 on: October 25, 2014, 03:34:39 am »
Nice,

Since you put :
SQLITE_DEFAULT_FOREIGN_KEYS=1

sqlite finally  take care for foreign keys.

Thx
Lazarus 1.8.4 + FPC 2.6.4 x86 (rebuild) and Lazarus 2.0, Windows 7 x64, unless otherwise specified

vincococka

  • Full Member
  • ***
  • Posts: 101
Re: SQLite3 3.8.7 for WinCE (arm4i)
« Reply #10 on: October 26, 2014, 02:04:59 am »
Hi,

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

Regards,
   V.
---
  Guide me God and I`ll find you

bambamns

  • Full Member
  • ***
  • Posts: 223
Re: SQLite3 3.8.7 for WinCE (arm4i)
« Reply #11 on: October 29, 2014, 05:25:33 am »
Ye it works like it has to be.
Lazarus 1.8.4 + FPC 2.6.4 x86 (rebuild) and Lazarus 2.0, Windows 7 x64, unless otherwise specified

bambamns

  • Full Member
  • ***
  • Posts: 223
Re: SQLite3 3.8.7 for WinCE (arm4i)
« Reply #12 on: November 07, 2014, 05:59:23 am »
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.
Lazarus 1.8.4 + FPC 2.6.4 x86 (rebuild) and Lazarus 2.0, Windows 7 x64, unless otherwise specified

vincococka

  • Full Member
  • ***
  • Posts: 101
Re: SQLite3 3.8.7.1 for WinCE (arm4i)
« Reply #13 on: November 09, 2014, 11:10:24 pm »
Hi all,

for those who are interested I`ve compiled current 3.8.7.1 version of SQLite for WinCE platform..
Enjoy!
---
  Guide me God and I`ll find you

bambamns

  • Full Member
  • ***
  • Posts: 223
Re: SQLite3 3.8.7.1 for WinCE (arm4i)
« Reply #14 on: November 10, 2014, 05:29:36 am »
Nice - but where is the link ?
Lazarus 1.8.4 + FPC 2.6.4 x86 (rebuild) and Lazarus 2.0, Windows 7 x64, unless otherwise specified