SQLite with password

[Radu]

Greetings! After I've search this forums and with Google I dare to ask: is there a way to open a SQLite database which is protected with password?

_SQLite_Open do not seems to support protected databases.

Thank you!

[jchd]

Standard SQLite3 API doesn't offer any form of protection and relies only on underlying OS privileges mecanism (if any) for "security". If you have an existing SQLite-style database which uses a specific API to gain access, then you should use this particular (non-standard) API.

If you're talking about Windows access rights, then run your script under a user of the group which has suffucient rights. Beware were the temporary files (temporary base, or journal files) go: you need read and write access there too.

There exists a SQLite-compatible library available offering encryption, hence a basic layer of security: System.Data.SQLite This is an ADO .net provider which you might be able to use as any ADO layer from AutoIt. I have no experience with this one, but there are a number of ADO users here.

Beside SQLite, I also use an ADO driver for Pervasive, which I have wrapped into an SQLite-style interface UDF because I find it much easier to have the same interface to several databases concurrently used. But that's only a personal preference (and I don't like ADO limitations!). If ever you want, I can share it with you, no problem.

Edited April 7, 2010 by jchd

[Radu]

Thank you very much. It's a sad news. I will try to digg for altenatives.

Chaning a little the subject: can than the DB file be embeded in final EXE? I've try with CTRL-F7 with no luck. If it's possible to embed that sqlite database... from where (a link, a command) should I start?

Edited April 7, 2010 by Radu

[jchd]

You can FileInstall (lookup help file) about anything you need to in a compiled AutoIt in order to get a standalone exe. Simply take care of size limitation in practice.

System.Data.SQLite can be used as a direct sqlite3.dll replacement if you whish.

[Radu]

Somewhere in forum: "No this is not possible (by simple way) to directly access database embeded in resources."

