Working with crypted SQLite database!

[Drazen D. Babic]

Hi all

if wondered, Jam CAN work with encryption for the SQLIte database.

For example, if we install this with pip:

pip install sqlcipher3-binary

it will provide Python library we can use. So now look carefully:

$ python
Python 3.8.10 (default, Sep 28 2021, 16:10:42)
[GCC 9.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.

>>> from sqlcipher3 import dbapi2 as sqlcipher
>>> db.execute('PRAGMA key=testing;')

>>> db = sqlcipher.connect('test1.db')

<sqlcipher3.dbapi2.Cursor object at 0x7f1e134ad420>
>>> db.execute('create table tbl(id integer, col text)')
<sqlcipher3.dbapi2.Cursor object at 0x7f1e134ad7a0>
>>> db.execute("insert into tbl values(1, 'Dean')")
<sqlcipher3.dbapi2.Cursor object at 0x7f1e134ad420>
>>> db.execute('select * from tbl;').fetchall()
[(1, 'Dean')]

With PRAGMA we provided the encryption key, which is "testing".

Let's see what happens when we provide the wrong key:

>>> db.execute('PRAGMA key=wrongkey;')
<sqlcipher3.dbapi2.Cursor object at 0x7f1e134ad420>
>>> db = sqlcipher.connect('test1.db')
>>> db.execute('select * from tbl;').fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlcipher3.dbapi2.DatabaseError: file is not a database

The database is encrypted and can't be accessed. The same would be with no key.

And how to do this with Jam? Simple. We just create below on Task/Server module and add a table (with DB Manual Mode, so it's not created in the App database):

```

from sqlcipher3 import dbapi2 as sqlcipher

def on_open(item, params):
    con = sqlcipher.connect('test.db')
    con.execute('PRAGMA key=testing;')
    try:
        sql = item.get_select_query(params)
        cursor = con.cursor()
        cursor.execute(sql)
        rows = cursor.fetchall()

    finally:
        con.close()
    return rows, ''        
```

And viola:

Now,

the question would be how to provide Jam support for encryption out of the box :)
Would anyone use it though? Not sure....

D.

[Drazen D. Babic]

According to this:

https://github.com/sqlcipher/sqlcipher

• Fast performance with as little as 5-15% overhead for encryption on many operations
• 100% of data in the database file is encrypted
• Good security practices (CBC mode, HMAC, key derivation)
• Zero-configuration and application level cryptography
• Algorithms provided by the peer reviewed OpenSSL crypto library.
• Configurable crypto providers

[Drazen D. Babic]

Here is the DB which can't be opened with any utilities but above Python, for example:

sqlite3 test2.db .schema

Error: file is not a database

but below works even with Jam

>>> from sqlcipher3 import dbapi2 as sqlcipher

>>> db = sqlcipher.connect('test2.db')
>>> db.execute('PRAGMA key=testing;')
<sqlcipher3.dbapi2.Cursor object at 0x7fd85b76b420>
>>> db.execute("insert into tbl(col) values( 'DeanINS')")
<sqlcipher3.dbapi2.Cursor object at 0x7fd85b76b7a0>

>>> db.execute('select * from tbl;').fetchall()

[(1, 'Deannnn'), (2, 'DeanINS')]
>>> db.commit()

[Drazen D. Babic]

Hi all, 

I finalised Jam changes to use encrypted sqlite database. It is actually very simple, copy sqlite.py into ie. sqlcipher.py file and modify sqlcipher.py with this:

import os
import sys

from sqlcipher3 import dbapi2 as sqlcipher

DATABASE = 'SQLCHIPER'
NEED_DATABASE_NAME = True
NEED_LOGIN = False
NEED_PASSWORD = True

.

.

def connect(database, user, password, host, port, encoding, server):
    connection = sqlcipher.connect(database)
    connection.create_function("UPPER", 1, sqlite_upper)
    cursor = connection.cursor()
    cursor.execute('PRAGMA key= %s' % password)
    cursor.execute("PRAGMA foreign_keys = ON")
    return connection

.

.

testing is the encryption key, which is the DB password.

Now,  modify db_modules.py with:

SQLITE, FIREBIRD, POSTGRESQL, MYSQL, ORACLE, MSSQL, SQLCIPHER = range(1, 8)
DB_TYPE = ('Sqlite', 'FireBird', 'PostgreSQL', 'MySQL', 'Oracle', 'MSSQL', 'SQLCIPHER')

def get_db_module(db_type):
    db = None
    if db_type == SQLITE:
        import jam.db.sqlite as db
    elif db_type == POSTGRESQL:
        import jam.db.postgres as db
    elif db_type == MYSQL:
        import jam.db.mysql as db
    elif db_type == FIREBIRD:
        import jam.db.firebird as db
    elif db_type == ORACLE:
        import jam.db.oracle as db
    elif db_type == MSSQL:
        import jam.db.mssql as db
    elif db_type == SQLCIPHER:
        import jam.db.sqlcipher as db
    return db

That is IT. No other changes are needed except installing the library with:

pip install sqlcipher3-binary

Here it the Jam with simple Demo (pls note "DB Manual mode", it is more customised Jam). Just install above with pip and run with" python server.py in the unzipped folder:

https://drive.google.com/file/d/1iu59pljz-syoAtKSHQfCl7LouVtdxEpy/view?usp=sharing

This method of using  encrypted DB is very valuable for some customers. It can be also expanded for encrypted admin.sqlite DB that Jam is using.

Meaning one can completely protect the intellectual property and data.

Plus, it is absolutely possible to encrypt the current data:

https://www.zetetic.net/sqlcipher/sqlcipher-api/#sqlcipher_export

Enjoy

[Drazen D. Babic]

Here is the complete encrypted Demo:

https://drive.google.com/file/d/1pw5sFUBVNrg7bKxJBgKDdslVgOWcCkqA/view?usp=sharing

We can change the password on builder, restart the app and data won't be accessible.

There is no way to look into the data with no password. To look at the data, use for example sqlcipher:

/usr/local/bin/sqlcipher encrypted.db
SQLite version 3.37.2 2022-01-06 13:25:41 (SQLCipher 4.5.1 community)
Enter ".help" for usage hints.
sqlite> PRAGMA key= 'testing';
sqlite> .schema
CREATE TABLE IF NOT EXISTS "DEMO_CUSTOMERS"
.

.

.

[Drazen D. Babic]

Hi all, 

here is the Windows x64 sqlcipher with the DLL for anyone who would like to try:

https://drive.google.com/file/d/1huh2s_yB-TAT8uSnPKc0mLj5yR3Gbvy3/view?usp=sharing

C:\Temp>sqlite3.exe test2.db

SQLite version 3.37.2 2022-01-06 13:25:41 (SQLCipher 4.5.1 community)
Enter ".help" for usage hints.

sqlite> .schema

Error: file is not a database

sqlite> PRAGMA key= 'testing';
ok
sqlite> .schema
CREATE TABLE tbl(id integer PRIMARY KEY, col text);
sqlite> .q

The software was built by me since impossible to find Windows build :) As in here:

