10.4 sydney issues

Greg0r · 2020-06-05 10:32:23

1. Sqlite demo does not work for me do not passes the tests on 10.4 See this: https://i.imgur.com/2SqXXmC.png
2. When update to Sqlite: 3.32.2 is planned ?

Last edited by Greg0r (2020-06-05 12:02:08)

ab · 2020-06-05 12:37:13

I don't have Sidney 10.4 and there is no community edition yet, so I can't investigate any further.
Do you mean TestSQL3 demo?

SQlite 3.32.2 is planned soon.
I waited for the latest 3.32.xxxx fixes - there are always last time minor fixes with SQlite3: the 3.xx.0 version is never the final.

Greg0r · 2020-06-05 13:22:55

ab wrote:

I don't have Sidney 10.4 and there is no community edition yet, so I can't investigate any further.
Do you mean TestSQL3 demo?
SQlite 3.32.2 is planned soon.
I waited for the latest 3.32.xxxx fixes - there are always last time minor fixes with SQlite3: the 3.xx.0 version is never the final.

Yes, TestSQL3 demo.
Correct about Sqlite minor version : )

ab · 2020-06-05 15:08:09

About SQlite3 3.32.xxx there is a big problem with codecs.

Critical changes to the public SQLite code were introduced on Feb 7, 2020: “Simplify the code by removing the unsupported and undocumented SQLITE_HAS_CODEC compile-time option”. With the release of SQLite version 3.32.0 on May 22, 2020 these changes finally took officially effect, although they weren't officially announced.

As an unexpected consequence, we are NOT ANY MORE able to compile the new SQlite3 amalgamation with our encryption patch.
Reverting the patch is not an option, since it not a few lines of code, and it mess with SQLite3 VM opcodes.
Perhaps a dedicated VFS may be the solution - this is the path choosen by https://github.com/utelle/SQLite3MultipleCiphers for instance.
But a VFS is a lot of work, and performance may also decrease.

Any feedback is welcome!
If anyone has an idea...

AOG · 2020-06-05 15:21:35

We could fork sqlite3 ... ;-)

edwinsn · 2020-06-05 17:03:45

Is there any way to ask the original SQLite author for help?

ab · 2020-06-05 17:06:13

About Sydney 10.4 issue, please check https://synopse.info/fossil/info/73a73e3bf8

Perhaps they did change the FinalizeRecord() implementation, and the fields are not released in order, but by type. Since I don't have the RTL source code (System.pas) I can't validate this behavior.
The idea is not to use IObjectDynArray any more, but high-level ObjArrayAdd/Delete/Clear functions, or a TDynArray/TSynDictionary.

Greg0r · 2020-06-05 17:58:06

@ab, test TestSQL3 worked, thanks,
sad news about troubles with encryption custom patch,
quick latest versions of sqlite was BIG mORMot advantage..

Last edited by Greg0r (2020-06-05 18:06:48)

utelle · 2020-06-06 07:58:37

ab wrote:

About SQlite3 3.32.xxx there is a big problem with codecs.
Critical changes to the public SQLite code were introduced on Feb 7, 2020: “Simplify the code by removing the unsupported and undocumented SQLITE_HAS_CODEC compile-time option”. With the release of SQLite version 3.32.0 on May 22, 2020 these changes finally took officially effect, although they weren't officially announced.
As an unexpected consequence, we are NOT ANY MORE able to compile the new SQlite3 amalgamation with our encryption patch.
Reverting the patch is not an option, since it not a few lines of code, and it mess with SQLite3 VM opcodes.
Perhaps a dedicated VFS may be the solution - this is the path choosen by https://github.com/utelle/SQLite3MultipleCiphers for instance.
But a VFS is a lot of work, and performance may also decrease.

As the author of wxSQLite3, a SQLite3 wrapper with encryption support, and SQLite3MultipleCiphers, which is based on a VFS shim, I'd like to add that I was confronted with the very same decision: (1) fork SQLite3 and merge upstream changes, or (2) implement a VFS shim. I came to the conclusion that maintaining a fork would incur growing pains in the long run. Implementing a VFS shim is indeed quite some work, but IMHO much less than maintaining a SQLite3 fork.