FileInstall will put the DB on the disk. I want this DB to be protected (that's why embedding could be an option - not if I have anyway to extract it and put it in on the harddisk).

You say: "System.Data.SQLite can be used as a direct sqlite3.dll replacement if you whish."

Well, but if I use that, where do I found a AU3 library to have access with password at DB? If I just replace the sqlite3.dll, the library for SQLite is the same, with no password option.

Thank you for patience!

[jchd]

Maybe System.Data.SQLite adds a simple API for that supplementing SQLite standard one.

I'll look into it and get back to you about that possibility.

[jchd]

Radu,

I have confirmation that the System.Data.SQLite dll is not only sqlite3.dll compatible, but also that the two extra functions for handling encryption (hence access) are the same than the classical one provisionned for payware SEE:

Yes, you can just drop it in as a replacement for sqlite3.dll. You don't have to change a thing. You can also rename it sqlite3.dll if you don't want to change a line in your app. And if you just use it as a replacement for sqlite3.dll, it doesn't require .NET.

The interface exposes two new functions sqlite3_key and sqlite3_rekey that are identical to the ones in SEE described here: http://www.hwaci.com/sw/sqlite/see.html

So you're just a couple of wrapper functions away from starting your project, with a decently encrypted DB. Obviously the overall solution doesn't make the DB imune to attacks by powerful attackers, but it will stop casual users from reading/messing with your data.

But wait: there is more after trying it myself: you don't even need to wrap those two functions yourself or have the maintainer (a cool guy ) of the SQLite UDF do it for you. You can use the pragmas mentionned on the page above.

You can test it by yourself: download the Windows binary from the System.Data.SQLite webpage and launch that script in the same directory:

#include <SQLite.au3>   ; don't include sqlite.dll.au3 !!!

_SQLite_Startup ("System.Data.SQLite.dll")
ConsoleWrite(_SQLite_LibVersion() & @LF)
_SQLite_Open("testcrypt.db")
_SQLite_Exec(-1, "pragma key = 'Radu is happy!';create table if not exists test (id integer, val text);" & _
                "insert into test values (1, 'abc');")
Local $row
_SQLite_QuerySingleRow(-1, "select * from test;", $row)
ConsoleWrite($row[1] & @LF)
_SQLite_Close()
_SQLite_Shutdown()

Take your favorite hex editor and look at what testcrypt.db looks like: white noise, nothing less. Hope you remember the key!

The version available from the webpage is 3.6.16, so it's a bit outdated (no foreign keys, no backup API, some dark corner bugs). I can shortly provide a binary compiled with 3.6.23.1 (latest official release), coming from a trusted source. If you need it I can post it and/or instructions about how to compile it.

The mud is clearing, isn't it?

Edited April 8, 2010 by jchd

[Radu]

I am shocked! This was a very nice finding, jchd, and I am sure many will love it. I can confirm that by using

_SQLite_Exec(-1, "pragma rekey = 'lolipop'")

the SQLite database become unreadable by human eye. And it's usable from AutoIt only with

_SQLite_Exec(-1, "pragma key = 'lolipop'")

So problem seems to be solved! I've just have to test now how it works the EXE on other computers (need that DLL to be provided or not).

Jchd: a huge thank you! As for the 3.6.16 versus 3.6.23.1, I guess AutoIt users (including me) will be happy to have a newer version, whatever goods is coming from this.

[jchd]

You're welcome Radu,

Here's System.Data.SQLite dll including untouched sources from SQLite v3.6.32.1 (latest available officially).

This version brought to you by courtesy of the author of SQLite Expert, Bogdan Ureche, whose product is an absolute must have for anyone using SQLite. The praise must go to him, theen the author of System.Data.SQLite dll, without ever forgetting Dr Richard Hipp for building SQLite and making it available to the entire universe for free. Our Jon is in the same basket, needless to say.

Edit: yes, you'll have to embed that dll as well.

Edited April 8, 2010 by jchd

[RobK]

You're welcome Radu,

Here's System.Data.SQLite dll including untouched sources from SQLite v3.6.32.1 (latest available officially).

This version brought to you by courtesy of the author of SQLite Expert, Bogdan Ureche, whose product is an absolute must have for anyone using SQLite. The praise must go to him, theen the author of System.Data.SQLite dll, without ever forgetting Dr Richard Hipp for building SQLite and making it available to the entire universe for free. Our Jon is in the same basket, needless to say.

Edit: yes, you'll have to embed that dll as well.

Once you convert the dll to be embedded - how do you reference for the SQL_Startup() method?

Thanks,

Rob

[BartW]

Hi al

I don’t know if this is the right way to do this but her I go

First I made SQLite.pw.dll.au3 This is System.Data.SQLite.dll for 32 and 64 bit to be embedded

I covered it whit the MemoryDllGen.au3 (my thanks to the maker of this)

Second I chance the following in the SQLite.au3 and rename it to SQLitePW.au3.

Added function _SQLite_Create_PW this function will create a password to lock the DB .

Added function _SQLite_Password this function will unlock the DB .

Change function _SQLite_open() the secont option is now to put in a password .

I hope this helps

SQLitePW.au3

SQLite.pw.dll.au3.zip

Sorry for my English

[jchd]

You don't exactly need that complicated way. Simply FileInstall the password-enabled DLL (x32, x64 or both) and you're done. The two functions you mention are just _SQLite_Exec($hDB, "pragma key=... stuff. If you insist on having functions for that, then simply put them in a private UDF you could name SQLitePW.au3 and keep on using the standard main UDF. This way, you won't have to merge (or forget to do so) those two tiny functions again when SQLite.au3 evolves.

[Zedna]

@jchd

As about System.Data.SQLite

I think it's woth of adding to Examples forum

[jchd]

Certainly, but currently with this build being taken over by the SQLite team it results in several versions for .Net, traditional C API, x32, x64, mixed assembly or not, with tons of issues with external MS DLLs. Watch the SQLite list about such issues and to witt, look at the official DL page:

http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Selecting the right build is getting trickier everyday.

This is going to get huge and problematic for most AutoIt users. I'm going to investigate another, simpler route to keep it manageable with only C API and encryption (and FTS3/4 and RTree). More about this soon (I hope).

Edited November 3, 2011 by jchd

[Skitty]

On first not, I only read the first and second post so I don't know if any one's even mentioned this but why not create a function that encrypts your data and then adds it to the database?

If the database already has "data" then extract it and encrypt it and use a decrypting function from there..

[jchd]

Fine, but then how do you think the DB engine will proceed with indices, queries, collations... ?

Also schema names won't be encrypted, will they? So in a column named "Base salary" what kind of data will you store? Encrypted integers of plaintext?

A DB is not a passive store. Encryption has to be performed by the pager, that is between the SQL engine and the filesystem.

Edited November 3, 2011 by jchd

[Skitty]

I have absolutely no idea what you're talking about but I make use of several functions that encrypt everything in an sqlite database, like the name of the address where the data is at and the data itself, I am not a pro nor do I portray my self as one so either way, it works for me, now ask me how to restore a vehicle and maybe we'll get somewhere if you know what I mean

[jchd]

OK, look: you maintain a table of the many tuples (pseudo, password) you created on this forum (I'm just kidding here). As a long-term user you know this is against forum's rules. To avoid censorship in case this list falls into Mods' hands, you encrypt it seriously (AES or something) before storing the data in the list.

You have something like that:

$aMyPseudos[5][2] = [ _
  "THAT1ANONYMOUSDUDE", "pass1", _
  "Joe456", "pass2", _
  "IfuckYouBastards", "pass3", _
  "pr0nAddict", "pass4", _
  "Ikarus841257896023490", "pass5" _
]
; table creation
_SQLite_Exec($hDB, "create table AutoIt_pseudos (pseudo char, pwd char);")
; fill table (I don't use a transaction nor escape strings here for brevity)
For $i = 0 To UBound($aMyPseudos) - 1
  _SQLite_Exec($hDB, "insert into AutoIt_pseudos values(" & MyCrypt(aMyPseudos[$i][0]) & "," & MyCrypt(aMyPseudos[$i][1]) & '");")
Next
; here our DB contains only garbage-looking data, thanks to MyCrypt()
;**********************************************************************************************
 
; two months later
; query the DB (you have a bad memory and don't remember well the figures after Ikarus)
; this won't work:
_SQLite_QuerySingleRow($hDB, "select * from AutoIt_pseudos where pseudo like 'Ikarus%';", $aRow)
 
; Now the question is: what do you think you'll get into $aRow?
 
; this won't work either (except if you're using a simplistic Vingenere cipher which your sister kid can decrypt):
_SQLite_QuerySingleRow($hDB, "select * from AutoIt_pseudos where pseudo like " & MyCrypt('Ikarus") & '%' & ";", $aRow)

Now think of a DB with 180 millions rows. To perform queries efficiently, you need one or more index, depending on your queries. But as you understand you'll be indexing garbage-looking data. The issue is that when you query your DB, you just don't know what to query for.

In short: no it doesn't work in general.

Disclaimer: typed on the fly and obviously untested, so forgive typos

Edited November 3, 2011 by jchd