I was surprised to see that my code didn't compile with the latest SQLite source, due to SQLITE_HAS_CODEC having been removed. I was even more surprised to see no mention of that in the release notes.

Is there an alternative method for me to implement full-db encryption?

One possibility would be to use a custom VFS.

Unfortunately, SQLITE_HAS_CODEC was never a documented or supported interface.

That's markedly inconvenient for me...

What is your suggestion for reimplementing my total-encryption layer? Is the VFS interface sufficient for that?

In principal, the VFS interface should be sufficient to implement your own encryption layer. However, the devil could be in the details - it heavily depends on the requirements of your encryption layer.

Most likely, the main problem will be to reliably identify which database page is read or written, because the VFS interface is rather low-level and provides for example only information about offset and size of a chunk to be read or written, but no information about the page number (which most encryption extensions need to know, but maybe yours is an exception).

Another critical issue could be the handling of attached database files. The ATTACH command had an undocumented additional keyword, namely KEY, for specifying the encryption key for the attached database. Most likely, support for this undocumented keyword has also gone.

You are certainly not the only one affected by this change. For example, there is the SQLCipher project. Sooner or later they will have to tackle this problem, too. And if you are lucky, they will make their solution publicly available.

Thanks, Ulrich. You're exactly right: each page has a different nonce for encryption, so the CODEC interface was just perfect.

Looking at the SQLCipher project you mentioned, it looks like it does something very similar to what I am doing.

I would vastly prefer not to have to backport the CODEC interface or manually merge upstream changes to SQLite, but if DRH isn't going to provide the interface any more it looks like I have no choice.

Arrgggggh!

You're exactly right: each page has a different nonce for encryption, so the CODEC interface was just perfect.

This makes a VFS implementation of your encryption layer slightly more difficult ... but not impossible.

I would vastly prefer not to have to backport the CODEC interface or manually merge upstream changes to SQLite, but if DRH isn't going to provide the interface any more it looks like I have no choice.

I doubt that the SQLITE_HAS_CODEC encryption API will ever come back. IMHO the removal of that API indicates that most likely the "official" commercial encryption extension offered by DRH (see SEE) has been reimplemented based on a different API (VFS or something else). From DRH's point of view this decision makes it certainly easier for the SQLite developer team to adopt new modern features in the future. That also means that supporting the SQLITE_HAS_CODEC API manually for future versions of SQLite will become more and more cumbersome over time.

Right. Of course, I have customers using the existing encrypted db format, so I have to make sure that any VFS edition of the encryption is 100% backward compatible.

For now I'll content myself with applying new SQLite patches to the 3.31 code, since I don't have the time to dedicate otherwise.

I have to make sure that any VFS edition of the encryption is 100% backward compatible

Or, it may be simpler to offer your clients an old-encryption and new-encryption SQLite3 shell to pipe a dump from old to new encrypted DB before using your updated encryption application. This also frees you to use whatever incompatible encryption you decide from now on.

While an interesting idea, it would be impractical since my product is a programming language which my clients use to make products for their clients. I don't see that as a great sell.

Of course, I have customers using the existing encrypted db format, so I have to make sure that any VFS edition of the encryption is 100% backward compatible.

You might be interested in what is going on in my GitHub account. :-)

Richard,

While I agree with everybody here that you and your staff have made an amazing work for years, I also agree with affected people that this is a significant inconvenience for some users, but a disaster for some others.

I've blindly upgraded by overwriting the previous DLL (I'm running a Windows-only scripting language which can only use a DLL) and now the fact that the encryption support has disappeared and that older versions are no more downloadable, I'm left with an number of unreadable DBs which represent more than 15 years of continuous hard work.

I can understand that you prefer to sell SEE to corps for megabucks (which you deserve without question) but self-employed individuals like me are just plain ruined. I'd prefer having died from COVID now.

Removing the support without any prior nor post notice wasn't fair nor consistent with your praised profile, since you knew what kind of havoc would result and even if that was a world-known undocumented feature.

Histrionics aside, what stops you from either:

1. Rolling back to the last supported version; OR

2. Forward-porting the missing feature into the current version; OR

3. Backporting fixes from the current version into the last version that had the extra feature you want.

SQLite is free open source software. You've been given the freedom to do all of these things. All I want to know is, what else is blocking you?

I missed the bit about you having overwritten your working DLL on first reading.

Are you telling me that your "more than 15 years of continuous hard work" isn't backed up anywhere?

But assuming you indeed have no way to recover this previously working DLL, there must be dozens of places online to download a copy. I hesitate to point you at any place to get precompiled binaries for Windows other than the software's trusted source, but this one is probably trustworthy.

I don't have the resource to backup every bit of system/programs/dlls everytime. I focus backups on user data.

I need system.data.sqlite for X86. Even X64 would help getting out of the trap.

If you're using System.Data.SQLite, you most likely have Visual Studio, and if you're using some third-party .NET toolchain, you can get Visual Studio Community Edition for free. The "ZIP Archive" link on this page will get you the prior release of System.Data.SQLite, which should have this feature. Build the library using the included solutions. Reference the project's build instructions if you need additional help.

Just chiming in to voice my disappointment about the removal SQLITE_HAS_CODEC encryption API. SQLeet also relies on this.

DRH you are amazing and I appreciate all of the amazing work you have done and made available to the world for free. So I have nothing but gratitude for that. The loss of this interface is an unfortunate inconvenience to SQLeet and SQLCipher and their users though. I hope they are able to create workarounds soon.

I was also very surprised to see that API was removed without any notice. It is a significant change that would, in my eyes, require some information in the release notes.

I know that there is the SEE extension but my company was very eager back then to use our own internal encryption library (which wraps a different OS project) and it worked flawless over the years.

So no updates in the foreseeable future ...