SQLite with encryption/password protection

Question

I'm just learning to use SQLite and I was curious if such is possible:

1. Encryption of the database file?

2. Password protect opening of the database?

PS. I know that there is this "SQLite Encryption Extension (SEE).", but according to the documentation, "The SEE is licensed software...." and "The cost of a perpetual source code license for SEE is US $2000."

Answer 1

You can use SQLite's function creation routines (PHP manual):

$db_obj->sqliteCreateFunction('Encrypt', 'MyEncryptFunction', 2);
$db_obj->sqliteCreateFunction('Decrypt', 'MyDecryptFunction', 2);

When inserting data, you can use the encryption function directly and INSERT the encrypted data or you can use the custom function and pass unencrypted data:

$insert_obj = $db_obj->prepare('INSERT INTO table (Clear, Encrypted) ' .
 'VALUES (:clear, Encrypt(:data, "' . $passwordhash_str . '"))');

When retrieving data, you can also use SQL search functionality:

$select_obj = $db_obj->prepare('SELECT Clear, ' .
 'Decrypt(Encrypted, "' . $passwordhash_str . '") AS PlainText FROM table ' .
 'WHERE PlainText LIKE :searchterm');

Answer 2

It's certainly possible and there exist several open source solutions besides SEE. Among them the encryption extension coming with wxSQLite3. See my answer to a similar question for details.

Answer 3

Keep in mind, the following is not intended to be a substitute for a proper security solution.

After playing around with this for four days, I've put together a solution using only the open source System.Data.SQLite package from NuGet. I don't know how much protection this provides. I'm only using it for my own course of study. This will create the DB, encrypt it, create a table, and add data.

using System.Data.SQLite;

namespace EncryptDB
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = @"C:\Programming\sqlite3\db.db";
            string passwordString = "password";
            byte[] passwordBytes = GetBytes(passwordString);
            SQLiteConnection.CreateFile(connectionString);
            SQLiteConnection conn = new SQLiteConnection("Data Source=" + connectionString + ";Version=3;");
            conn.SetPassword(passwordBytes);
            conn.Open();
            SQLiteCommand sqlCmd = new SQLiteCommand("CREATE TABLE data(filename TEXT, filepath TEXT, filelength INTEGER, directory TEXT)", conn);
            sqlCmd.ExecuteNonQuery();
            sqlCmd = new SQLiteCommand("INSERT INTO data VALUES('name', 'path', 200, 'dir')", conn);
            sqlCmd.ExecuteNonQuery();
            conn.Close();
        }
        static byte[] GetBytes(string str)
        {
            byte[] bytes = new byte[str.Length * sizeof(char)];
            bytes = System.Text.Encoding.Default.GetBytes(str);
            return bytes;
        }
    }
}

Optionally, you can remove conn.SetPassword(passwordBytes);, and replace it with conn.ChangePassword("password"); which needs to be placed after conn.Open(); instead of before. Then you won't need the GetBytes method.

To decrypt, it's just a matter of putting the password in your connection string before the call to open.

        string filename = @"C:\Programming\sqlite3\db.db";
        string passwordString = "password";
        SQLiteConnection conn = new SQLiteConnection("Data Source=" + filename + ";Version=3;Password=" + passwordString + ";");
        conn.Open();

Answer 4

SQLite has hooks built-in for encryption which are not used in the normal distribution, but here are a few implementations I know of:

• SEE - The official implementation.
• wxSQLite - A wxWidgets style C++ wrapper that also implements SQLite's encryption.
• SQLCipher - Uses openSSL's libcrypto to implement.
• SQLiteCrypt - Custom implementation, modified API.
• botansqlite3 - botansqlite3 is an encryption codec for SQLite3 that can use any algorithms in Botan for encryption.
• SQLiteCrypto - SQLiteCrypto is The Easier Way To Encrypt Sqlite Database By AES-256 and SHA-256

The SEE, SQLiteCrypt and SQLiteCrypto require the purchase of a license.

Disclosure: I created botansqlite3.

Answer 5

Well, SEE is expensive. However SQLite has interface built-in for encryption (Pager). This means, that on top of existing code one can easily develop some encryption mechanism, does not have to be AES. Anything really. Please see my post here: https://stackoverflow.com/a/49161716/9418360

You need to define SQLITE_HAS_CODEC=1 to enable Pager encryption. Sample code below (original SQLite source):

#ifdef SQLITE_HAS_CODEC
/*
** This function is called by the wal module when writing page content
** into the log file.
**
** This function returns a pointer to a buffer containing the encrypted
** page content. If a malloc fails, this function may return NULL.
*/
SQLITE_PRIVATE void *sqlite3PagerCodec(PgHdr *pPg){
  void *aData = 0;
  CODEC2(pPg->pPager, pPg->pData, pPg->pgno, 6, return 0, aData);
  return aData;
}
#endif

There is a commercial version in C language for SQLite encryption using AES256 - it can also work with PHP, but it needs to be compiled with PHP and SQLite extension. It de/encrypts SQLite database file on the fly, file contents are always encrypted. Very useful.

http://www.iqx7.com/products/sqlite-encryption