Not case-sensitive save in SQL?

Question

On my login form after i save my content to SQL and if i try to get the information from the database the information passes the evaluation to true even if the information provided is typed both ways - upper case or lower case.Here is my login code,please help me understand.I'am contacting database with Entity Framework.the currUser is a variable where I save the current user information.

try
      {
          if (!string.IsNullOrWhiteSpace(username) && !string.IsNullOrWhiteSpace(password))
          {
              var users = from c in context.CustomerTables where c.username == username && c.password == password select c;
              List<CustomerTable> table = users.ToList();
              if (table.Any())
              {
                  MessageBox.Show("Successfully logged in.\nWelcome " + username + "!", "Welcome", MessageBoxButton.OK, MessageBoxImage.Asterisk);
                  currUser.username = username;
                  currUser.password = password;
                  return true;
              }
              else
              {
                  MessageBox.Show("Username or password is invalid.", "Error logging in", MessageBoxButton.OK, MessageBoxImage.Error);
                  return false;
              }
          }
          else
          {
              MessageBox.Show("Username and password format is invalid!","Null username or password",MessageBoxButton.OK,MessageBoxImage.Warning);
              return false;
          }

You shouldn't be saving passwords in plaintext. See http://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database for example. — CodeCaster, Nov 30 '13 at 20:33
I know that but that's not the question...I got usernames there too — Christo Christov, Nov 30 '13 at 20:34
Either make the column, table and/or database case sensitive, or do the comparison in code. — Lasse V. Karlsen, Nov 30 '13 at 20:35
How would you compare two string for case sensitivity..looping through each char? — Christo Christov, Nov 30 '13 at 20:36
The keyword you're looking for is "collation". Or do as @Lasse says, do a case-sensitive string comparison from C# on your users object. — CodeCaster, Nov 30 '13 at 20:37

Tonci · Accepted Answer · 2014-11-12T20:43:00.353

0

The simplest fix would be to replace

if (table.Any())

with

if (table.Any() && table[0].username == username && table[0].password == password)

The reason why this would work is that string comparison in C# is case-sensitive by default.

edited Nov 12 '14 at 20:43

answered Nov 30 '13 at 20:49

Tonci

471
2
8

This code works!!Thanks but why this did work and my code failed? – Christo Christov Nov 30 '13 at 20:58
Because your string comparison was made by SQL and string comparison in SQL is case-insensitive by default. (You could have changed that with collation, as mentioned in comments to your question, but I believe this is a bit simpler.) – Tonci Nov 30 '13 at 20:59
Whether or not string comparison in sql is case sensitive depends on the RDBMS. @ChristoChristov is obviously using one where it is not. – Dan Bracuk Nov 30 '13 at 21:06
What do you mean @DanBracuk – Christo Christov Nov 30 '13 at 21:08
@ChristoChristov when a database instance is set up, the admin can set if it is case sensitive or not. Oracle's default is case sensitive, SQL Server's default is not. But when an Oracle database is set up, the admin can set it to not be case sensitive (overriding the default). The same is true for SQL Server--- the admin can set it up so it is case sensitive (overriding the default), or leave it case insensitive. – StarPilot Nov 12 '14 at 21:13

Not case-sensitive save in SQL?

1 Answers1