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???
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???
Would it work to detach the databases, do the encryption stuff and then reattach?
Ok… either I am missing something huge, or Xojo has some missing required important database functions
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.
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
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…
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='';
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
This seems to “infer” it can be done, but not to clear on how
https://www.sqlite.org/see/doc/trunk/www/readme.wiki
Right
Hence why the AttachDatabase method has an optional password
http://documentation.xojo.com/index.php/SQLiteDatabase.AttachDatabase
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
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
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…
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
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
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=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…
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.
I just tested it without a problem.
db1.enctryptionkey = 'test1'
if db1.connect then ...
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.
This also worked fine.
PRAGMA main.rekey='testone'
PRAGMA test2.rekey='testtwo'
I can send you the sample project if you would like.