Safe read-only sqlite3 database

Question

I want to give my website users arbitrary read-only access to an SQLite3 database, without letting them write to the database or do any other damage. How?

Making the db file read-only helps a little, but commands like "ATTACH", ".load" and ".output" allow people to read/write other files, which may not be protected.

Of course, if I knew all such commands, I'd just filter against them, but I'm mostly worried about commands I haven't thought of.

I tried briefly to alter sqlite3's source code to disallow writes, but this is harder than it looks: even the SELECT statement appears to do some internal INSERTS/etc.

Note: I've considered DOS attacks, and will ulimit cputime to 5s or something. My main concern is damage to files/"hacking", not DOS.

chroot() may work, but seems extreme.

Thoughts?

Answer 1

Try making the sqlite database file read only.

If you need to update it:

1)block read access from users.

2)give it a write bit.

3)make your modifications.

4)then remove the write bit. 

5)Restore user access.

If you don't do this, then it would be a race condition vulnerability.

Answer 2

Of course, if I knew all such commands, I'd just filter against them, but I'm mostly worried about commands I haven't thought of.

Have you considered using a whitelist instead of a blacklist? Only allow statements that start with SELECT or EXPLAIN.

Answer 3

Assure that your user has write access and that other users (especially the user that the webserver runs as) has only read access to the file itself. How you do this of course depends on your platform (Linux, Windows, etc.)

Answer 4

Make your database file read only in the operating system. Once you've done that SQLite can't override it. If you still have issues it's not a SQLite issue. They might still be able to find a php/cgi/etc issue but that's the nature of the security beast.