Password Protection Broken in SQLite 1.0.113: Migration and Rollback Solutions

Understanding the Removal of SQLITE_HAS_CODEC and Its Impact on Database Encryption

Issue Overview

The core issue revolves around the removal of the SQLITE_HAS_CODEC feature from SQLite version 3.32.1 and its downstream impact on the System.Data.SQLite NuGet package (version 1.0.113.1). This feature was responsible for enabling password protection and encryption of SQLite databases using methods like ChangePassword in the connection string. Developers who relied on this undocumented feature suddenly found their applications unable to open or modify password-protected databases after updating to System.Data.SQLite 1.0.113.1. The problem is compounded by the unavailability of older NuGet packages (e.g., 1.0.112) on nuget.org, making rollbacks difficult. This creates a critical compatibility gap for applications that depend on legacy encryption, especially those already deployed in production environments.

The SQLITE_HAS_CODEC feature had been part of SQLite’s codebase for over 15 years but was never officially documented or supported. Its removal was a deliberate decision by the SQLite development team to streamline the core library and avoid maintaining unsupported code. System.Data.SQLite 1.0.113.1, which bundles SQLite 3.32.1, inherited this change, breaking backward compatibility with databases encrypted using earlier versions. The absence of this feature means that methods like ChangePassword and the ability to open password-protected databases via connection strings are no longer functional. Developers are left with three primary challenges: restoring access to existing encrypted databases, migrating to supported encryption methods, and addressing the unavailability of older NuGet packages for rollback.

Root Causes: Why Password Protection Stopped Working and Rollbacks Are Problematic

Possible Causes

1. Removal of SQLITE_HAS_CODEC from SQLite Core
   The SQLite development team removed the SQLITE_HAS_CODEC flag in February 2020, starting with SQLite 3.32.x. This flag enabled compile-time support for database encryption extensions, including the ChangePassword method. Since System.Data.SQLite 1.0.113.1 uses SQLite 3.32.1, any dependency on SQLITE_HAS_CODEC-based encryption became invalid. The removal was not highlighted in SQLite’s release notes, catching many developers off guard.

2. System.Data.SQLite Dependency on SQLite Versions
   System.Data.SQLite is a wrapper around the native SQLite library. Updates to System.Data.SQLite often include newer SQLite versions. When System.Data.SQLite 1.0.113.1 adopted SQLite 3.32.1, it inherited the removal of SQLITE_HAS_CODEC, breaking all encryption-related functionality. Developers who assumed backward compatibility were unprepared for this hard break.

3. NuGet Package Deprecation and Version Unavailability
   NuGet.org enforces a policy of retaining only the most recent package versions by default. Older versions of System.Data.SQLite (e.g., 1.0.112) were removed from the public repository, making it impossible to roll back via standard NuGet commands. This forced developers to seek alternative sources for older packages or modify their build pipelines to reference legacy binaries directly.

4. Reliance on Undocumented Features
   The SQLITE_HAS_CODEC feature and its associated encryption methods were never officially supported or documented by SQLite. Developers who built solutions around these methods unknowingly introduced a long-term risk, as the SQLite team reserves the right to remove undocumented features without warning.

Resolving Encryption Breakage: Rollbacks, Alternatives, and Migration Strategies

Troubleshooting Steps, Solutions & Fixes

1. Rolling Back to System.Data.SQLite 1.0.112
The immediate solution for maintaining compatibility with existing encrypted databases is to revert to System.Data.SQLite 1.0.112. However, since this version is no longer available on NuGet.org, alternative methods are required:

• Using Alternative NuGet Sources
  Some community-maintained NuGet repositories still host older versions. For example, the command

  Install-Package System.Data.SQLite.Core -Version 1.0.112.2 -Source http://nuget.yuanbei.biz/nuget/Default  
  

  installs version 1.0.112.2 from a third-party source. Caution: Verify the integrity of third-party repositories to avoid security risks.

• Direct Download from SQLite.org
  Older binaries are available on the SQLite.org downloads page. For example:

  • 64-bit bundle for .NET 4.0:

    https://system.data.sqlite.org/blobs/1.0.112.0/sqlite-netFx40-setup-bundle-x64-2010-1.0.112.0.exe  
    

  Modify the version number in the URL to download specific releases.

• Manual Reference in Projects
  Download the 1.0.112 binaries and reference them directly in your project:

  1. Remove the System.Data.SQLite NuGet package.
  2. Add the DLLs (e.g., System.Data.SQLite.dll) as direct file references.
  3. Ensure all dependencies (e.g., SQLite.Interop.dll) are included in the build output.

2. Migrating to Supported Encryption Solutions
For long-term stability, migrate to encryption methods endorsed by SQLite or third-party providers:

• SQLite Encryption Extension (SEE)
  The official SQLite Encryption Extension provides documented, supported encryption. However, SEE requires a paid license (starting at $2,000), which may be prohibitive for small projects.

  • Steps:
    1. Purchase and integrate the SEE license.
    2. Rebuild SQLite with SEE enabled.
    3. Use the sqlite3_key and sqlite3_rekey functions for encryption.

• Third-Party Libraries like SQLiteCrypt
  Libraries such as SQLiteCrypt offer encryption at a lower cost. These libraries replace the native SQLite library and provide APIs for password management.

  • Steps:
    1. Replace sqlite3.dll with SQLiteCrypt’s modified version.
    2. Update connection strings to use SQLiteCrypt’s encryption methods.
    3. Re-encrypt existing databases using the new library.

• Application-Level Encryption
  Encrypt sensitive data before storing it in the database using .NET libraries like AES:

  using System.Security.Cryptography;  
  // Encrypt data  
  byte[] encryptedData = Aes.Create().EncryptCbc(plaintextBytes, iv);  
  // Decrypt data  
  byte[] decryptedData = Aes.Create().DecryptCbc(encryptedData, iv);  
  

  This approach avoids dependency on database-level encryption but requires schema changes and manual data migration.

3. Upgrading Existing Encrypted Databases
If rolling back is not feasible, existing databases must be decrypted using System.Data.SQLite 1.0.112 and re-encrypted with a new method:

1. Use a temporary project with System.Data.SQLite 1.0.112 to open the encrypted database.
2. Export all data to an unencrypted database or intermediate format (e.g., SQL scripts).
3. Switch to the new encryption method (SEE, SQLiteCrypt, or application-level encryption).
4. Re-import the data into a new encrypted database.

4. Mitigating NuGet Versioning Issues
To prevent future disruptions:

• Host a private NuGet repository with vetted package versions.
• Use nuget.config to pin dependencies to specific versions:

  <configuration>  
    <packageSources>  
      <add key="Private Repository" value="https://my-nuget-server/nuget" />  
    </packageSources>  
    <packageVersionConstraints>  
      <add id="System.Data.SQLite.Core" version="[1.0.112]" />  
    </packageVersionConstraints>  
  </configuration>  
  

5. Communicating with End Users
If your application is distributed to end users:

• Provide a migration tool to decrypt legacy databases and convert them to the new format.
• Offer clear documentation on updating existing installations.
• Consider backward-compatible updates that support both old and new encryption methods during a transition period.

This guide provides a comprehensive path forward for developers impacted by the removal of SQLITE_HAS_CODEC, balancing immediate rollback needs with long-term migration strategies.