Querying a database for username and password

Question

I'm totally new to C#. I am validating username and password with case-sensitive from database sql server 2008. Here is my login code; what should I do?

SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=db_WiBo;Integrated Security=True");

SqlCommand cmd = new SqlCommand("SELECT * from tb_Account where Username= '"+textBox1.Text+"'  AND Password= '"+textBox2.Text+"' ", conn);

conn.Open();
SqlDataReader reader = cmd.ExecuteReader()

if (reader.HasRows)
{
   reader.Close();
   this.Hide();
}

Thanks in advance

Your `SqlCommand` has a [SQL Injection vulnerability](http://www.troyhunt.com/2013/07/everything-you-wanted-to-know-about-sql.html). You appear to be storing your passwords in plain-text too, not good. — DGibbs, Nov 12 '13 at 17:36
The first thing you should do is remove that big SQL injection risk by using parametrized queries. The second thing would probably be checking the collation on your database field/table to enable case sensitivity... and the third would be to hash passwords in your database. — Laurent S., Nov 12 '13 at 17:37
+Hash passwords, don't store them... and use a good hash function: http://throwingfire.com/storing-passwords-securely/ — dwerner, Nov 12 '13 at 17:39

Ahmed ilyas · Accepted Answer · 2013-11-12T17:41:37.593

1) you should be using stored procedures or parameterised query instead of SQL Concatenation. you are presenting a huge SQL Server injection security flaw in your code already.

what is the problem you are having? code looks fine - are you sure the inputs match the data in the database if there are no records being retrieved?

the code simply executes the query and if found, closes the reader and hides the form.

you should also not store raw passwords in the DB - another security flaw. instead, hash them/encrypt them and check that value against the hashed/encrypted value from the input. for case sensitivity, you could use Collation

http://technet.microsoft.com/en-us/library/ms184391.aspx

http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/

score 2 · Answer 2 · edited May 23 '17 at 11:56

A few things:

You are missing a ';' at the end of SqlDataReader reader = cmd.ExecuteReader().
As was already mentioned, use a Stored Procedure for what you are doing.
As was also mentioned, use parameterized queries since you are accepting input from textboxes.
Use a using statement to manage your connection and command objects.
Don't forget to .Close() your connection when you are done.
Oh, and I almost forgot, hash your passwords!

Here is an example of the above:

using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=db_WiBo;Integrated Security=True"))
{
   using (SqlCommand cmd = new SqlCommand(@"SELECT * from tb_Account where Username= '@username'  AND Password= '@password' ", conn))
   {
      cmd.Parameters.Add("@username", textBox1.Text);
      cmd.Parameters.Add("@password", textBox2.Text);
      conn.Open();

      SqlDataReader reader = cmd.ExecuteReader();
      if (reader.HasRows)
      {
         reader.Close();
         this.Hide();
      }
      conn.Close();
   }
}

score 1 · Answer 3 · edited May 23 '17 at 10:31

1

Other than your SQL injection issues, you need to use a collation or cast to binary. I found another SO question that has a bunch of helpful things:
How to do a case sensitive search in WHERE clause (I'm using SQL Server)?

edited May 23 '17 at 10:31

Community

1
1

answered Nov 12 '13 at 17:37

Bill Sambrone

4,334
4
48
70

score 1 · Answer 4 · answered Nov 12 '13 at 17:37

1

First of all: Do not use string concat for that. This can easily be used for code injection.

You can use stored procedures instead, or LinQ to SQL or even use the built-in membershipprovider

answered Nov 12 '13 at 17:37

Ole Albers

8,715
10
73
166

score 1 · Answer 5 · answered Nov 12 '13 at 17:53

Notwithstanding the design issues already pointed out with respect to security, if your SQL Server instance's default collation is case-insensitive, you'll need to select a suitable collation. The default collation for a SQL Server installation is [usually, depending on the server locale] SQL_Latin1_General_Cp1_CI_AS, meaning Latin-1 code page, case-insensitive, accent-sensitive.

When you create a database, you can specify a default collation for that database. And when you create a table, you may specify the collation to be used for each char, varchar, nchar or nvarchar column.

You may also change these via appropriate DDL statements. Note that altering the collatino may affect data integrity, causing things like primary keys and unique indices to be broken.

Create a table with and specifying the collation for its columns is easy:

create table account
(
  id       int         not null identity(1,1) primary key clustered ,
  user_id  varchar(32) collate SQL_Latin1_General_Cp1_CS_AS not null unique ,
  password varchar(32) collate SQL_Latin1_General_Cp1_CS_AS not null unique ,
  ...
)

You may use alter table and alter database to change the collations as well.

Supported collations can be found at http://technet.microsoft.com/en-us/library/ms180175.aspx and http://technet.microsoft.com/en-us/library/ms188046.aspx

You should

Note that mixed collations can cause problems when comparing 2 columns, and
Heed the security issues raised by others.

Querying a database for username and password

5 Answers5