DB Encryption / Attached Database

Databases
1

the syntax for encrypting a database with Xojo is

DB.Encrypt "mysecretcode"

and to remove the Encryption

DB.Encrypt ""

But what if I have one or more ATTACHED databases and I wish to perform either operation on a SPECIFIC schema???

2

Would it work to detach the databases, do the encryption stuff and then reattach?

3

Ok… either I am missing something huge, or Xojo has some missing required important database functions

  • When dealing with a SINGLE database, encrypting/decrypting is not a problem
  • Attaching an ENCRYPTED database to a UNENCRYPTED database is not a problem
  • Attaching an ENCRYPTED or UNENCRYPTED database to a ENCRYPTED database IS A PROBLEM, as it indicates that the destination (MAIN) schema is encrypted… yet there is no way to pass the password for the MAIN in the attach command. You CAN pass a password for the database you wish to attach, but not for the one you want to attach TO.
  • Attempting to change the encryption on an ATTACHED database requires it be detached, a new temp connection made in order to alter the encryption, and then that temp connection closed, and the database reattached to the main database again

bullets #1 and #2 are not problems
bullet #4 can be worked around, although it is messy

But I can find NO solution for #3, except to REMOVE the encryption from MAIN, attach the database, and REPLACE the encryption again… that has security risks involved… The app should never remove or alter the encryption state except at the direct request of the user.

4

If you’re using SQL to attach the DB this isn’t a Xojo issue
ATTACH is a built in of SQLite

http://sqlite.org/lang_attach.html

There’s no provision for a password in the ATTACH command

5

Wasn’t placing “blame”… was just hoping there was something I’d missing.

The Xojo AttachDatabase command does have a password, but it is the password of the database to be attached, not the password of the database to attach to. So I am just putting a msgbox in the app that tells the user they cannot make attachments to a encrypted database… Although it seems you can encrypt MAIN after you have made attachments…

6

It IS possible to change the encryption key of an attached database… you just have to do it differently than expected.

execute this SQL Statement

PRAGMA <schema>.rekey='newkey';

to remove the encryption

PRAGMA <schema>.rekey='';
7

For #3 theres no means to provide the encryption key of the encrypted database you’re attaching TO
And thats not just our API
I looked at the SQLite code and it also doesn’t have a means to provide one that I can find

8

This seems to “infer” it can be done, but not to clear on how
https://www.sqlite.org/see/doc/trunk/www/readme.wiki

9

Right
Hence why the AttachDatabase method has an optional password
http://documentation.xojo.com/index.php/SQLiteDatabase.AttachDatabase

10

Not sure what you mean by “Right”… Right what?

The optional password in Xojo AttachDatabase function is the password for the database TO BE ATTACHED, not for the database to be attached to…
If you were attaching TEMP to MAIN, this password would be for TEMP if TEMP were encrypted… what is missing is the required password for MAIN in this transaction…

This seems to be the same as the SQLite doc I posted above… the confusing sentence was

which infers that MAIN can be encrypted… but observation shows that not to be true

11

Right as in - “yes you can provide a key for the db you’re attaching if its not the same as the password for the database you already have open”

There is no facility in Xojo or SQLITE to provide the password for the db to be attached TO as part of the attach command (again this is a sqlite command not a xojo creation)

You already had to have the database open which assumes you already provided the key at some prior point

12

This is a true statement… however what you seem to be missing (or I am)

this FAILS

If DB.AttachDatabase(path,schema,Cypher_Key) Then

with “Error file is encrypted or is not a database”

but ONLY if “DB” is encrypted… it DB is not encrypted then all is good…

13

you can try the raw sql command of the form

ATTACH DATABASE 'file2.db' AS two KEY X'78797a7a79';

as well since that is more or less internally all we do

path quoting may be an issue

That said this doesn’t appear to be a Xojo bug

14

I have tried it with the Xojo function…
I have tried it with pure SQL (basically what you posted above)
both having the same results…

The only way I can see is using the SQLmethod with a “REKEY=’’” before and a “REKEY=‘key’” after…
which I think it what I will do, but the app will ask the user for permission to do that since I consider it a security risk

15

And maybe file a report with the SQLite folks ?
It does seem that if you have already opened a db which required you to provide your password that you should not need it again to attach a db - but you would need the password for the db being attached

16

[quote=344007:@Norman Palardy]And maybe file a report with the SQLite folks ?
It does seem that if you have already opened a db which required you to provide your password that you should not need it again to attach a db - but you would need the password for the db being attached[/quote]
that is what I thought as well… but it seems not to be the case…
seems Emile mentioned this exact situation on this forum a year or two ago…

17

This seems to be the relevant part of the documentation:

If your main database is not encrypted, you only need passwords to attach encrypted databases.
However, if your main database is encrypted, ATTACH assumes the same key.
In order to ATTACH a non-encrypted database, you must specify an empty string, KEY '' as part of the ATTACH statement.

18

I just tested it without a problem.

  1. create test1.db, encrypt with key ‘test1’
  2. create test2.db, encrypt with key ‘test2’
  3. connect to test1

db1.enctryptionkey = 'test1' if db1.connect then ...

  1. attach test2.db

db1.sqlexecute("ATTATCH DATABASE 'C:\\Users\\User\\Desktop\\test 2.db' as test2 key 'test2'")
no errors

db1.sqlexecute("ATTATCH DATABASE 'C:\\Users\\User\\Desktop\\test 2.db' as test2 key 'test2'")
error: database test2 is already in use.

I’ve tried all combinations of encrypted/unencrypted with no errors attaching.

19

This also worked fine.

PRAGMA main.rekey='testone' PRAGMA test2.rekey='testtwo'

20

I can send you the sample project if you would like.