Protecting SQLite Data

[ChrisW67]

Works fine for me. You are using the Sqlcipher version of sqlite3, aren't you?

// First, a freshly built sqlcipher
chrisw@newton /tmp/sqlcipher $ ./sqlite3 plain.db
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table data(a integer);
sqlite> insert into data values (1);
sqlite> insert into data values (2);
sqlite> insert into data values (3);
sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'secret';
sqlite> create table encrypted.data as select * from data;
sqlite> select * from encrypted.data;
1
2
3
sqlite> .q
 
// See that the encrypted file is not identified as Sqlite
chrisw@newton /tmp/sqlcipher $ file plain.db encrypted.db 
plain.db:     SQLite 3.x database
encrypted.db: data
 
// And that the data is there
chrisw@newton /tmp/sqlcipher $ ./sqlite3 encrypted.db
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma key = "secret";
sqlite> .tables
data
sqlite> select * from data;
1
2
3
sqlite> .q
 
// Now with the system, non-encrypting sqlite3
chrisw@newton /tmp/sqlcipher $ /usr/bin/sqlite3 encrypted.db 
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
Error: file is encrypted or is not a database
sqlite> .q

// First, a freshly built sqlcipher
chrisw@newton /tmp/sqlcipher $ ./sqlite3 plain.db
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table data(a integer);
sqlite> insert into data values (1);
sqlite> insert into data values (2);
sqlite> insert into data values (3);
sqlite> ATTACH DATABASE 'encrypted.db' AS encrypted KEY 'secret';
sqlite> create table encrypted.data as select * from data;
sqlite> select * from encrypted.data;
1
2
3
sqlite> .q

// See that the encrypted file is not identified as Sqlite
chrisw@newton /tmp/sqlcipher $ file plain.db encrypted.db 
plain.db:     SQLite 3.x database
encrypted.db: data

// And that the data is there
chrisw@newton /tmp/sqlcipher $ ./sqlite3 encrypted.db
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma key = "secret";
sqlite> .tables
data
sqlite> select * from data;
1
2
3
sqlite> .q

// Now with the system, non-encrypting sqlite3
chrisw@newton /tmp/sqlcipher $ /usr/bin/sqlite3 encrypted.db 
SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
Error: file is encrypted or is not a database
sqlite> .q

To copy to clipboard, switch view to plain text mode 

[zim]

This is my code, and I am not getting any errors.

Somehow the db is unencrypted.

#include <QtSql/QSqlError>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlRecord>
#include <QVariant>
#include <QDebug>
 
shonaDB::shonaDB()
{
    db = new  QSqlDatabase(QSqlDatabase::addDatabase("QSQLCIPHER","newDB"));
    dbCopy = new QSqlDatabase(QSqlDatabase::addDatabase("QSQLCIPHER","oldDB"));
    dbCopy->setDatabaseName("shonaOld.db");
    db->setDatabaseName("shonaNew.db");
 
    if(!db->open())
    {
        qDebug() << "db open fail";
    }
 
    if(!dbCopy->open())
    {
        qDebug() << "dbCopy open fail";
    }
 
 
    QSqlQuery qry(QSqlDatabase::database("newDB"));
 
    qry.prepare("PRAGMA key = 'xyz'");
 
    if(!qry.exec())
    {
        qDebug() << "key fail";
    }
 
}
 
 
 
 
void shonaDB::copyDatabase()
{
 
       QSqlQuery qry(QSqlDatabase::database("newDB"));
 
        qry.prepare("ATTACH DATABASE 'newDB' AS encrypted KEY 'xyz' ");
 
        if(!qry.exec())
        {
            qDebug() << dbCopy->lastError().text();
       }else{
           qDebug()<< "Success ATTACH";
       }
 
       qry.prepare("CREATE TABLE encrypted.dictionary (txtVar text, txtVarTwo text, txtVarThree text);");
 
       if(!qry.exec())
       {
           qDebug() << dbCopy->lastError().text();
      }else{
          qDebug()<< "Success ct";
      }
 
 
       qry.prepare("INSERT INTO encrypted.dictionary SELECT * FROM dictionary;");
 
       if(!qry.exec())
       {
           qDebug() << dbCopy->lastError().text();
      }else{
          qDebug()<< "Success transfer";
      }
 
 
}

#include <QtSql/QSqlError>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlRecord>
#include <QVariant>
#include <QDebug>

