sqlite-net-pcl with SQLitePCLRaw.bundle_sqlcipher

AlbertKAlbertK MYMember ✭✭✭

Hi,

I have installed the SQLite net PCL with Sqlcipher via nuget on Visual Studio 2015.
My Question is how do I get to use/enable the SQLite encryption. A sample code would be appreciated. Thank you

https://www.nuget.org/packages/SQLitePCLRaw.bundle_sqlcipher/1.1.2
https://www.nuget.org/packages/SQLitePCLRaw.bundle_sqlcipher/1.1.2

Tagged:

Best Answer

  • AlbertKAlbertK MY ✭✭✭
    Accepted Answer

    @JohnMiller
    I have manage to get the SqlCipher working, It is based in the open sourced SqlCipher version 3.3.1 and the SQLRAW provider comes from Eric Sink https://www.nuget.org/packages/SQLitePCLRaw.bundle_sqlcipher/1.1.2 and the SQLite-Net-PCL comes from (Frank A. Krueger) https://www.nuget.org/packages/sqlite-net-pcl/

    What is need is to add the SQLite-Net-PCL into the project first then add the SQLitePCLRaw.bundle_sqlcipher. I am enclosing the sample code.

    using System.IO;
    using Android.App;
    using Android.Widget;
    using Android.OS;
    using Android.Util;
    using SQLite;
    
    
    namespace SampleSqlCipher
    {
    
        public class SampleOrder
        {
            [PrimaryKey, AutoIncrement]
            public int? key_id { get; set; }
            public string name { get; set; }
            public float quantity { get; set; }
    
            public SampleOrder() { }
        }
    
        [Activity(Label = "SampleSqlCipher", MainLauncher = true, Icon = "@drawable/icon")]
        public class MainActivity : Activity
        {
            const string TAG = "SampleSqlCipherMain";
            bool isDB = false;
            protected override void OnCreate(Bundle bundle)
            {
                base.OnCreate(bundle);
    
               //Need this to initialize the SQLitePCL 
                SQLitePCL.Batteries_V2.Init();
    
    
                var dbpath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
                var pathwithDB = Path.Combine(dbpath, "mydatabase.db");
    
                Log.Info(TAG, "Path: " + pathwithDB);
    
    
                SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(pathwithDB);
    
                //The "PRAGMA key" is the command SQLCipher Encryption key
                // xyz1921 is the passphrase for encrypting and decrypting the Database
                //This statement will not raise exception if the key is wrong
                var t = conn.Query<int>("PRAGMA key=xzy1921");
    
                try
                {
               //This will try to query the SQLite Schema Database, if the key is correct then no error is raised
                    var tt = conn.Query<int>("SELECT count(*) FROM sqlite_master");
                isDB = true;
                }
                catch(SQLite.SQLiteException ex)
                {
                isDB = false;
                    Log.Info(TAG, "Error: " + ex.Message);
                   // Display message to your user
    
                }
    
            if(isDB)
            {
                    conn.CreateTable<SampleOrder>();
                    conn.Insert(new SampleOrder() { name = "test" });
                    var table = conn.Table<SampleOrder>();
    
            }   
                 SetContentView (Resource.Layout.Main);
            }
        }
    }
    

Answers

  • JohnMillerJohnMiller USForum Administrator, Xamarin Team Xamurai

    @AlbertK,

    I wasn't aware of any encryption for this package. You might want to take a look at something like SQLCipher: https://www.zetetic.net/sqlcipher/

  • AlbertKAlbertK MYMember ✭✭✭
    Accepted Answer

    @JohnMiller
    I have manage to get the SqlCipher working, It is based in the open sourced SqlCipher version 3.3.1 and the SQLRAW provider comes from Eric Sink https://www.nuget.org/packages/SQLitePCLRaw.bundle_sqlcipher/1.1.2 and the SQLite-Net-PCL comes from (Frank A. Krueger) https://www.nuget.org/packages/sqlite-net-pcl/

    What is need is to add the SQLite-Net-PCL into the project first then add the SQLitePCLRaw.bundle_sqlcipher. I am enclosing the sample code.

    using System.IO;
    using Android.App;
    using Android.Widget;
    using Android.OS;
    using Android.Util;
    using SQLite;
    
    
    namespace SampleSqlCipher
    {
    
        public class SampleOrder
        {
            [PrimaryKey, AutoIncrement]
            public int? key_id { get; set; }
            public string name { get; set; }
            public float quantity { get; set; }
    
            public SampleOrder() { }
        }
    
        [Activity(Label = "SampleSqlCipher", MainLauncher = true, Icon = "@drawable/icon")]
        public class MainActivity : Activity
        {
            const string TAG = "SampleSqlCipherMain";
            bool isDB = false;
            protected override void OnCreate(Bundle bundle)
            {
                base.OnCreate(bundle);
    
               //Need this to initialize the SQLitePCL 
                SQLitePCL.Batteries_V2.Init();
    
    
                var dbpath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
                var pathwithDB = Path.Combine(dbpath, "mydatabase.db");
    
                Log.Info(TAG, "Path: " + pathwithDB);
    
    
                SQLite.SQLiteConnection conn = new SQLite.SQLiteConnection(pathwithDB);
    
                //The "PRAGMA key" is the command SQLCipher Encryption key
                // xyz1921 is the passphrase for encrypting and decrypting the Database
                //This statement will not raise exception if the key is wrong
                var t = conn.Query<int>("PRAGMA key=xzy1921");
    
                try
                {
               //This will try to query the SQLite Schema Database, if the key is correct then no error is raised
                    var tt = conn.Query<int>("SELECT count(*) FROM sqlite_master");
                isDB = true;
                }
                catch(SQLite.SQLiteException ex)
                {
                isDB = false;
                    Log.Info(TAG, "Error: " + ex.Message);
                   // Display message to your user
    
                }
    
            if(isDB)
            {
                    conn.CreateTable<SampleOrder>();
                    conn.Insert(new SampleOrder() { name = "test" });
                    var table = conn.Table<SampleOrder>();
    
            }   
                 SetContentView (Resource.Layout.Main);
            }
        }
    }
    
  • kitko112kitko112 HKMember

    @AlbertK ,

    Hi AlbertK, I am a newbie of Xamarin.

    I have tried your sample code, I just found that no exception was threw even I gave a different "PRAGMA key" for the second call, didI miss something?

    For my understanding, the database instance will be created when the code is first loaded, and "PRAGMA key" is used to encrypt the database for the first call. "PRAGMA key" will be used to decrypt for the subsequent call.

    I wonder should I create and encrypt the sqlite database using SQLCipher and copy it to my android device.

  • AlbertKAlbertK MYMember ✭✭✭

    @kitko112 , the second time you open the DB with the "PRAGMA Key" it will decrypt the DB if the key value is correct otherwise an exception will be thrown.

    No, you do not need to encrypt the DB with SQLCipher and copy to the android device.

Sign In or Register to comment.