SQLite database encryption key limited to 16 characters!

[Georgios_Poulopoulos]

Hello,
While trying to add salt to SQLite database encryption passwords, I came across this limitation: the SQLite engine only takes into account the first 16 characters of the encryption key and silently ignores any characters above this limit.
Consider the following code:

[code]dim now as new Date
dim filename_int as Int64 = now.TotalSeconds
dim filename as string = str(filename_int)

// we create a database using a long key
dim initDB as new SQLiteDatabase
initDB.DatabaseFile = GetFolderItem(“c:\temp” + filename)
initDB.EncryptionKey = “abcdefghijklmnopqrstuvwxyz”

dim createOutcome as Boolean = initDB.CreateDatabaseFile
initDB.Close
MsgBox str(createOutcome) // true = create OK, false = create fail

// then we try to read the database using a shorter key
dim readDB as new SQLiteDatabase
readDB.DatabaseFile = GetFolderItem(“c:\temp” + filename)
readDB.EncryptionKey = “abcdefghijklmnop”

dim readOutcome as Boolean = readDB.Connect
MsgBox str(readOutcome) // result is true (!!!) in 2017R2.1 & 2018R1 (windows)
[/code]

What happened in my case, where my salt was 27 characters long, followed by the actual user password is obvious; the first 16 characters of the salt were used as the encryption password and any user password would unlock the database!

is this limitation intended, or something’s not right with the SQLite engine?
Could others confirm this and if yes, Is it a bug or a feature?

I think 16 characters are not enough at a time when all serious password-protection mechanisms recommend at least 20 characters.
Xojo team, could we please do something about it?

[Christian_Schmitz]

Please pass in a hash of the password to make it secure.

[Philippe_Casgrain]

So I overheard this from a SmartFriend™. 16 characters at 8 bits per character sounds suspiciously like 128-bit encryption, which is the default in SQLite.

You may have encountered an SQLite-ism.

https://www.sqlite.org/see/doc/trunk/www/readme.wiki

If I take your sample code and replace your keys with:

initDB.EncryptionKey = "aes256:abcdefghijklmnopqrstuvwxyz"
readDB.EncryptionKey = "aes256:abcdefghijklmnop"

I get True/False, which is what you expect (it can be created, but not read).

[DaveS]

Key Material

The amount of key material actually used by the encryption extension depends on which variant of SEE you are using. With see-rc4.c, the first 256 byte of key are used. With the see-aes128-ofb and and see-aes128-ccm variants, the first 16 bytes of the key are used. With see-aes256-ofb, the first 32 bytes of key are used.

If you specify a key that is shorter than the maximum key length, then the key material is repeated as many times as necessary to complete the key. If you specify a key that is larger than the maximum key length, then the excess key material is silently ignored.

For the “-textkey” option, up to 256 bytes of the passphrase are hashed using RC4 and the hash value becomes the encryption key.

and as far as I know… the SQLite baked into Xojo does NOT support Encryption Prefixes (ie. aes256: etc)

[Greg_O_Lone]

@384204:@Dave S

and as far as I know… the SQLite baked into Xojo does NOT support Encryption Prefixes (ie. aes256: etc)

We added aes256 in 2018r1.

[Christian_Schmitz]

MBS SQL Plugin comes also with built in SQLite library which includes AES 256 for years now.

[Philippe_Casgrain]

I forgot to say I tried this with 2018r1

[Georgios_Poulopoulos]

Dave found the documentation I was looking for, especially the disturbing part that I was afraid myself:

…then the excess key material is silently ignored…

So, question answered: it’s not a bug, it’s a feature and I should probably have to live with it.
I just think the maximum key length deserves a special mention in the Xojo documentation itself, since encryption is a sensitive issue.

Thanks

[Philippe_Casgrain]

[quote=384729:@Georgios Poulopoulos]So, question answered: it’s not a bug, it’s a feature and I should probably have to live with it.
[/quote]

If you use 2018r1 and prefix the password with “aes256:” (case-sensitive) you should get the behaviour you desire.