Encrypting SQLite Databases and Fields Using System.Data.SQLite.Core

Database-Level Encryption vs. Field-Level Encryption: Use Cases and Technical Constraints

When working with System.Data.SQLite.Core, encryption requirements often fall into two categories: full database encryption and field-level encryption. Each approach addresses distinct security objectives and introduces unique technical challenges.

Database-Level Encryption

This method encrypts the entire SQLite database file, including metadata, schemas, and all stored data. System.Data.SQLite.Core relies on the SQLite Encryption Extension (SEE) or third-party libraries like SQLCipher to enable transparent encryption. The encryption operates at the storage layer, meaning data is encrypted on disk and decrypted in memory during queries. This approach is ideal for scenarios requiring compliance with regulations like GDPR or HIPAA, where all data-at-rest must be protected.

Field-Level Encryption

Field-level encryption involves encrypting specific columns or fields within the database. This is implemented at the application layer using .NET cryptographic APIs (e.g., AesManaged, RSA). Data is encrypted before insertion and decrypted after retrieval, ensuring sensitive information (e.g., credit card numbers) remains encrypted even if the database file is exposed. However, this method complicates query operations, as indexed searches on encrypted fields become impractical.

Key Technical Differences

• Performance: Database-level encryption has minimal overhead for read/write operations since decryption occurs in memory. Field-level encryption incurs CPU costs during data transformation and complicates query logic.
• Key Management: Database encryption requires a single encryption key, while field-level encryption may involve multiple keys (e.g., per-field or per-row keys).
• Attack Surface: Field-level encryption limits exposure if a subset of data is compromised, whereas a breached database key exposes all data.

Common Pitfalls in SQLite Encryption Implementations

Database-Level Encryption Issues

1. Missing or Incompatible Encryption Extensions:
   System.Data.SQLite.Core does not natively include encryption support. Developers must use a build compiled with SEE or SQLCipher. Deploying the wrong binary (e.g., community edition without SEE) results in errors like "file is not a database" when opening an encrypted file.

2. Incorrect Key Initialization:
   Failing to set the encryption key via PRAGMA key or omitting it from the connection string prevents decryption. Keys must be supplied before executing any other commands.

3. File Corruption During Encryption:
   Encrypting an existing unencrypted database without using the ATTACH and VACUUM INTO commands can corrupt data. Directly applying encryption to an open database may also truncate the file.

Field-Level Encryption Issues

1. Algorithm Misconfiguration:
   Using deprecated algorithms (e.g., DES) or insecure modes (e.g., ECB) undermines security. AES-CBC or AES-GCM with proper initialization vectors (IVs) is recommended.

2. Key Storage Vulnerabilities:
   Hardcoding keys in source code or storing them in plaintext configuration files exposes them to extraction. Secure key management systems (e.g., Azure Key Vault, Windows DPAPI) are essential.

3. Encoding/Decoding Errors:
   Converting encrypted byte arrays to strings without Base64 or hex encoding leads to data loss. Using Encoding.UTF8.GetBytes() on ciphertext can produce invalid characters.

4. Transaction Rollbacks:
   Encrypting/decrypting data within transactions without proper error handling may leave partial updates, causing inconsistencies.

Implementing Robust Encryption: Step-by-Step Solutions

Database-Level Encryption with System.Data.SQLite.Core

1. Use a Compatible Build:
   Obtain a System.Data.SQLite.Core package compiled with SEE or SQLCipher. Verify this by checking if the SQLiteConnection supports the Password property in the connection string.

2. Initialize the Encryption Key:

   using (var connection = new SQLiteConnection("Data Source=encrypted.db;Password=MyStrongKey123!"))  
   {  
       connection.Open();  
       // Execute queries  
   }  
   

   Alternatively, use PRAGMA key after opening the connection:

   using (var cmd = new SQLiteCommand("PRAGMA key = 'MyStrongKey123!'", connection))  
   {  
       cmd.ExecuteNonQuery();  
   }  
   

3. Encrypt an Existing Database:
   Attach the unencrypted database and re-encode it with encryption:

   using (var connection = new SQLiteConnection("Data Source=:memory:;"))  
   {  
       connection.Open();  
       using (var cmd = new SQLiteCommand("ATTACH DATABASE 'plaintext.db' AS plaintext KEY '';", connection))  
       {  
           cmd.ExecuteNonQuery();  
       }  
       using (var cmd = new SQLiteCommand("SELECT sqlcipher_export('main', 'plaintext');", connection))  
       {  
           cmd.ExecuteNonQuery();  
       }  
       using (var cmd = new SQLiteCommand("DETACH DATABASE plaintext;", connection))  
       {  
           cmd.ExecuteNonQuery();  
       }  
   }  
   

Field-Level Encryption with .NET Cryptography

1. Encrypt Data Before Insertion:

   public static byte[] Encrypt(string plaintext, byte[] key, byte[] iv)  
   {  
       using (Aes aes = Aes.Create())  
       {  
           aes.Key = key;  
           aes.IV = iv;  
           ICryptoTransform encryptor = aes.CreateEncryptor(aes.Key, aes.IV);  
           using (MemoryStream ms = new MemoryStream())  
           using (CryptoStream cs = new CryptoStream(ms, encryptor, CryptoStreamMode.Write))  
           {  
               byte[] plainBytes = Encoding.UTF8.GetBytes(plaintext);  
               cs.Write(plainBytes, 0, plainBytes.Length);  
               cs.FlushFinalBlock();  
               return ms.ToArray();  
           }  
       }  
   }  
   

2. Store IVs Securely:
   Generate a unique IV for each encryption operation and store it alongside the ciphertext:

   byte[] iv = new byte[16];  
   using (RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider())  
   {  
       rng.GetBytes(iv);  
   }  
   

   Insert both the IV and ciphertext into the database:

   INSERT INTO SensitiveData (EncryptedField, IV) VALUES (@ciphertext, @iv);  
   

3. Decrypt Data After Retrieval:

   public static string Decrypt(byte[] ciphertext, byte[] key, byte[] iv)  
   {  
       using (Aes aes = Aes.Create())  
       {  
           aes.Key = key;  
           aes.IV = iv;  
           ICryptoTransform decryptor = aes.CreateDecryptor(aes.Key, aes.IV);  
           using (MemoryStream ms = new MemoryStream(ciphertext))  
           using (CryptoStream cs = new CryptoStream(ms, decryptor, CryptoStreamMode.Read))  
           using (StreamReader sr = new StreamReader(cs))  
           {  
               return sr.ReadToEnd();  
           }  
       }  
   }  
   

Key Management Best Practices

• Use Windows Data Protection API (DPAPI) for encrypting encryption keys:

  byte[] encryptedKey = ProtectedData.Protect(  
      plaintextKey,  
      optionalEntropy,  
      DataProtectionScope.CurrentUser  
  );  
  

• Rotate keys periodically and re-encrypt affected data.

Debugging Common Errors

• "File is not a database": Confirm the correct encryption library is used and the key matches.
• Padding Errors in Decryption: Ensure the same padding mode (e.g., PKCS7) is used for encryption and decryption.
• Garbage Characters in Decrypted Text: Avoid encoding ciphertext as UTF-8; use Base64 instead.

By addressing these layers systematically, developers can secure SQLite databases effectively while balancing performance and maintainability.