Login not case sensitive

Question

What should i do to make this case sensitive?

When I try this code with input in different cases but with the same spelling of username and password, it still logs in. My fields are all varchar in database.

if (user_txt.Text != "" & pass_txt.Text != "")
{
    string queryText = "SELECT Count(*) FROM stiguidancesample.users " + "WHERE username = @Username AND password = @Password";
    MySqlConnection cn = new MySqlConnection(MyConnectionString);
    MySqlCommand cmd = new MySqlCommand(queryText, cn);

    {
        cn.Open();
        cmd.Parameters.AddWithValue("@Username", user_txt.Text);  // cmd is SqlCommand 
        cmd.Parameters.AddWithValue("@Password", pass_txt.Text);
        int result = Convert.ToInt32(cmd.ExecuteScalar());
        if (result > 0)
            MessageBox.Show("Loggen In!");
        else
            MessageBox.Show("User Not Found!");
    }
}

Never (as in "never ever") store password in plaintext - not in a file, not in a database, not on a piece of paper. — Sergey Kalinichenko, Feb 22 '14 at 16:00
[How to store passwords in a database](http://stackoverflow.com/q/1054022/335858). — Sergey Kalinichenko, Feb 22 '14 at 16:07
(1) For the case sensitive issue, check this one http://stackoverflow.com/questions/3969059/sql-case-sensitive-string-compare ... (2) As mentioned in above comment and answers, password shouldn't be stored as plain text — Tun Zarni Kyaw, Feb 22 '14 at 16:26

score 0 · Answer 1 · answered Feb 22 '14 at 16:05

I don't know if all databases are the same but SQL Server and MySQL are both apparently case-insensitive when comparing for text equality. That's as it should be for a login. It shouldn't matter what case the user enters their user name in; it should still match.

As for the password, you should not be saving it directly so it shouldn't matter. You should be hashing the password and storing that. The chances of anyone entering a password value that leads to text containing the same characters in a different case as the hash that you have stored in the database is next to nothing.

To expand on that, you hash the password and save the result when the user registers and then, when they login, you hash the password they provide and compare that to the value in the database. If the hash values match then the passwords are considered to match.

score 0 · Answer 2 · edited May 23 '17 at 10:33

0

The collation is relevant in how the strings are compared. See for e.g. this answer here: How can I make SQL case sensitive string comparison on MySQL?

or read the manual here: http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

edited May 23 '17 at 10:33

Community

1
1

answered Feb 22 '14 at 16:08

Ulrich Thomas Gabor

6,584
4
27
41

score 0 · Answer 3 · answered Sep 12 '16 at 10:10

Your code should be like below

if (user_txt.Text != "" & pass_txt.Text != "")
{
    string queryText = "SELECT Count(*) FROM stiguidancesample.users " +  "WHERE       username = @Username AND password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS ";
MySqlConnection cn = new MySqlConnection(MyConnectionString);
MySqlCommand cmd = new MySqlCommand(queryText, cn);

  {
    cn.Open();
    cmd.Parameters.AddWithValue("@Username", user_txt.Text);  // cmd is SqlCommand 
    cmd.Parameters.AddWithValue("@Password", pass_txt.Text);
    int result = Convert.ToInt32(cmd.ExecuteScalar());
    if (result > 0)
        MessageBox.Show("Loggen In!");
    else
        MessageBox.Show("User Not Found!");
  }
}

Just add

COLLATE SQL_Latin1_General_CP1_CS_AS

after your @Password in query text.

Login not case sensitive

3 Answers3