Hi,

I'm not sure if my question is related to SQLite specifically but I would like to hear some ideas about this.

I currently implemented a series of API endpoints to access some data from an SQLite table. However, I need to add another endpoint to allow users to query via sending SQL queries directly. This means users can construct any SQL queries to the endpoint then the API will return the results.

My question is how can I make sure the users cannot pass some dangerous queries(for example, DROP)? The endpoint should only be used for read-only purposes. My initial idea is to only allow the query that starts with SELECT and not allow multiple queries in one SQL query line.

I never implemented anything like this before so is there any framework or library I can use to achieve this goal? I don't want to overdesign my API implementations to make it too complicated.

The current implementation language is Python and the WSGI framework is Flask.

Thanks.

What you need is the Authorizer API:
sqlite.org/c3ref/set_authorizer.html

The Authorizer has many more uses than just preventing database changes. It can also be use to limit viewing of sensitive database content, or perhaps specific tables or sensitive columns within tables - it can get very specific - as can be seen in the data for your second question.

As to answering the second question - that's the database. In your case it is "main". It's always main for the file you open at the start of the connection, but can be different for the TEMP database or any attached database schema.

See sqlite.org/lang_attach.html which also nicely explains, among other things, the use of "main" and "temp".

sorry there was a typo in the paste so it was not working, after I fixed the typo it's working.

A couple thoughts:

Check if your python sqlite driver supports setting an authorizer and use that to allow only select queries.

You might also want to check the interrupt API so you can stop long running queries from bringing your API to a halt.

It is always dangerous to accept arbitrary input directly from the user, keep that in mind as you think of different ways they can break your backend

Whatever other clever solutions you and others come up with, you can open the database connection as read-only (eg with the file: API) if the endpoint shouldn't ever be able to alter the database.

What counts as "dangerous" radically depends on who you are allowing to access the API and what the purpose of the database is, but allowing raw SQL to be entered by a user is a terrible idea if you require any sort of privacy or controlled alteration of data.

Read-only should be fairly bombproof though, if that is sufficient and required.

Other people have covered features built into SQLite well. My knowledge is more general-purpose.

First, you can include in your server-side API code a check to see that the command starts with SELECT . This is highly effective !

Second, if your are implementing a remote access API which you want to protect, there are some techniques you may want to use.

1. Pass along a checksum as a parameter of the query. Your endpoint takes the SQL command it needs and uses an algorithm to calculate a checksum, using a well known checksum technique. The server checks to see that the SQL command fits the checksum.
2. Encrypt the SQL command. Or encrypt the checksum. Or both.
3. Incorporate the SQL command and checksum into the same encrypted parameter. This is more difficult for an attacker to understand.

How effective these are depends on how easy/likely it is for an enemy to launch an interception and man-in-the-middle attacks.

Other protection techniques include checking the originating IP addresses of any requests. You may have an idea about where your 'trusted' endpoints are.

Second, if your are implementing a remote access API which you want to protect, there are some techniques you may want to use.

1. Pass along a checksum as a parameter of the query. Your endpoint takes the SQL command it needs and uses an algorithm to calculate a checksum, using a well known checksum technique. The server checks to see that the SQL command fits the checksum.
2. Encrypt the SQL command. Or encrypt the checksum. Or both.
3. Incorporate the SQL command and checksum into the same encrypted parameter. This is more difficult for an attacker to understand.

Or run inside HTTPS with standard session-binding and a CSRF token. This is a problem that's had extensive effort put into solving it; reinventing it all yourself is a lot of work. (Proper encrypted channels are better than checksumming of requests.)

First, you can include in your server-side API code a check to see that the command starts with SELECT . This is highly effective !

If and only if the script/program checks that it is a single command (not multiple commands concatenated)

You may also want to have a look at a progress callback, e.g.

https://www.sqlite.org/c3ref/progress_handler.html

I imagine it would be possible for a malicious user to send you a select that enters an infinite loop if you are attempting to consume the entire result set in the response. So you would probably want to implement a progress handler to abort very long queries.

But if it were me, I would never expose sqlite to untrusted input.

Simple open the connection to the file with the FILE being readonly. That is, use a URL filename of the form file:<filename>?mode=ro.

Then simply send the statement to be executed and return the result.

Nothing complicated at all.

Should take less than 15 seconds to write.

That's a good idea. Someone could have posted that earlier.

As soon as something is public-facing, you have to imagine every way it can be broken.

Are you going to limit the API requests? As soon as someone finds out your API works, they'll script it. So at work we use someones API to retrieve details of hotel x. x is numeric, so its easy to create a loop which retrieves all hotels from 0 to 9999999. Great for us, not for them.

Never assume you can limit on their IP address. This can be spoofed, VPN'ed, etc. Much better to let them have to come in on a predefined key (usually a UUID). This also gives you easy way to knobble individual users from using your API.

Log everything. Twice. OK, overkill, but log so that you can spot the users passing wacky APIs, or 8000 requests a minute. And don't just log and archive the logs. Go through them.

If you're giving people 'raw' access to the database, are there any tables in that you don't want them to see? Any secure information? Don't assume they can't see those table names.

Great for us, not for them.

Now why on earth would you think that? The purpose for which "them" exists at all is to provide information to "you" via their API (thus, without "you" making the requests that you do, "they" would not exist any longer -- "they" would be bankrupt).

If your behaviour were annoying to "them", then "them" would put "a stop" to it.

The implementation of "a stop" may range from mere rate limiting to dispatching an assassin, as may be appropriate in the circumstances.