Qt SQLITE and username/password

chopper

There's a question in the forums about this, with an indefinite answer - does anyone involved using SQLITE and Qt (via the Qt driver) know definitively if there's a way to protect the database?

I'm specifying a username and password via the Qt API as I think I'm supposed to be using it, and SQLITE has supported username/password protection for roughly 10 years or so - ergo, I expect I'm just using it wrong.

Here's how I create a database in local storage that I would expect a SQL viewer could NOT view without the same username/password combination (but unfortunately it can...)

QString qsUsername = "test-name";
QString qsPassword = "test-password";

QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );

db.setDatabaseName( QString::fromStdString( sDestinationFile ) );
db.setUserName( qsUsername );
db.setPassword( qsPassword );

if( false == db.open() )
{
	qDebug() << __FUNCTION__ << " Error:  Could not establish destination storage: " << db.lastError().text();
	return false;
}

QSqlQuery query;

if( false == query.exec( "create table person (id integer primary key, firstname varchar(20), lastname varchar(30), age integer)" ) )
{
	qDebug() << __FUNCTION__ << " Error:  Could not establish structure of storage: " << query.lastError().text();
	return false;
}

I get the expected database, in the expected location, but anybody can view it...

Is this just something missing from the Qt SQL Driver for some reason or am I just doing it wrong?

Thanks

JonB

@chopper
I don't use SQLite so I may be completely misunderstanding. You are passing a username & password from Qt when you open the database. That has nothing to do with whether other people, not even using Qt, can or cannot access your database, that has to do with the protection on the database when it was created, or if you alter that later, no?

Oh, SQLite is creating this database? Does the creation happen at db.setDatabaseName() or db.open() time? Just wondering whether your setUserName/setPassword() need to come before if it's the setDatabaseName()?

chopper

From the way I read it, because Qt needs to abstract away the differences between databases as best it can, is that if you wish to create a database protected by a password you need to specify a username/password BEFORE you create the database (which I believe happens when you call 'open' on a database that does not exist.

chopper

@JonB Out of sheer morbid curiosity I had tried specifying the database name afterwards but it, as expected, made no difference since they are all passive settings before you call 'open.'

mrjj

@chopper
Hi as far as i know, the open source version of SQLite offer no encryption/protection.
https://www.sqlite.org/prosupport.html
see the SQLite Encryption Extension (SEE)

A common way is to use
https://github.com/sqlcipher/sqlcipher
or similar.

chopper

@mrjj Good lord, I think you're right... The references to username/password were all by language packages or systems built on top of Sqlite.

That seems like such a simple thing to do - generate AES256 key, protect key with password derived from username/password - and have whatever Sqlite uses for a disk pager encrypt/decrypt on the fly...

Well. Crap. Looks like I'll be encrypting data written to the database, but that's inefficient and screws up schemas and indexing...

Thx!