CODEWITHSUNDEEP
c#.netsql-server
War Gravy
War Gravy

Reputation: 1683

How to find username and passwords in SQL table using C#

I currently have a table on a sql server named "users" with the following columns/datatypes respectively:

username/nvarchar(max)
email/nvarchar(max)
password/nvarchar(max)

My table has one row:

[test][[email protected]][test]

I am using the following code to search through the table. I take the username (which is actually the email) and split it so that it becomes test. I then try to select the row that has test as it's username. Once I create the reader, I am trying to compare the email in the table with the username and the password from the table with the password:

  using (SqlConnection conn = new SqlConnection())
  {
        conn.ConnectionString ="connectionstring";
        conn.Open();
        //grab the username and password from the request
        string username = req.Form[0]; //[email protected]
        string password = req.Form[1]; //test

        if (!String.IsNullOrEmpty(username) && !String.IsNullOrEmpty(password))
        {
            //hash the password
            //lookup the password and username in the table
            SqlCommand command = new SqlCommand(String.Format("SELECT username, email, password FROM users WHERE username = '@{0}'", username.Split('@')[0]), conn);
            SqlDataReader reader = command.ExecuteReader();

            while(reader.Read())//THIS always evaluates to false
            {
                if (reader[1] == username && reader[2] == password) { return true; }
            } 
        }
        return false;
    }

I have debugged it with Visual Studio 2013 so I know for sure that my username and password is [email protected] and test. I don't get any exceptions and reader has the three column values in it's object, but it can't find the only row in the table that matches the username. I am very new to SQL queries so it is very possible that I am doing something wrong there. My problem is it never enters the while loop to check the email with the username and the password with the password. Also am I using appropriate datatypes for this?

The debugger shows that reader has the following:

 Depth: 0
 FieldCount: 3
 HasRows: false
 IsClosed: false
 RecordsAffected: -1
 VisibleFieldCount: 3

Upvotes: 0

Views: 5993

Answers (2)

Dgan
Dgan

Reputation: 10285

Try this:

You are Quering DataBase Like this

SELECT username, email, password FROM users

SqlCommand cmd = new SqlCommand();
cmd.connection = con;

cmd.CommandText = "SELECT username, email, password FROM users";
SqlDataReader reader = cmd.ExecuteReader();

while(reader.Read())//THIS always evaluates to false
{
     if (reader.GetString(0) == username &&
         reader.GetString(2) == password) 
     {
         return true; 
     }
}

EDIT:

Later Understood Your ResultSet Returns only one row, which is not best practice You have to split it in your code as suggested by @Serv and then Check for
UserName and Passwords

Upvotes: 1

Marco
Marco

Reputation: 23937

You have a problem with your query syntax: You are writing it up, as if you are passing a parameter in it, which results in the following:

SELECT username, email, password FROM users WHERE username = '@test'

What you really want is this string

SELECT username, email, password FROM users WHERE username = 'test'

To do this you have to change your SQL command to this:

 SqlCommand command = new SqlCommand(String.Format("SELECT username, email, password FROM users WHERE username = '{0}'", username.Split('@')[0]), conn);

Your problem was the following: Because you were checking against '@test' you weren't getting results back. following this your reader didn't read any data, because there was none and therefore reader.Read() never returned true and your while loop failed.

After this you will parameterize your query, because of reasons!

SqlCommand command = new SqlCommand("SELECT username, email, password FROM users WHERE username = @usern", conn);
command.Parameters.Add('usern', username.Split('@')[0]);
SqlDataReader reader = command.ExecuteReader();
while(reader.Read()) {
    ....
}

Upvotes: 5

Related Questions