apsw.SQLError: SQLError: SQL logic error

[abdelouahabb]

I am trying to encrypt an Sqlite DB, I used Nuitka to compile the app to be distribuble, sadly when there is that cart to be validated with more than 5 products for example (a for loop inside a transaction), it always crashes giving this error :

18:15-27_07_23,498 tornado.application ERROR Uncaught exception POST /admin/fastcart (127.0.0.1)
HTTPServerRequest(protocol='http', host='localhost:8000', method='POST', uri='/admin/fastcart', version='HTTP/1.1', remote_ip='127.0.0.1')
Traceback (most recent call last):
  File "C:\Users\PHONES~1\Desktop\cashier\APPLIC~1\tornado\web.py", line 1786, in _execute
  File "C:\Users\PHONES~1\Desktop\cashier\APPLIC~1\app.py", line 356, in post
apsw.SQLError: SQLError: SQL logic error

And there is nothing about it to debug

• is that a memory issue ?

The Sqlite DB encrypter used is https://github.com/Mister7F/sql-crypt

[rogerbinns]

SQLError corresponds to the SQLite error code SQLITE_ERROR and is what SQLite returns when it has nothing more specific.

You can get logging from SQLite which should provide more information.

I'll check sql-crypt.

[rogerbinns]

When I run sql-crypt test suite I see the same error code you are seeing, and it looks like most of the tests fail.

Also it doesn't take the right approach to SQLite encryption which is to take a portion of each page to store the encryption information. Instead it makes the file bigger with the extra information and ends up making additional writes without being careful with atomic writes and failures.

ie it doesn't look like sql-crypt would ever work securely and robustly nor does its test suite work. I suspect you are hitting problems the moment your data spans more than one page.

I don't think there is anything more I can do. If encryption matters then I recommend you go the proper route.

[abdelouahabb]

When I run sql-crypt test suite I see the same error code you are seeing, and it looks like most of the tests fail.

Also it doesn't take the right approach to SQLite encryption which is to take a portion of each page to store the encryption information. Instead it makes the file bigger with the extra information and ends up making additional writes without being careful with atomic writes and failures.

ie it doesn't look like sql-crypt would ever work securely and robustly nor does its test suite work. I suspect you are hitting problems the moment your data spans more than one page.

I don't think there is anything more I can do. If encryption matters then I recommend you go the proper route.

So it was the main bug ! So I need to rebuild Python's sqlite with encryption support ? The open solution I found is that one : https://pypi.org/project/pysqlcipher3/

[rogerbinns]

I'd recommend you get your code working correctly first without encryption. Encryption is something you can add at the very end.

You also need to examine your security approach. It looks like you are implementing a web service and that it would mean the encryption key would be deployed with your code. Is that providing a meaningful difference in your security goals?

If encryption really matters and is important, then go the proper route.

The one you found says:

Note: this project is no longer being actively maintained. Security vulnerabilities may exist in this code. Use at your own risk

[abdelouahabb]

I'd recommend you get your code working correctly first without encryption. Encryption is something you can add at the very end.

You also need to examine your security approach. It looks like you are implementing a web service and that it would mean the encryption key would be deployed with your code. Is that providing a meaningful difference in your security goals?

If encryption really matters and is important, then go the proper route.

The one you found says:

Note: this project is no longer being actively maintained. Security vulnerabilities may exist in this code. Use at your own risk

Thank you for the tip, Well, the idea is that I am using an offline application with a web interface, the database is a single user, using SQLite as encrypted because some data should not be changed just by installing sqlite browser and altering data (plus, app serial is send in a blank encrypted DB so they can use it)

[rogerbinns]

Thanks for providing more detail. If this is all about making money then you are best off paying for the proper SQLite encryption extension because that makes for a robust business.

But what you are describing seems to be preventing intentional or unintentional tampering. An easy way of doing that is to make the database unrecognisable to SQLite on disk. The vfs example xor's the database which achieves that. You can do something similar and you only have to do it for any of the first 16 bytes of the database to be effective.

Since you are providing the code and data for people to run on their own machines, it is always feasible for them to extract all of it. You have to make the tradeoff between the complexity and robustness of your obfuscation, and how much effort is put in. Good luck!

[abdelouahabb]

Thanks for providing more detail. If this is all about making money then you are best off paying for the proper SQLite encryption extension because that makes for a robust business.

But what you are describing seems to be preventing intentional or unintentional tampering. An easy way of doing that is to make the database unrecognisable to SQLite on disk. The vfs example xor's the database which achieves that. You can do something similar and you only have to do it for any of the first 16 bytes of the database to be effective.

Since you are providing the code and data for people to run on their own machines, it is always feasible for them to extract all of it. You have to make the tradeoff between the complexity and robustness of your obfuscation, and how much effort is put in. Good luck!

I think the best I can do then is the obfuscation way, the idea is to not make it easy for someone to temper data (it's a business app, where for example a store seller could temper data without being traced and could harm clients, so better to lock this for business credibility), Thank you very much again 😇 BTW: here the trick I was using, maybe this could help someone who lends here:

• python package : I use Nuitka to compile it, difficult to break the source.
• serial number is stored inside the database (each client has an initialized DB with his own serial, i generate it using a script, then I send it to them), the program has the way to check for it, and since it's difficult to decrypt the database, I initialize the serial inside the DB itself, so to break the serial, one needs to understand how it's generated, and what is the serial itself, so even breaking the app itself to not control the serial, the DB will not be able to read or write 😁 i hope this could help

[rogerbinns]

I have another suggestion for tamper detection that doesn't involve encryption. There is a counter that is part of each database that is incremented on each change. You can do pragma data_version to get it, but the value doesn't change within the same connection. Fortunately there is a way to always get it using filecontrol (more text in https://github.com/rogerbinns/apsw/commit/13c09e6948c75bc8c102b32ac9e02c52004045fd):

# We use ctypes to provide the correct C level data types and pointers
import ctypes

def get_data_version(db):
    # unsigned 32 bit integer
    data_version = ctypes.c_uint32(0)
    ok = db.filecontrol(
        "main",  # or an attached database name
        apsw.SQLITE_FCNTL_DATA_VERSION,  # code
        ctypes.addressof(data_version))  # pass C level pointer
    assert ok, "SQLITE_FCNTL_DATA_VERSION was not understood!"
    return data_version.value

You then store the data version (plus one) when your code makes changes, in a suitable table. On loading the database you can then compare the data version with your stored value, and if they are different then the database has been changed outside of your code.

You can even automate this and make sqlite browser and similar tools fail. First create a your own function for example named my_custom_data_version, and then create a trigger on each table that uses that function, something like this:

CREATE TRIGGER example_table_trigger UPDATE ON example_table 
BEGIN
  UPDATE tracker SET data_version = my_custom_data_version();
END

That will keep tracker.data_version up to date automatically. And when someone uses sqlite browser or similar, they will fail updates because there is no function named my_custom_data_version, as it only exists when your code is running.

In any event there isn't anything more I can do about this in APSW, so I am closing the issue.

[abdelouahabb]

That's just perfectly clever 😁 Thank you very much for the trick !