According to an answer I got on Zetetic's SQLCipher forum, they seem to intend to maintain their own SQLite3 fork. However, looking at a recent SQLCipher post it is unclear at the moment, whether they will make their solution publicly available or not. If yes, you may be able to adapt it to your implementation of the SQLite3 encryption extension.

Regarding performance of a VFS shim I doubt that it will significantly decrease. Yes, the VFS shim adds one level of indirection, but compared to the time required for encrypting or decrypting a database page this will be neglectable.

The VFS layer is rather dumb and "knows" (almost) nothing about internal SQLite3 structures. Therefore the "hard work" in implementing the VFS shim is to analyze how SQLite accesses the database files and how essential information (like the page number) can be deduced. However, these obstacles are already out of the way.

A disadvantage of SQLite3MultipleCiphers is that it requires patches to the SQLite3 amalgamation. This was not necessary for my previous solution, based on the SQLITE_HAS_CODEC API. Although there are only 4 patches - all of them very small -, I would have preferred a solution without them. Since you intend to support a single encryption scheme only, you might get away without patching the SQLite3 amalgamation code. However, you mentioned "messing with SQLite3 VM opcodes", so it is likely that you will be forced to apply patches, too.

utelle · 2020-06-06 08:08:27

edwinsn wrote:

Is there any way to ask the original SQLite author for help?

It is very unlikely to get help from the original author, D.R. Hipp in this matter. In fact, it was his decision to remove the SQLITE_HAS_CODEC API, on which most independent SQLite encryption extensions were based up to now. Although that API was part of SQLite for over 15 years, it was an undocumented feature, and D.R. Hipp is of course free to remove undocumented features any time he wants.

D.R. Hipp offers a commercial SQLite Encryption Extension (SEE) at a price of 2000 USD. This is the "help" you can get ... if you can afford it.

Greg0r · 2020-06-06 09:19:00

Agree with utelle, that author will not help there, probably intentional block even
I don't think that this change will be ever reverted

I have no idea for now which method will be the best and work stable and fast, but my imagination and experience tells me that forking SQLite is the worst possible idea.
Perfect solution should also do not require to touch amalgamation c oryginal files.

Last edited by Greg0r (2020-06-06 09:20:01)

edwinsn · 2020-06-06 09:30:44

Thanks @utelle for the very insightful input! I guess @ab can benefit from it.

So far my impression is that a VFS layer is the way to go. I guess forking and adopting all upstream changes in the future will be a lot of work and more error-prone.

utelle · 2020-06-06 10:39:09

Greg0r wrote:

Agree with utelle, that author will not help there, probably intentional block even

I don't think that D.R. Hipp made this decision to annoy users/developers. In the long run it certainly frees him to further improve SQLite and to introduce new exciting features.

Greg0r wrote:

I don't think that this change will be ever reverted

