database encryption

Databases
1

Hi All.

I am trying my hand at database encryption, and this is what I have so far based on reading the F1 docs.

[code]Dim dbfile as FolderItem
dbfile = GetOpenFolderItem("")

if dbfile <> NIL then
DB = New SQLiteDatabase
DB.DatabaseFile = dbfile
DB.Encrypt (“password changed to protect the stupid”)

if DB.Connect then

'unlock key is : password changed to protect the stupid
DB.EncryptionKey = unlockKey
DB.Decrypt 

AddPassword.Enabled = True
ChangePassword.Enabled = True
DeletePassword.Enabled = True

LoadPasswords  'this is so that I can load my database.  this will be a method, but I haven't done it yet

Self.Title = "Passwords (" + dbfile.Name + ")"

end if
end if
[/code]

If I look at the database (the .sqlite file) with a simple text viewer, I can read it, but I am believing that this should not be so.

Thinking about it, I wondered if first I have to check when the database is opened if it IS encrypted.

Any help is appreciated.

Regards

2

You shouldn’t be decrypting it. By setting the key, it will access the encrypted data directly.

3

Hi Kem

Thanks for the reply.

That didn’t help. Can still see the clear text in the file if I look.
Should I have had the encryption built first before creating my database?

Regards

4

just apply the key and it will encrypt

5

Please post your new code.

6

Here you go guys.

I think I might be applying the key in the wrong place?

[code]Dim dbfile as FolderItem
dbfile = GetOpenFolderItem("")

if dbfile <> NIL then
DB = New SQLiteDatabase
DB.DatabaseFile = dbfile
DB.Encrypt (“password changed to protect the stupid”)

if DB.Connect then

DB.EncryptionKey = unlockKey


AddPassword.Enabled = True
ChangePassword.Enabled = True
DeletePassword.Enabled = True

LoadPasswords

Self.Title = "Passwords (" + dbfile.Name + ")"

end if
end if[/code]

7

If DBFile exists, won’t it already be encrypted?

In any case, set it before the call to Encrypt.

8

DB.Encrypt should be a one-time call when you first create the database. From there on out, simply apply the key.

9

Or just never call Encrypt. Set the key, as Dave said, and be done with it. Do this before the Connect/Create and it’ll just work. Encrypt is for changing the key.

10

A bit of clarification, Michael, about the Encrypt method. Its only purpose is to encrypt an existing database that currently isn’t encrypted. Same for the Decrypt method for decrypting an encrypted database. Neither call is necessary for encrypting a new database nor for connecting to one. If your app is creating a new database, supplying the EncryptionKey before calling CreateDatabaseFile (or CreateDatabase in 2019 v2 and later) will automatically create the database encrypted. Additionally, as long as you supply the EncryptionKey before trying to Connect, there is nothing special needed to connect to and use an encrypted database.

The only time the app should use Encrypt and Decrypt is if your app needs to change the EncryptionKey, though I can’t imagine a case where that would be necessary.

11

Thanks for the responses.

My database already exists, so if I put the Encryptionkey in the app open event, that should encrypt it, correct?

I’m going to go through what you folks have given me to chew on, and see what I can come up with.
Sorry for the slow response. my beloved case of the flu has had me bed ridden for that last several hours. I’m up cuz I needed water.

Regards

12

Hi All.

Since my database already existed, I was going by what I read on this doc from the web docs…

https://documentation.xojo.com/api/databases/sqlitedatabase.html#sqlitedatabase-encrypt

Regards

13

It’s a little bit more complicated than “does the database already exist?” The good news is not by much.

if database exists then
  if database connects without password then
    database set encryption key
    database encrypt

  else
    database set encryption key
    if database connect then
      // connected to encrypted database, do nothing else

    else
      // not a database or the encryption key is wrong

    end

else
  // Database doesn't exist here
  database set encryption key
  database create

end
14

Hi Tim.

My stupidity is showing.

I went and did what you said in the pseudo-code, and this is what happens.

