SQLite DBs within Document Root
This is not going to be another rant about SQLite security, but a couple of things have put me back on the war path.
I was going over the most recent PHP Magazine when I noticed a snippet of code in which a SQLite database was being opened like:
$db = sqlite_open("{$_SERVER['DOCUMENT_ROOT']}/db.sqlite");
The author probably denied direct access to his .sqlite files with something like
<filesmatch ".sqlite$"> Deny from all </filesmatch>
in his .htaccess file. However, to create the SQLite journal file the webserver needs write access to the folder in which the SQLite database is stored, so this would also mean that the document_root would need to be writeable by the webserver.
That leaves the door open for all kinds of frightening things to happen. I’m not a security expert (or an SQLite expert) so I’m wondering if there is any case where doing the above would not be considered a security risk?
10 Comments »
RSS feed for comments on this post. TrackBack URL
Keep preaching the word, Aaron. Authors should always make note of “obvious” security precautions. Glad you’re paying attention to this stuff.
SQLite, an immensely powerful and beautiful database engine for when a full database server is just not needed (such as embedding), has no security. It’s just a file. If you can get that file, you can do whatever you want with it and you have all the data contained therein.
100% with you here. I had an old teacher that used to say don’t use bad examples in the blackboard because even if you point out that they are bad examples, they still get ingrained.
It doesn’t even take a lot of mental effort to put the database in the “right” place in an example and would have made the author look like an uber-geek fi they had just taken the time to look up the FHS.
I just read your other SQLite security post. It’s sort of irrelevent about security of SQLite to say that SQLite is more or less secure than other systems. It’s all about how you use it.
I’ll use MySQL as an example, MySQL files are incredibly easy to transport, which I’d say is a great feature. For comparison, it’s trickier to transport PostgreSQL files, you have to muck with a couple internals to make them work in a different location, although it can be done too.
Once you’ve gotten your hands on some MySQL database files, it’s no effort to start the database server in no-passwords mode with those files. So it comes down to putting them in the right place and ensuring that you have done your best to configure access controls on and to your server.
I didn’t read the article, but based on the code you’ve shown, document root isn’t necessarily writable by the web server. Unless another example illustrates the creation of the database, we can only make guesses as to how that’s done.
However, I agree that examples, although contrived, could often be improved with very little effort or extra space. Bad practices are taught in at least half of all PHP articles that I’ve read, and this is a big problem.
In this case, the author could have used a path such as /db/db.sqlite. Although this provides no additional information or value to the article, it does avoid the assumptions that are associated with known paths. A reader curious about the permissions of /db can then pursue that question and be more likely to receive sound advice in return.
I’m not sure how to best educate developers about SQLite security, but driving home the fact that it carries the same risks as storing data in a file is a step in the right direction.
By the way, you have some errant backslashes in your post.
Chris, even if the webserver doesn’t create the database it needs a writeable directory to keep it’s Journal file. I have no idea if it needs to write to a Journal if it’s only doing SELECT queries.
The Zend SQLite article uses the following path:
$db = $_SERVER['DOCUMENT_ROOT'].”/../library.db”;
I think that’s a good solution, as it does stress the fact that the database is not inside the document root.
Under your assumption, Aaron, that the web app will want to write to the database, I’m trying to see why the Zend SQLite is any better, in fact, I’m wondering if it’s much worse? A parent dir, presumably one that also may hold log/ conf/ et al, is now allowing write access to the web server account.
Am I missing something?
gcomnz, good point.
As usual there’s a load of waffle about security but no practical ways to deal with. I’m getting really sick of searching for information and coming across waffle. How about some practical advice for a change ffs.
SarahBell ( ) wrote: