CODEWITHSUNDEEP
sql-servervb.net
Akaglo
Akaglo

Reputation: 127

How to Make Password Case Sensitive

I have the following code that verifies user id and password, which are retrieved from SQL Server, but I want the verification of the password to be case sensitive. 

    Dim frm As New MarksEntryFrm
    Dim flag As Boolean
    flag = False
    cmd = New SqlCommand("Select a.Form,a.AcademicYear,b.SubjectID,b.UserID,b.Password,c.Term from StudentDetails.Programmes a, StudentDetails.Subjects b,RegistrationDetails.Registration c where b.SubjectID='" & cboSubjCode.SelectedItem & "' and b.UserID='" & txtUserName.Text & "' and b.Password='" & txtPassword.Text & "'", cn)
    dr1 = cmd.ExecuteReader
    ctr = ctr + 1
    If dr1.Read Then
        frm.Show()
        ctr = 0
        Me.Hide()
    ElseIf ctr < 3 Then
        MessageBox.Show("Incorrect Subject Code,User Name or Password. Please try again.", "Wrong data entered", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
    Else
        MsgBox("Unathorized access. Aborting...")
        Close()
    End If
    dr1.Close()

Upvotes: 0

Views: 9763

Answers (4)

talha2k
talha2k

Reputation: 25493

Why are you not hashing your password???? Now Thats Primitive...

Well Case-sensitivity is controlled by the Collation property of columns in SQL Server Table Designer.

The default is not case sensitive.

Here are two solutions for you to try:

  1. In SQL Server Table Designer, modify the Collation property of Password field in Column Properties window, check the "Case Sensitive" CheckBox.

  2. First retrieve the password from database into a variable, and then compare it with the TextBoxPassword.Text.

You can see an example here: http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/2d805aaa-d7d8-4183-8a12-cb578df5d8d8

Hope this helps.

Upvotes: 5

Elias Hossain
Elias Hossain

Reputation: 4469

Use your SQL as below:

You do need to use COLLATE in this case.

cmd = New SqlCommand("Select a.Form,a.AcademicYear,b.SubjectID,b.UserID,b.Password,c.Term from StudentDetails.Programmes a, StudentDetails.Subjects b,RegistrationDetails.Registration c where b.SubjectID='" & cboSubjCode.SelectedItem & "' and b.UserID='" & txtUserName.Text & "' and b.Password='" & txtPassword.Text & "' COLLATE Latin1_General_BIN ", cn)

Upvotes: 1

TomTom
TomTom

Reputation: 62157

I have the following code that verifies user id and password, which are retrieved from the SQL Server,

And you are sued for gross neglect. YOu should never even store a non-hashed password in sql server. If you pull te password hash out of sql server you have no problem with the cae insensitivity of the standard sql collation.

And ou are sued again for gross neglect - you should never pass unsanitized user input into a sql string. This is common knowedge by now by professional standards, not adhering to them means you are fully liable to the damage.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

To literally answer your question, you could add the following to your password comparison:

and b.Password='" & txtPassword.Text & "' collate Latin1_General_CS_AS"

But before you go any further, go and read up on SQL Injection (and so switch to a parameterised query), and any recent news report about the release of people's passwords (and so, find out about password hashing).

So, marginally better would be:

cmd = New SqlCommand("Select a.Form,a.AcademicYear,b.SubjectID,b.UserID,b.Password,c.Term from StudentDetails.Programmes a, StudentDetails.Subjects b,RegistrationDetails.Registration c where b.SubjectID=@SubjectID and b.UserID=@UserID and b.Password=@Password collate Latin1_General_CS_AS", cn)
cmd.Parameters.AddWithValue("@Subject",txtSubject.Text)
cmd.Parameters.AddWithValue("@UserID",txtUserName.Text)
cmd.Parameters.AddWithValue("@Password",txtPassword.Text)

Which at least protects you from SQL Injection (although AddWithValue isn't without its own issues).

Upvotes: 4

Related Questions