User authentication to database using .NET web service

Question

I have a database created in SQL Server 2008 on my machine. It contains a table named login having two columns of username and password with certain values.

This is my web service code written in Visual Studio 2008. I need to check whether the user name and password I provide is correct or not i.e basically I want to authenticate the user via web service.

So can anyone tell me how should I can do that?

I hope none of your students are named [Bobby Tables](http://xkcd.com/327/). — vcsjones, Jul 30 '11 at 14:34
Nope not at all. I m just not able to authenticate the user via the web service..can u tell me what has to be done ? — Parth Doshi, Jul 30 '11 at 14:42

score 3 · Accepted Answer · edited May 23 '17 at 11:50

I would urge you to use parameters when dealing with SQL as to not be vulnerable to SQL Injection; especially for a public facing web service.
It appears from your queries you are storing passwords in plain text. Again, I would urge you to salt and hash your user's passwords for better security.

You can accomplish what you are looking for by changing your query to a COUNT(*), and executing as a scalar, and making sure the count is greater equal to one. Something like this:

myCommand.CommandText = "select COUNT(*) from Login where Username = @Username AND Password = @HashedPassword";
int rowCount = (int)mycommand.ExecuteScalar();
return rowCount == 1 ? "success" : "bad username or password";

The SqlDataReader should be removed from your original code.

EDIT: So what is this code doing?

We've changed the SQL Query slightly. I've also modified it to use parameters. So what this query is saying is Get me the number of users that have this username and this password as an integer.

So if one user has that username and password, it would return 1. If no one had that username and password, it would return 0.

ExecuteScalar is useful when you aren't expecting any rows back; just a single value. This is true in our case; all we are expecting is a number: the number of users.

So when you put all of this code together; it should look something like this:

try
{
    using(SqlConnection myConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=student;User ID=sa;Password=123"))
    {
        myConnection.Open();

        using (SqlCommand myCommand = new SqlCommand())
        {
            myCommand.Connection = myConnection;
            myCommand.CommandText = "SELECT COUNT(*) FROM Login WHERE UserName = @UserName AND Password = @Password";
            myCommand.Parameters.Add("@UserName", SqlDbType.VarChar).Value = UserName;
            myCommand.Parameters.Add("@Password", SqlDbType.VarChar).Value = Password;
            return (int) myCommand.ExecuteScalar() == 1 ? "success" : "bad username or password";
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    return "an error occurred.";
}

I am new to this . Can u explain what will the code u have given actually do ? — Parth Doshi, Jul 30 '11 at 14:43
what should my GetLoginDetails method return if I use your code within it ? — Parth Doshi, Jul 30 '11 at 14:52
@Parth_90: GetLoginDetails should return whatever you want it to return. If you want to return a magic string such as `success` and `failed`, that's up to you. You could also just return a boolean instead of a string as well. — vcsjones, Jul 30 '11 at 14:59
Hey thanks a lot for the code ..But I m getting error in this line : myCommand.Parameters.Add("@UserName", SqlDbType.VarChar).Value = username; myCommand.Parameters.Add("@Password", SqlDbType.VarChar).Value = password; ...the error says the name "username" doesn't exist in current context.. — Parth Doshi, Jul 30 '11 at 15:05
@Parth_90: I fixed my code if you want to re-copy and paste it. I mis-cased the variable names of `UserName` and `Password` from your original code. — vcsjones, Jul 30 '11 at 15:07
@vcsjones let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/1981/discussion-between-parth-90-and-vcsjones) — Parth Doshi, Jul 30 '11 at 15:35

User authentication to database using .NET web service

1 Answers1