DB Encryption / Attached Database

Databases
21

not sure what you are doing Neil… .but my observations, as well as those of others is that it doesn’t work

22

Are you testing on Windows? Not sure how that could make a difference. I’ll post a sample project in a minute if I can get it done before my dinner guest arrive.

23

sample

  1. Unzip and place the db files on your desktop.
  2. Build the encrypt project if you wish to assist in encrypting/decrypting
  3. Run the attach test project.
  4. in the first box type ‘test1’, press the fisrt OK button. Now connected to test1.db (encrypted).
  5. in the first textbox in the second row type: ATTATCH DATABASE 'C:\\Users\\<User>\\Desktop\\test2.db' as test2 key 'test2'
  6. Press the button directly below it.
  7. You now have two encrypted databases connected both having different encryption keys.
  8. press ok again and the error verifies that test2 is already attached.
24

ok… not sure what that was but here is a straight forward… non-interactive test that shows it will fail.
I created two databases (encrypted the first one, and not the second, as for this exercise the state of the 2nd one isn’t the issue)

 Dim db1 As SQLiteDatabase
 Dim f As FolderItem
 Dim sql As String
 Dim abort As Boolean
 Dim t As TextOutputStream
 f=SpecialFolder.Desktop.child("attach_test.trace_log")
 If f.exists Then f.delete
 t=TextOutputStream.Create(f)
 //
 // Make MAIN Database Connection
 // this database is Encrypted with "test1" as its key
 //
 t.writeline "Attempting to CONNECT to TEST1.DB"
 db1 = New SQLiteDatabase
 db1.DatabaseFile = GetFolderItem("").Child("test1.db")
 db1.EncryptionKey = "test1"
 If db1.Connect Then
  t.writeline "Connection to TEST1.DB : Succeeded"
  //
  // Get the 2nd database Path (NOT ENCRYPTED)
  //
  f=GetFolderItem("").Child("test2.db")
  t.WriteLine "Attempting to attach TEST.DB2"
  t.WriteLine "Path : "+f.NativePath
  If f=Nil Then 
   t.write "NIL Path..."
   abort=True
  Elseif f.exists=False Then 
   t.write "file not found"
   abort=True
  Else
   //
   SQL="ATTACH DATABASE '"+f.NativePath+"' AS test2"
   t.WriteLine "SQL : "+SQL
   db1.SQLExecute(SQL) 
   If db1.error=False Then 
    t.writeline "Attached TEST2.DB to TEST1.DB"
   Else
    t.writeline "Attachment to TEST1 DB FAILED : "+ db1.ErrorMessage
    abort=True
   End If
  End If
 Else
  t.writeline "Connection to TEST1.DB FAILED : "+ db1.ErrorMessage
  abort=True
 End If
 If abort Then t.writeline "program aborted."
 //
 //
 //
 t.close

here is the logged results

Attempting to CONNECT to TEST1.DB
Connection to TEST1.DB : Succeeded
Attempting to attach TEST.DB2
Path : /Users/daveS/Downloads/attach test/Test2.db
SQL : ATTACH DATABASE '/Users/daveS/Downloads/attach test/Test2.db' AS test2
Attachment to TEST1 DB FAILED : file is encrypted or is not a database
program aborted.

If I remove the encryption from test1.db and run it again
the ONLY program change was to then comment out

db1.EncryptionKey = “test1”

Attempting to CONNECT to TEST1.DB
Connection to TEST1.DB : Succeeded
Attempting to attach TEST.DB2
Path : /Users/daveS/Downloads/attach test/Test2.db
SQL : ATTACH DATABASE '/Users/daveS/Downloads/attach test/Test2.db' AS test2
Attached TEST2.DB to TEST1.DB

So… by this observation and again backed up by data from other users, and the internet… you cannot attach to an existing encrypted database…

BUT if someone can show me a flaw in that test program, I’d be all over it :slight_smile:

25

I answered this earlier, but apparently got lost in the ensuing flurry.
Change this line:

SQL="ATTACH DATABASE '"+f.NativePath+"' AS test2"
to

SQL="ATTACH DATABASE '"+f.NativePath+"' AS test2 KEY ''"
In order to ATTACH to an encrypted database, you MUST supply a key if it does not match the “main” database key, even if that key is blank (non-encrypted).

26

BUT if someone can show me a flaw in that test program, I’d be all over it :)[quote=344031:@John A Knight, Jr]I answered this earlier, but apparently got lost in the ensuing flurry.
Change this line:

SQL="ATTACH DATABASE '"+f.NativePath+"' AS test2"
to

SQL="ATTACH DATABASE '"+f.NativePath+"' AS test2 KEY ''"
In order to ATTACH to an encrypted database, you MUST supply a key if it does not match the “main” database key, even if that key is blank (non-encrypted).[/quote]

AND SOMEONE DID!

This has sucessfully been incorporated into Tadpole!
The next release of Tadpole will have all the controls necessary to add encryption on the fly, remove it, change passwords, and allow attachment of encrypted or non-encrypted databases to a master database that is in either state, all with unique passwords if required… Thank you again, Mr. Knight!

27

Sorry. My guests just left. So I’m back. Glad you got it figured out.

28

I have all the “logic” in tadpole to support your suggestions from the other day (except #1 as we discussed) … The only thing left is to change the navigator so it can visually indicate if a database is encrypted or not. There are menu items under “DATABASE” and in the right click contextual menu already for “ENCRYPT”,“DECRYPT” and “CHANGE PASSWORD”