shonaDB::shonaDB()
{
    db = new  QSqlDatabase(QSqlDatabase::addDatabase("QSQLCIPHER","newDB"));
    dbCopy = new QSqlDatabase(QSqlDatabase::addDatabase("QSQLCIPHER","oldDB"));
    dbCopy->setDatabaseName("shonaOld.db");
    db->setDatabaseName("shonaNew.db");

    if(!db->open())
    {
        qDebug() << "db open fail";
    }

    if(!dbCopy->open())
    {
        qDebug() << "dbCopy open fail";
    }


    QSqlQuery qry(QSqlDatabase::database("newDB"));

    qry.prepare("PRAGMA key = 'xyz'");

    if(!qry.exec())
    {
        qDebug() << "key fail";
    }

}




void shonaDB::copyDatabase()
{
 
       QSqlQuery qry(QSqlDatabase::database("newDB"));

        qry.prepare("ATTACH DATABASE 'newDB' AS encrypted KEY 'xyz' ");

        if(!qry.exec())
        {
            qDebug() << dbCopy->lastError().text();
       }else{
           qDebug()<< "Success ATTACH";
       }

       qry.prepare("CREATE TABLE encrypted.dictionary (txtVar text, txtVarTwo text, txtVarThree text);");

       if(!qry.exec())
       {
           qDebug() << dbCopy->lastError().text();
      }else{
          qDebug()<< "Success ct";
      }


       qry.prepare("INSERT INTO encrypted.dictionary SELECT * FROM dictionary;");

       if(!qry.exec())
       {
           qDebug() << dbCopy->lastError().text();
      }else{
          qDebug()<< "Success transfer";
      }


}

To copy to clipboard, switch view to plain text mode 

[ChrisW67]

Your copyDatabase() method should open the unencrypted database (i.e. oldDB), attach the encrypted database, and then push the table contents into the attached database. You are currently opening the newDB and attaching a second copy of the newDB. I am surprised that the create table doesn't fail.

[zim]

Hi

do you need two database objects to attach the encrypted database.

[ChrisW67]

If you are doing this in Qt code then you use a single db object and use a QSqlQuery to execute an ATTACH statement. The attachment is only good on that db instance in my experience. You need to open the unencrypted database and attach the encrypted database (your last code post doesn't do that).

Once you have the encrypted database, do you need to allow the user to repeat this in your application? If not then why not just do the process from the command line as in my earlier example.

[SixDegrees]

It might be simpler to protect the website through any of a number of server controls that require a user to enter a password before access to a site is granted. These are simple to set up and most hosting services provide the services and tools required. Your unencrypted database then lives within the security provided by restricted access to the web page that interacts with it.

This also makes it simple to grant a few priveleged users (or only yourself) access to administrator functions.

[ChrisW67]

Website? This looks like a desktop application.

[SixDegrees]

Website? This looks like a desktop application.

It wasn't clear to me; Firefox was mentioned earlier, so I thought I'd mention the server-based approach as an alternative.

[wysota]

I just read the thread and I'm still not convinced this whole approach is safe. At some point there will be a plaintext version of the password somewhere in the process memory and no executable mangling will prevent that. Dumping core at this moment will sooner or later reveal this password. To me only situations where you do not pass the plaintext password (like challenge and response solutions) are possible candidates for a good solution.

[ChrisW67]

I just read the thread and I'm still not convinced this whole approach is safe. At some point there will be a plaintext version of the password somewhere in the process memory and no executable mangling will prevent that.

This is the perpetual problem of any DRM scheme... you are giving the attacker both the encrypted data and the key. There's no way around it. All you can do is make it difficult to extract. For example, the Armadillo wrapper includes anti-dumping measures, anti-debugger measures, on-the-fly encryption of code segments, object file rearrangement and other things. Properly used the key is very hard to get at and you have to weigh the effort required to attack the protections against the value of the thing being protected.

[wysota]

The key can can be embedded in hardware in such a way that it never leaves the sillicon. Of course you can still try to copy the chip.

[ChrisW67]

We might have to wait until Qt 6.0 for the addition of the QtSilicon module

[stampede]

Somehow the db is unencrypted.

Using windows xp, got the same issue, compiled sqlcipher and could open "encrypted" databases created with sqlcipher/sqlite3.exe with any sqlite browser.
This is important how do you configure the sqlcipher, when I've followed the tutorial it wasn't working, but then I reconfigured using additional options:

configure --disable-tcl --disable-amalgamation --prefix=/c/qt/src/plugins/sqldrivers/sqlcipher CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-LC:/OpenSSL-Win -leay32"

configure --disable-tcl --disable-amalgamation --prefix=/c/qt/src/plugins/sqldrivers/sqlcipher CFLAGS="-DSQLITE_HAS_CODEC" LDFLAGS="-LC:/OpenSSL-Win -leay32"

To copy to clipboard, switch view to plain text mode 

After make I get sqlite3.exe which I can use to create really encrypted databases ( as in ChrisW67 post ).

------------------------------
edit:
just tested the sqlcipher plugin for Qt, works ok too