Database returning username check as incorrect

Question

Whenever i check my database with correct data the database returns it as an incorrect username, im not sure whether it is my code, or my database, her is the code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
public partial class Login : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button_Login_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection("Data Source=TOSHIBA0007\\TESTSERVER;Initial Catalog=users;Integrated Security=True");
            conn.Open();
            string checkuser = "select count(*) from userdatabase where Username=' " + Username.Text + " ' ";
            SqlCommand UserComm = new SqlCommand(checkuser, conn);
            int temp = Convert.ToInt32(UserComm.ExecuteScalar().ToString().Replace(" ", ""));
            conn.Close();
            if (temp == 1)
            {
                conn.Open();
                string checkPasswordQuery = "select Password from userdatabase where Username=' "+Username.Text+" ' ";
                SqlCommand passCom = new SqlCommand(checkPasswordQuery, conn);
                string password = passCom.ExecuteScalar().ToString().Replace(" ", "");
                if(password == Password.Text)
                {
                    Session["New"] = Username.Text;
                    Response.Write("Password Accepted");
                }
                else
                {
                    Response.Write("Password Incorrect");
                }        
            }
            else
            {
                Response.Write("Username is Incorrect");
            }
    }
}

any and all help will be greatly appreciated as i am stumped as to why this is not working.

Answer 1

Problem : You are giving space before and after username in your query.

1.Replace This:

string checkuser = "select count(*) from userdatabase where Username=' " + Username.Text + " ' ";

With This:

 string checkuser = "select count(*) from userdatabase where Username='" + Username.Text + "'";

2.Replace This:

string checkPasswordQuery = "select Password from userdatabase where Username=' "+Username.Text+" ' ";

With This:

string checkPasswordQuery = "select Password from userdatabase where Username='"+Username.Text+"'";

Suggestion: 1 your query is open to sql injection attacks so i would suggest you to use parameterised queries to avoid them.

Suggestion 2 : you don't need to do multiple comparisions for username and password. you can write a single query as below to find the valid user:

select count(*) from userdatabase where Username=@username and Password=@password;

Solution: using Parameterised Queries with single query

protected void Button_Login_Click(object sender, EventArgs e)
{
        SqlConnection conn = new SqlConnection("Data Source=TOSHIBA0007\\TESTSERVER;Initial Catalog=users;Integrated Security=True");
        conn.Open();
        string checkuser = "select count(*) from userdatabase where Username=@username and Password=@password";
        SqlCommand UserComm = new SqlCommand(checkuser, conn);
        UserComm.Parameters.AddWithValue("@username",Username.Text);
        UserComm.Parameters.AddWithValue("@password",Password.Text);
        int temp = Convert.ToInt32(UserComm.ExecuteScalar());
        conn.Close();
        if (temp == 1)
        {
           Session["New"] = Username.Text;
           Response.Write("User Is Valid!");
        }
        else
        {
           Response.Write("Invalid User Credentials!");
        }   
}

Suggestion 3: you should not store passwords as plain text in Database, please take care of that. see this link for more info on how to store passwords in a secured way.