Prevent Non-SELECT SQL Statements

Question

I'm allowing users to input their own SQL statement to execute, but only if it's a SELECT statement. Is there a way to detect if the SQL statement is anything but this, i.e. an ALTER, INSERT, DROP, etc.? I'll worry about other concerns such as a query locking up a table and such later, but this is more of a proof of concept right now. I can restrict the service account on the server running the app to have read-only rights on the db, but I'm interested in seeing it handled in the app.

This is my approach to it by detecting the first word of the query, but this seems vulnerable. Is there a cleaner way to do this detection?

public void ExecuteQuery(string connectionString, int id)
{
    //The SQL statement will be user input
    var sql = "SELECT ColumnA, ColumnB, ColumnC FROM MyTable where MyTableId = @Id";
    var split = sql.Split(' ');

    if (split[0].ToUpper() != "SELECT") Console.WriteLine("Only use a SELECT statement.");
    else
    {
        using (var connection = new SqlConnection(connectionString))
        using (var cmd = new SqlCommand(sql, connection))
        {
            cmd.Parameters.AddWithValue("@Id", SqlDbType.Int);
            cmd.Parameters["@Id"].Value = id;
            connection.Open();

            var reader = cmd.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    Console.WriteLine($"{reader["ColumnA"]}, {reader["ColumnB"]}, 
                                       {reader["ColumnC"]}");
                }
            }
            finally
            {
                reader.Close();
            }

            cmd.ExecuteNonQuery();
        }
    }
}

Answer 1

I would scrap any attempt at trying to manage this in application code. Instead use SQL Server.

A good way to manage things is to do the following.

1. Create a New Schema.
2. Create a Database Role, the role should own the schema.
3. Create Views over the tables you want to expose even if they are 1:1 they should be in the Schema.
4. Add the service account to the Role.

The service account can only SELECT from the view and you have absolute control of what you are exposing and what data they can SELECT.

This is easily searchable / auditable even for other developers and DBA's. It also gives you a great view of the impact of any schema changes.