[quote]First. made a copy of my database (DUH. I’m stupid, but not THAT stupid)
Verified with Textedit (Mac User here) and I could read all of the the text, in clear text.
Ran my program with your steps (as I think they should be from what I read.
Got my data filling in my listbox. Yay
Closed the debug.
Checked the file.
Now looks like exploded lawn furniture. Again, yay.[/quote]

Run the program to load the file, open the database… nothing.

So, did the following:

Ran a debug with breaks…

[quote]Open
Select my file with open dialog
dbfile is not nil
Last Error = 0
Get DB.Connect

And here is where it is interesting,

It jumps right down into the else to get the encryption key.
Added DB.Decrypt and still the box never fills in.[[/quote]

Here is the code as it stands now.

[code]Dim dbfile as FolderItem
dbfile = GetOpenFolderItem("")

if dbfile <> NIL then
DB = New SQLiteDatabase
DB.DatabaseFile = dbfile

if DB.Connect then
DB.EncryptionKey = “howdyDoody”
DB.Encrypt

AddPassword.Enabled = True
ChangePassword.Enabled = True
DeletePassword.Enabled = True

LoadPasswords  'this is so that I can load my database.  this will be a method, but I haven't done it yet

Self.Title = "Passwords (" + dbfile.Name + ")"

else

DB.EncryptionKey = "howdyDoody"
DB.Decrypt

end if
end if[/code]

Update: I don’t believe it, but this code seems to work!

[code]Dim dbfile as FolderItem
dbfile = GetOpenFolderItem("")

if dbfile <> NIL then
DB = New SQLiteDatabase
DB.DatabaseFile = dbfile

DB.EncryptionKey = “howdyDoody”
DB.Decrypt

if DB.Connect then

AddPassword.Enabled = True
ChangePassword.Enabled = True
DeletePassword.Enabled = True

LoadPasswords  'this is so that I can load my database.  this will be a method, but I haven't done it yet

Self.Title = "Passwords (" + dbfile.Name + ")"

else

DB.EncryptionKey = "howdyDoody"
DB.Decrypt

end if
end if[/code]

If anyone sees any possible catastrophies waiting to occur, would be appreciated.

Regards

15

Stop trying to decrypt the database!

[code]Public Function OpenDatabase() as SQLiteDatabase
Const EncryptionKey = “howdyDoody”

Dim File As FolderItem = GetOpenFolderItem("")
If File = Nil Then
Return Nil
End If

Dim Database As New SQLiteDatabase
Database.DatabaseFile = File

If Database.Connect Then
// The database is not encrypted, so encrypt it.
Database.Encrypt(EncryptionKey)
Return Database
End If

// The database did not connect, so it might be encrypted
Database.EncryptionKey = EncryptionKey
If Database.Connect Then
// That was it
Return Database
End If

// Since the database did not connect with the encryption key supplied, there’s no way to continue
Return Nil
End Function[/code]

Use this method like Self.DB = Self.OpenDatabase() in your App.Open event. Check Self.DB for Nil afterwards. If it returns Nil, either the user cancelled, selected a file that isn’t a sqlite database, or the database is encrypted with a different key. You decide what to do if it is Nil.

16

Hi Thom.

Thanks for your help… actually everyone for all their help.

My problem is … mental gymnastics.

I am sitting here saying to myself, how does the program know the database is encrypted. I can give it ANY key with the EncryptionKey command, so how /where does the database say “Yup right key?”

Or if the database is NOT encrypted and I give it a key, that I want it used to encrypt the database? Maybe I just want the key in case the USER wants to encrypt the database.

I know I am probably being pretty dense, but I want to understand why what I am doing is doing what it is supposed to be doing. Without that, I don’t consider myself a programmer. I am a cut and paster…

Once again, thanks to everyone for their help with my folly.

Regards

17

It doesn’t. It can’t. It can only attempt to connect to the database and either succeed (it was encrypted with this key) or fail (it either wasn’t encrypted, or this is the wrong key).

There are only 2 people who can know if the database is encrypted.

  • You - hard code the key in your code
  • The User - ask for the key

There are 2 ways to encrypt a database

  • Set the key and then Create the database
  • Connect to an un-encrypted database and call Encrypt

The only reason to call Decrypt is if you want to revert the database to plain text that is viewable in a text editor.

If you don’t know whether the database is encrypted, you can attempt to connect to it without a key. If that succeeds, it wasn’t encrypted. If it fails, then it is encrypted with some key (or it isn’t actually a database file).

18

For completeness, if you want to change the encryption key you’ll also need to decrypt the db using the existing key first then encrypt it with the new one.

19

You don’t need to Decrypt the database to change the encryption key. Connect with the original key and call Encrypt with the new key. You can also call Encrypt with an empty key, which is equivalent to calling Decrypt.