SQLite with encryption password protection

Question

I m just learning to use SQLite and I was curious if such is possible    Encryption of the database file  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

User · Answer

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

User · Answer

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  sqleet - another encryption implementation  using ChaCha20 Poly1305 primitives   Note that wxSQLite mentioned above can use this as a crypto provider    The SEE and SQLiteCrypt require the purchase of a license   Disclosure  I created botansqlite3

User · Answer

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

User · Answer

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- gt pPager  pPg- gt pData  pPg- gt 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

User · Answer

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

User · Answer

The  net library System Data SQLite also provides for encryption

User · Answer

I had also similar problem  Needed to store sensitive data in simple database  SQLite was the perfect choice except security   Finally I have placed database file on TrueCrypt encrypted valume  Additional console app mounts temporary drive using TrueCrypt CLI and then starts the database application  Waits until the database application exits and then dismounts the drive again  Maybe not suitable solution in all scenarios but for me working well

User · Answer

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

User · Answer

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

[encryption] SQLite with encryption/password protection

Examples related to encryption

Examples related to sqlite

Examples related to password-protection