RC6 - New encrypt option for Sqlite DB: SqlCipher

RC6 Sqlite provided some options for DB encryption (new "codec" property) ...
One of the options is SqlCipher ... however, I couldn't find a way to open a DB generated and encrypted by the SQL Browser or SQLiteStudio software ...

My need is to open SqlCipher 3 files, but I was also unable to open files encrypted with SqlCipher 4 with RC6 ...

I also tried to inform SqlCipher options through a pragma (https://www.zetetic.net/sqlcipher/sqlcipher-api/), but without success ...

Olaf (maybe only you have this information), can you give me some information about the possibility of opening SqlCipher 3 files or what exactly the RC6 SqlCipher codec can work with?


--
Thiago

Quote:

---

Originally Posted by ThiagoPSanches

Olaf (maybe only you have this information), can you give me some information about the possibility of opening SqlCipher 3 files or what exactly the RC6 SqlCipher codec can work with?

---

Since RC-version 6.0.4 I'm using Ulrich Telles SQLite-MultipleCyphers-module...

And by default the Codec-Property sits at the "RC4"-option
(for backwards-compatibility with enrypted RC4/RC5 and also System.Data.SQLite produced DB-Files).

When you switch the Codec-Prop away from "RC4" - to one of the other Codecs, you will have to set the
"special, extended Cypher-Config-Options" (via the SQLite-Pragma-interface).

And for SqlCipher, there's quite a few, as documented here:
https://utelle.github.io/SQLite3Mult...her_sqlcipher/

The Pragma-interface (commands need to be applied via cConnection.Execute, shortly after Opening the DB):
https://utelle.github.io/SQLite3Mult...g_sql_pragmas/

HTH

Olaf

Quote:

---

Originally Posted by ThiagoPSanches

. . . however, I couldn't find a way to open a DB generated and encrypted by the SQL Browser or SQLiteStudio software ...

---

This is from SQLiteStudio open database dialog

https://www.vbforums.com/images/ieimages/2021/03/6.png

Is this SqlCipher option not working with SQLiteStudio?

cheers,
</wqw>

Quote:

---

Originally Posted by Schmidt

Since RC-version 6.0.4 I'm using Ulrich Telles SQLite-MultipleCyphers-module...

And by default the Codec-Property sits at the "RC4"-option
(for backwards-compatibility with enrypted RC4/RC5 and also System.Data.SQLite produced DB-Files).

When you switch the Codec-Prop away from "RC4" - to one of the other Codecs, you will have to set the
"special, extended Cypher-Config-Options" (via the SQLite-Pragma-interface).

And for SqlCipher, there's quite a few, as documented here:
https://utelle.github.io/SQLite3Mult...her_sqlcipher/

The Pragma-interface (commands need to be applied via cConnection.Execute, shortly after Opening the DB):
https://utelle.github.io/SQLite3Mult...g_sql_pragmas/

HTH

Olaf

---

Now it worked!

I tested it with the SqlCipher version 3 and 4.

Here are some notes, in case anyone needs...

Code:

---

Dim sCn As New cConnection
Dim sRs As New cRecordset

'*** Define 'CodecType' before 'OpenDB'
sCn.CodecType = CODEC_TYPE_SQLCIPHER

'*** Inform the Path and DB File to open
'*** Do not enter the 'EncrKey' parameter here
sCn.OpenDB "C:\Users\Th\Desktop\Cipher3.db"

'*** Indicate that the file is a 'SqlCipher 3' (if the file is 'SqlCipher 4', it is also necessary to indicate here: legacy=4)
sCn.Execute "PRAGMA legacy=3;"

'*** Inform 'EncrKey' here, that way and after setting the 'legacy' parameter
sCn.Execute "PRAGMA key=123;"


'*** TEST
sCn.Execute "CREATE TABLE Tab_TesteTeste(fld1 INTEGER PRIMARY KEY, fld2 TEXT);"
sCn.Execute "INSERT INTO Tab_TesteTeste(fld1, fld2) VALUES(1,'a');"

sRs.OpenRecordset "SELECT fld1 || fld2 FROM Tab_TesteTeste", sCn
MsgBox "Ok!" & vbCrLf & "fld1 || fld2: " & sRs(0)

sCn.Execute "DROP TABLE Tab_TesteTeste;"
Set sRs = Nothing
Set sCn = Nothing

---

Note: Even though all SqlCipher encryption parameters are pre-defined for version 4 (https://utelle.github.io/SQLite3Mult...her_sqlcipher/), to open a DB SqlCipher 4 it was necessary to inform 'legacy=4'


Thank you very much!


--
Thiago Peres Sanches

Quote:

---

Originally Posted by wqweto

This is from SQLiteStudio open database dialog

https://www.vbforums.com/images/ieimages/2021/03/6.png

Is this SqlCipher option not working with SQLiteStudio?

cheers,
</wqw>

---

I meant that I was not able to open a DB created and encrypted by SqliteStudio on RC6...

Everything is fine with SqliteStudio (it generates SqlCipher, by default, version 3... but it has options to generate in any version) and everything is fine with RC6, too...


--
Thiago

Quote:

---

Originally Posted by ThiagoPSanches

Now it worked!

I tested it with the SqlCipher version 3 and 4.

Here are some notes, in case anyone needs...

Code:

---

Dim sCn As New cConnection
Dim sRs As New cRecordset

'*** Define 'CodecType' before 'OpenDB'
sCn.CodecType = CODEC_TYPE_SQLCIPHER

'*** Inform the Path and DB File to open
'*** Do not enter the 'EncrKey' parameter here
sCn.OpenDB "C:\Users\Th\Desktop\Cipher3.db"

'*** Indicate that the file is a 'SqlCipher 3' (if the file is 'SqlCipher 4', it is also necessary to indicate here: legacy=4)
sCn.Execute "PRAGMA legacy=3;"

'*** Inform 'EncrKey' here, that way and after setting the 'legacy' parameter
sCn.Execute "PRAGMA key=123;"

---

---

Nice!

Here are some more PRAGMAs which set useful non-default settings that I'm successfully using with sqlite

Code:

---

    Set slcn = New cConnection
    slcn.OpenDB m_sDbFile
    slcn.Execute "PRAGMA auto_vacuum=incremental"
    slcn.Execute "PRAGMA journal_mode=WAL"
    slcn.Execute "PRAGMA temp_store=MEMORY"
    slcn.Execute "PRAGMA cache_spill=OFF"
    slcn.Execute "PRAGMA synchronous=FULL"
    slcn.BusyTimeOutSeconds = 5

---

The synchronous=FULL minimizes db corruption (and slowing down transaction commits) while journal_mode=WAL makes sqlite super efficient even for single reader/single writer scenario.

cheers,
</wqw>

Quote:

---

Originally Posted by wqweto

Here are some more PRAGMAs which set useful non-default settings that I'm successfully using with sqlite

Code:

---

    Set slcn = New cConnection
    slcn.OpenDB m_sDbFile
    slcn.Execute "PRAGMA auto_vacuum=incremental"
    slcn.Execute "PRAGMA journal_mode=WAL"
    slcn.Execute "PRAGMA temp_store=MEMORY"
    slcn.Execute "PRAGMA cache_spill=OFF"
    slcn.Execute "PRAGMA synchronous=FULL"
    slcn.BusyTimeOutSeconds = 5

---

---

There are parameters (pragmas) there that I didn't know...

Thank you for the informations!

Another useful pragma I usually enable these days (immediately after connection instancing), is:
PRAGMA foreign_keys = ON
(the automatics with regards to "On Delete Cascade" and/or "On Update Cascade" is something I don't want to miss anymore)

The default still is, to leave Foreign-Key support disabled, to not "torpedo" older sqlite-Apps.

Olaf

cache_spill is a new one on me. Off to visit the docs....

Hi Olaf,
I'm using VB6 and RC6(version: 6.0.0.8), and creating a new SQLITE database using:

sCnn.CreateNewDB SQLiteFileName, "mypassword"

But I can't open it using DB Browser for SQLITE (SQLCipher version)
I have tried using my passphrase with SQLCipher 3 defaults and SQLCipher 4 defaults and no luck

Also tried using:
sCnn.CodecType = CODEC_TYPE_SQLCIPHER
before creating the database, but it doesn´t make any difference.

What can I be doing wrong ?

Quote:

---

Originally Posted by jotajota52

What can I be doing wrong ?

---

Did you read, what Thiago wrote in his posting #4 (in the little Code-Section and its comments)?

I'd try to follow these instructions exactly (regarding App-Startup and establishing a Cnn to an encrypted DB, using the Pragma's).

Olaf

Thanks for the help!

Yes, I read Thiago's post and code, but the problem I'm facing is different because I'm creating a new database using RC6 and encrypting with RC6, but then I'm not able to read the database with other software, like DB Browser for SQLITE (SQLCipher version)

The only PRAGMA I can use before the create statement is:
sCnn.CodecType = CODEC_TYPE_SQLCIPHER
and it's not helping.

So I tried creating the database first, without encryption, and then tried to rekey:

sCnn.CodecType = CODEC_TYPE_SQLCIPHER
sCnn.OpenDB SQLiteFileName
sCnn.Execute "PRAGMA legacy=4;"
sCnn.ReKey Mypassword
set sCnn= Nothing

But that is not encrypting the database.

What else can I try ?

George.

Just change "OpenDB" to "CreateNewDB"... Like this:

Code:

---

Dim sCn As New cConnection

'*** Define 'CodecType' before 'OpenDB'
sCn.CodecType = CODEC_TYPE_SQLCIPHER

'*** Inform the Path and NEW DB File
'*** Do not enter the 'EncrKey' parameter here
sCn.CreateNewDB "C:\Users\Th\Desktop\Cipher3_New.db"

'*** Indicate that the file is a 'SqlCipher 3' (if the file is 'SqlCipher 4', it is also necessary to indicate here: legacy=4)
sCn.Execute "PRAGMA legacy=3;"

'*** Inform 'EncrKey' here, that way and after setting the 'legacy' parameter
sCn.Execute "PRAGMA key=123;"

'*** You have to create something in the DB for the password to be applied
sCn.Execute "CREATE TABLE TabTabTab(a INT); DROP TABLE TabTabTab;"

Set sCn = Nothing

MsgBox "Ok!"

---

--
Thiago

Quote:

---

Originally Posted by ThiagoPSanches

Just change "OpenDB" to "CreateNewDB"... Like this:

Code:

---

Dim sCn As New cConnection

'*** Define 'CodecType' before 'OpenDB'
sCn.CodecType = CODEC_TYPE_SQLCIPHER

'*** Inform the Path and NEW DB File
'*** Do not enter the 'EncrKey' parameter here
sCn.CreateNewDB "C:\Users\Th\Desktop\Cipher3_New.db"

'*** Indicate that the file is a 'SqlCipher 3' (if the file is 'SqlCipher 4', it is also necessary to indicate here: legacy=4)
sCn.Execute "PRAGMA legacy=3;"

'*** Inform 'EncrKey' here, that way and after setting the 'legacy' parameter
sCn.Execute "PRAGMA key=123;"

'*** You have to create something in the DB for the password to be applied
sCn.Execute "CREATE TABLE TabTabTab(a INT); DROP TABLE TabTabTab;"

Set sCn = Nothing

MsgBox "Ok!"

---

--
Thiago

---

Thank you Thiago,
My password was made up of numbers and letters
I just realized that the key must be only numbers for it to work!

Is there a way to pass an alphanumeric key ?


George

Nevermind, I just changed the following
sCnn.Execute "PRAGMA key='" & mypassword & "';"
and now is working!!

Thank you very much Thiago and Olaf for all the help!!!

@schmidt -

I've converted several of my older MS Access database applications to SQLite. These applications have all used un-encrypted MS Access databases. I'm also using VS Community 2019 ((VB.Net).

I am now facing the conversion of an encrypted MS Access database to an encrypted SQLite database.

I've searched for help on how to encrypt an SQLite database and have found lots (and lots) of information, but I've not been able to figure out how to do this. I've read that SQLite does not include encryption and that you need an extension for this. There are several commercial extensions available (e.g., SEE) but these are expensive (an SEE license is $2000).

I think the SQLCipher software can encrypt a SQLite database and it is open-source (free). Reading more about this software, it appears that you have to compile the software in order to use it. This is where my understanding/experience "hits the wall" and I need some help.

Can you give me some guidance on how to proceed (or point me to a good step-by-step tutorial)?

Quote:

---

Originally Posted by Mark@SF

@schmidt -

I've converted several of my older MS Access database applications to SQLite.
I'm also using VS Community 2019 ((VB.Net).

I am now facing the conversion of an encrypted MS Access database to an encrypted SQLite database.

---

The free Encryption-Modules for SQLite (which are an extension on top of "vanilla-sqlite") -
are the responsibility of the different "Wrapper-Authors".

For the SQLite-COM-wrapper (behind the RC6-libs, used in VBA/VB6 and VBScript), I'm providing that service.

The "official .NET-wrapper for SQLite" does (to my knowledge) not come with support for the free Encryption-extensions.

And I don't have the time currently, to dive into the intricacies of:
- recompiling the .NET-wrapper for SQLite in a proper manner (using proper includes)...
- so that these free crypto-extensions will work as they should, without breaking other things in the .NET-wrapper

As said, this is the JOB of the devs who are producing the current SQLite-wrappers for a certain target-language or -environment.
(I'd suggest, to ask your question in the SQLite-Forum... chances are - somebody has already published something .NET-related regarding crypto-extensions).

HTH

Olaf

Quote:

---

Originally Posted by Schmidt

And I don't have the time currently, to dive into the intricacies of:
- recompiling the .NET-wrapper for SQLite in a proper manner (using proper includes)...
- so that these free crypto-extensions will work as they should, without breaking other things in the .NET-wrapper

---

I was just about to suggest that there is nothing stopping him from just using your library in .Net until a suitable .Net alternative can be found but I decided to test this before I made this suggestion. It seems that the vbRichClient library cannot work in .Net. I don't pretend to be a COM expert but based on the errors reported by Visual Studio 2019, I'm guessing your library doesn't fully respect the COM specification. For whatever reason VB6 puts up with it and I've heard rumors it even works in TwinBASIC but as far as the .Net is concerned, it's type library is broken. It generated 32 errors, among them it even found an interface that is not derived from IUnknown:-

Code:

---

Warning                Processing COM reference "VB" from path "C:\Program Files (x86)\Microsoft Visual Studio\VB98\VB6.OLB". Type library importer has encountered an interface not derived from IUnknown: '___MSJetSQLHelp'.

---

As far as I know, this is a major COM violation since the COM specification/documentation makes it's clear that all interfaces must be derived from IUnknown. I then tested ADO(ActiveX Data Objects 6), another COM library that is roughly comparable in complexity to vbRichClient and that worked without a problem. It not only got imported correctly, it actually worked. I was able to query a database using plain old COM ADO in .Net:-

Code:

---

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim c As New ADODB.Connection
        Dim sb As New SqlConnectionStringBuilder

        sb.InitialCatalog = "EmSqlDB"
        sb.DataSource = "VBBOX"
        c.Provider = "sqloledb"

        c.ConnectionString = sb.ToString & ";Integrated Security=SSPI"
        c.Open()

        Dim rs As ADODB.Recordset = c.Execute("Select * From TblProducts")

        Do Until rs.EOF
            Debug.WriteLine(rs.Fields.Item(2).Value.ToString)
            rs.MoveNext()
        Loop

    End Sub

---

Unfortunately my knowledge of COM is far too limited to figure out how to make vbRichClient work in a .Net solution but it would have been a nice easy solution to the OP's problem if it did. This is one of the rare instances where your library actually covers something that isn't by the .Net ecosystem, as far as we know anyway.

Quote:

---

Originally Posted by Niya

...as far as the .Net is concerned, it's type library is broken.

---

Well, as long as the true COM-tools have no problem with referencing it (VB6 and VBA as well as TwinBasic and VBScript),
I'm not really interested in, what the .NET-Typelib-parser throws out as warnings.

Quote:

---

Originally Posted by Niya

... it even found an interface that is not derived from IUnknown:-

Code:

---

Warning        Processing COM reference "VB" from path "C:\Program Files (x86)\Microsoft Visual Studio\VB98\VB6.OLB". Type library importer has encountered an interface not derived from IUnknown: '___MSJetSQLHelp'.

---

---

This indicates, that the .NET typelib-parser goes "one level too deep" in its "COM-reference-imports" -
(trying to resolve also implemented interfaces, instead of being satisfied with the Default-interface of a Class) -
and besides, I'm not the Author of 'VB6.olb', Microsoft is...

Quote:

---

Originally Posted by Niya

This is one of the rare instances where your library actually covers something that isn't by the .Net ecosystem...

---

Well, SQLite-wise there's a lot more missing, as e.g.:
- Zip-support
- CSV-support
- superfast JSON-serialization of queried Resultsets
- JET-Sql-Function-support as e.g.: Left$, Mid$, Right$, DateDiff, DatePart

Graphics-wise:
- SVG Read/Write support
- Alpha-Channel-JPGs
- ControlPoints-support
- ...plus a lot more convenience-stuff, not found in .NET

At least proper JSON-support was recently included in .NET (though long after the RC-lib shipped with it).

Olaf

Quote:

---

Originally Posted by Schmidt

Well, as long as the true COM-tools have no problem with referencing it (VB6 and VBA as well as TwinBasic and VBScript),
I'm not really interested in, what the .NET-Typelib-parser throws out as warnings.

---

Oh I'm challenging this for the mere fact that none of us on the .Net side have never had any problems using COM libraries in .Net. The most common use case you see is Office Interop(Word, Excel etc). .Net has never had a problems referencing these. I've personally used Excel interop and the Media player with no problem as far as referencing and instantiating objects go. I've also used the UPNP library successfully at one time which allows you to automatically forward internet traffic from your router to your application. I even showed in this very thread that ADO also works with zero problems despite the fact that it is completely unneeded in .Net.

Look, I get it. vbRichClient was made for VB6 users and that's fine. But what I absolutely will not co-sign is this fantasy of "true COM tools". This phrase is design to bamboozle innocent folks who don't know any better. COM at the end of the day is a binary standard. It is not a VB6 only thing. Folks have been using COM in C++ to access DirectX since the dawn of time. If a binary a implements a COM server, it should be consumable by any COM client. If any COM client cannot do that, as far as I'm concerned, it's broken. In this regard, it is vbRichClient that is not a "true COM tool". It's some kind of Frankenstein library that looks like a COM binary. No one could deny that you are a COM expert but Microsoft created the COM specification and if one of their tools is saying the type library for your tool is broken, who am I to question that. You are using your own standards, not the ones that Microsoft recommends.

I'm not saying you have to do anything about it. If you are satisfied enough that it works with VB6, hey that's fine. But I wouldn't dare say it's a proper COM tool if it commits so many COM violations that it breaks with certain COM clients.

Quote:

---

Originally Posted by Schmidt

This indicates, that the .NET typelib-parser goes "one level too deep" in its "COM-reference-imports" -
(trying to resolve also implemented interfaces, instead of being satisfied with the Default-interface of a Class) -
and besides, I'm not the Author of 'VB6.olb', Microsoft is...

---

I guess you know better than the people who invented COM about what it should and shouldn't be doing....right? ;)

Quote:

---

Originally Posted by Schmidt

Well, SQLite-wise there's a lot more missing, as e.g.:
- Zip-support
- CSV-support
- superfast JSON-serialization of queried Resultsets
- JET-Sql-Function-support as e.g.: Left$, Mid$, Right$, DateDiff, DatePart

Graphics-wise:
- SVG Read/Write support
- Alpha-Channel-JPGs
- ControlPoints-support
- ...plus a lot more convenience-stuff, not found in .NET

At least proper JSON-support was recently included in .NET (though long after the RC-lib shipped with it).

---

There is far too much in the world for any single library to cover every thing there is. Even massive frameworks like .Net and the Java Platform won't cover all things. There were bound to be things that independent library authors like yourself would cover that have been ignored by the titans in the industry. Hell, even I have personal libraries that covers things that they missed.

What is important is that these Frameworks at least cover the more common cases. I don't think large populations of developers are going to lose their minds over Zip support in SQLite or transparent Jpegs. Also, who the hell even uses transparent Jpegs? Like 10 people? :ehh:

Quote:

---

Originally Posted by Schmidt

At least proper JSON-support was recently included in .NET (though long after the RC-lib shipped with it).

---

This isn't saying anything. vbRichClient is still a 3rd party library and there were 3rd party libraries in the .Net ecosystem that covered JSON long before it became standard in the framework. Just like someone has to search for and install vbRichClient, they would have to search for and install a JSON library. I'd even go as far as saying .Net still had the advantage here because of things like Nuget that makes installing 3rd party libraries effortless as opposed to all the song and dance it requires to deploy vbRichClient.

Quote:

---

Originally Posted by Niya

I guess you know better than the people who invented COM about what it should and shouldn't be doing....right? ;)

---

I already tried to explain it to you...
But again, ... maybe it will sink in this time...

VB6 and modern Office-VBA (when they check in COM-Dlls, parsing their typelibs) -
are usually satisfied with parsing the Default-Interfaces of an (imported) COM-Dll-Class.

If such a parsed Class *also* implements "additional interfaces", these are (AFAIK) left to compiler-evaluation -
(when a "Left-Hand-side" assignment enters the game, involving a COM-TypeCast via QueryInterface - "away" from the default-interface, to an implemented one).

So yes, I think the developers of the MS-devs of these true COM-tools (Office-VBA, VB6, VBScript) knew what they were doing
(with regards to TypeLib-parsing of COM-Dlls) - whilst the .NET-guys who wrote the Typelib-parser for .NET-imports did not.

So, the .NET-Typelib-parser complains (with a warning, not an error) about some incorrect stuff in
an Interface-lib (an MS-one: VB6.olb), which is indirectly addressed via [Implements Printer] in one of the exposed Classes of the RC-lib
(but Types of that interface are not part of the default-interface of that Class which implements this secondary Printer-interface).

So, if 3 MS-Tools (as well as TwinBasic) do it right - and one MS-Tool (the .NET Typelib-parser) has problems -
who do you think is to blame?

In either case your bug-report has to go to MS (not to the Author or the RichClient):
1) for "overzealous" TLB-parsing when importing complex COM-libs (due to pre-scanning secondary implemented types)
2) and additionally you might kindly ask to fix the "warning-message-causing MS-VB6.olb"

Despite that (all being MS-VS2019 .NET problems) - I'm not a VS2019-user - so I don't even know if you're using your tool correctly.
I'd suggest you ask questions about that in a VS2019-group - here we're talking about VB6 and its COM-tools.

Olaf

Quote:

---

Originally Posted by Schmidt

I already tried to explain it to you...
But again, ... maybe it will sink in this time...

VB6 and modern Office-VBA (when they check in COM-Dlls, parsing their typelibs) -
are usually satisfied with parsing the Default-Interfaces of an (imported) COM-Dll-Class.

If such a parsed Class *also* implements "additional interfaces", these are (AFAIK) left to compiler-evaluation -
(when a "Left-Hand-side" assignment enters the game, involving a COM-TypeCast via QueryInterface - "away" from the default-interface, to an implemented one).

So yes, I think the developers of the MS-devs of these true COM-tools (Office-VBA, VB6, VBScript) knew what they were doing
(with regards to TypeLib-parsing of COM-Dlls) - whilst the .NET-guys who wrote the Typelib-parser for .NET-imports did not.

So, the .NET-Typelib-parser complains (with a warning, not an error) about some incorrect stuff in
an Interface-lib (an MS-one: VB6.olb), which is indirectly addressed via [Implements Printer] in one of the exposed Classes of the RC-lib
(but Types of that interface are not part of the default-interface of that Class which implements this secondary Printer-interface).

So, if 3 MS-Tools (as well as TwinBasic) do it right - and one MS-Tool (the .NET Typelib-parser) has problems -
who do you think is to blame?

In either case your bug-report has to go to MS (not to the Author or the RichClient):
1) for "overzealous" TLB-parsing when importing complex COM-libs (due to pre-scanning secondary implemented types)
2) and additionally you might kindly ask to fix the "warning-message-causing MS-VB6.olb"

