Redirecting or showing message alert box when using parameterized query with DataReader

Question

I had this code that displays record from database using DataAdapter, and I made it in such a way that when any user tries to go to the page directly by inserting the page address directly in the address bar of the browser, it will show a message box telling the user that the Session has timed out.

But in order to avoid SQL injection attack, I changed the display and decided to use DataReader and parameterized query. After I changed and used parameterized query with DataReader, I tried to navigate directly to the page by right clicking the page and viewing through the browser then I got this error:

Server Error in '/' Application.

Error:The parameterized query '(@passname nvarchar(4000))SELECT * FROM DetailTable WHERE passname' expects the parameter '@passname', which was not supplied. 

This is the initial code I had

public void Details()
        {
            try
            {
                if (Session["index"] != null)
                {
                    string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(connectionString))
                    {
                        con.Open();
                        SqlCommand cmd = new SqlCommand
                        {
                            CommandText = "SELECT * FROM DetailTable WHERE passname = '" + Session["index"] + "'",
                            Connection = con
                        };
                        SqlDataAdapter sda = new SqlDataAdapter();
                        DataSet ds = new DataSet();
                        sda.SelectCommand = cmd;
                        sda.Fill(ds, "detail");
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            orgName.Text = ds.Tables[0].Rows[0]["Name"].ToString();
                            title.Text = ds.Tables[0].Rows[0]["passname"].ToString();
                            datelbl.Text = ds.Tables[0].Rows[0]["Dated"].ToString();
                            timelbl.Text = ds.Tables[0].Rows[0]["timed"].ToString();
                            venuelbl.Text = ds.Tables[0].Rows[0]["venue"].ToString();
 
                            string eventname = ds.Tables[0].Rows[0]["Passtype"].ToString().Trim().ToUpper();
                            if (eventname == "VISITOR")
                            {
                                datelbl.Visible = false;
                                timelbl.Visible = false;
                            }
                            else if (eventname == "RESIDENT")
                            {
                                datelbl.Visible = false;
                                timelbl.Visible = false;
                            }
                            else if (eventname == "OTHERS")
                            {
                                datelbl.Visible = false;
                                timelbl.Visible = false;
                            }
                        }
                        else
                        {
                            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Record not found');", true);
                        }
                    }
                }
                else
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Timed Out');", true);
                }
            }
            catch (SqlException ex)
            {
                string msg = "Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
        }

This is the updated code using parameterized query and DataReader.

public void Details()
{
    try
    {
            string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT * FROM DetailTable WHERE passname = @passname", con))
                {
                    cmd.Parameters.AddWithValue("@passname", Session["index"]);
                    con.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.Read())
                    {
                    orgName.Text = dr["Name"].ToString();
                    title.Text = dr["passname"].ToString();
                    datelbl.Text = dr["Dated"] == DBNull.Value ? (string)null : Convert.ToDateTime(dr["Dated"]).ToLocalTime().ToString("MMM d, yyyy");
                    timelbl.Text = dr["timed"] == DBNull.Value ? (string)null : Convert.ToDateTime(dr["timed"]).ToLocalTime().ToString("hh:mm tt");
                    venuelbl.Text = dr["venue"].ToString();
                     
                    string eventname = dr["Passtype"].ToString().Trim().ToUpper();
                        if (eventname == "VISITOR")
                        {
                            datelbl.Visible = false;
                            timelbl.Visible = false;
                        }
                        else if (eventname == "RESIDENT")
                        {
                            datelbl.Visible = false;
                            timelbl.Visible = false;
                        }
                        else if (eventname == "OTHERS")
                        {
                            datelbl.Visible = false;
                            timelbl.Visible = false;
                        }
                    }
                    con.Close();
                }
            }
    }
    catch (SqlException ex)
    {
        string msg = "Error:";
        msg += ex.Message;
        throw new Exception(msg);
    }
}

Do I get to check this in the page load event, and not in the details display code, so that it will redirect to another page or show message alert box?

Like this

protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["index"] == null)
            {
                ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Timed Out');", true);
Response.Redirect("homePage.aspx");
            }
            else
            {
                Details();
            }
        }

When you take a look at the first code, you will see that the declaration is done inside the details display code

 if (Session["index"] != null)
                {

}

But in the updated code it did not work, so I had to remove the "(Session["index"] != null) { } and put it in the page load event and it worked but I don't know if this is the correct thing to do.

protected void Page_Load(object sender, EventArgs e)

        {

            if (Session["index"] == null)

            {

                ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Timed Out');", true);

Response.Redirect("homePage.aspx");

            }

            else

            {

                Details();

            }

        }

Answer 1

I'm still very confused.... Your code registers a script on the current page but rather than returning this page the code redirects to homePage.aspx. I can't see homePage.aspx but I'm pretty sure homePage.aspx does not register the same "alert" script.

protected void Page_Load(object sender, EventArgs e)
{
    if (Session["index"] == null)
    {
        ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Timed Out');", true);
        Response.Redirect("homePage.aspx");
    }
    else
    {
        Details();
    }
}

I question how the code that sets the passname parameter is being reached when Session["index"] is null. Maybe you should check for null or empty?

Set a breakpoint in key locations throughout the code. Then single-step through each line of code using the Visual Studio debugger. The goal is to figure out where the code you wrote stops functioning as you intended. When you find that line or lines of code then you found the bug.

The other useful debugging tool is the local and watch windows which shows the value of variables as you execute each line of code while single stepping.

First look at the Visual Studio Debugger

Answer 2

In checking if Session is empty or null, this is what I did in the page load event. and it works, but what I don't know is if it is the correct thing or if it is the right thing to do, putting it in the page load event

 protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["index"] == null)
            {
                Response.Redirect("Default.aspx");
            }
            else
            {
                Details();
            }
        }