Protecting SQLite Data

[zim]

I created an application that queries an SQLite database. The tables in the database have taken months to fill.

Is there a way to protect my data from thieves. I do not want them to just open up the database and taking my work.

To be precise can one put a password on an SQLite database.

[Lykurg]

Have you seen my wiki article? [WIKI]Building QSQLITE driver with AES-256 encryption support[/WIKI]

[zim]

Thank you.

I was a little reluctant to follow the tutorial because I have Windows 7 64bit. I also spent weeks trying to build MySQL driver on Win 64 7.

Anyway this tutorial was precise.

Thank you

[squidge]

Do remember that the database is only secure whilst you keep the passphrase/key confidential. If you expose the key in plain text in your application for example it will be very easy for someone to find with a simple hex editor. Your encryption is then worthless, regardless of what it is.

[zim]

Thanx, So if I was to place the key in the source code i.e. bdapp.cpp that works?

I also wanted to know if there is a way to encrypt an existing database?

Thanx.

[nish]

Thanx, So if I was to place the key in the source code i.e. bdapp.cpp that works?

Thats exactly does NOT works!!

apply a little protection in your password. A very simple example :-
suppose your password is "AB".
instead of

database.open(filename, "AB");

database.open(filename, "AB");

To copy to clipboard, switch view to plain text mode 

use

database.open(filename, QString("BC").replace('B',  'B'-1 ).replace('C', 'C'-1 ));

database.open(filename, QString("BC").replace('B',  'B'-1 ).replace('C', 'C'-1 ));

To copy to clipboard, switch view to plain text mode 

ofcourse this is a stupid example in itself but anyone who opens your binary in hex editor will only see "BC" as password.
But then this also does not prevent anyone who is going to debug your binary. But at least you get the idea.

I also wanted to know if there is a way to encrypt an existing database?

i dont know a direct way, but i would prefer just to open the old db and export it to a new encrypted one.

[ChrisW67]

Encrypt an existing database... have a look here: http://sqlcipher.net/documentation/api#atach

[Lykurg]

Do remember that the database is only secure whilst you keep the passphrase/key confidential. If you expose the key in plain text in your application for example it will be very easy for someone to find with a simple hex editor.

That's a topic what is on my mind since I wrote the article. But since I am not really an encryption specialist and my application where I use encrypted databases is not a high secure one, I am currently satisfied with:

• Store an ascii string in the source code
• Do a caesar cipher
• Do some letter swaps
• Do some position swaps

That's not secure after all, I know, but it for my use right now it is ok.


If any of you is more into encryption stuff, it would be nice you if you would extend our wiki with a short article on how to store a password along an application in a safe way.

[nish]

That's a topic what is on my mind since I wrote the article. But since I am not really an encryption specialist and my application where I use encrypted databases is not a high secure one, I am currently satisfied with:

• Store an ascii string in the source code
• Do a caesar cipher
• Do some letter swaps
• Do some position swaps

That's not secure after all, I know, but it for my use right now it is ok.


If any of you is more into encryption stuff, it would be nice you if you would extend our wiki with a short article on how to store a password along an application in a safe way.

no matter how much a mathematic guru anyone is, there is no full proof way to store a password with your application.
After any type of encryption of your key, You HAVE to decrypt it in your code and pass it to the sqlite function! and thats where your decrypted key can
be easily seen in the debugger. The cracker doesn't need to go through you super duper decryption function in ASM to see it.

[squidge]

For the most basic of encryption, the only thing you need to do is to protect it against people "fishing" with a hex editor. For this, even if you just XOR each character of the password with a fixed number, it will satisfy the requirement (so Lykurg's method is more than enough here)

Protecting against someone armed with a debugger is a complete story on itself. The easiest way to do this is to static link the SQLite library with your application and compress/encrypt the executable with an application such as Armadillo, PEcrypt, ASProtect and such like. The static linking removes the ability for anyone to compile there own (possibly rogue) version of SQLite and attach it to your application. This is permissable in any country which acknowledges software placed into the public domain (as is SQLite). Typically, this will make it too much effort for someone to want to hack you application to find the password (unless they are really determined, in which case, there's nothing you can do).

Of course, you still need a reasonable password. Anyone can run your database through a dictionary attack. Ensure you use a long password consisting of letters, numbers and special characters.

[zim]

Thank you everyone!

I followed through the SQLCIPHER tutorial on their website. Code ran, no errors.

created a new db, set the key, encrypted it, attached it, copied my existing sql tables to it.

I then opened the newly encrypted db with a regular firefox extesion. I was able to browse all the data on the encrypted db. I even tried using different keys on the same db, worked.

It seems like the QSQLCIPHER is not encrypting the db, since the db can be viewed without unencrypting it.

[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.