Despite that (all being MS-VS2019 .NET problems) - I'm not a VS2019-user - so I don't even know if you're using your tool correctly.
I'd suggest you ask questions about that in a VS2019-group - here we're talking about VB6 and its COM-tools.

Olaf

---

I'll concede this to you as the resident COM expert. But hey, I have never seen any of the well known COM libraries fail like this with .Net. Something is wrong somewhere.

Quote:

---

Originally Posted by Schmidt

I'm not a VS2019-user - so I don't even know if you're using your tool correctly.

---

You might be 100% correct here, even so, it still says a lot that I didn't require any special knowledge to get .Net to work with Office, or even ADO. I don't think Microsoft intended every developer wanting to use a COM library also be a COM expert. I mean I'm more technically proficient than your average front-end developer just getting by doing basic tasks and this problem is beyond even my capabilities. Do you think Microsoft intended this? Perhaps we just got a clue about why Microsoft abandoned COM as their underlying object model for Visual Basic.

You know, come to think of it, I have had countless problems like this with COM over the entire 10+ years when I was a VB6 programmer, especially when it comes to deploying them in the wild. I have never and I stress, never had these kinds of problems with .Net. COM is too fragile and breaks too damn easily. Removing COM as the foundational object model was the best thing Microsoft ever did for Visual Basic. This little experience only strengthens my resolve on .Net being a better development platform than VB6.

