Strange thing is data inside SQL Server Management Studio

Question

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 wasn't working properly..

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

So, confused by this error I decided to check the data in SQL Server, and there is this:

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

but now there are 9 white spaces.. looks like SQL Server Management Studio 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

@Steve so I should chose varchar or nvarchar to fix the problem? — berg96, Oct 08 '16 at 15:23

score 6 · Accepted Answer · edited May 23 '17 at 12:01

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 weakness to avoid. A well know good practice is to hash and salt a password before storing it in the database

Thanks steve, but what does .Trim to the text? it removes white spaces? — berg96, Oct 08 '16 at 15:26
Yes see string.Trim on MSDN https://msdn.microsoft.com/en-us/library/system.string.trim(v=vs.110).aspx — Steve, Oct 08 '16 at 15:30
Thank you very much Steve. I'm kinda new and that taught me a lot. — berg96, Oct 08 '16 at 15:39

HansVG · Answer 2 · 2016-10-08T15:39:16.977

You should use nvarchar(10) instead of nchar(10). char and nchar will always have a fixed length. So if you store an 'a' in a nchar(10) or char(10) it will get padded to the right with spaces to 10 charachters. If you store it as a nvarchar(10) it will be stored as the length of your string.

Only use nchar and char when the length of the column will always be the same.

After converting from nchar to nvarchar. You should also update the data in your columns so they won't have the trailing spaces anymore. (Do this for all the columns which you wish to convert)

update Login set Role = LTRIM(RTRIM(Role))

score 1 · Answer 3 · answered Oct 09 '16 at 05:49

You can use trim() in the c# code to prevent %20 (blank space) in the url, It is because the role returned by database is 'admin____' (____ = blank space) and not 'admin', There are two options,

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

(or)

2.  Response.Redirect(role.trim() + ".aspx");

Strange thing is data inside SQL Server Management Studio

3 Answers3