#37 [wxsqlite3] possible to add variant CCM to AES 128

[mjpetty74]

Hi,

I'm wondering if it's possible to add the CCM variant to AES128 to the wxsqlite3 project. More specifically using the prebuilt sqlite3shell (aes128/lib/release) I am attempting to create an encrypted template to use in another project. But the sqlite engine that it uses will only accept encryption of this type.

Thanks for considering,
Mike

[Ulrich Telle]

The commercial encryption extension SEE offered by the SQLite developers supports AES-128-CCM as one of its encryption methods:

http://www.hwaci.com/sw/sqlite/see.html

That is, the answer to your question is obviously that in principle it should be possible to add an AES-128_CCM mode. However, this won't be trivial.

Even if the encryption extension coming with wxSQLite3 would offer an AES-128-CCM mode, this would not guarantee that this mode would be compatible with the enryption method used by this other project. For example the methods could differ in how the encryption key is deduced from the passphrase. Or the other project not only saves the MAC of each database page, but also other information items like initial vectors.

If you want to create database templates for another project which uses encrypted databases, you will have to use the very same SQLite encryption extension the other project is using. Otherwise it will be unlikely that your templates are usable by the other project.If the encryption extension of this other project is not publicly available, it's likely that it uses the SQLite SEE component.

May I ask of which "other project" you are talking?

Regards,

Ulrich

[mjpetty74]

Hi,

Unfortunately I know nothing is ever trivial, but at least thank you for considering it ;)

Correct - I know about the SEE project (and others), and it in fact is the one used by Adobe AIR. The license at this point is too cost prohibitive to get my project off the ground at the moment - to which I only want to create a single seeded template. By default AIR creates a sqlite db with UTF16LE (but will read/write UTF8) and if encrypted using AES 128 CCM mode.

Sadly (but understandable) Adobe doesn't include the sqlite3.dll with the AIR runtime deployment. Otherwise I could use this.

I am fighting two problems here:
1) Few if any tooling for db management uses UTF16 (this is reserved typically for asian languages) - I have countered this by creating "standard" UTF8 db's to use as a seeded template.
2) Is the encryption phase - I could use the db (UTF16, 128CCM) created by adobe - but then I have no ability to attach mixed mode (defeating the purpose of pre-setting tables, triggers, etc), and no 3rd party verification phase that what I coded was actually correct (other than switching back and forth between un/encrypted).

Like I said, thanks for at least considering it. I can completely understand if that is an endeavor you don't want to undertake.
Cheers.

[Ulrich Telle]

Correct - I know about the SEE project (and others),
and it in fact is the one used by Adobe AIR.

I already suspected that you wanted to work with databases created by a SQLite version with SEE support. This would require to implement a clone of the respective SEE method. Sorry, I'm not going to do that, even if I add additional encryption methods to the wxSQLite3 encryption extension in the future.

The license at this point is too cost prohibitive
to get my project off the ground at the moment

For a commercial project 2000 USD should usually be affordable. However, for a non-profit project it's way too much. And that's why the wxSQLite3 encryption extension exists in the first place.

By default AIR creates a sqlite db with UTF16LE (but will read/write UTF8)

The SQLite library allows to access text in UTF-8 or UTF-16 encoding independent of the actual encoding used for the database. Personally I prefer UTF-8, because it avoids endianess problems.

and if encrypted using AES 128 CCM mode.

Couldn't you create your intended templates in unencrypted mode and let users encrypt them themselves using AIR?

I am fighting two problems here:

1) Few if any tooling for db management uses UTF16 (this is reserved
typically for asian languages) - I have countered this by creating
"standard" UTF8 db's to use as a seeded template.

As far as I understand the SQLite documentation you can't attach databases which use a different encoding than the main database. That is, your templates should probably have the same encoding (UTF-16) as the AIR databases.

2) Is the encryption phase - I could use the db (UTF16, 128CCM) created by
adobe - but then I have no ability to attach mixed mode (defeating the purpose
of pre-setting tables, triggers, etc), and no 3rd party verification phase that
what I coded was actually correct (other than switching back and forth between
un/encrypted).

If AIR allows to attach databases it should be possible to attach an unencrypted UTF-16 database, at least in principle. However, I have to admit that I know nothing about AIR, so I really don't know of a feasible approach you could use.

Regards,

Ulrich