Login, Email and Password Validation not working correctly with SQL tables Vb-CodePudding

what I've been trying to do is grab the username, password and email from my sql table and validate it through my login form on vb.net. So, when I type in the username, password, and email in the form it should tell me if the login was successful or not. However, Whenever I type in the username, password and email from the sql table I created (MembershipInfo) into my login form I keep getting the error "Username, Password or Email is not correct, please try again" even though I know the username, password and email are correct (currently looking at it). I've tried multiple videos on youtube and even some solutions on here as well as on other sites, but nothing works. Can someone please tell me what I'm doing wrong? Here is my vb code:

Imports System.Data

Imports System.Data.SqlClient

Public Class WebForm1
    Inherits System.Web.UI.Page

  Public Shared conS As String = "Server= ; Database=Yourdatabase ;Trusted_Connection=Yes;"

    Public Shared con As SqlConnection = New SqlConnection(conS)

    Protected Sub TextBox8_TextChanged(sender As Object, e As EventArgs) Handles TextBox8.TextChanged

    End Sub

    Protected Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click

        Dim un, pw, em, dbUN, dbPW, dbEM As String

        un = TextBox7.Text
        pw = TextBox8.Text
        em = TextBox9.Text

        Dim cmdUN As New SqlCommand("Select UserName from MembershipInfo Where Username = @p1", con)

        With cmdUN.Parameters
            .Clear()
            .AddWithValue("@p1", un)
        End With

        Dim cmdPW As New SqlCommand("Select Pass from MembershipInfo Where UserName = @p1", con)

        With cmdPW.Parameters
            .Clear()
            .AddWithValue("@p1", un)
        End With

        Dim cmdEM As New SqlCommand("Select Email from MembershipInfo where UserName = @p1", con)

        With cmdEM.Parameters
            .Clear()
            .AddWithValue("@p1", un)
        End With

        Try
            If con.State = ConnectionState.Closed Then con.Open()
            dbUN = cmdUN.ExecuteScalar
            dbPW = cmdPW.ExecuteScalar
            dbEM = cmdEM.ExecuteScalar
        Catch ex As Exception
            Response.Write(ex.Message)
        Finally
            con.Close()
        End Try

        If un = dbUN And pw = dbPW And em = dbEM Then
            MsgBox("Login Sucessful", vbExclamation, "Welcome")
        Else
            MsgBox("Username, Password or Email does not match, please try again", vbExclamation, "Error")
        End If

    End Sub
End Class

And here is my sql code (I don't know if its needed but its better to be cautious):

Create Table MembershipInfo
(

MembershipID INT NOT NULL PRIMARY KEY Identity(1,1),

Firstname varchar(50) not null,

Lastname varchar(50) not null,

UserName char(50) not null,

Pass char(50) not null,

Email char(50) not null

); 

INSERT INTO MembershipInfo VALUES

('Jaycie', 'Adams', 'Imtotiredforthis', 'Golden1@1', '[email protected]'),

('Bret', 'Steidinger', 'HellowWord', 'Wowwzaa12@', '[email protected]'),

('Rebecca', 'Wong', 'SomethingSomething1@1', 'Outofideas11', '[email protected]'),

('Ciel', 'Phantomhive', 'DownwiththeQeen1@1', 'FellintomytrapWaha22@', '[email protected]'),

('Jane', 'Borden', 'TiredTM4@1', 'Justtakemypasswordalready@3', '[email protected]');

Select * from MembershipInfo;

CodePudding user response：

Do not declare Connections outside of the method where they are used. This is true of any database object that exposes a Dispose method.

Why not just use the email as the username.

These lines from Create Table will require fixed length strings. Not at all what you want. Stick to varchar or nvarchar.

UserName char(50) not null,
Pass char(50) not null,
Email char(50) not null

I am not familiar with the Insert syntax that you used.

Using blocks handle closing and disposing objects even if there is an error.

Use the Add method of the Parameters collection for Sql Server. http://www.dbdelta.com/addwithvalue-is-evil/ and https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ and another one: https://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications Here is another https://andrevdm.blogspot.com/2010/12/parameterised-queriesdont-use.html

Try to use meaningful names for you controls.

Protected Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
    If IsLoginValid(txtEmail.Text, txtPassword.Text) Then
        MsgBox("Login Sucessful", vbExclamation, "Welcome")
    Else
        MsgBox("Username, Password or Email does not match, please try again", vbExclamation, "Error")
    End If
End Sub

Private Function IsLoginValid(email As String, pword As String) As Boolean
    Dim RetVal As Integer
    Using cn As New SqlConnection(conS),
            cmd As New SqlCommand("Select Count(*) From MembershipInfo Where Email = @Name And Pass = @Pass")
        cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = email
        cmd.Parameters.Add("@Pass", SqlDbType.VarChar, 50).Value = pword
        cn.Open()
        RetVal = CInt(cmd.ExecuteScalar)
    End Using
    If RetVal = 1 Then
        Return True
    End If
    Return False
End Function

The worst part of this code is that you are storing passwords as plain text. Passwords should be salted and hashed before storage.