Quote:

---

Originally Posted by Niya

I don't think Microsoft intended every developer wanting to use a COM library also be a COM expert.

---

That's why VBA, VBScript and VB6 exist...
The latter being able, to even produce these COM-libs with ease in a true *native* format (not behind an additional VM-layer).
Thats what I see as a "true COM-tool" (since you asked about it).

Quote:

---

Originally Posted by Niya

I have had countless problems like this with COM over the entire 10+ years when I was a VB6 programmer,
especially when it comes to deploying...

---

One of the reasons why I developed DirectCOM.dll as one as my first activities when starting with VB5/VB6
(long before the SxS-mechanisms became available in XP, then allowing for regfree deployments another way).

Most professional VB6-Devs know about regfree-Mode for a long time now - and use it appropriately.

At the place where I work, we deploy our VB6-based main-solution (a dozen executables in a "Root-Folder") -
along with about 100 different Dlls and OCXes in a Bin-Folder of about 170MB unzipped -
all via XCopy, in a regfree manner (for a loong time already).

Guess you didn't stick around long enough...

Olaf

Quote:

---

Originally Posted by Schmidt

Guess you didn't stick around long enough...

---

I guess not. ;)

I need to document which encryption my Sqlite DBs use.
How could I tell which Cypher version my DBs use if I use the default Cipher encryption of RC6?

