Check read-only query string

Question

I am creating a postgreSQL database reader, which also includes a way for the user to enter their own query. I want to protect the database by checking if the typed query contains any modifying code. this is my check:

private bool chech_unwanted_text(string query) { if (query.Contains("DELETE") || query.Contains("delete") || query.Contains("CREATE") || query.Contains("create") || query.Contains("COPY") || query.Contains("copy") || query.Contains("INSERT") || query.Contains("insert") || query.Contains("DROP") || query.Contains("drop") || query.Contains("UPDATE") || query.Contains("update") || query.Contains("ALTER") || query.Contains("alter")) { return false; } else return true; } 

Is this the right method to test a secure request or is there another, more reliable way to achieve this?

I know about granting rights to users, but this does not work because I do not have a superuser account.

Answer 1

You must deal with this using an account with access only to read the database, and not by checking the request. Most DBMSs have a privilege mechanism to handle these kinds of things, and PostgreSQL certainly does.

Answer 2

Allowing users to enter their own queries will never be a good idea. The only safe way to do this is to give them read rights (which you said you couldn't do).

As for your code snippet, the best way to check if you contain a query, the verb uses LINQ:

 private readonly string[] verbs = new string[] { "delete", "create", "insert", ... }; private bool check_unwanted_text(string query) { // convert to lowercase query = query.ToLowerInvariant(); // can any verb be found in query? return verbs.Any(v => query.Contains(v)); } 

But no, I would not use this for SQL sanitation.

Answer 3

There is no reliable general way to check whether a given query modifies the contents of a database based solely on the text of the query.

Consider this query:

 SELECT * FROM myview; 

Where myview is defined as follows:

 CREATE VIEW myview AS select foo, bar FROM myfunc(); 

Answer 4

If you cannot make the account read-only, you can still make the specific transaction read-only by issuing the SET TRANSACTION READ ONLY SQL command immediately after the start of the transaction. Then any attempt to modify the data in this transaction will fail with

ERROR: transaction is read only

Answer 5

Look at this a little below: Case insensitive "Contains (string)"

Basically make the case insensitive (otherwise you will need a lot of code to check for different versions of DeLetE (DELEte, etc.)

Also, can you create a list with forbidden keywords and skip it, maybe a little cleaner?

What happens if a user needs to request something with these keywords?

 SELECT IsDeleted FROM Users; 

Maybe you can look at the parameters or create your own "builder request" depending on the complexity of the requests (a bunch of drop-down lists based on the data in the database?).

A few suggestions.

But you should, if possible, follow read-only user recommendations.