Login Authentication from SQL Server database

Question

I have login issue authentication on windows form C# application. Once I register user it send user data to a SQL Server database. When I am trying to log in. Even if credentials match to data in data base message box showing up. Please see the code below.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using travel_booking.UserControlers;
using System.Data.SqlClient;

namespace travel_booking
{
    public partial class UserContrLogin : UserControl
    {
        internal Action<object, EventArgs> OnUserLogin;
        UserContrRegister userContrRegister;

        public UserContrLogin()
        {
            InitializeComponent();
        }

        public void setUserContrRegister(UserContrRegister userContrRegister)
        {
            this.userContrRegister = userContrRegister;
        }

        private void Exit_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void LoginButton_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection(@"//Removed by me as it is sensitive data");
            sqlConnection.Open();
            string query = "Select * from tblUser Where Email = ' " + txtEmail.Text.Trim() + "' and Password = '" + txtPassword.Text.Trim() + "'";

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
            DataTable dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable);

            if (dataTable.Rows.Count > 0)
                this.Hide();
            else
                MessageBox.Show("Email or/and Password is/are invalid. Please try again");

            sqlConnection.Close();
        }
    }
}

Use Command Parameters to pass values to the query, don't concatenate strings (you also have an extra space). Don't use common terms to name your Columns (you could use a prefix for the names, e.g., fPassword, colPassword etc.). Make sure that you have setup your storage to support Unicode. — Jimi
– Jimi, Commented Apr 26, 2021 at 2:08
You should also dispose your connection and adapter with using blocks — Charlieface
– Charlieface, Commented Apr 26, 2021 at 10:13

Charlieface · Accepted Answer · 2021-04-26 10:13:27Z

1

You can use this code to work much better

public void Login()
{
    SqlConnection sqlConnection = new SqlConnection(@"//Removed by me as it is sensitive data");
    sqlConnection.Open();
    string query = "Select * from tblUser Where Email = @Email and Password = @Password";
    SqlCommand command = new SqlCommand();
    command.Connection = sqlConnection;
    command.CommandType = CommandType.Text;
    command.Text = query;

    command.Parameters.AddWithValue("@Email", txtEmail.Text.Trim());
    command.Parameters.AddWithValue("@Password", txtPassword.Text.Trim());

    SqlDataReader reader = command.ExecuteReader();
    if(reader.Read() == true)
    {
        this.Hide();
    }
    else
    {
        MessageBox.Show("Email or/and Password is/are invalid. Please try again");
    }

}

I use the command.Parameters.AddWithValue() to avoid the concatenation of the string of your query that can cause an SQL INJECTION

edited Apr 26, 2021 at 10:13

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

answered Apr 26, 2021 at 4:35

Andrei Solero

8021 gold badge6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charlieface Over a year ago

AddWithValue is evil., specify the parameter types and lengths. You should also dispose your connection and adapter with using blocks

Collectives™ on Stack Overflow

Login Authentication from SQL Server database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related