Results 1 to 9 of 9

Thread: SQLite - SQLCipher encryption?

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jan 2022
    Posts
    26

    SQLite - SQLCipher encryption?

    Hi,

    When looking for a library to support encrypting a SQLite library--SQLCipher is spewed everywhere on the web.... How do I install it? Is it available for non-commercial use?

    I figured installing the Nuget package would work, but attempting connection to an encrypted database fails. (.db encrypted within DB Browser (SQLCipher))


    System.Data.SQLite.SQLiteException: 'SQL logic error
    Cannot use "Password" connection string property: library was not built with encryption support, please see "https://www.sqlite.org/see" for more information'



    Code:
                Dim ConnString As String = "Data Source=" & DatabasePath &
                                                "; Version=3;Password=" & Password & ";"
                _Connection = New SQLiteConnection(ConnString)
                _Connection.Open()
    I've also tried by using the setter property .SetPassword and the same message occurs.

    I installed with Nuget: NuGet\Install-Package sqlite-net-sqlcipher -Version 1.8.116

    And at the top of the module I simply Import System.Data.SQLite

    I have zero idea how to install another way. At this point I assume this is only available commercially.


    Thanks
    Last edited by BitFlipper88; Jan 30th, 2023 at 06:10 PM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQLite - SQLCipher encryption?

    Have you been here?

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jan 2022
    Posts
    26

    Re: SQLite - SQLCipher encryption?

    Yes I have. Would you mind pointing me in the right directory on how to use that in a project? Dependencies and binaries are not my strongpoint.

    I was hoping for an interop for System.Data.SQLite.

  4. #4
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: SQLite - SQLCipher encryption?

    I may stand corrected but as far as I know, and assuming your project is in .Net them SQLite library for .NET does not have built-in encryption support.....

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jan 2022
    Posts
    26

    Re: SQLite - SQLCipher encryption?

    @schoemr That depends what "SQLite library" means in your statement. I've learned a lot about SQLite encryption options for .NET in the last 48 hours and i'll summarize them below.

    System.Data.SQLite, which is the popular ADO.NET library for communicating with SQLite DBs, supports encryption apparently, but documentation is minimal from what I could find. A stackoverflow post out there explains it is RC4 encrypted, which has many known vulnerabilities. https://stackoverflow.com/questions/...yption-is-used

    As for SQLCipher, I reached out to SQLCipher forums to request help here
    https://discuss.zetetic.net/t/how-to...in-vb-net/5750

    It appears there are 3 options for implementing SQLCipher in .NET.

    1.) Purchase Zetetic's supported implementation based on System.Data.SQLite.

    2.) Use an unsupported, non-ADO open source .NET implementation like:
    https://www.nuget.org/packages/sqlite-net-sqlcipher

    3.) Use Microsoft.Data.SQLite.Core with SQLitePCL.raw - which is very unfriendly, but is ADO.NET. I had zero success trying to get this to work. Brice Lambson has a blog post here: https://www.bricelam.net/2016/06/13/...ncryption.html


    There are of course options aside from SQLCipher, but I haven't found any free solutions. Just a foreword on SQLite encryption: It should be noted that whatever solution a developer goes with, the encrypted database is tightly coupled to that library. A database encrypted with one API does not guarantee it will work with another API simply because it is a SQLite database and you provide it a working password. Encryption is handled differently for many of the technologies and will vary.

    My options at this point are to explore option 2 and use a non-ADO API. If that doesn't sit well with me--- possibly ditch SQLite entirely if another DB system meets security and budget requirements of my project.

    Thanks

  6. #6
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: SQLite - SQLCipher encryption?

    I have encrypted one of our test databases mssql2008R2 on the source (aka sql server encryption). It's troublesome but when you do it, you're done. You don't need any add on or mumbo jumbo.
    Last edited by sapator; Feb 1st, 2023 at 09:24 AM.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  7. #7
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: SQLite - SQLCipher encryption?

    I found the DB.
    You get something like this after the encryption:

    Name:  Clipboard01.jpg
Views: 538
Size:  15.4 KB

    That you can decrypt with a function:

    Code:
    CREATE FUNCTION [dbo].[fnVSAPIDecrypt] 
    (     
    	@ValueToDecrypt varbinary(max)
    )
    RETURNS nvarchar(max)
    AS
    BEGIN
       
    DECLARE  @DecryptedValue nvarchar(MAX)
    
    	
    SELECT @DecryptedValue = cast( DecryptBykey(@ValueToDecrypt) as nvarchar(max))
    
    
        RETURN @DecryptedValue
    
    END
    GO
    Again it's a little pain to create the encryption and it's only standard SQL.
    Last edited by sapator; Feb 1st, 2023 at 09:32 AM.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Jan 2022
    Posts
    26

    Re: SQLite - SQLCipher encryption?

    @sepator
    What DB system? SQLServer? I want to stick with a single file-based system, as a server solution is overkill for this app.

  9. #9
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: SQLite - SQLCipher encryption?

    Yep. As I've said ms(Microsoft)sql2008R2 .
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width