PDA

View Full Version : Encrypting an existing sqlite database in sqlcipher



thefatladysingsopera
18th October 2011, 19:16
I am using sql cipher in Qt and i want to encrypt an existing sqlite database.On the Api docs here: http://sqlcipher.net/sqlcipher-api#attach this is how to do it:


ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'secret'; -- create a new encrypted database
CREATE TABLE encrypted.t1(a,b); -- recreate the schema in the new database (you can inspect all objects using SELECT * FROM sqlite_master)
INSERT INTO encrypted.t1 SELECT * FROM t1; -- copy data from the existing tables to the new tables in the encrypted database
DETACH DATABASE encrypted;

The following two line confuse me:

CREATE TABLE encrypted.t1(a,b);

and

INSERT INTO encrypted.t1 SELECT * FROM t1;

What does (a,b) mean and why is it used in the first statement and not in the second?.

Thanks.

Added after 1 40 minutes:

Someone has suggested to me that the a,b could be table columns.I am looking into that.

ChrisW67
19th October 2011, 00:37
"(a, b)" is the columns. Since the original t1, and the encrypted.t1 have an identical schema then "select * from t1" will produce columns in exactly the same order as in the definition of encrypted.t1 so a column list is not required.

thefatladysingsopera
19th October 2011, 08:06
Hello Chris,thanks.I have the sql cipher driver working fine but i cannot create an encrypted database yet.Lets put aside the attach database method for a moment.What other method is there to make an encrypted database?.


QSqlDatabase db = QSqlDatabase::addDatabase("QSQLCIPHER");
db.setDatabaseName(/*add the database path here*/);
if (!db.open())
qFatal("Could not access database.");
QSqlQuery query(db);
query.exec("PRAGMA key = 'secretkey';");

In this example http://www.qtcentre.org/wiki/index.php?title=Building_QSQLITE_driver_with_AES-256_encryption_support there is no mention of creating an encrypted database i have noted.

ChrisW67
19th October 2011, 23:32
That looks good to me. What is the problem? The new database will be zero bytes until you actually define some tables in it.

thefatladysingsopera
21st October 2011, 11:14
I will try keying the database using the command shell and creating some tables and i will report back what i get.The problem i had is that the database wasn't being encrypted.

thefatladysingsopera
21st October 2011, 19:47
I have re-evaluated my applications needs and have resolved to put aside the sqlite encryption and to obscure only parts that are of paramount importance such as start of trial dates etc.
The driver sql cipher i believe is still very good and i shall resume from where i left off in the future but as of now i have another solution which works just fine.

@Chris tried keying the database from the sqlite windows shell but the database is not being encrypted.I have decided to try some other day since i have lots of class work to do.

Thanks.

7021