Error in gridview trying to show data of only the current user

Question

The C# code:

string str = "Data Source=(LocalDB)\\MSSQLLocalDB;";
str += "AttachDbFilename=|DataDirectory|DinoData.mdf;";
str += "Integrated Security= True";

SqlConnection c;
c = new SqlConnection(str);

if (Show.Text == "all" || Show.Text == "All" || Show.Text == "all table" || Show.Text == "All table" || Show.Text == "All Table" || string.IsNullOrWhiteSpace(Show.Text))
{
    DataTable dt = new DataTable();

    String req;
    req = "SELECT * FROM [User] Where Username = "+Session["CurentUserid"];

    SqlDataAdapter da = new SqlDataAdapter(req, c);
    da.Fill(dt);

    datagrid.DataSource = dt;
    datagrid.DataBind();
}
else
{
    if (!string.IsNullOrWhiteSpace(Show.Text))
    {
        DataTable dt = new DataTable();

        String req;
        req = Show.Text+ " Where Username = " + Session["CurentUserid"];

        SqlDataAdapter da = new SqlDataAdapter(req, c);
        da.Fill(dt);

        datagrid.DataSource = dt;
        datagrid.DataBind();
    }
}

The error:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Invalid column name 'Niss'.

Please help, Niss is the id of one of my users

where is this error occurring? By the way you could change all your conditions in the first `if` to `Show.Text.ToLower().Contains("all")` — techspider, Jun 02 '16 at 20:52
I bet the username stored in the session variable is Niss, right? — Steve, Jun 02 '16 at 20:53

score 1 · Accepted Answer · edited May 23 '17 at 10:29

This is a classical error caused by not using a parameterized query. You have forgotten to put the username value between single quotes so your Niss user has been confused as a column name

req = "SELECT * FROM [User] Where Username = @user";
SqlDataAdapter da = new SqlDataAdapter(req, c);
da.SelectCommand.Parameters.Add("@user", SqlDbType.NVarChar).Value = Session["CurentUserid"];
da.Fill(dt);

More in depth: UserName is a text column and thus if you want to search a value on this column you need to put the value between single quotes.
However fixing the problem adding a couple of quotes

Where Username = '"+Session["CurentUserid"] + "'"

just changes the problem because if you have a user with a single quote in its user name you are facing again a syntax error. The only correct way is to use a parameterized query as I have shown.

I leave the discussion about Sql Injection and all the problems you could face in your code in the else part (where you use a whole textbox to build your query to this well known answer

Good to be of help, but I think you are on a dangerous path. The else part and the use of Show.Text is a very big security risk. Read the answers posted in the link above. — Steve, Jun 02 '16 at 21:05

Error in gridview trying to show data of only the current user

1 Answers1