CODEWITHSUNDEEP
securitysqlite
user354134
user354134

Reputation:

Safe read-only sqlite3 database

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?

EDIT : Wow, did I really ask this 3+ years ago?

Since then, I've actually written a program to do this.

which I think is reasonably secure (but I could be wrong).

Here is a sample query.

Upvotes: 7

Views: 2146

Answers (4)

Michael
Michael

Reputation: 9402

You haven't mentioned how you are providing access to the SQLite database.

If you are doing so through the C API (e.g. writing a CGI in C that takes a raw SQL query, passes it to sqlite, and then returns whatever was returned), then the dot commands like ".load" are of no concern. These are implemented by the sqlite3 shell program, and will not work when calling the C API functions directly.

In this case you can call sqlite3_open_v2 passing SQLITE_OPEN_READONLY as one of the flags to prevent the database from being written.

The ATTACH command can be disabled by calling sqlite3_limit() to set SQLITE_LIMIT_ATTACHED to 1 to prevent attaching a second database from succeeding. Since the DETACH statement "detaches an additional database connection previously attached using the ATTACH statement" it sounds like this would prevent one from detaching the original database in order to bypass this restriction.

As far as I can tell from looking at the SQL understood by SQLite, this should close up all the holes. You may wish to run through the pragmas with a fine-tooth comb just to make sure, if there is anything I missed let me know and I'll update this answer.

Upvotes: 1

Jay
Jay

Reputation: 14471

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.

Upvotes: 0

Pete
Pete

Reputation: 419

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.)

Upvotes: 0

dan04
dan04

Reputation: 91199

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.

Upvotes: 1

Related Questions