login routine with information returned from database

[Guest]

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

 

[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'

 

[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

 

[Guest]

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