How can i use e_sqlcipher with microsoft.data.sqlite?

Question

I'm trying to switch my app over from access to SQLite, and id like to have encryption. I am trying to use "Microsoft.data.sqlite" with "SQLitePCLRaw.bundle_e_sqlcipher", but setting the password seemingly does nothing. Following the official guide : from here

it set it up like this : Public DBConnection As String = "Data Source=" & Environment.CurrentDirectory & "\test.db;Mode=ReadWriteCreate;Password=testtest123" and opened the connection and i also tried doing this :

Dim DBConnection As String = "Data Source=" & Environment.CurrentDirectory & "\test.db;Mode=ReadWriteCreate"

    Try
        Using dbconn As New SqliteConnection(DBConnection)
            dbconn.Open()
            Dim command = dbconn.CreateCommand()
            command.CommandText = "SELECT quote($password);"
            command.Parameters.AddWithValue("$password", "testtest123")
            Dim quotedpsw = New String(command.ExecuteScalar)

            command.CommandText = "PRAGMA key = " & quotedpsw
            command.Parameters.Clear()
            command.ExecuteNonQuery()
        End Using
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

I tried these methods with setting SQLitePCL.raw.SetProvider(New SQLitePCL.SQLite3Provider_e_sqlcipher()) before the connection is used, none of them encrypted it and also didn't give exceptions. I also tried using "SQLitePCLRaw.bundle_sqlcipher" but it gives this exception: Method not found „Int32 SQLitePCL.ISQLite3Provider.sqlite3_win32_set_directory(Int32, System.String)”.'

I also tried encrypting the database outside with DB Browser for SQLite both with SQLCipher 3 and 4 but neither of those ways could I then connect to the db from my app... Thanks!

EDIT.: I thought I'd restart so i removed every package and redownloaded the two I mentioned originally(+ the provider package), and now when i set SQLitePCL.raw.SetProvider(New SQLitePCL.SQLite3Provider_e_sqlcipher()) every time it gives an exception : "e_sqlcipher" DLL couldn't be loaded. The specified module couldn't be found. Id also be open to using another solution if anyone has good experiences with another one.

OT, I would recommend against using `Environment.CurrentDirectory`. You are probably assuming that that will return the path of the folder containing the EXE but that is not necessarily the case and it can actually change during a session. If you want the program folder path then, in WinForms, use `Application.StartupPath`. That said, while I'm not 100% sure it's supported by that SQLite provider, I think that you can hard-code the path as `"Data Source=|DataDirectory|\test.db"`. That would certainly work for Access or SQL Server Express. — jmcilhinney, Aug 19 '21 at 06:10
Thanks, I have tested it and it does return data no problem the way I have it in the op. i had it like this just for testing but i'm going to hardcode it just to rule that out. — bdwixx, Aug 19 '21 at 06:15

score 1 · Answer 1 · answered Aug 19 '21 at 08:30

After moving the dll into the solution explorer the dll not loading error went away. Then what i did was i set the filename to one that didnt exist so that i could create a new database with microsoft.data.sqlite with sqlitepclraw provider set to sqlcipher(using batteries.init), then with the password in the connection string i ran a query to create a table, like this it did encrypt it, then i just used DB Browser to export data from the old database into the new encrypted one. I have tried many other things, and this is the one way i could make it work

How can i use e_sqlcipher with microsoft.data.sqlite?

1 Answers1