Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
On 20 Feb 2011, at 11:28am, Roger Binns wrote: > And if your customers care then they will already have existing solutions > for encryption and protection which includes dealing with incapacitation of > users, system administration, backups etc. It is not a good idea to defeat > those. And given the actual situation that Robert is writing for, there's a very high chance that someone somewhere will write their password down. Any time someone starts talking about the strength of AES-256 outside a need-entry-only environment I suspect that time's being wasted. Yes absolutely: security experts have to understand encryption strength and common faults in the use of encryption. But whoever cracks this system isn't going to do it by running a brute-force combinatoric attack. You could write the data to a pen drive rather than the hard disk, and let the teacher carry it in a pocket or purse. That would be more secure than leaving the data file on a desktop computer in a school. If there's any chance the teacher will type their password in while students are in the room, you've pretty-much blown your security: all it takes is a mobile phone with video recording pointing at the teacher and someone can watch shoulder and arm movements. Or even just dust the keyboard soon after a login and find out which keys are most greasy. Or dust the keyboard before a login and find which keys don't have dust on any more. If the teacher has their back to a window, have someone shoulder-surf them from outside the window. Or look at the reflection in the window. It all depends on how much advantage there is to cracking that data. If the students can change their marks, that would be worth cracking. If the system is just used to arrange who gets which lunch-shift, there's less chance anyone will bother. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/20/2011 02:32 AM, Robert Hairgrove wrote: > There are certain parts of an SQLite database or page header (the first > 100 bytes, for example) which have known values. I think it is perhaps > even dangerous to encrypt this data, at least with the same method used > for the rest of the file. If the file is not a plain old SQLite file then you must encrypt *especially* the initial page. If you do not do this then regular SQLite can attempt to open the file and depending on luck can reject it, corrupt it or do other hideous things. > SEE encrypts the entire file, according to the information on the > website. All of the file contents are encrypted but the entire file is not encrypted at once. Instead each page is encrypted separately in order to allow for random access. (Note that getting this right is hard and requires careful design. For example did you note offset 20 in the SQLite header?) > So if I leave the headers unencrypted, am I disclosing anything I should > be (somehow) hiding? You are seriously wasting your time! If whole file encryption is ok then use 7zip and an unmodified SQLite copying across as needed. If you want the file encrypted while in use then use SEE. Any other scheme you come up with will have weaknesses you can drive a truck through. In addition there are likely to be bugs and your testing won't be thorough enough. Users hate it when you lose their data in the name of security that doesn't exist :-) > AES-256 is an accepted > standard, and AFAICT offers the best openly available encryption today. Algorithms are not that important - it is how they are initialized, combined, ordered, padded, randomized, compressed and many other things you haven't considered. If you really do still want to proceed then may I suggest just using plain XOR. It is trivial to test, hard to implement wrong and good enough. Anyone who would take the effort to "crack" it would find other ways anyway. It is also evidence - ie anyone who gets the plain text contents of the database had to make an effort to do so and cannot claim to have done so accidentally. This would then be sufficiently useful for discipline or prosecution. And if your customers care then they will already have existing solutions for encryption and protection which includes dealing with incapacitation of users, system administration, backups etc. It is not a good idea to defeat those. Alternatively provide this all SaaS style so that everything lives on systems you control with a web interface. That way the data and the encryption keys will not be living on an arbitrary end user system. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1g+tEACgkQmOOfHg372QQLiQCdHooHlbtW6J+ldqY3ZGROQ4hm xEEAmwYZ1at5ZroQsQBEUpVhXUNko+PH =wl8w -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
On 20 Feb 2011, at 10:52, Robert Hairgrove wrote: > On Sun, 2011-02-20 at 09:35 +, Philip Graham Willoughby wrote: >> For this task I would use AES-256 in counter mode with an appropriate nonce >> (the counter is trivially derived from the file offset of the block to be >> read/written). The key should be derived from the user's password using >> 1-iteration PBKDF2 with the SHA-256 hash algorithm as the pluggable hash >> function and a suitably long salt. >> >> If you are only doing sequential block writes you can use CBC mode rather >> than counter mode - either can be used for random reads. > > Thanks, Phil. This is very helpful to me. AES-256 is an accepted > standard, and AFAICT offers the best openly available encryption today. It's still less secure than CBC-mode ROT-13 if you use it incorrectly, and if you do not understand why that is you are very likely to do just that. Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
On 20 Feb 2011, at 10:32, Robert Hairgrove wrote: > On Sun, 2011-02-20 at 09:35 +, Philip Graham Willoughby wrote: >> On 20 Feb 2011, at 09:10, Robert Hairgrove wrote: >> >>> I am not starting from scratch doing my own encryption; there are enough >>> open source libraries publicly available which are good enough for my >>> purposes. >> >> And all of them offer approximately no security if you use them incorrectly. > > Thanks, I realize this. > > Another question: > There are certain parts of an SQLite database or page header (the first > 100 bytes, for example) which have known values. I think it is perhaps > even dangerous to encrypt this data, at least with the same method used > for the rest of the file. If I used the same algorithm and key, etc. to > encrypt the header data as the rest of the file, it might be trivial to > decrypt it, knowing the published file format (which is explained in > great detail on the SQLite website). > > SEE encrypts the entire file, according to the information on the > website. But I'm sure they must have taken this into consideration when > they designed their library... > > So if I leave the headers unencrypted, am I disclosing anything I should > be (somehow) hiding? Any encryption algorithm which is vulnerable to a known plaintext attack is considered insecure. Therefore any algorithm which is considered secure does not have the problem which you are worrying about. Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
On Sun, 2011-02-20 at 09:35 +, Philip Graham Willoughby wrote: > For this task I would use AES-256 in counter mode with an appropriate nonce > (the counter is trivially derived from the file offset of the block to be > read/written). The key should be derived from the user's password using > 1-iteration PBKDF2 with the SHA-256 hash algorithm as the pluggable hash > function and a suitably long salt. > > If you are only doing sequential block writes you can use CBC mode rather > than counter mode - either can be used for random reads. Thanks, Phil. This is very helpful to me. AES-256 is an accepted standard, and AFAICT offers the best openly available encryption today. Bob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
On Sun, 2011-02-20 at 09:35 +, Philip Graham Willoughby wrote: > On 20 Feb 2011, at 09:10, Robert Hairgrove wrote: > > > I am not starting from scratch doing my own encryption; there are enough > > open source libraries publicly available which are good enough for my > > purposes. > > And all of them offer approximately no security if you use them incorrectly. Thanks, I realize this. Another question: There are certain parts of an SQLite database or page header (the first 100 bytes, for example) which have known values. I think it is perhaps even dangerous to encrypt this data, at least with the same method used for the rest of the file. If I used the same algorithm and key, etc. to encrypt the header data as the rest of the file, it might be trivial to decrypt it, knowing the published file format (which is explained in great detail on the SQLite website). SEE encrypts the entire file, according to the information on the website. But I'm sure they must have taken this into consideration when they designed their library... So if I leave the headers unencrypted, am I disclosing anything I should be (somehow) hiding? Bob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
On 02/20/2011 01:10 AM, Robert Hairgrove wrote: > I saw that, but I find it a little intrusive, programmatically speaking, > from a licensing standpoint. Why don't you ask DRH then since you won't be the first to want to include SEE with QT and a commercial app? > I am not starting from scratch doing my own encryption; there are enough > open source libraries publicly available which are good enough for my > purposes. Yes, there are many libraries, algorithms etc. Even if you were an expert in this stuff, there would still be a large possibility of incorrectly using or combining them. History is littered with examples. > Compression is not the same as encryption. 7zip supports encryption and they have done it right. For example they have used key strengthening. Compressing the data before encryption also helps since there are fewer patterns. > I do worry that some student might get hold of the file and try to hack it. The students will be able to get physical access so pretty much anything can be worked around. > ... hash ... user-supplied passphrase ... value known internally > final encryption key ... unique hash value ... As I said :-) Anyone can design a scheme they themselves cannot break. It requires far more skill and experience to come up with something that is actually strong. In your situation I would just use SEE working with DRH to ensure appropriate usage. If whole file encryption is okay then I would use 7zip and its encryption features with temporary files plus the backup API in order to copy the database between the 7zip archive and the regular filesystem. Using a 7zip archive also lets you keep older copies etc. You implementing or using any kind of encryption scheme also means you defeat good system management practises. For example if whoever sets the password is incapacitated then the data cannot be recovered. Good systems management practises will typically use encryption systems (eg a filesystem) that can be accessed both by the user and by appropriate administrators. > As to the gun, Bruce Schneier already pointed out that this is one of > the more expensive options in the attack tree. ;) $60k seems like a lot and he refers to a gang. My swag is that you could hire a local thug for a few thousand to wave a gun around, all depending on the possibility of being caught or observed. Safes are far more likely to be somewhere secure and discreet and to contain valuable items. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
On 20 Feb 2011, at 09:10, Robert Hairgrove wrote: > I am not starting from scratch doing my own encryption; there are enough > open source libraries publicly available which are good enough for my > purposes. And all of them offer approximately no security if you use them incorrectly. For this task I would use AES-256 in counter mode with an appropriate nonce (the counter is trivially derived from the file offset of the block to be read/written). The key should be derived from the user's password using 1-iteration PBKDF2 with the SHA-256 hash algorithm as the pluggable hash function and a suitably long salt. If you are only doing sequential block writes you can use CBC mode rather than counter mode - either can be used for random reads. Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
On Sat, 2011-02-19 at 17:08 -0800, Roger Binns wrote: > On 02/19/2011 03:40 PM, Robert Hairgrove wrote: > > But before I "try this at home", I thought I would ask if there are any > > caveats I should be aware of? Thanks for any helpful advice! Thanks, Roger ... those are great links! > Unless your time has no value, I'd suggest using this: > > http://www.hwaci.com/sw/sqlite/see.html I saw that, but I find it a little intrusive, programmatically speaking, from a licensing standpoint. Although SQLite itself is PD licensed, I am using it coupled with the Qt source code which is LGPL licensed. So I'm not really not sure how this encryption add-in would play with that if I have to recompile something I got from Qt in the first place. My application is a closed-source commercial one, and linking dynamically to Qt without changing any of the Qt sources is allowed under the LGPL. If I can hook in my own read/write routines like this at runtime, I won't have to alter and/or recompile any of the library sources. I *could* build the Qt SQLite module with SEE as a plug-in, but would rather link it statically (and the SEE people apparently want it that way, too). In this case, I would need to treat the SQLite sources the same as any of the other Qt sources. But "IANAL", so maybe I am being too cautious here. > (Note that it is supported, tested and cryptographically sound. It would > take you a long time to achieve the same.) I am not starting from scratch doing my own encryption; there are enough open source libraries publicly available which are good enough for my purposes. > If you just want whole file encryption then I'd recommend using an archive > tool and storing/extracting as appropriate. For example 7zip does this well > and is open source. Compression is not the same as encryption. The application is primarily for school teachers (single-user desktop use, which is why SQLite is ideal for this) and the database might contain stuff like confidential student reports, grades, etc. Since I am not trying to protect highly sensitive government secrets or medical data here, although someone could probably use the app for that as well, I do worry that some student might get hold of the file and try to hack it. A clever student would see right away that compression and not encryption was employed -- and a student's time usually DOES come cheap! ;) > If you really want to do your own thing then beware that the encryption key > has to be where the data is encrypted/decrypted. You should carefully study > exactly what it is you a protecting, who you are protecting it from, how > long it is protected etc. These can help: > > http://www.schneier.com/paper-attacktrees-ddj-ft.html > > If you really do still want to proceed then xRead/xWrite are an appropriate > place to do it. Thanks, this is what I needed to know. > http://en.wikipedia.org/wiki/Initialization_vector > > Anyone can design a scheme they themselves cannot break. It requires far > more skill and experience to come up with something that is actually strong. > > Also consider that what you may actually need is just some obfuscation. For > example you could just XOR the database contents with deterministic bytes. > If you did this then seeing the contents would go from costing a few dollars > (load the file into the command line shell) into a few hundred or thousand > (figure out what it is you did). In any event an attacker could always > point a gun or use a hardware keylogger if they don't want to be discovered. > That would workaround any encryption scheme. I would hash the user-supplied passphrase with a value known internally to my program to create the final encryption key, so the hacker would have to have a copy of the executable of my program in addition to whatever data was gleaned by using a key logger. And each licensed copy of my program would have a unique hash value embedded within the executable. As to the gun, Bruce Schneier already pointed out that this is one of the more expensive options in the attack tree. ;) Bob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/19/2011 04:04 PM, Simon Slavin wrote: > An alternative to the 'straight SQLite' solution would be to write your own > routine to dump to disk instead of using the backup API. I have no idea > whether this would be better or worse, It would be worse. The backup API knows how to copy from a database that is in use, so you will always get a valid output no matter what is happening to the source. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1gahUACgkQmOOfHg372QSHuwCglSO5eXwvLJSEtQk6CHNolnIm pQQAoJ/FHT7sKrUCmMHj5Ouf468/PYMt =qo/X -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/19/2011 03:40 PM, Robert Hairgrove wrote: > But before I "try this at home", I thought I would ask if there are any > caveats I should be aware of? Thanks for any helpful advice! Unless your time has no value, I'd suggest using this: http://www.hwaci.com/sw/sqlite/see.html (Note that it is supported, tested and cryptographically sound. It would take you a long time to achieve the same.) If you just want whole file encryption then I'd recommend using an archive tool and storing/extracting as appropriate. For example 7zip does this well and is open source. If you really want to do your own thing then beware that the encryption key has to be where the data is encrypted/decrypted. You should carefully study exactly what it is you a protecting, who you are protecting it from, how long it is protected etc. These can help: http://www.schneier.com/paper-attacktrees-ddj-ft.html If you really do still want to proceed then xRead/xWrite are an appropriate place to do it. However your scheme already suffers one weakness: http://en.wikipedia.org/wiki/Initialization_vector Anyone can design a scheme they themselves cannot break. It requires far more skill and experience to come up with something that is actually strong. Also consider that what you may actually need is just some obfuscation. For example you could just XOR the database contents with deterministic bytes. If you did this then seeing the contents would go from costing a few dollars (load the file into the command line shell) into a few hundred or thousand (figure out what it is you did). In any event an attacker could always point a gun or use a hardware keylogger if they don't want to be discovered. That would workaround any encryption scheme. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk1gaZ4ACgkQmOOfHg372QRiQACfRDeVyC6Z8pTSNwsIvMJLukfv knYAoLWDSilcXoxwAzJyrTTn0eU+Wo2k =Qfrv -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
On 19 Feb 2011, at 11:40pm, Robert Hairgrove wrote: > This works fine, but I would like to offer the user the option to > encrypt the database before writing it to disk. If it is encrypted, they > would need to decrypt it again when it was loaded into memory (duh!) An alternative to the 'straight SQLite' solution would be to write your own routine to dump to disk instead of using the backup API. I have no idea whether this would be better or worse, but it could be done without learning intimate details of SQLite and should be easier to adapt and modify for different platforms and database engines. It would also mean the disk version of the database was in a format that could be read by a program which didn't have SQLite compiled into it. You could start from something like 'SELECT * FROM mytable' or even using 'group_concat()' on each column. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
My application (written in C++ together with the Qt libraries) uses an in-memory database which is stored to disk using the SQLite backup API at the end of a session or at periodic intervals (i.e. auto-save functionality) and loaded from disk into the memory database at program startup. This works fine, but I would like to offer the user the option to encrypt the database before writing it to disk. If it is encrypted, they would need to decrypt it again when it was loaded into memory (duh!) After studying the SQLite sources a bit, it seems that the easiest way to do this would be to replace the function pointers of the sqlite3_vfs struct "xRead" and "xWrite" with my own functions, similar to the way we used to "bend" interrupt routines under MS-DOS -- remember those? :) -- to point to our custom interrupt handlers. I would call sqlite3_vfs_find(NULL) to get a pointer to the default VFS, then copy that to a static object and just replace those two function pointers, then register the new VFS (do I even need to do that, or can I just plug the pointer to my static sqlite3_vfs struct into the sqlite3 object whose pointer is passed to the backup API functions?) My own code would save the original pointers and use them inside the encryption and decryption routines for doing the actual disk I/O. There are open source implementations of a variety of encryption algorithms which work on fixed block sizes; i.e. if I encrypt the entire database instead of just one page at a time (as other encryption routines seem to do), it should be exactly the same size (or perhaps just a few bytes larger due to padding) as the original file. IOW, a block of 8 bytes, when encrypted, would reside at the same offset as the original data. But before I "try this at home", I thought I would ask if there are any caveats I should be aware of? Thanks for any helpful advice! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
