SQLITE_HAS_CODE is gone?
(1) By Ron Aaron (ronaaron) on 2020-05-24 12:53:43 [link]
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?
(2) By anonymous on 2020-05-24 20:50:39 in reply to 1 [link]
One possibility would be to use a custom VFS.
(3) By Richard Hipp (drh) on 2020-05-25 01:59:48 in reply to 1 [link]
Unfortunately, `SQLITE_HAS_CODEC` was never a documented or supported interface.
(4) By Ron Aaron (ronaaron) on 2020-05-25 03:50:12 in reply to 3 [link]
That's markedly inconvenient for me... What is your suggestion for reimplementing my total-encryption layer? Is the VFS interface sufficient for that?
(5) By Ulrich Telle (utelle) on 2020-05-25 21:29:06 in reply to 4
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](https://www.zetetic.net/sqlcipher/). Sooner or later they will have to tackle this problem, too. And if you are lucky, they will make their solution publicly available.
(6) By Ron Aaron (ronaaron) on 2020-05-26 03:53:43 in reply to 5 [link]
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!
(7) By Ulrich Telle (utelle) on 2020-05-26 14:20:03 in reply to 6 [link]
> 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](https://www.hwaci.com/sw/sqlite/see.html)) 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.
(8) By Ron Aaron (ronaaron) on 2020-05-27 05:31:38 in reply to 7 [link]
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.
(9) By Tony Papadimitriou (tonyp) on 2020-05-27 14:06:17 in reply to 8 [link]
>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.
(10) By Ron Aaron (ronaaron) on 2020-05-27 14:12:32 in reply to 9 [link]
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.
(11) By Ulrich Telle (utelle) on 2020-05-27 14:56:38 in reply to 8 [link]
> 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](https://github.com/utelle). :-)
(14) By jchd (jchd18) on 2020-07-16 18:56:07 in reply to 3 [link]
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.
(15) By Warren Young (wyoung) on 2020-07-16 19:05:06 in reply to 14 [link]
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?
(16) By Warren Young (wyoung) on 2020-07-16 19:18:30 in reply to 15 [link]
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. : https://chocolatey.org/packages/sqlite/3.31.1
(17) By jchd (jchd18) on 2020-07-16 19:50:35 in reply to 16 [link]
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.
(18) By Warren Young (wyoung) on 2020-07-16 20:04:37 in reply to 17 [link]
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. : https://visualstudio.microsoft.com/ : https://system.data.sqlite.org/index.html/info/c08d07c8237b1df0 : https://system.data.sqlite.org/index.html/doc/trunk/www/build.wiki
(19) By RandomCoder on 2020-07-17 00:52:55 in reply to 18 [link]
And if it helps, I have a build from my build system available for download from [here](https://kw9jd52kekregb9.s3.amazonaws.com/temp/System.Data.SQLite-c08d07c823.build.zip) Of course, you would be better off to follow the directions to build it yourself, if for no other reason than to have trust in the final binaries.
(12) By Adam S Levy (alaskanarcher) on 2020-06-27 21:17:24 in reply to 1 [link]
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.
(13) By Mike (sentinel101) on 2020-07-08 19:16:55 in reply to 1 [link]
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 ...