0

I have a small program that let students test their SQL queries built in ASP.Net and using an SQL Server Database. The problem is that some queries "hang" the console and SQL Server. With that in mind, I want to limit the execution time to 10s or throw an exception.

Right now the code looks something like this:

        SqlCommand cmd = new SqlCommand();

        cmd.CommandText = TextBox1.Text; //Their query
        cmd.CommandType = CommandType.Text;
        cmd.CommandTimeout = 1;
        cmd.Connection = sqlConnection1;

        sqlConnection1.Open();
        try
        {
            GridView1.DataSource = cmd.ExecuteReader();
            GridView1.DataBind();
            int count = GridView1.Rows.Count;
            Label1.Text = count.ToString() + " Rows";
        }
        catch (Exception ex)
        {
            Label1.Text = ex.Message;
            GridView1.DataSource = null;
            GridView1.DataBind();
        }
        finally
        {
            sqlConnection1.Close();
        }

CommandtTimeout is not doing it for me because often times the problem is that the queries are continually streaming rows (Mainly due to a cartesian product operation).

Is there a way to stop the query mid execution without looping through every row?

Aufban
  • 33
  • 8
  • 1
    I hope you _**really**_ trust your users. This isn't just normal SQL injection, this is explicit SQL injection. Have you looked at `SqlConnection.​Connection​Timeout`? – Flydog57 Nov 21 '18 at 19:13
  • this is on a virtual machine running a training database and the user can only do select statements. `SqlConnection.​Connection​Timeout` does not solve the problem since they try cartesian product queries and SQL Server serves rows. slowly but surely. – Aufban Nov 21 '18 at 19:40
  • Please note that ASP is different from ASP.Net. –  Nov 21 '18 at 20:15

2 Answers2

1

You can use threads for checking the timeout of the code. 

//Variables
string input;

//Code to run until time exceeds
Thread thread = new Thread(() => {
    input = Console.ReadLine();
});
thread.Start();

//Check if time exceeded
if (!thread.Join(TimeSpan.FromSeconds(10)))
{
    thread.Abort();
    throw new Exception("Time exceeded.");
}
kara
  • 130
  • 1
  • 11
  • Please never call `Thread.Abort()`. Even if you handle any exceptions it can still corrupt the run-time meaning that the remaining threads may not function correctly. It should only be used when forcing an application to close. – Enigmativity Dec 02 '18 at 05:37
0

Instead of using GridView1.DataSource = cmd.ExecuteReader(); and GridView1.DataBind();, you're going to have to write the code to keep reading the DataReader until you determine that it has taken too long or too many rows have been read already.

slolife
  • 19,520
  • 20
  • 78
  • 121