Dangers of SQLite Ignorance
Note: If you came here looking for a commercial solution to encrypt SQLite Databases, head over to the official SQLite professional Support page.
We return to regular programming…
As discouraged as using register_globals is, most people who write about PHP will never admit that using register_globals is in itself is a security risk. Sloppy coding, coupled with register_globals being turned on can create security problems. Because of this, register_globals was turned off by default. A wise decision.
SQLite could very easily fall into the same category as register_globals. [Note: the previous statement is bullocks… keep moving]
I’ve been going through the Zend Contest Entries and have been very surprised to see that only one of the five software packages that I have reviewed so far which used SQLite, does anything at all to protect their database. Due to the fact that many of these packages use SQLite because of the “no-installation-necessary” factor, it is very unlikely that the end user will bother securing the database themselves.
In a few months I wonder what goodies we’ll be able to find when searching in google for “This file contains an SQLite 2.1 database”. We already have a few hits.
There was recently a long discussion about SQLite security on PHP Internals mailing list, the general consensus was that it was the responsibility of the author of the software to make sure that the security of SQLite databases is maintained. It is also the responsibility of the person installing the package to secure by means of a .htaccess or other protection at webserver/filesystem level.
I realize the futility of this rant, but can’t help but think that there has gotta be a better way.
September 22nd, 2004 at 3:53 pm
It would be good if there was a common configuration rule in apache’s configuration files that denies UNIX “hidden” files.
Every file that starts with a “.” would not be accessible from the web.
This way the developped would not have to care about the env, give a .htaccess (it’s not enabled on all hosts). Ask for a “out-of-the-web” path, … just create .files
September 22nd, 2004 at 4:13 pm
Aaron, is there a list of best security practices regarding SQLite files? Having the specifics on the web in a blog entry or dedicated page would be very helpful. (I don’t use SQLite myself.)
September 22nd, 2004 at 4:30 pm
Paul, I haven’t seen any best practice page. Common sense is generally encouraged.
In the Zend SQLite tutorial (which is a must-read for all aspiring SQLite users), they suggest that you use the following syntax to open a database:
$db = $_SERVER['DOCUMENT_ROOT']."/../database.sqlite";
$handle = sqlite_open($db) or die("Could not open database");
That makes a lot of sense, and is a simple way to ensure that the damn thing is outside your DocumentRoot.
If you HAVE to put it in one of your web accessible directories just drop the following in a .htaccess folder in the same directory
<FilesMatch ".sqlite$">
Deny from all
</FilesMatch>
Ilia had a very cool tip in this post where he mentions of renaming your database to use the .php extention, and then naming the first table ‘<?php ‘ which will cause PHP to generate a parse error, rather than allow the file’s download. Kludgy, but cute!
September 22nd, 2004 at 6:02 pm
I think this whole issue with SQLite security is totally overblown. SQLite tables have the same security risks as text files, and I don’t see anyone writing blog entries about them.
Yes, I agree, you shouldn’t create SQLite files in your document root, but is it as dangerous as register_globals? I don’t think so.
September 22nd, 2004 at 6:21 pm
Ditto for what Adam said. I also don’t see people getting up in arms about editing files directly in your document root with an editor that make automatic backup copies (ala emacs’ file.php~ or #file.php#) — another way to expose raw data to users.
September 22nd, 2004 at 10:03 pm
This isn’t an SQLite-specific problem, we had this problem all the time although in a different look, remember those .inc, .class and .conf files? Nobody used to pay attention to those and they became a serious security issue on critical sites. The solutions however are limited, encrypt the SQLite file, put it outside DocumentRoot and/or deny access to it with .htaccess, how much work can that be?
But thank you Aaron for pointing it out, sometimes even the obvious must be noted.
September 22nd, 2004 at 10:03 pm
Now that you mention it, I’ll have to agree that the association between register_globals and SQLite is not accurate, since an exposed database isn’t likely to be exploitable.
If when looking over the Zend contest entries I found that four out of five entries in stored their data in a file called ‘config.txt’, I would be just as concerned.
My point is not that the security of SQLite needs to be enhanced, I very much like the way SQLite works. Rather, implementers of SQLite databases should be aware of the security implications and use common sense.
September 23rd, 2004 at 4:13 pm
Thanks for the reminding us all. It’s easy to forget these things.
Linked here
September 24th, 2004 at 3:29 am
SQLite security
Aaron Wormus has posted pretty negative entry on his weblog about SQLite security. A SQLite database is a regular file and like any file it can be accessed trough the webserver. There has been quite an uproar about this issue and many experienced coders f
September 25th, 2004 at 2:48 am
Well, time for a shameless plug
I just released a new version of DB_Sqlite_Tools
http://pear.php.net/package/DB_Sqlite_Tools/ which now supports the insertion and retrieval “on the fly” of encrypted data to/from an Sqlite database. I have also posted a sample with the relevant live demo, and encrypted db online on the package homepage at http://sqlite.biz
September 28th, 2004 at 6:46 pm
Dangers of SQLite Ignorance
Aaron doesn’t explicitly list the dangers but I can guess that they include overwriting db files. I haven’t looked at SQLite yet however, it’s on the TODO list!
February 4th, 2005 at 6:57 pm
Hi there,
I just get SQLite 3.0.8 and put it running at a Windows 2000 Pro workstation, intended to test him togheter PHP 5.0.3.
I created (via prompt) a database, but when trying to see him at web, via PHP, i receive the not well explain message bellow:
“file is encrypted or is not a database”
Even using OO or structural programming technique.
What mess is going on here?
Thanks!
March 6th, 2005 at 4:10 pm
[…] Feature Story SQLite DBs within Document Root This is not going to be another rant about SQLite security, but a couple of things have p […]
March 25th, 2005 at 4:20 pm
.htaccess files…
i’ve never heard of them before (yeah, rain on me if you will) but now i love them… *does a little moogle dance*
June 2nd, 2005 at 3:01 pm
Hello, people
Why do things always have to get so complicated ? I mean, come on, don’t you people see any simple solution to this ? I discovered SQLite quite recently and I made a complete rewrite of a program of mine which keeps record of all the software on all CD/DVD’s and all boxes and everything. I find this SQLite definitely GREAT !!!
This way I could finally drop the Microfost Visual FoxShit which I had used before for the program, and also give up the unnecessary auxiliary Windoze which, by the way, sucks a lot.
Is it that complicated to make some simple procedure which encrypts/decrypts data at insertion/extraction from the database, so that the data itself is encrypted, not the database itself ? I don’t think so.
The beauty of this approach is that every user can make his own procedure with his own key(s), instead of some unique method, easy to crack by the curious ones who make our lives harder.
In the other hand, come on, people !!!! LET’S BE HONEST !! IF YOU REALLY WANT SECURITY, DIDN’T YOU EVER HEAR OF POSTGRESQL OR SMTH ELSE ?
I don’t need any security for a program like mine, which I don’t think will present any interest at all for some cracker/hacker to crack/hack. If I needed any, I think I’d use Postgres or something else. And let’s be honest, of the few hundred lines my program has (I wrote it in Tcl/Tk) barely 20-30 would be more than enough to put up an encryption/decryption procedure to store encrypted data.
August 18th, 2005 at 4:23 pm
online video poker game
Take your time to check out some relevant pages on Play Poker Online for Free
May 13th, 2006 at 8:57 am
i lay there like a piece of meat, being seen to by a butcher with seven and a half fingers left.
May 17th, 2006 at 7:37 pm
betting online sports…
wrinkle curries silencers dickens nakedness nebulous?Dewey odds calculator http://odds-calculator.extra-sport-betting.com/ …
October 13th, 2006 at 2:48 pm
…
bulldogging…
October 24th, 2006 at 10:17 am
[…] Aaron Wormus worries about the embedded database SQLite and bad security practises (Hint: Keep the SQLite database file OUT of the web root) […]
January 19th, 2007 at 1:15 am
christian definition name…
gimmemoney 526486 News about christian definition name….
June 29th, 2007 at 8:42 pm
dell online store…
Hi. Thanks for the good read….
July 31st, 2007 at 7:08 pm
…
haha nice one! :p…
October 30th, 2007 at 12:18 pm
wager backpack…
revokes Pabst trainee …
January 17th, 2008 at 1:28 pm
shop for car insurance online…
synthesizer:mistakable Bundestag requests.refrigerator …