CODEWITHSUNDEEP
c#sqlsql-serverdatabase-connectionsqlcommand
JaGo
JaGo

Reputation: 257

Sending authentication information to SQL Server isn't working (C#)

I already asked about a similar issue to this one but I've narrowed it down to my problem and I've been working on this for hours and unable to figure this out.

Basically, I have a visual studio wep application and I'm trying to use a login page and sql server database to validate user credentials. The user enters a string for username and password on the log-in screen which gets sent here on the code behind: 

private bool ValidateUser(string userName, string passWord)
        {
            SqlConnection conn;
            SqlCommand cmd;
            string lookupPassword = null;


                // Consult with your SQL Server administrator for an appropriate connection
                // string to use to connect to your local SQL Server.
                conn = new SqlConnection(ConfigurationManager.ConnectionStrings["databaseConnect"].ConnectionString);
                conn.Open();

                // Create SqlCommand to select pwd field from users table given supplied userName.
                cmd = new SqlCommand("Select Password from Users where User=@userName;", conn);
                cmd.Parameters.Add("@userName", System.Data.SqlDbType.VarChar, 25);
                cmd.Parameters["@userName"].Value = userName;

                lookupPassword = (string)cmd.ExecuteScalar();

            // If no password found, return false.
                 if (null == lookupPassword)
                 {
                     return false;
                 }

private void cmdLogin_ServerClick(object sender, System.EventArgs e)
      {
          if (ValidateUser(txtUserName.Value,txtUserPass.Value) )

           (logs in)

      }

The application is connected a table in a database, which currently holds one test item, as shown here: https://i.sstatic.net/Xa7kH.jpg

However, when I enter "test" as username and "password" as password, it doesn't accept the log-in.

I tried to include only the relevant parts of my code to make it more clear for anybody trying to answer the question but here's a few comments about my problem:

-When I set if (null == lookupPassword) to "return true" instead of "return false" the application allows logins. Which means lookupPassword is still null after "lookupPassword = (string)cmd.ExecuteScalar();"

-The application works fine when I change if(ValidateUser(txtUserName.Value,txtUserPass.Value)) to if(userName=test and passWord=password). So the problem is not with the actual act of logging into the application, it just isn't finding the SQL Server credentials to be true.

-"databaseConnect" is working elsewhere on the application, so that is not the issue.

-The application is not breaking when I submit the login credentials, it is simply not accepting them as correct.

Going off that, it seems to me that the problem comes from these four lines:

cmd = new SqlCommand("Select Password from Users where User=@userName;", conn);
cmd.Parameters.Add("@userName", System.Data.SqlDbType.VarChar, 25);
cmd.Parameters["@userName"].Value = userName;

lookupPassword = (string)cmd.ExecuteScalar();

Does anybody see where the problem might be for me? I'm very new to programming so please keep the answers as simple as possible please. Thank you in advance for any help. :)

Upvotes: 0

Views: 148

Answers (1)

nabrond
nabrond

Reputation: 1388

This table design is using several reserved words in SQL Server. If you cannot modify this schema, you can update your query as follows:

SELECT [Password] FROM [Users] WHERE [User] = @Username

That being said, storing passwords in plaintext is a horrible security practice. Passwords should never be stored encrypted or as plaintext, they should be hashed using a salt to help avoid rainbow tables from cracking your password. I would look into the suggestion from @Richard regarding Rfc2898DeriveBytes. You can also search Google or Bing for using salt and SHA256.

Upvotes: 1

Related Questions