login routine with information returned from database

6 Mar 2006 5:01 PM

steven scaife

Hi i am creating a login routine that i have overloaded and wondered how i
could return some data back to my program or even if i am going about the
right way of achieving what i want to do.

The function takes some parameters the server name, the database, the
username and the stored procedure name the other overloaded function takes
the same parameters plus a password and two optional parameters for the
username and password input parameters for the stored proc defaulted at
@username and @password.

Then the function opens a data reader using cmd.executereader if the
datareader has rows then it returns true if not it returns false. It is
meant to be a generic login dll for use in my apps either windows or asp.
Sometimes i may need to return the full name of the user or one or two other
parameters how could i do this i was thinking about having some public
property's but i am unsure of how to go about setting them. I keep getting
an error invalid attempt to read when no data is present. When i call
dr.hasrows does this then empty my datareader i remember from my course
manual that a datareader is readonly forward only?

Is this a good way of doing things or have i got the idea mixed up

The code is below, by the way i am coming from a predominately ASP
background into .net so i know little of it really

Public Overloads Function LoginCheck(ByVal Username As String, ByVal
Password As String, _
    ByVal Server As String, ByVal DBName As String, ByVal StoredProc As
String, ByVal numReturns As Integer, Optional _
    ByVal Param1 As String = "@Username", Optional ByVal Param2 As String =
"@Password") As Boolean
        Dim conn As SqlConnection = Nothing
        Dim dr As SqlDataReader = Nothing
        Dim cmd As New SqlCommand
        Dim P1 As New SqlParameter(Param1, SqlDbType.VarChar)
        Dim P2 As New SqlParameter(Param2, SqlDbType.VarChar)
        Try
            conn = New SqlConnection("data source=" & Server & "; integrated
security=true; initial catalog = " & DBName)
            cmd.CommandText = (StoredProc)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = conn

            'set up the paramaters
            P1.Value = Username
            P1.Direction = ParameterDirection.Input
            P2.Value = Password
            P2.Direction = ParameterDirection.Input

            cmd.Parameters.Add(P1)
            cmd.Parameters.Add(P2)

            conn.Open()
            dr = cmd.ExecuteReader

            If dr.HasRows Then
                Select Case numReturns
                    Case 0
                        'do nothing
                    Case 1
                        Return1 = dr("Fullname").ToString
                    Case 2

                End Select
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            Console.WriteLine(ex.Message)

            If Not dr Is Nothing Then
                dr.Close()
            End If
            If Not conn Is Nothing Then
                conn.Close()
            End If

        Finally
            If Not dr Is Nothing Then
                dr.Close()
            End If
            If Not conn Is Nothing Then
                conn.Close()
            End If
        End Try

    End Function

    Dim m_Return1 As String

    Public Property Return1() As String
        Get
            Return m_Return1
        End Get
        Set(ByVal Value As String)
            m_Return1 = Value
        End Set
    End Property

6 Mar 2006 5:10 PM

Marina Levit [MVP]

You need to call .Read on the datareader before trying to read anything from
it. HasRows was put in there for databinding, so you can tell if you got
results without advancing to the first row.

I would change your If statement to be 'If dr.Read() Then'

Show quote

"steven scaife" <stevensca***@discussions.microsoft.com> wrote in message
news:D58021AF-B117-4EFB-8A40-6411CB27A1DB@microsoft.com...
> Hi i am creating a login routine that i have overloaded and wondered how i
> could return some data back to my program or even if i am going about the
> right way of achieving what i want to do.
>
> The function takes some parameters the server name, the database, the
> username and the stored procedure name the other overloaded function takes
> the same parameters plus a password and two optional parameters for the
> username and password input parameters for the stored proc defaulted at
> @username and @password.
>
> Then the function opens a data reader using cmd.executereader if the
> datareader has rows then it returns true if not it returns false. It is
> meant to be a generic login dll for use in my apps either windows or asp.
> Sometimes i may need to return the full name of the user or one or two
> other
> parameters how could i do this i was thinking about having some public
> property's but i am unsure of how to go about setting them. I keep
> getting
> an error invalid attempt to read when no data is present. When i call
> dr.hasrows does this then empty my datareader i remember from my course
> manual that a datareader is readonly forward only?
>
> Is this a good way of doing things or have i got the idea mixed up
>
> The code is below, by the way i am coming from a predominately ASP
> background into .net so i know little of it really
>
> Public Overloads Function LoginCheck(ByVal Username As String, ByVal
> Password As String, _
>    ByVal Server As String, ByVal DBName As String, ByVal StoredProc As
> String, ByVal numReturns As Integer, Optional _
>    ByVal Param1 As String = "@Username", Optional ByVal Param2 As String =
> "@Password") As Boolean
>        Dim conn As SqlConnection = Nothing
>        Dim dr As SqlDataReader = Nothing
>        Dim cmd As New SqlCommand
>        Dim P1 As New SqlParameter(Param1, SqlDbType.VarChar)
>        Dim P2 As New SqlParameter(Param2, SqlDbType.VarChar)
>        Try
>            conn = New SqlConnection("data source=" & Server & ";
> integrated
> security=true; initial catalog = " & DBName)
>            cmd.CommandText = (StoredProc)
>            cmd.CommandType = CommandType.StoredProcedure
>            cmd.Connection = conn
>
>            'set up the paramaters
>            P1.Value = Username
>            P1.Direction = ParameterDirection.Input
>            P2.Value = Password
>            P2.Direction = ParameterDirection.Input
>
>            cmd.Parameters.Add(P1)
>            cmd.Parameters.Add(P2)
>
>            conn.Open()
>            dr = cmd.ExecuteReader
>
>            If dr.HasRows Then
>                Select Case numReturns
>                    Case 0
>                        'do nothing
>                    Case 1
>                        Return1 = dr("Fullname").ToString
>                    Case 2
>
>                End Select
>                Return True
>            Else
>                Return False
>            End If
>
>        Catch ex As Exception
>            Console.WriteLine(ex.Message)
>
>            If Not dr Is Nothing Then
>                dr.Close()
>            End If
>            If Not conn Is Nothing Then
>                conn.Close()
>            End If
>
>        Finally
>            If Not dr Is Nothing Then
>                dr.Close()
>            End If
>            If Not conn Is Nothing Then
>                conn.Close()
>            End If
>        End Try
>
>    End Function
>
>    Dim m_Return1 As String
>
>    Public Property Return1() As String
>        Get
>            Return m_Return1
>        End Get
>        Set(ByVal Value As String)
>            m_Return1 = Value
>        End Set
>    End Property
>

6 Mar 2006 6:21 PM

W.G. Ryan - MVP

Steven:

If you are accepting user input to create the connection string, be very
careful. Believe it or not, there is such thing a connection string
injection attack.
http://msmvps.com/blogs/williamryan/archive/2006/01/15/81115.aspx

I'd recommend a change though although your approach isn't 'wrong'. I'd
consider using output parameters to determine if the use is authenticated or
not and grabbing those values. It can be a lot lighter and login screen
usually get a good bit of traffice.

HTH,

bill
Show quote

"steven scaife" <stevensca***@discussions.microsoft.com> wrote in message
news:D58021AF-B117-4EFB-8A40-6411CB27A1DB@microsoft.com...
> Hi i am creating a login routine that i have overloaded and wondered how i
> could return some data back to my program or even if i am going about the
> right way of achieving what i want to do.
>
> The function takes some parameters the server name, the database, the
> username and the stored procedure name the other overloaded function takes
> the same parameters plus a password and two optional parameters for the
> username and password input parameters for the stored proc defaulted at
> @username and @password.
>
> Then the function opens a data reader using cmd.executereader if the
> datareader has rows then it returns true if not it returns false. It is
> meant to be a generic login dll for use in my apps either windows or asp.
> Sometimes i may need to return the full name of the user or one or two
> other
> parameters how could i do this i was thinking about having some public
> property's but i am unsure of how to go about setting them. I keep
> getting
> an error invalid attempt to read when no data is present. When i call
> dr.hasrows does this then empty my datareader i remember from my course
> manual that a datareader is readonly forward only?
>
> Is this a good way of doing things or have i got the idea mixed up
>
> The code is below, by the way i am coming from a predominately ASP
> background into .net so i know little of it really
>
> Public Overloads Function LoginCheck(ByVal Username As String, ByVal
> Password As String, _
>    ByVal Server As String, ByVal DBName As String, ByVal StoredProc As
> String, ByVal numReturns As Integer, Optional _
>    ByVal Param1 As String = "@Username", Optional ByVal Param2 As String =
> "@Password") As Boolean
>        Dim conn As SqlConnection = Nothing
>        Dim dr As SqlDataReader = Nothing
>        Dim cmd As New SqlCommand
>        Dim P1 As New SqlParameter(Param1, SqlDbType.VarChar)
>        Dim P2 As New SqlParameter(Param2, SqlDbType.VarChar)
>        Try
>            conn = New SqlConnection("data source=" & Server & ";
> integrated
> security=true; initial catalog = " & DBName)
>            cmd.CommandText = (StoredProc)
>            cmd.CommandType = CommandType.StoredProcedure
>            cmd.Connection = conn
>
>            'set up the paramaters
>            P1.Value = Username
>            P1.Direction = ParameterDirection.Input
>            P2.Value = Password
>            P2.Direction = ParameterDirection.Input
>
>            cmd.Parameters.Add(P1)
>            cmd.Parameters.Add(P2)
>
>            conn.Open()
>            dr = cmd.ExecuteReader
>
>            If dr.HasRows Then
>                Select Case numReturns
>                    Case 0
>                        'do nothing
>                    Case 1
>                        Return1 = dr("Fullname").ToString
>                    Case 2
>
>                End Select
>                Return True
>            Else
>                Return False
>            End If
>
>        Catch ex As Exception
>            Console.WriteLine(ex.Message)
>
>            If Not dr Is Nothing Then
>                dr.Close()
>            End If
>            If Not conn Is Nothing Then
>                conn.Close()
>            End If
>
>        Finally
>            If Not dr Is Nothing Then
>                dr.Close()
>            End If
>            If Not conn Is Nothing Then
>                conn.Close()
>            End If
>        End Try
>
>    End Function
>
>    Dim m_Return1 As String
>
>    Public Property Return1() As String
>        Get
>            Return m_Return1
>        End Get
>        Set(ByVal Value As String)
>            m_Return1 = Value
>        End Set
>    End Property
>

7 Mar 2006 11:44 AM

steven scaife

Thanks for the help i can now get my values into the propertys. With regards
to the connection string I was thinking of using something similar to an
include file in asp. Where the server and database are pulled from a
seperate file or class the only thing the user has control over is the
username and password, but all the users of the systems are employees of the
company i work for and have about as much computer ability as a monkey, so i
am probably safe from attacks for the time being. I will have a proper read
of that article when i get a chance as it is something i will be needing to
know but i have had a quick scan of it and its a 2.0 control and i am
developing in .net 1.1, is there a similar control. I will be adding a hash
to the routine as well on the inputted username and password would this help
stop injection attacks as it wont be the same string they put in, so i would
assume the damage wont be able to be done.

thanks for your time

Show quote

"W.G. Ryan - MVP" wrote:

> Steven:
>
> If you are accepting user input to create the connection string, be very
> careful. Believe it or not, there is such thing a connection string
> injection attack.
> http://msmvps.com/blogs/williamryan/archive/2006/01/15/81115.aspx
>
> I'd recommend a change though although your approach isn't 'wrong'. I'd
> consider using output parameters to determine if the use is authenticated or
> not and grabbing those values. It can be a lot lighter and login screen
> usually get a good bit of traffice.
>
> HTH,
>
> bill
> "steven scaife" <stevensca***@discussions.microsoft.com> wrote in message
> news:D58021AF-B117-4EFB-8A40-6411CB27A1DB@microsoft.com...
> > Hi i am creating a login routine that i have overloaded and wondered how i
> > could return some data back to my program or even if i am going about the
> > right way of achieving what i want to do.
> >
> > The function takes some parameters the server name, the database, the
> > username and the stored procedure name the other overloaded function takes
> > the same parameters plus a password and two optional parameters for the
> > username and password input parameters for the stored proc defaulted at
> > @username and @password.
> >
> > Then the function opens a data reader using cmd.executereader if the
> > datareader has rows then it returns true if not it returns false. It is
> > meant to be a generic login dll for use in my apps either windows or asp.
> > Sometimes i may need to return the full name of the user or one or two
> > other
> > parameters how could i do this i was thinking about having some public
> > property's but i am unsure of how to go about setting them. I keep
> > getting
> > an error invalid attempt to read when no data is present. When i call
> > dr.hasrows does this then empty my datareader i remember from my course
> > manual that a datareader is readonly forward only?
> >
> > Is this a good way of doing things or have i got the idea mixed up
> >
> > The code is below, by the way i am coming from a predominately ASP
> > background into .net so i know little of it really
> >
> > Public Overloads Function LoginCheck(ByVal Username As String, ByVal
> > Password As String, _
> >    ByVal Server As String, ByVal DBName As String, ByVal StoredProc As
> > String, ByVal numReturns As Integer, Optional _
> >    ByVal Param1 As String = "@Username", Optional ByVal Param2 As String =
> > "@Password") As Boolean
> >        Dim conn As SqlConnection = Nothing
> >        Dim dr As SqlDataReader = Nothing
> >        Dim cmd As New SqlCommand
> >        Dim P1 As New SqlParameter(Param1, SqlDbType.VarChar)
> >        Dim P2 As New SqlParameter(Param2, SqlDbType.VarChar)
> >        Try
> >            conn = New SqlConnection("data source=" & Server & ";
> > integrated
> > security=true; initial catalog = " & DBName)
> >            cmd.CommandText = (StoredProc)
> >            cmd.CommandType = CommandType.StoredProcedure
> >            cmd.Connection = conn
> >
> >            'set up the paramaters
> >            P1.Value = Username
> >            P1.Direction = ParameterDirection.Input
> >            P2.Value = Password
> >            P2.Direction = ParameterDirection.Input
> >
> >            cmd.Parameters.Add(P1)
> >            cmd.Parameters.Add(P2)
> >
> >            conn.Open()
> >            dr = cmd.ExecuteReader
> >
> >            If dr.HasRows Then
> >                Select Case numReturns
> >                    Case 0
> >                        'do nothing
> >                    Case 1
> >                        Return1 = dr("Fullname").ToString
> >                    Case 2
> >
> >                End Select
> >                Return True
> >            Else
> >                Return False
> >            End If
> >
> >        Catch ex As Exception
> >            Console.WriteLine(ex.Message)
> >
> >            If Not dr Is Nothing Then
> >                dr.Close()
> >            End If
> >            If Not conn Is Nothing Then
> >                conn.Close()
> >            End If
> >
> >        Finally
> >            If Not dr Is Nothing Then
> >                dr.Close()
> >            End If
> >            If Not conn Is Nothing Then
> >                conn.Close()
> >            End If
> >        End Try
> >
> >    End Function
> >
> >    Dim m_Return1 As String
> >
> >    Public Property Return1() As String
> >        Get
> >            Return m_Return1
> >        End Get
> >        Set(ByVal Value As String)
> >            m_Return1 = Value
> >        End Set
> >    End Property
> >
>
>
>