I tried to locate that info, but in "Documentation", there is nothing here:

https://www.vbrichclient.com/#/en/Documentation/SQLite/
"SQLite
No content yet..."

Thank you!

RC4, it's here...

Quote:

---

Originally Posted by Schmidt

Since RC-version 6.0.4 I'm using Ulrich Telles SQLite-MultipleCyphers-module...

And by default the Codec-Property sits at the "RC4"-option
(for backwards-compatibility with enrypted RC4/RC5 and also System.Data.SQLite produced DB-Files).

When you switch the Codec-Prop away from "RC4" - to one of the other Codecs, you will have to set the
"special, extended Cypher-Config-Options" (via the SQLite-Pragma-interface).

And for SqlCipher, there's quite a few, as documented here:
https://utelle.github.io/SQLite3Mult...her_sqlcipher/

The Pragma-interface (commands need to be applied via cConnection.Execute, shortly after Opening the DB):
https://utelle.github.io/SQLite3Mult...g_sql_pragmas/

HTH

Olaf

---

I thought AES256 was a subtype of SQLCipher, but then I noticed that RC6 cConnection provides the following:

cn.CodecType = CODEC_TYPE_SQLCIPHER

cn.CodecType = CODEC_TYPE_AES256

I would like to read about it in a documentation, but I didn't find one. If it exists, can you please tell me where it is, and if not, could somebody explain what CODEC_TYPE_AES256 is?
Thank you!