Absolutely right. This post on the SQLite Forum shows that obviously the commercial SQLite Encryption Extension (SEE) was updated as well. And my guess is that it is now based on a VFS implementation (although I can't be absolutely sure about that).

Greg0r wrote:

I have no idea for now which method will be the best and work stable and fast, but my imagination and experience tells me that forking SQLite is the worst possible idea.

A VFS shim is currently the only way to intercept SQLite's file I/O without requiring to tamper too much with SQLite's core implementation. So a VFS based implementation of the encryption extension is a rather logical path.

Greg0r wrote:

Perfect solution should also do not require to touch amalgamation c oryginal files.

I fully agree, but it will be quite difficult to accomplish this goal, if one wants to preserve full compatibility with the previous SQLITE_HAS_CODEC API. For example, SQLite's ATTACH statement has an undocumented KEY parameter for setting the passphrase for an attached database. In the public SQLite version 3.32.x and later this parameter is not passed forward anywhere, but silently ignored. If this feature is used in user applications there is no way to avoid patching the SQLite code, unfortunately. In user applications the use of the KEY could be avoided by using an URI parameter in the database file name. However, this requires to modify the code of the user application accordingly.

ab · 2020-06-06 12:39:16

@utelle
Thanks a lot for the input.
I already followed your work on encryption, and I guess following https://github.com/utelle/SQLite3MultipleCiphers path is the only solution.

Since we will start working on VFS for encryption, I guess I would perhaps add read-only compression of an existing database.
Our ORM is already able to do sharding of SQlite3 databases, e.g. for logging SOA calls. Check https://synopse.info/files/html/api-1.1 … AGESHARDDB and https://synopse.info/files/html/api-1.1 … ORAGESHARD
It is a pretty common practice to have a database become read-only after a while. With a proper VFS, we may be able to compress the pages with a very efficient algorithm like https://synopse.info/files/html/api-1.18/SynLizard.html and optional encryption, of course.

edwinsn · 2020-06-06 12:49:41

ab wrote:

I guess I would perhaps add read-only compression of an existing database.

Do you mean after the db's compressed, it'll become readonly?

MC · 2020-06-06 16:18:26

Hello

I use

sqlite3.serialize

and

sqlite3.deserialize

in order to encrypt/decrypt SQLite database with ECC to/from disk.

Please see https://synopse.info/forum/viewtopic.ph … 926#p29926.

In pratice I use :memory: database, save it to memory stream and encrypt the stream to disk.

Forloading, I load file to memory stream, decrypt it, and load in memory database with decrypted stream.

I hope it could be useful.

Last edited by MC (2020-06-06 16:25:50)

ab · 2020-06-06 21:29:46

@edwin
Yes, compression for read-only DB.
Compressing data updates could lead to a lot of fragmentation - unless you need to have very complex algotithms. Only if there is inserts, and seldom update, with huge transactions, then maybe compressing a read/write DB is managable.

@MC
You are right: serialize/unserialize could be a good idea to encrypt and compress an archived sharded DB.
What is the performance you obtain?
Easier than VFS. But only if the db file is not too big, and could stay in memory.
With our SynLizard, we can uncompress at more than 2GB/second so when archiving such a DB on a spinning drive, we could get good performance.

utelle · 2020-06-07 11:50:16

MC wrote:

I use sqlite3.serialize and sqlite3.deserialize in order to encrypt/decrypt SQLite database with ECC to/from disk.
In pratice I use :memory: database, save it to memory stream and encrypt the stream to disk.
Forloading, I load file to memory stream, decrypt it, and load in memory database with decrypted stream.

There are certainly use cases for which the serialize/deserialize approach makes sense, e.g. read-only databases which are not too big.

In general, using solely a memory based database is not such a good idea, because it violates the D(uration) of the ACID principle. For example, in case of a power loss all database modifications are gone.

An alternative to transparent encryption of the whole database can be to encrypt only security critical database content. For example, the commercial variant of SQLCipher offers additional SQL functions to encrypt and decrypt data. The advantage of that approach is that is doesn't require any modifications of the SQLite code. The disadvantage is that you can't use SQL's strengths to operate on encrypted data, like indexing, searching etc.

MC · 2020-06-08 06:52:30

@ab

At first, I tried to create a VFS and in particular by taking inspiration from https://www.developpez.com/actu/244423/ … ourlourou/.

But I didn't succeed. Too complex for me.

So I used serialize and deserialize which is much simpler and leaves me a complete autonomy on the way to cipher (my ECC AEAD contribution).

After many tests, I was surprised by the performance.

This is in production at my customers.

I have database sizes of 250 MB without any problem.

@utelle

In my program :memory: database is not read only.

I agree with you : in case of a power loss I will loose all changes.

But in memory database could be backuped in interval or when critical tables have been updated ?

Last edited by MC (2020-06-08 08:41:57)

ab · 2020-06-08 09:27:15

@mc
Yes, the problem with this developpez.com page is that it didn't use VFS, but just override read/write calls to be replaced by their own wrappers.
VFS in pure pascal is IMHO a dead-end, because we need some SQLite3 structures.
A small wrapper in c just as utelle did with wxsqlite3 was easier to implement, just redirecting to pascal for the actual encryption (our SynCrypto has AES-NI so is much faster than default wxsqlite3).
But for compression of read-only DB, serialize/unserialize may be an idea, if the purpose is not to look for a single row by index, but brute-force search of the content (which is likely to be the case e.g. for our shared DB like SOA log).

This morning, I tried to adapt https://github.com/utelle/SQLite3MultipleCiphers into our needs.
The code base is much bigger, due to multi-cipher support.
I am working on it..

Greg0r · 2020-06-08 10:01:24

@ab, good decision SQLite3MultipleCiphers and VFS is the best way to go, serialize/deserialize due to memory issues is not an option.
In long term much harder solution will pay and I guess it should be future major sqlitle changes resistant.
+ optional compression is very nice addition.

ab · 2020-06-09 19:16:33

After a lot of painful debugging and adaptation in plain C, we just updated SQLite3 to latest 3.32.2 revision
- switched to VFS pattern for encryption
- sqlite3 cross-compilation is now done from Linux scripts

Check https://synopse.info/fossil/info/a78b036842

There is a bug pending in https://github.com/utelle/SQLite3Multip … /issues/10
And I refactored the source to be able to compile with Embarcadero bcc32 and bcc64 compilers for Delphi.
But we now have a good and stable port which will be able to use the plain SQlite3 amalgamation file and cross-compile almost on all targets.

Still some linking issues on arm to fix, at least on my PC.
Any feedback is welcome.

edwinsn · 2020-06-10 03:43:28

Amazing work, ab!

You said "VFS is a lot of work" just few days ago, and you still finish it sooo quickly?

emk · 2020-06-10 04:31:36

Congratulations!

Just asking: any performance penalty, or any downsides?

Greg0r · 2020-06-10 07:43:02

Superb news, congratz!
for me does not worked however but I suspect c compiler version issues..
Tried with freeware bcc32c C++ 7.x for Win32: https://www.embarcadero.com/free-tools/ccompiler
[dcc32 Error] SynSQLite3Static.pas(1287): E2068 Illegal reference to symbol '_STRLEN' in object file 'D:\mORMot-master\SQLite3\sqlite3.obj'
Unfortunately don't have XE7 compiler to test

Last edited by Greg0r (2020-06-10 07:55:19)

ab · 2020-06-10 08:05:18

@emk
No performance penalty to be expected. The VFS layer is very thin.

@Greg0r
Why do you expect to compile it on your side?
We need to upgrade the .obj on github - they are not updated yet.

Greg0r · 2020-06-10 08:13:20

@ab, ok I thought it will need own compilation but if that's matter of your github update then ok, thx

ps. you was so right about sqlite version...current comming is : 3.32.3

Last edited by Greg0r (2020-06-10 08:28:41)

sakura · 2020-06-10 08:24:09

Have you updated https://synopse.info/files/sqlite3obj.7z yet, as the file stamps are from January and the compiled app complains, that versions do not match (Delphi 10.4).

Vitaly · 2020-06-10 08:26:35

I guess, it is not updated, yet. I have the same warning.

utelle · 2020-06-10 11:34:49

ab wrote:

After a lot of painful debugging and adaptation in plain C, we just updated SQLite3 to latest 3.32.2 revision
- switched to VFS pattern for encryption
- sqlite3 cross-compilation is now done from Linux scripts

Great work. I would really appreciate if you could give some feedback about the problems you experienced while adapting the SQLite3MultipleCiphers code. Especially the Linux side, since I develop under Windows most of the time and have only limited Linux experience. Additionally, I would be interested in getting some information about your fast AES implementation. Maybe in a private message?

ab wrote:

There is a bug pending in https://github.com/utelle/SQLite3Multip … /issues/10

I have fixed this bug already. However, there might be other bugs lurking around. Not all aspects of the new VFS based implementation have been tested yet.

Another SQLite update is coming soon. Version 3.32.3 will probably be released within the next few days.

ab · 2020-06-10 14:16:52

@utelle
Our AES implementation uses low-level AES-NI opcodes, so is very fast, with no need of using external libraries.
You will find some reference asm e.g. in https://github.com/synopse/mORMot2/blob … asmx64.inc

Under Linux, everything is fine.
I have linking issues, but it is not due to your code for sure.
The only problem I saw is that we usually use not the default 'unix' VFS on Linux, but 'unix-excl' which is better for WAL support - and you can't use it in sqlite3_openv2() otherwise our encryption is not triggered.
So I changed https://github.com/synopse/mORMot/blob/ … 3mc.c#L243

Another small problem is that your code requires a C99 compiler, which is not the case e.g. for Embarcadero C++ Builder XE7 I use.
All variable declarations should be moved at the beginning of the function. I have fixed this in my own fork.

You can check the latest state at https://github.com/synopse/mORMot/tree/ … algamation
It includes your fix - thanks!

utelle · 2020-06-10 19:58:34

ab wrote:

Our AES implementation uses low-level AES-NI opcodes, so is very fast, with no need of using external libraries.
You will find some reference asm e.g. in https://github.com/synopse/mORMot2/blob … asmx64.inc

Thanks for the pointer. I'll take a look. Although the generic AES implementation used in wxSQLite3/SQLite3MultipleCiphers is fast enough in many use cases, there are of course use cases where speed really matters. So I'm looking for other AES implementations making use of AES instructions of the processor.

ab wrote:

Under Linux, everything is fine.

Good to hear. Thanks.

ab wrote:

The only problem I saw is that we usually use not the default 'unix' VFS on Linux, but 'unix-excl' which is better for WAL support - and you can't use it in sqlite3_openv2() otherwise our encryption is not triggered.
So I changed https://github.com/synopse/mORMot/blob/ … 3mc.c#L243

Yes, this is a problem I'm aware of. Choosing a different default VFS has to be done either before the SQLite3 initialization function is called for the first time, or the additional initialization function has to be modified (as you did).

In principle, VFSs can be nested. However, there is no API which would allow to check whether VFSs are compatible with each other. Quite often they are not. I will have to do further research, how to handle VFS nesting.

ab wrote:

Another small problem is that your code requires a C99 compiler, which is not the case e.g. for Embarcadero C++ Builder XE7 I use. All variable declarations should be moved at the beginning of the function. I have fixed this in my own fork.

Thanks for mentioning this. Some time ago I had a report from a user who used an old Visual C++ compiler; he had similar problems. Maybe I can lift off the requirement of C99.

ab wrote:

You can check the latest state at https://github.com/synopse/mORMot/tree/ … algamation
It includes your fix - thanks!

The next weeks or months may bring additional changes, fixes etc to SQLite3MultipleCiphers. The project is still "Work in progress".

okoba · 2022-04-20 13:21:57

@ab can I ask why did you use SQLite3MultipleCiphers VFS approach instead of adding VFS in runtime in using SQLite API? I mean why patching SQLite when you write most of the encryption anyway.
Could you add a VFS in Pascal like the sqlite3mc_vfs?

ab · 2022-04-20 15:20:40

SQLite3MultipleCiphers was a working solution, with minimal impact to the SQlite3 sources.

We didn't write any VFS interface in pascal yet, because it seems to be a lot of work to implement outside of C.

okoba · 2022-04-20 15:57:03

As one who read it and know what it does exactly, can you explain how this way of encryption works?
I guess every page is encrypted when written. But a little more on how every page is encrypted in relation to the neighbors pages or HMAC storage. Also why the first bytes of the file (SQLite format) is not encrypted.
In summery I like to know more on how the encryption of SQLite is implemented without going in depth.

ab · 2022-04-20 16:39:47

Encryption is done in CodecAESProcess() of mormot.db.raw.sqlite3.static.pas using AES-CTR or AES-OFB. It uses mORMot optimized asm, which is faster than OpenSSL on x86_64 (our main server target).
Each page is encrypted with an IV derived from its page number. By default with mORMot a page size is 4KB. No overhead is used for IV or HMAC storage.
The first bytes of the files are not encrypted, because it is mandatory for proper work with most SQLite3 tools. This is what all other libraries do, including the official https://www.sqlite.org/see/doc/release/www/readme.wiki - see https://utelle.github.io/SQLite3Multipl … gacy_mode/

Key derivation from password is done in CodecGenerateKey() using PBKDF2 safe iterative key derivation over the SHA-3 algorithm, reduced into 128-bit. There is no benefit of using AES-256 in practice with a password-derivated key.
It uses 1000 rounds by default, and you can customize the password derivation using overridden parameters in JSON format instead of the plain password, following TSynSignerParams fields.

The SQLite3 source is not patched to implement the VFS itself (it is not mandatory), it is patched to add some key-related high-level features.
See https://utelle.github.io/SQLite3Multipl … rch_patch/

okoba · 2022-04-20 19:51:42

Great! Thank you. I suggest add them to the encryption code of SQLite unit for future. It is informative. Also adding a line for how to prepare the JSON structure is good. I can provide a patch if you see fit.

So th patch is needed mostly for WAL and adding PRAGMA, other features seems not very needed.

Why did you said the AES256 is not needed password-derivated key? I couldn’t find a reference for it?
When is suggest to use OFB or CTR?
Exactly what bytes are not encrypted? From 0 to 16 or more for page size?
Can we have them encrypted to make the file fully noise like? It will be useful when it is not needed to be used by other tools other than severs, and also storing backups of the database without leaking any information.

okoba · 2022-04-20 21:46:30

Btw I’m still amazed at how you made faster encryption code then OpenSSL. It is not a simple code to even understand, let alone make it this fast.
Well done!

mORMot Open Source

#1 2020-06-05 10:32:23

10.4 sydney issues

#2 2020-06-05 12:37:13

Re: 10.4 sydney issues

#3 2020-06-05 13:22:55

Re: 10.4 sydney issues

#4 2020-06-05 15:08:09

Re: 10.4 sydney issues

#5 2020-06-05 15:21:35

Re: 10.4 sydney issues

#6 2020-06-05 17:03:45

Re: 10.4 sydney issues

#7 2020-06-05 17:06:13

Re: 10.4 sydney issues

#8 2020-06-05 17:58:06

Re: 10.4 sydney issues

#9 2020-06-06 07:58:37

Re: 10.4 sydney issues

#10 2020-06-06 08:08:27

Re: 10.4 sydney issues

#11 2020-06-06 09:19:00

Re: 10.4 sydney issues

#12 2020-06-06 09:30:44

Re: 10.4 sydney issues

#13 2020-06-06 10:39:09

Re: 10.4 sydney issues

#14 2020-06-06 12:39:16

Re: 10.4 sydney issues

#15 2020-06-06 12:49:41

Re: 10.4 sydney issues

#16 2020-06-06 16:18:26

Re: 10.4 sydney issues

#17 2020-06-06 21:29:46

Re: 10.4 sydney issues

#18 2020-06-07 11:50:16

Re: 10.4 sydney issues

#19 2020-06-08 06:52:30

Re: 10.4 sydney issues

#20 2020-06-08 09:27:15

Re: 10.4 sydney issues

#21 2020-06-08 10:01:24

Re: 10.4 sydney issues

#22 2020-06-09 19:16:33

Re: 10.4 sydney issues

#23 2020-06-10 03:43:28

Re: 10.4 sydney issues

#24 2020-06-10 04:31:36

Re: 10.4 sydney issues

#25 2020-06-10 07:43:02

Re: 10.4 sydney issues

#26 2020-06-10 08:05:18

Re: 10.4 sydney issues

#27 2020-06-10 08:13:20

Re: 10.4 sydney issues

#28 2020-06-10 08:24:09

Re: 10.4 sydney issues

#29 2020-06-10 08:26:35

Re: 10.4 sydney issues

#30 2020-06-10 11:34:49

Re: 10.4 sydney issues

#31 2020-06-10 14:16:52

Re: 10.4 sydney issues

#32 2020-06-10 19:58:34

Re: 10.4 sydney issues

#33 2022-04-20 13:21:57

Re: 10.4 sydney issues

#34 2022-04-20 15:20:40

Re: 10.4 sydney issues

#35 2022-04-20 15:57:03

Re: 10.4 sydney issues

#36 2022-04-20 16:39:47

Re: 10.4 sydney issues

#37 2022-04-20 19:51:42

Re: 10.4 sydney issues

#38 2022-04-20 21:46:30

Re: 10.4 sydney issues

Board footer