https://discuss.zetetic.net/search?q=Windows%2010%20%23sqlcipher

If someone is keen to use attached DLL with Jam, here is how:

https://stackoverflow.com/questions/252417/how-can-i-use-a-dll-file-from-python

All of this is way easier on Linux :)

Let me know of any issues

Enjoy

[Drazen D. Babic]

Tested Windows sqlcipher.dll and all working!

Here is the screenshot from my Win10 x64.

 

Because Python does not know anything about sqlcipher.dll, I renamed old sqlite3.dll to sqlite3.dll_old (note the date!) and replaced it with provided dll on above link. Thus, this IS sqlite3 and it will function as one with non encrypted database. However, it also supports encryption which "normal" sqlite3 does not.

Than created Jam support as above and just used in sqlcipher.py:

from sqlite3 import dbapi2 as sqlipher

That is all. 

How to encrypt database:

https://discuss.zetetic.net/t/how-to-encrypt-a-plaintext-sqlite-database-to-use-sqlcipher-and-avoid-file-is-encrypted-or-is-not-a-database-errors/868

Hope this will help Windows users to build secure sqlite3 Apps.

D.

[Fabio Lenzarini]

good job Drazen!

[muhammad mursaleen]

Hi
Drazen Thanks for the detailed answer, I'm not able to install sqlcipher3-binary on Windows. 

Do you guys know how we will be able to encrypt and decrypt SQLite files in Django? I have tried multiple ways to encrypt SQLite files in Django but I got no proper solution.

[Drazen Babic]

Hi,

I dont follow, there is no sqlcipher-binary install on Windows. This is why you can find the DLL here, on this thread.

Just replace sqlite3.dll with this one and youll be able to decrypt, providing the pragma key is known.

No pragma key, no way to do anything.

D.

[Yeoresley Dominguez]

Good afternoon I was looking in the group, something specific about sqlite3 database encryption, I would like to know if someone has managed to do this option with the admin.sqlite database, if so, if possible, could you explain to me how to do this please? . greetings Yeoresley

[Dean D. Babic]

Hi,

you would need to find all bits and pieces to change in the jam/ folder for admin DB.

If working with Windows, the above thread has the DLL:

"Hi all, 

here is the Windows x64 sqlcipher with the DLL for anyone who would like to try:

..."

So you can try first with plain test2.db database on Windows x64. 

But here is a tip, how would Jam upgrade work? ;) Since this changes would not be included in Jam.