Complete SQLLite newbie - issues with SQLCipher in a Windows 10 UWP app


#1

Hi all you SQLCipher experts - hoping you can help me.

I’m working on a Windows 10 UWP app using Visual Studio 2017. We need to use SQLCipher to store user documents in an encrypted format but I am running into issues that I can’t find any answer for. As the tile says I am new to SQLite so I found a blog that showed me how to get going with SQLCipher to create an encrypted db - the code below uses Eric Sink’s SQLitePCLRaw library as that was the only thing I could get working :frowning:

    private void CreateSQLCipherDB()
    {
        const string key = "#a1b2c3d4#";
        string query = null;
        string dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "testdb.sqlite");
        SQLitePCL.sqlite3 sqlite3 = null;

        var sqlProvider = new SQLitePCL.SQLite3Provider_sqlcipher();
        SQLitePCL.raw.SetProvider(sqlProvider);

        try
        {
            SQLitePCL.raw.sqlite3_open(dbPath, out sqlite3)

            query = $"PRAGMA key = '{key}'";
            SQLitePCL.raw.sqlite3_exec(sqlite3, query)

            query = $"CREATE TABLE Table1 (id INTEGER PRIMARY KEY NOT NULL, message TEXT);";
            SQLitePCL.raw.sqlite3_exec(sqlite3, query)

            query = $"INSERT INTO Table1 VALUES(NULL, 'David Bowie');";
            SQLitePCL.raw.sqlite3_exec(sqlite3, query)

            query = $"INSERT INTO Table1 VALUES(NULL, 'Nina Simone');";
            SQLitePCL.raw.sqlite3_exec(sqlite3, query)

        }
        catch(Exception ex)
        {
            SQLitePCL.raw.sqlite3_close(sqlite3);
        } 
    }

So this code works fine and creates what I believe is an encrypted database… the problem is how to then read values back out of the database. I have the code below but I honestly have no idea what to do after the “int rows = SQLitePCL.raw.sqlite3_exec(sqlite3, query);” line to get my data into a List and I can’t seem to find any reference to the raw PCL commands (using C#) that I need.

    private void QuerySQLCipherDB()
    {
        const string key = "#a1b2c3d4#";
        string dbPath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "testdb.sqlite");
        SQLitePCL.sqlite3 sqlite3 = null;

        var sqlProvider = new SQLitePCL.SQLite3Provider_sqlcipher();
        SQLitePCL.raw.SetProvider(sqlProvider);

        SQLitePCL.raw.sqlite3_open(dbPath, out sqlite3);

        try
        {
            query = $"PRAGMA key = '{key}'";

            var query = "SELECT id,message FROM Table1";
            int rows = SQLitePCL.raw.sqlite3_exec(sqlite3, query);

           //How do I get the above data into a useful collection object?

        }
        catch (Exception ex)
        {
            SQLitePCL.raw.sqlite3_close(sqlite3);
        }
    }

So can anyone point me in the right direction please? I know it’s probably stupidly simple but I’ve searched google and read forums until I’m blue in the face but can’t seem to find decent documentation or examples for using the raw SQLite commands in C#.

TIA for any help offered :slight_smile:


#2

Hi @mike_lockbox

Thank you for your interest in SQLCipher. It appears you may be sourcing open source packages for your UWP application. If this development is for an organization, you might consider our commercial SQLCipher for Windows UAP package, a turnkey solution. We offer trials upon request here.


#3

Hi, thanks for the reply . I thought the package I was using was a free version of your product so apologies for any confusion caused. We use your licensed product on the iPad version of our app (which I don’t develop) so we will probably also end up using that on the Windows10 version… I’ll download the trial and give that a go :slight_smile:


#4

I’m still confused about how to install SQLCipher and get it set up - I tried to find a NuGet package but couldn’t find anything relevant - is the software only provided via an installer once you license it or get a trial? I did find this article from 2015 (https://www.zetetic.net/blog/2015/12/01/sqlcipher-for-windows-uap/) but it’s well out of date given UAP is dead and the packqge “SQLCipher for Universal Window App Platform” no longer exists.

Also, if we wanted to use the OpenSource one instead does that work with UWP? If so, how do I go about installing it and setting it up and what other NuGet packages would I need? And what are the differences between the open source version and the licensed version?

Sorry about all the questions but there are so many packages for SQLLite and various forms of SQLCipher I am finding it all extremely confusing to know which to use.


#5

Hello @mike_lockbox

We have just approved your trial request and sent over an email with instructions for downloading the latest package along with your trial license key. If you have any questions, please feel free to reach out. With regard to your question about utilizing open source, are you referring to using the source of SQLCipher itself? If so, you are welcome to compile and package the source and dependencies for your target platform. Alternatively, we sell a turnkey solution utilizing SQLCipher on the UAP platform.


#6

Thanks for that - trial is now installed and everything working A-OK :slight_smile:


#7

Hi @mike_lockbox

Excellent, we are happy to hear your evaluation is going well!