sqlite question

[koranthala]

Hi,
I use sqlalchemy to use a sqlite db in my program. The program is
working perfectly as of now. But now, a new requirement has come that
the db has to be encrypted.
I found two options while searching internet - SQLite Encryption
Extension and Sqlite-Crypt. Now, buying the license is not an issue.
But I cannot understand how to use it along with sqlalchemy.
There are two issues here -
(1) If I am buying the encrypted db, then I get only the C-code. I am
not sure how sqlalchemy links to the sqlite code.
(2) In the sqlalchemy documention, I couldnt find anything associated
with encryption.

Has anybody done this? Is it possible to use encrypted sqlite along
with sqlalchemy?

TIA
K

 

[Dennis Lee Bieber]

I found two options while searching internet - SQLite Encryption
Extension and Sqlite-Crypt. Now, buying the license is not an issue.
But I cannot understand how to use it along with sqlalchemy.
There are two issues here -
(1) If I am buying the encrypted db, then I get only the C-code. I am
not sure how sqlalchemy links to the sqlite code.
(2) In the sqlalchemy documention, I couldnt find anything associated
with encryption.

I suspect, besides building an sqlite3.dll (if Windows), you might
have to modify the pysqlite DB-API adapter to support whatever new
arguments have been added to various calls (most like the db.connect()
parameters have changed to require an encryption password)

In theory, once a connection is made, everything else should be
transparent -- and I'm fairly certain that most of the object-relational
mappers still use the standard distributed db-api modules underneath.

 

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I suspect, besides building an sqlite3.dll (if Windows), you might
have to modify the pysqlite DB-API adapter to support whatever new
arguments have been added to various calls (most like the db.connect()
parameters have changed to require an encryption password)

The extension requires an extra C api call after the database is opened to
set the encryption key. (There is also another API to change the key.)

This means that it is not possible for the same pysqlite to work against
SQLite built with and without the encryption extension. The changes needed
in pysqlite are a simple matter of programming although you'll want them
incorporated back into the core so you don't have to maintain them (wrapped
in some sort of ifdef).

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrZb6sACgkQmOOfHg372QRSYwCg2J/YSvkqLs8EJ1iJkE2wnbaY
nl0AoKYxgMEnBQNjDyYHv1xWC0Tia74U
=eA2B
-----END PGP SIGNATURE-----

 

[koranthala]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


The extension requires an extra C api call after the database is opened to
set the encryption key.  (There is also another API to change the key.)

This means that it is not possible for the same pysqlite to work against
SQLite built with and without the encryption extension.  The changes needed
in pysqlite are a simple matter of programming although you'll want them
incorporated back into the core so you don't have to maintain them (wrapped
in some sort of ifdef).

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla -http://enigmail.mozdev.org

iEYEARECAAYFAkrZb6sACgkQmOOfHg372QRSYwCg2J/YSvkqLs8EJ1iJkE2wnbaY
nl0AoKYxgMEnBQNjDyYHv1xWC0Tia74U
=eA2B
-----END PGP SIGNATURE-----

One problem is that pysqlite is written in C, and I have no knowledge
of C whatsoever. I will try to incorporate the changes mentioned, but
I am not sure now at all :-(

K

 

[Diez B. Roggisch]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


The extension requires an extra C api call after the database is opened to
set the encryption key. (There is also another API to change the key.)

This means that it is not possible for the same pysqlite to work against
SQLite built with and without the encryption extension. The changes needed
in pysqlite are a simple matter of programming although you'll want them
incorporated back into the core so you don't have to maintain them (wrapped
in some sort of ifdef).

It might be possible to wrap the needed call using ctypes, if the DLL/SO
can be loaded explicitly. Which it should be I'd say.


Diez