OK, I've set up a local CVS repository just so i can play with the client. I got sqlite 3.0 downloaded, loaded into KDevelop, and built. I downloaded blowfish C source, haven't downloaded it yet.
First step is going to just be intercepting sqlite's page read/write commands. That's probably the best place to do the encryption (as you suggested). There are a number of tricky bits to that, like how to handle changes in block siz (if you read a 1024 bit block and decrypt, is is still 1024 bits?)
Anyway, the important thing is that I've gotten started.
What a coincidence - I was just looking at that site last night. Looks pretty new, but easy to get a free license w/ beta. I am was going to download a trial version, but it isn't there yet. In fact, nothing much is on the site.
Anyway, this isn't source and we'd still need to write the bit to generate the passcode from the certs. I guess that part would also prompt for a cert passphrase if desired. What it saves is actually adding the encrypt/decrypt part (which we would do w/ blowfish or something). I don't know how big a savings that would be, and whether it would be worth linking to a library we don't have source for (maybe we could get it).
I didn't find much online searching for encryption on sqlite, which is odd becasue the architecture is definitely there. The xCodec structure member is almost certainly the key, but no one is talking about it much yet. I'm sure that is how sqlcrypt does it.
sqlite has a macro named CODEX that gets called on pages immediately before
writing to disk. It calls a member function pointer of the page structure,
so you can supply your own codex function, which would be our en/decrypter.
That might be all we need. I spent enough time tracing through the code to
know what questions to ask, I'm going to do a little research on what others
have done, but it seems like all we need do is create an encrypt/decrypt
object with a certificate authority encoded in it. Seems reasonable to me.
One thing I do need to look at, though, is the effect of updating pages if
the codex changes the size of the page. Does Playmaker do a lot of deleting
records and creating new ones? Could be an issue if file continues to grow,
might need a "shrink" utility. Happily, sqlite3 provides 'vacuum' to do just
that.
I think the main reason people don't comment much on ecrypting/decrypting sqlite is in deferece of the author's attempt to make money from offering that service. The few posts I do see point the inquirer to the author's page about it: [url=http://www.hwaci.com/sw/sqlite/prosupport.html]here[/url].
I agree its probably better to own the source. I was re-thinking it this morning and I realized that the sqlcrypt author would probably not support the binary on all the platforms we support (Win, OS X, Palm, Linux, CE, etc.) We might want to look into his lead on AES though.
Also, I'm almost finished with the first draft of the doc which will describle the whole process. I'll post it here by the end of today hopefully.
Yup - a pretty good example of how to profit from open source, if he is successful at it. $1650/year with a 20 hour limit seems a bit high to me, but who knows. If sqlite gets popular enough, he might make something of it.
I don't think the sqlcrypt guy will be giving it away free for long - looks like he;s planning to run a limited beta and give it free to that group only.
Anyway, looking good. I've got sqlite doing a simple encryption now (just XORing against a magic cookie). I have a source file named pmcrypt.c, pretty simple API to call it. once you trace it all through.
There seem to be three basic crypt functions passed to the CODEC macro. Read from disk, Write to disk, and Refresh Data after write (so you encrypt a page, write it, then restore it to the page cache). Within that you can be writing to the main file or the journal file. It should be transparent through transactions (commit,rollback) but I haven't tried that yet. So there are op codes 0 - 7, but I've only seen these in operation:
0=REFRESH
3=READ MAIN
6=WRITE MAIN
7=WRITE JOURNAL
There is also avoid pointer passed to the xCodec function, which I guess we can set to a certificate. So it looks promising to go this way. There are a couple of other neat little utils for setting keys and attaching to DBs, don't know if they will matter. I think the idea there is you can encrypt the access to the DB as well as the data, but who cares?
No, I don't think he intends to give it away either - he's trading licenses for beta testing. I just find it ironic that the original author has so generously given the sqlcrypt guy the opportunity to compete with him head on. I guess by working with an open source community to help create a product like sqlite3, the tide rises for all developers including himself.
Very exciting news on the pmcrypt.c work! Next step: integrate AES or Blowfish as the crypt method?
.rekey calls the rekey(...) function, allows you to change the encryption key. Of course, we won't be using shell.c, which is just the front end interface. Yes, there are hooks to key() and rekey(), I'm not sure how we will use them if at all. I imagine we will need some function to update certs (prob rekey()), but don't know what that will look like yet. I won't be able to look at openSSL until next week.
OK, I've set up a local CVS repository just so i can play with the client. I got sqlite 3.0 downloaded, loaded into KDevelop, and built. I downloaded blowfish C source, haven't downloaded it yet.
First step is going to just be intercepting sqlite's page read/write commands. That's probably the best place to do the encryption (as you suggested). There are a number of tricky bits to that, like how to handle changes in block siz (if you read a 1024 bit block and decrypt, is is still 1024 bits?)
Anyway, the important thing is that I've gotten started.
Before you go too far, check out http://www.sqlcrypt.com let me know what you think. Anything to save time right?
What a coincidence - I was just looking at that site last night. Looks pretty new, but easy to get a free license w/ beta. I am was going to download a trial version, but it isn't there yet. In fact, nothing much is on the site.
Anyway, this isn't source and we'd still need to write the bit to generate the passcode from the certs. I guess that part would also prompt for a cert passphrase if desired. What it saves is actually adding the encrypt/decrypt part (which we would do w/ blowfish or something). I don't know how big a savings that would be, and whether it would be worth linking to a library we don't have source for (maybe we could get it).
I didn't find much online searching for encryption on sqlite, which is odd becasue the architecture is definitely there. The xCodec structure member is almost certainly the key, but no one is talking about it much yet. I'm sure that is how sqlcrypt does it.
[from an email message 11/3/04]
sqlite has a macro named CODEX that gets called on pages immediately before
writing to disk. It calls a member function pointer of the page structure,
so you can supply your own codex function, which would be our en/decrypter.
That might be all we need. I spent enough time tracing through the code to
know what questions to ask, I'm going to do a little research on what others
have done, but it seems like all we need do is create an encrypt/decrypt
object with a certificate authority encoded in it. Seems reasonable to me.
One thing I do need to look at, though, is the effect of updating pages if
the codex changes the size of the page. Does Playmaker do a lot of deleting
records and creating new ones? Could be an issue if file continues to grow,
might need a "shrink" utility. Happily, sqlite3 provides 'vacuum' to do just
that.
I think the main reason people don't comment much on ecrypting/decrypting sqlite is in deferece of the author's attempt to make money from offering that service. The few posts I do see point the inquirer to the author's page about it: [url=http://www.hwaci.com/sw/sqlite/prosupport.html]here[/url].
I agree its probably better to own the source. I was re-thinking it this morning and I realized that the sqlcrypt author would probably not support the binary on all the platforms we support (Win, OS X, Palm, Linux, CE, etc.) We might want to look into his lead on AES though.
Also, I'm almost finished with the first draft of the doc which will describle the whole process. I'll post it here by the end of today hopefully.
Yup - a pretty good example of how to profit from open source, if he is successful at it. $1650/year with a 20 hour limit seems a bit high to me, but who knows. If sqlite gets popular enough, he might make something of it.
Yeah, but now he's competing with the sqlcrypt guy whose using the code he gave away freely.
I don't think the sqlcrypt guy will be giving it away free for long - looks like he;s planning to run a limited beta and give it free to that group only.
Anyway, looking good. I've got sqlite doing a simple encryption now (just XORing against a magic cookie). I have a source file named pmcrypt.c, pretty simple API to call it. once you trace it all through.
There seem to be three basic crypt functions passed to the CODEC macro. Read from disk, Write to disk, and Refresh Data after write (so you encrypt a page, write it, then restore it to the page cache). Within that you can be writing to the main file or the journal file. It should be transparent through transactions (commit,rollback) but I haven't tried that yet. So there are op codes 0 - 7, but I've only seen these in operation:
0=REFRESH
3=READ MAIN
6=WRITE MAIN
7=WRITE JOURNAL
There is also avoid pointer passed to the xCodec function, which I guess we can set to a certificate. So it looks promising to go this way. There are a couple of other neat little utils for setting keys and attaching to DBs, don't know if they will matter. I think the idea there is you can encrypt the access to the DB as well as the data, but who cares?
No, I don't think he intends to give it away either - he's trading licenses for beta testing. I just find it ironic that the original author has so generously given the sqlcrypt guy the opportunity to compete with him head on. I guess by working with an open source community to help create a product like sqlite3, the tide rises for all developers including himself.
Very exciting news on the pmcrypt.c work! Next step: integrate AES or Blowfish as the crypt method?
What does the .rekey function do?
Are therer hooks to the key() and rekey() functions?
.rekey calls the rekey(...) function, allows you to change the encryption key. Of course, we won't be using shell.c, which is just the front end interface. Yes, there are hooks to key() and rekey(), I'm not sure how we will use them if at all. I imagine we will need some function to update certs (prob rekey()), but don't know what that will look like yet. I won't be able to look at openSSL until next week.