I am building a website for the company I work for, for which the front-end has been done. Currently the site populates itself from information read off a JSON file.
I will be leaving this job soon, and want to leave the site so that other employees will be able to update it without needing to know how to read and write JSON data properly. So I am writing a VERY simple site to do the updates (list the projects currently on the live site, lets users add a project, delete a project or reorder projects.) The data is all text, and I am doing some basic sanitizing in JS before calling the server to write. What's more, I am using .htaccess to require employees to log in to even get to this part of the site.
So now I am to the point where I need an actually database to write to. As our website is run off of an OSX server, we already had SQLite3 installed. It seemed from what I read here on StackOverflow that SQLite works just fine as a live database, as long as I dont need UN/PW access, or the need to scale up to 100k+ hits. (We are a small company - we will be getting 2-3 hits a day).
I starting setting up a PHP page to connect to the database and write to it, and after a silent failure, realized that the .DB file needed RW access for group in order for the website to access it. This would mean a chmod to at least 444, if not 777, for the db and the directory holding it (I assume). I asked my boss, who handles server admin, about setting this up, and he said he was not comfortable doing this. And while I understand his concern, I am not sure what else I am supposed to do. Without RW access on the file, I don't know any other means to make this work.
So what are my options? Should I abandon SQLite and just install MySQL? And if I do stick with SQLite, what are best practices for security? Is there some accepted best way to set it up the batabase / encrypt data / hide the database?
Thanks!
What you may be confusing here is UNIX/Linux file permissions with web accessibility.
In *NIX, every file belongs to an owner and a group. Every process on the machine, including the user on a terminal, is running under a user which belongs to a group (or groups). That is what file permissions are about. They control which process can access which files. The user may have direct permissions for a file (user permissions), indirect permissions through his group membership or, if nothing else, other permissions.
This all has nothing to do with a web user. The web server is a process listening to incoming HTTP requests on port 80 (or elsewhere). It then decides how to respond to these requests. At no point does this kind of do-I-respond-or-don't-I have anything to do with file permissions. The web server has its own rules for which requests to allow and which to deny, which have nothing to do with file permissions.
If the web server is trying to access some file on disk, then file permissions play a role. The web server is running as some user belonging to some group, typically www-data. That means the web server can read, write or execute files that it has permissions to. If the web server does not have read permissions for a file, it won't be able to serve it. But just because the web server has write permissions to a file does not mean in any way that a website visitor can write to that file in any way, because the web server won't let them. The web server serves web request, it doesn't offer direct file access to random users.
The typical setup is that the web server is running as www-data. An incoming request causes the web server to start a PHP script, which also runs as child process with www-data permissions. That PHP process reads or writes files; it can only read and write files with the permissions of www-data and you control what the script reads or writes.
If you want to use a SQLite database, something will have to have permissions to that file. It's true that you typically want to separate permissions as much as possible. But in this case you need to give the www-data user (or whatever your PHP is running as) permissions to that file. So what? That's not a security flaw per se. The worst thing that could happen is that an attacker who gets permissions of www-data will be able to write to the database. But before that he'll have to have circumvented a whole lot of other security mechanisms, so that should be the least of your worries.