Altjen B. Altjen B. - 1 month ago 11
ASP.NET (C#) Question

Strange thing is data inside SQL server management

I am making a simple project in ASP.NET and have this code to proceed to login, detect the role and open the exact page.

string cmdText = "SELECT Username,Role FROM Login WHERE Username = '" + TextBox1.Text + "' AND Password = '" + TextBox2.Text + "'";

string username = "";
string role = "";

using (SqlCommand SelectCommand = new SqlCommand(cmdText, connectionstring))
{
SqlDataReader myReader;
connectionstring.Open();
myReader = SelectCommand.ExecuteReader();


while (myReader.Read())
{
username = myReader["Username"].ToString();
role = myReader["Role"].ToString();
}

myReader.Close();

Response.Redirect(role + ".aspx");
}


I set it role+".aspx" because I was having some weird error with the if function.. it wasnt working properly..

but still was having problem redirecting to the page.. and I notice this

image1

so, confused by this error I decided to check the data in sql..
and there is this:
image2

there are 5 white spaces after the role.. I tried to delete them. but after save the data the spaces apear again.. I notice that the same thing is with the name and password

image3

but now there are 9 white spaces.. looks like SQL Management System is trying to fill the max 10 letters...

username, password and role are nchar(10) type.. is that the problem? should I change to fix that? or it can be done on other way

Answer

An nchar column stores its values in a fixed length space. If you declare a column to be of type nchar(10) then your values are all padded with spaces to reach the 10 characters length.
If you can change your schema then change the column to be of nvarchar(10) type and (not sure about it) probably you need to reedit all the values already there.

See What is the difference between char, nchar, varchar, and nvarchar in SQL Server?

If you can't change the schema then you could Trim the results in code

 Response.Redirect(role.Trim() + ".aspx");

However, looking at your code, I see a very big problem. You are using the famigerate string concatenation to build your query. This is a well know weakness on code and leads to Sql Injection attacks and to parsing errors.
You should fix ASAP that query using parameters

string cmdText = "SELECT Role FROM Login WHERE Username = @name AND Password = @pass";
// You already know the username
string username = textBox1.Text;
string role = "";
using (SqlCommand SelectCommand = new SqlCommand(cmdText, connectionstring))
{
    connectionstring.Open();
    SelectCommand.Parameters.Add("@name", SqlDbType.NVarChar).Value = textBox1.Text;
    SelectCommand.Parameters.Add("@pass", SqlDbType.NVarChar).Value = textBox2.Text;

    using(SqlDataReader myReader = SelectCommand.ExecuteReader())
    {
        while (myReader.Read())
        {
            role = myReader["Role"].ToString();
        }
    }
    Response.Redirect(role.Trim() + ".aspx");
}

As a final note, consider that storing passwords in clear text in a database is another security risk to be avoided. A well know practice is to hash and salt a password before storing it in the database

Comments