For some reason using the SQLite encrypt does not encrypt the database, what am I missing?
I am connecting to an existing database and then doing the encrypt right afterward, I get no errors, but the DB is never encrypted I can open it without a key in Xojo ( unencrypted ) and it opens fine in SB Browser for SQLite ( with no encryption )
Just using the Wiki code below doesn’t do it…
Dim db As New SQLiteDatabase
db.DatabaseFile = GetFolderItem("db.sqlite")
If db.Connect Then
db.Encrypt("mapass")
Else
//handle error here
End If
You have to set the encryption key BEFORE you connect or create a database. From the Language Reference:
“Encrypting a Database
To encrypt a new database, specify a value for the EncryptionKey property before you call CreateDatabaseFile or before you call Connect. This creates a new encrypted database:”
[quote=341928:@Kimball Larsen]You have to set the encryption key BEFORE you connect or create a database. From the Language Reference:
“Encrypting a Database
To encrypt a new database, specify a value for the EncryptionKey property before you call CreateDatabaseFile or before you call Connect. This creates a new encrypted database:”[/quote]
I am thrown off by the Wiki because it says this about the encryption.
To encrypt an existing database, use the Encrypt method. AES-128 encryption is used.
[quote=341928:@Kimball Larsen]You have to set the encryption key BEFORE you connect or create a database. From the Language Reference:
“Encrypting a Database
To encrypt a new database, specify a value for the EncryptionKey property before you call CreateDatabaseFile or before you call Connect. This creates a new encrypted database:”[/quote]
Doing that throws an error no database connection. Again it’s an existing database I am trying to encrypt. Looking at the Wiki it states use db.encrypt to encrypt an existing db unless I am reading things totally wrong.
Using the wiki code, are you actually checking for the error condition of perhaps not actually connecting to the database?
i.e., put a breakpoint in the else statement where it says //handle error here and see if you are actually connecting to the database .
Dim db As New SQLiteDatabase
db.DatabaseFile = GetFolderItem("db.sqlite")
If db.Connect Then
db.Encrypt("mapass")
Else
dim i as integer = 0 //put a breakpoint on this line. See if you have any errors in the db object in the debugger.
End If
I suspect your original call to db.connect is failing, but you are ignoring the error because there is nothing in the else clause of your if db.connect.
Kimball thanks so much for the suggestions ( a fellow Idahoan I see ). I guess I should show my code, I have msg boxes for each event. I am getting the connected and encrypted msg boxes without error. When I tried the zsource.EncryptionKey = “mypass” before the connect it threw an error so I removed it from the code below.
The database has 41k records so it should take a little time I think to encrypt even on a 32gig 8 core SSD machine. It instantly gets to the encrypted msg box.
Dim dbname as string
Dim dbFile As FolderItem
Dim zsource As New SQLiteDatabase
dbname = namefield.text
dbFile =app.executablefile.parent.child(dbname)
zsource.DatabaseFile = dbFile
If zsource.Connect Then
msgBox ("Connected!")
zsource.Encrypt("mypass")
msgBox ("Should be encrypted")
else
msgBox ("Database connection error!")
end if
Wait a minute! There’s someone else in Idaho that has heard of Xojo? If you are close to Meridian let’s get lunch sometime…
As for your issue: The number of records (41k is not really all that many) has less of an impact than the size of the individual records. If each record is only a few bytes, then it’ll still encrypt / decrypt very fast. If each record is several MB worth of image blob, then yeah, it may take a few seconds to encrypt.
However, that does not really get to the root of your issue: As I understand, you have an SQLite database that is currently unencrypted. Your app wants to connect to this database, and convert it to an encrypted database. Correct?
A few questions come to mind immediately (forgive me if these seem obvious, but I find it best to start with the obvious stuff):
Are you 100% certain that this is a valid SQLite database?
Is this external db file in a version of SQLite that is compatible with Xojo? (i.e., relatively recent, not using some funky other encryption format like SQLCipher?
Can you connect to the database file and perform queries successfully without messing with any encryption stuff at all?
Kimball … #2 should not be an issue, if the Database is not currently encrypted… It might be if he were attempted to DECRYPT an DB from another source… Xojo uses 128Bit AES I believe. My Tadpole app can read databases encrypted with this method, but not if encrypted with other methods.
In that case… Check #1 and #3 as you suggested…
But if it passes the CONNECT, then #1 is not the issue,
[quote=341946:@Kimball Larsen]Wait a minute! There’s someone else in Idaho that has heard of Xojo? If you are close to Meridian let’s get lunch sometime…
As for your issue: The number of records (41k is not really all that many) has less of an impact than the size of the individual records. If each record is only a few bytes, then it’ll still encrypt / decrypt very fast. If each record is several MB worth of image blob, then yeah, it may take a few seconds to encrypt.
However, that does not really get to the root of your issue: As I understand, you have an SQLite database that is currently unencrypted. Your app wants to connect to this database, and convert it to an encrypted database. Correct?
A few questions come to mind immediately (forgive me if these seem obvious, but I find it best to start with the obvious stuff):
Are you 100% certain that this is a valid SQLite database?
Is this external db file in a version of SQLite that is compatible with Xojo? (i.e., relatively recent, not using some funky other encryption format like SQLCipher?
Can you connect to the database file and perform queries successfully without messing with any encryption stuff at all?[/quote]
Sandpoint, Idaho way north of you ie 60 miles south of the Candian border.
yes I created it in the open source SQL Broswer app
Yes its been working, fine with Xojo I can browser query it etc without any issues.
Yes #2 , I get no errors when I connect and query it at all. Everything works fine, just this darn encrypt not wanting to play nice.
So I take it by all appearance what I am attempting to do, should work?
SQL Browser has an encrypt function but from what I read each program implements encrypt differently and may not work with Xojo so I decided not to encrypt it outside of Xojo.
Xojo uses the built-in encryption mechanism that is provided by the author of SQLite. This method is pretty expensive to license, and is incompatible with any of the other sqlite encryption schemes out there (like SQLCipher - one of the most popular open-source ones). Tadpole can open Xojo-encrypted databases because it was written in Xojo, so has access to the built in encryption provided by SQLite. Other tools that claim to support database encryption for SQLite that were not written in Xojo or in some other tool that licenses the encryption libraries from SQLite directly will not be able to open encrypted Xojo databases.
This is true, and that license costs around $2,000.00
However that is a “standard” 128bit AES encryption, and is NOT unique to XOJO…
I have used an “Xojo Encrypted” SQLite database with an iOS app… (Written in SWIFT) and it worked fine.
Yes, SQLBrowser uses SQlCipher, which is not compatible with the native encryption that Xojo uses.
The only other thing that comes to mind is if there are large differences between the file version formats of the SQLite database created by SQLBrowser vs. the engine version bundled with your version of Xojo. If they are not very close in version, you may run into problems.
There are several options for creating an encrypted database in SQLite using tools that were written in Xojo so they use the right flavor of encryption. Tadpole is one, SQLiteManager is another. I’ve never had success moving between encrypted / decrypted databases using SQLBrowser and Xojo.
Huh. I tried for about a week several years ago to get any flavor of php to open / play with Xojo encrypted sqlite files. NOTHING worked. There are a bunch of old posts on this forum where Norman and I tried to figure out exactly which encryption scheme the dang thing was using, but nothing worked.
Out of curiosity, what encryption library are you using in Swift? I’m currently working on several different Swift apps that will share data with my Xojo apps, and it would really be handy if the databases were portable. As it is now, I’ve opted for encryption using one of the readily-available SQLCipher-based SQLite libraries available in Swift. That would not open any existing Xojo-encrypted databases for me, but I can create them from scratch and use SQLCipher to encrypt them.
Here’s some really boring reading about the history of encryption support in SQLite + Xojo. Some of the details in those discussions may have changed, as they are pretty old.
Xojo currently uses SQLite 3.14 (unless that was upped in Xojo2017).
And it has no problems at all going back to at least Sqlite 3.07 (circa 2006 I think)… I haven’t tried anything older.
heck macOS El Capitan only bundles 3.08 for some reason,
@Tony Chamberlain: One other thought comes to mind: Do you have read/write permissions on the database file itself? Perhaps you are able to connect to and read from the file, but are unable to write anything to it? Without any encryption, can you perform updates to any of the rows and have those changes persist across launches of your app?