SQLite with encryption/password protection

问题:

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.\"

回答1:

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.

回答2:

You can password protect SQLite3 DB. For the first time before doing any operations, set password as follows.

SQLiteConnection conn = new SQLiteConnection(\"Data Source=MyDatabase.sqlite;Version=3;\");
conn.SetPassword(\"password\");
conn.open();

then next time you can access it like

conn = new SQLiteConnection(\"Data Source=MyDatabase.sqlite;Version=3;Password=password;\");
conn.Open();

This wont allow any GUI editor to view Your data. Later if you wish to change the password, use conn.ChangePassword(\"new_password\"); To reset or remove password, use conn.ChangePassword(String.Empty);

回答3:

The .net library System.Data.SQLite also provides for encryption.

回答4:

You can get sqlite3.dll file with encryption support from http://system.data.sqlite.org/.

1 - Go to http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki and download one of the packages. .NET version is irrelevant here.

2 - Extract SQLite.Interop.dll from package and rename it to sqlite3.dll. This DLL supports encryption via plaintext passwords or encryption keys.

The mentioned file is native and does NOT require .NET framework. It might need Visual C++ Runtime depending on the package you have downloaded.

UPDATE

This is the package that I\'ve downloaded for 32-bit development: http://system.data.sqlite.org/blobs/1.0.94.0/sqlite-netFx40-static-binary-Win32-2010-1.0.94.0.zip

回答5:

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.

回答6:

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();

回答7:

You can always encrypt data on the client side. Please note that not all of the data have to be encrypted because it has a performance issue.

回答8:

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

回答9:

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\');

回答10:

Yes, it\'s possible. If targeting .Net Standard 4.6.1+ or Core, I think a fairly straightforward to get Sqlite encryption is to use Microsoft.Data.Sqlite per my answer here.

回答11:

For projects using Javascript, the package written by journeyapps works seamlessly.

https://github.com/journeyapps/node-sqlcipher

It worked on Mac/Windows/Linux for me. It compiles SQLCipher on your platform. There is no need to pay for the licenses from Zetetic.