system login using query select 1

Question

I am trying to code the login page, here is my code:

'login form code
Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click

        Dim username As String = txtUsername.Text.Trim
        Dim pwd As String = txtPassword.Text.Trim
        Dim insertQry As String = "select 1 from UserInfo where username = '" & username & "' and userpassword = '" & pwd & "'"  
        Dim res As Boolean = executeReader(insertQry)
End Sub

database module

Imports System.Data.SqlClient

Module DBconn
    Public conn_ As New SqlConnection("")   

    Public Function executeReader(ByVal query As String)
        Try
            Dim cmd As New SqlCommand(query, conn_)
            conn_.Open()
            Dim r2 As SqlDataReader = cmd.ExecuteReader()
            
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

End Module

My question is how to do validation of username and password check with select 1 query?

Answer 1

Take a look at this example, it does the following:

1. It returns the count of the primary key value in the SQL query (documentation)
2. It uses parameters to pass the values to the WHERE clause (documentation)
3. It uses ExecuteScalar to return a single value from the command (documentation)

Private Function ValidateLogin(ByVal username As String, ByVal password As String) As Boolean
    Dim count As Integer = 0

    'Declare the connection object
    Using con As SqlConnection = New SqlConnection

        'Wrap code in Try/Catch
        Try
            'Set the connection string
            con.ConnectionString = "" 'TODO: set this value

            'Create a new instance of the command object
            Using cmd As SqlCommand = New SqlCommand("SELECT Count(UserInfoId) FROM UserInfo WHERE username=@username AND userpassword=@password", con)

                'Parameterize the query
                With cmd.Parameters
                    .Add("@username", SqlDbType.VarChar).Value = username
                    .Add("@password", SqlDbType.VarChar).Value = password
                End With

                'Open the connection
                con.Open()

                'Use ExecuteScalar to return a single value
                count = Convert.ToInt32(cmd.ExecuteScalar())

                'Close the connection
                con.Close()
            End Using
        Catch ex As Exception
            'Display the error
            Console.WriteLine(ex.Message)
        Finally
            'Check if the connection object was initialized
            If con IsNot Nothing Then
                If con.State = ConnectionState.Open Then
                    'Close the connection if it was left open(exception thrown)
                    con.Close()
                End If
            End If
        End Try
    End Using

    'Return row count is greater than 0
    Return count > 0
End Function