Connecting to decrypted SQLite database

Hi,

with this code below I connect to an encrypted database and use it without problems:

[code]dim pdb as new SQLiteDatabase
dim pdbsql as string

pdbsql = “SELECT … sql blabla … not the problem”

dim datapdb as RecordSet

pdb.DatabaseFile = GetFolderItem(“performance_database.sqlite”)
pdb.EncryptionKey=“mysecretpassword”

if pdb.connect then
pdb.Decrypt
datapdb=pdb.sqlselect(pdbsql)
If datapdb <> Nil Then
if datapdb.field(“intV1”).value = Nil then
lblTakeoffSpeeds.bold = true
lblTakeoffSpeeds.textcolor = &cFF0000
lblTakeoffSpeeds.text =“T/O NOT ALLOWED!”
else
lblTakeoffSpeeds.bold = true
lblTakeoffSpeeds.textcolor = &c00FF00
lblTakeoffSpeeds.text=("V1 = " + datapdb.Field(“intV1”).Value + " / " + "VR = " + datapdb.Field(“intVR”).Value + " / " + "V2 = " + datapdb.Field(“intV2”).Value + " / " + "VFS = " + datapdb.Field(“intVFS”).Value)
datapdb.Close
end if
pdb.encrypt(“mysecretpassword”)
end if
else
MsgBox("Connection error: " + pdb.ErrorMessage)
end if
[/code]

But if the database is already decrypted I get the error message “Connection error: file is encrypted or is not a database.” … both is wrong: the database is decrypted (because I can open it and work with it with DB Browser for SQLite) and it is definitely a sqlite database.

Because of the connection error I am not able to encrypt the database again before using it …

How can I make sure that a normally encrypted database can be encrypted again when it was left decrypted ???

Thank you very much in advance,
Gerson Nerger

I tried to read a DB encrypted with XOJO and couldn’t open this DB with “DB Browser for SQLite”.

I think both use different methods fo encrypting a SQLite DB.

XOJO uses SEE (https://www.hwaci.com/sw/sqlite/see.html , a $2000 License Modul)

Stefan Cyrus

You don’t need to decrypt the database in order to use it. Simply supply the encryption key and connect.

Your best option is to try to connect without the encryption key first. If that works, you know it wasn’t encrypted.

Something like:

[code]Const EncryptionKey = “TheSecretPassword”
Dim Connection As New SQLiteDatabase
Connection.DatabaseFile = FolderItem
If Connection.Connect Then
Connection.Encrypt(EncryptionKey)
Else
Connection.EncryptionKey = EncryptionKey
If Not Connection.Connect Then
// Wrong encryption key? Throw exception?
Return
End If
End If

// Now connected to an encrypted database that is ready for use
[/code]

You could reverse the logic too and attempt to connect with encryption first, you would just need to clear the .EncryptionKey property before trying to connect without encryption.

@Thom McGrath Thank you very much for your reply. This answered my question. I thought decrypting is necessary to use an encrypted database.

If you decrypt it during connecting to it, any other passerby with access to that folder will find an unencrypted database. That goes against the reason you encrypted the database to begin with.

I agree.

There may be uses for a temporary decryption. I did so with a project where I had to examine the database, and my preferred db editor does not give me the option to connect to encrypted SQLite databases.
So very much like Thom wrote, I try to open the db encrypted and if successful, during a debug run I decrypt it and connect again. Vice versa in a build.

It should be noted that I worked on local copies of the database, so there is no risk of others having access to the decrypted file.
In any other case, it’s surely better to keep it encrypted all the time.