Quote:

---

Originally Posted by tmighty2

I would like to read about it in a documentation, but I didn't find one...

---

Really now... after Thiago just posted the Link to it?

Here it is, for the 3rd time:

And for SqlCipher, there's quite a few, as documented here:
https://utelle.github.io/SQLite3Mult...her_sqlcipher/

Hint: the CodecType-Enum-Values of the RC6 are reflected in the tree-menu (to the left of the page)...
Olaf

Dear Olaf,

we are currently working on manually decrypting database files where the cConnection.CodecType is set to CODEC_TYPE_AES256. To ensure that our approach aligns correctly with the encryption mechanisms used, we need to confirm the exact encryption parameters.

Could you please provide the values or confirm if the following details are correct for this encryption type?

(Javascript)

Code:

---

let salt = data.slice(0, 16);
let iv = data.slice(16, 32);
let encrypted = data.slice(32);

keySize: 256 / 8,
iterations: 256000

---

Additionally, if possible, could you share a snippet of the encryption code, similar to this example, to ensure we are implementing the decryption process correctly?

Code:

---

function decodeAes(pass, data) {
    let salt = data.slice(0, 16);
    let iv = data.slice(16, 32);
    let encrypted = data.slice(32);

    let key = CryptoJS.PBKDF2(pass, CryptoJS.enc.Hex.parse(salt.toString('hex')), {
        keySize: 256 / 8,
        iterations: 256000
    });

    let decrypted = CryptoJS.AES.decrypt(
        {
            ciphertext: CryptoJS.enc.Hex.parse(encrypted.toString('hex'))
        },
        key,
        {
            iv: CryptoJS.enc.Hex.parse(iv.toString('hex')),
            padding: CryptoJS.pad.Pkcs7,
            mode: CryptoJS.mode.CBC
        }
    );
    return Buffer.from(decrypted.toString(CryptoJS.enc.Latin1), 'latin1');
}

