SQL validation of username and password

[darthswift00]

How does one validate username and password from sql database? I have used parameters in doing validating my database, but have noticed that username and passwords are being accepted when i type it straight out(EG. username= e001 , password=dave@road) , even though they have characters in capital letters(such as username=E001 , password=Dave@Road).

Here is my coding:

Private Sub btnlogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnlogin.Click
    Try
        If (txtuname.Text = "-") Or (txtuname.Text = "0") Or (txtuname.Text = "") Or (txtpass.Text = "0") Or (txtpass.Text = "-") Or (txtpass.Text = "") Then
            pberror.Visible = True
            pberror2.Visible = True
            MessageBox.Show("Invalid Entry", "ErrorMessageBox", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtuname.Text = ""
            txtpass.Text = ""
            pberror.Visible = False
            pberror2.Visible = False
            Return
        End If
        empeid = txtuname.Text
        DatasetLogin1.Clear()
        sqldLoginadapter.SelectCommand.Parameters("@eid").Value = txtuname.Text
        sqldLoginadapter.SelectCommand.Parameters("@epass").Value = txtpass.Text
        sqldLoginadapter.Fill(DatasetLogin1, "empdetails")
        If (DatasetLogin1.Tables("empdetails").Rows.Count > 0) Then
            pberror.Visible = False
            pberror2.Visible = False
            pbright.Visible = True
            pbright2.Visible = True
            MessageBox.Show("Login Successfull! Welcome Employee: " & empeid, "MessageBox", MessageBoxButtons.OK, MessageBoxIcon.Information)
            empeid = lblename.Text
            PDL_Main_Menu.NameStrip.Text = empeid
            Me.Hide()
            PDL_Main_Menu.Show()
        Else
            pberror.Visible = True
            pberror2.Visible = True
            MessageBox.Show("Not Registered Employee Details!. Please Try Again", "ErrorMessageBox", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtuname.Text = ""
            txtpass.Text = ""
            pberror.Visible = False
            pberror2.Visible = False
            Return
        End If
    Catch con As SqlClient.SqlException
        MessageBox.Show("Error in the connection. Please try again later", "ErrorConnection", MessageBoxButtons.OK, MessageBoxIcon.Warning)
    End Try
End Sub

I have connected to my database using sqladapter through the wizard, and set my parameters for username and password .

Is there another simple way that i can do validations of username and password?

Thanks.

[G_Waddell]

I think it is because you are usng a sqladapter and then checking for tables - you are getting a table back but does it actually contain data? Or is it empty?

I would use a SqlCommand object and run executescalar on it. Something like:

dim sSQL as string ="SELECT COUNT(UserID) FROM tblUsers WHERE (Userid = @id) AND (Password =@epass)"
dim cmd as new SQLCommand
dim UserCount as integer

with cmd
 .connection = MySqlConnectionObject
 .CommandType = Commandtype.Text
 .Text = sSQL
 .Parameters.AddWithValue("@id",txtUname.text)
 .Parameters.AddWithValue("@epass",txtPass.text)
 end with

 UserCount = cmd.ExecuteScalar

 If UserCount > 0 then
    'valid
else
    'invalid
End if

That way when you run executescalar on the command object you will get a zero back if it is not valid and a one back if it is - if you get a number larger than one your records have been duplicated...

[darthswift00]

Hi thanks for the reply. There is data stored on my database, as it does pick up any of my username and passwords from there. If i type a random username and password thats not on my database, it doesnt pick it up.

My problem is that why doesnt it validate the exact username and password that contains capital letters instead of just reading the characters as is? eg: username = E001 and password is Tom@Danny.(It should validate that when user types his/her username and password , it must be exactly as it's stored in database)not validate it as such: username e001 password=tom@danny.

How to resolve this?

Thanks for your reply ill try this coding out. one thing i want to know, must i delete my sqladapter that i made using wizard when im using your coding? As i have parameters set there already or can i just modifie the sqladapter coding with your select statement?

[Reverend Jim]

SQL queries are normally case insensitive, however you can force a query to be case sensitive. In the following two queries, the first is case insensitive and the second is case sensitive

select * from myTable where myID = '34WASH'
select * from myTable where myID COLLATE Latin1_General_CS_AS = '34WASH'

If you want to set one or more columns to be case sensitive by default you can do

ALTER TABLE mytable 
    ALTER COLUMN mycolumn VARCHAR(50) 
    COLLATE Latin1_General_CS_AS 
GO 

Change the table, column names and sizes as appropriate.

[darthswift00]

I have a problem , as my employees id have a primary key check to them. Can i alter the table column like so:

Alter table empdetails
alter column empid varchar(10)primary key constraint chkempid Check
(empid Like ('E[0-9][0-9][0-9]'))
collate Latin1_General_CS_AS

My employees id's go as follows: E001 , E002 etc