check for read only query string

Question

i am creating a postgreSQL database reader which also includes a method for a user to type 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 check for a edit-safe query or is there an other, more reliable way to achieve this?

i know about granting rights to users but that is not working because i don't have a super-user account.

Answer 1

You should handle this by using an account with only read access the the database, not by checking the query. Most DBMSs have a privilege mechanism to handle this kind of thing, and PostgreSQL certainly does.

Answer 2

Granting users to type their own queries in never a good idea. The only safe way to do it is to grant them read-only rights (which you said you are unable to do).

Regarding your code snippet, a better way to check if you query contains a verb is using 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 consider using this for SQL sanitizing.

Answer 3

There is no reliable, general way of checking whether given query is changing database contents, basing only on query text.

Consider this query:

SELECT * FROM myview;

Where myview is defined as follows:

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