SQLite query restrictions

Question

I am building a little interface where I would like users to be able to write out their entire sql statement and then see the data that is returned. However, I don't want a user to be able to do anything funny ie delete from user_table;. Actually, the only thing I would like users to be able to do is to run select statements. I know there aren't specific users for SQLite, so I am thinking what I am going to have to do, is have a set of rules that reject certain queries. Maybe a regex string or something (regex scares me a little bit). Any ideas on how to accomplish this?

def input_is_safe(input):
    input = input.lower()
    if "select" not in input:
        return False
    #more stuff
    return True

score 3 · Accepted Answer · edited May 23 '17 at 12:24

3

I can suggest a different approach to your problem. You can restrict the access to your database as read-only. That way even when the users try to execute delete/update queries they will not be able to damage your data.

Here is the answer for Python on how to open a read-only connection:

db = sqlite3.connect('file:/path/to/database?mode=ro', uri=True)

edited May 23 '17 at 12:24

Community

1
1

answered Dec 12 '16 at 22:33

cha

10,301
1
18
26

This is much better than keeping 2 database copies and automatically copying one database to the other every time someone gets through my castle wall. Props for thinking out of the box. Thanks. – Chase Roberts Dec 12 '16 at 23:23
Also [PRAGMA query_only = ON](http://www.sqlite.org/pragma.html#pragma_query_only). – CL. Dec 13 '16 at 08:57
1

@CL, that's very good to know about. But how does the OP make sure nobody sneaks `PRAGMA query_only = OFF; DROP TABLE users` into the query? – alexis Dec 13 '16 at 13:06

score 0 · Answer 2 · answered Dec 12 '16 at 22:21

0

Python's sqlite3 execute() method will only execute a single SQL statement, so if you ensure that all statements start with the SELECT keyword, you are reasonably protected from dumb stuff like SELECT 1; DROP TABLE USERS. But you should check sqlite's SQL syntax to ensure there is no way to embed a data definition or data modification statement as a subquery.

My personal opinion is that if "regex scares you a little bit", you might as well just put your computer in a box and mail it off to <stereotypical country of hackers>. Letting untrusted users write SQL code is playing with fire, and you need to know what you're doing or you'll get fried.

answered Dec 12 '16 at 22:21

alexis

48,685
16
101
161

I'm not saying that I can't or won't write regex expressions, it's just that I don't feel confident doing it. – Chase Roberts Dec 12 '16 at 22:26
Gotcha. And I'm not saying you really need regexps in particular-- just that you need to be careful and really know what you're getting into. – alexis Dec 12 '16 at 22:44

score 0 · Answer 3 · answered Dec 13 '16 at 13:18

Open the database as read only, to prevent any changes.
Many statements, such as PRAGMA or ATTACH, can be dangerous. Use an authorizer callback (C docs) to allow only SELECTs.
Queries can run for a long time, or generate a large amount of data. Use a progress handler to abort queries that run for too long.

SQLite query restrictions

3 Answers3

Linked

Related