---

This information is crucial for ensuring compatibility and maintaining the high security standards of our application. Your assistance in this matter would be greatly appreciated.

Quote:

---

Originally Posted by tmighty2

we are currently working on manually decrypting database files ...

---

Ulrich Telles work on "multiple ciphers" targets the SQLite-library specifically.
(which encrypts and decrypts dynamically and *page-wise* - and not over "the whole DB").

Generally, messing with a DB-File-Format outside the provided "Handler-lib" of the DB-vendor,
is a bad idea - so I'd refrain from applying javascript-based "CryptoJS-routines" to SQLite-DBFiles directly.

Please use only the mechanisms, built into the sqlite-library itself -
setting the new, advanced params via Pragma-instructions, as shown in this thread.

These pragmas, are documented here: https://utelle.github.io/SQLite3Mult...g_sql_pragmas/

Olaf

> ciphertext: CryptoJS.enc.Hex.parse(encrypted.toString('hex'))

Why would you want to potentially convert to hex and back hundreds of MBs of data?

But probaby this is the current state of web development. . .

cheers,
</wqw>

qustion1: how can i open an encrypted sqlite.db by custom method
for example:
PageSize:4096
KDF step:64000
HMac: SHA1
KDF: SHA1
Plain Text Header Length: 16

how to set a hexkey? codes below show error: unrecognized token.

