Most supported way to protect flatfiles (sqlite db) from HTTP access?

Question

I am developing a PHP application that uses SQLite as database management system, MySQL and PostgreSQL etc. is not an alternative (although I would really like to use pgsql), because I want the setup to be very beginner-friendly and zero-headache alike. Now many people use a shared hosting, and alot of them only offer direct FTP access to the htdocs-directory, but not above that. That means the customers would have to put the SQLite-Database-File inside their htdocs, meaning that it is accessible to the world and anyone can download it.

What is the best way to give the customer some kind of protection from that, that is simple and also supported on all HTTP servers?

Answer 1

You can use a .htaccess file and lock down the database name so it's not externally accessible. Many servers will already do this for all filenames starting with a ".". This will work on Apache of course, but you'll still need fixes for other web servers.

Your best bet is to really set it up such that it can be hosted above htdocs. Maybe you can use an installation script that checks the document root and moves the sqlite file to a higher directory if possible.

Unfortunately, because sqlite is read and written to by the same user as the web server, there's no easy way to lock it down from external access. Hiding it and moving it are the only real solutions I can think of.

Answer 2

If it would be Apache web server then use .htaccess file. I'm not sure how to do it on IIS.

I guess as first thing you should put empty index.html file or equivalent to prevent listing directory in browser. That won't prevent to access that file if the name and path is known but it's better than nothing if you not sure what web server it's going to be on.

Answer 3

By default (for Linux installations that I've run across), Apache actually blocks anything that begins with .ht from being directly served:

<Files ~ "^\.ht">
    Order allow,deny
    Deny from all
</Files>

If you'd rather use a filename like sqlite.db for your database file, then you could block it by using this rule:

<Files ~ "\.db$">
    Order allow,deny
    Deny from all
</Files>

Of course, to implement this rule on most hosting providers, you will have to distribute a .htaccess file containing the rule. Those can be missed sometimes because they don't show up in *NIIX directory listings (or in an FTP client) by default.

I'm not aware of any similar type of rule that can be used for the Microsoft IIS server in a shared hosted environment.

Answer 4

There is no protection which will work on all HTTP server software being used. The best you can do is to move it outside the document root as regardless which web server is being used it should never be served.

Actually protecting files from being served inside the document root requires knowledge about the server software and changing its configuration.