Code:

---

    Dim cn As New cConnection
    Dim rs As New cRecordset
    cn.CodecType = CODEC_TYPE_AES256
    cn.OpenDB "C:\Users\Administrator\Desktop\1.db"
    'cn.Execute "PRAGMA legacy=3;"  'SqlCipher 3
    cn.Execute "PRAGMA hexkey=31011b4219534911a17f99a9031d1c16f06e0c9120bd6e0457c4e3f63340804c;"

---

question2: how can i open an encypted sqlite.db and save it do a decryped one?

1 - I haven't tested opening your specific example, but the way would be to apply the PRAGMAs with the custom parameters, as documented here:

https://utelle.github.io/SQLite3Mult...g_sql_pragmas/

2 - To save a decrypted file, you could:

- Make a copy of the file
- Open the DB with the encryption key
- Execute cn.Execute "PRAGMA rekey='';" to remove the encryption from the file.

Additionally, there is also the CopyDatabase method in RC6:

Code:

---

cn.CopyDatabase "C:\Users\Administrator\Desktop\New.db", ""

---

i want to load a memory database from a byte array
which byte array is loaded from a encrypted database, need fix some of the bytes in byte array before loading
is there a way?

The only way to populate a memory database, as far as I know, is by using SQL scripts — CREATE TABLE, INSERT INTO, etc. — or by attaching another SQLite database or even a CSV file. In those cases, I would use INSERT INTO ... SELECT.

It would be very useful for me too to populate a memory database from a byte array (“content”), but I’ve never seen anything like that.

cn.CopyDataBase "c:\newpath\new.db","newKey"
this way can make a encrypted sqlite data, but i can not use db browser(sqlcipher) to open it.

From what I've observed, when you provide a key in the EncrKey parameter (the second argument of CopyDataBase),
it will always create a new database using the default RC5/6 encryption.

i use rc6 to open a sqlcipher 4 legacy data.

Code:

---

    Dim cn As New cConnection
    Dim rs As New cRecordset
    cn.CodecType = CODEC_TYPE_SQLCIPHER
    cn.OpenDB "C:\Users\Administrator\Desktop\1.db"
    cn.Execute "PRAGMA legacy=4;"  'SqlCipher 4
    cn.Execute "PRAGMA hexkey='c75e9e27cd9c326dee440b4213e113f5b1a7a0931a3a05d9239eed86965f0d8c475523650e16bf4f1ea295184a98e129';"
    Set rs = cn.OpenRecordset("select * from table;")
    Debug.Print rs.RecordCount

---

it shows: Cannot compile SelectStatement: file is not a database