Comparing String Variable to SQL table VB.Net

Question

So By Now I've Probably had little experience with Database Programming and just SQL in general, so if what I say is beyond confusing I Apologize. I've been trying to create a login form that accesses my database table and compare what the User has written to the table itself. After tirelessly searching the internet, I can't seem to understand why the Code I've written can't read the Table. here is an example maybe someone can help me understand my issue?

Public Function CompareDbValues(Compare_1 As String)
    Using connection As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\User\OneDrive\Software\WindowsApplication1\WindowsApplication1\Database1.mdf;Integrated Security=True")
        connection.Open()

        Dim sSQL As String = "SELECT UserName, Password FROM [Table] WHERE UserName = 'Me.UserName.Text' AND Password = 'Me.Password.Text'"
        Using Command As New SqlCommand(sSQL, connection)
            Dim Reader As SqlDataReader
            Reader = Command.ExecuteReader()

            If Reader.HasRows Then
                Do While Reader.Read()
                    If Compare_1 = Reader("UserName").ToString Then

                        Return True

                    Else

                        Return False

                    End If
                Loop
            End If


        End Using


        connection.Close()
    End Using

    Return False

End Function

My issue Seems to be when my code hits the "If Reader.HasRows Then" Line. Once again thanks for taking a look.

Incidentally, you might want to get into the habit of keeping passwords secure, e.g.: Salted Password Hashing - Doing it Right. — Andrew Morton
– Andrew Morton, Commented Aug 27, 2015 at 8:46

Zohar Peled · Accepted Answer · 2015-08-27 07:17:38Z

2

First, Your sql is sent to the database like this:

SELECT UserName, Password 
FROM [Table] 
WHERE UserName = 'Me.UserName.Text' AND Password = 'Me.Password.Text'

Since I Assume no user will select Me.UserName.Text as a user name and Me.Password.Text as a password, I think no one will ever pass this login.

Second, if you are thinking of fixing this by concatenating the textboxes texts to the sql string, think again. This is a major security hazard. Read about Sql injection attacks.

The proper way is to use parameterized queries or stored procedures.

Dim sSQL As String = "SELECT 1 " & _
                     "FROM [Table] " & _
                     "WHERE UserName = @UserName " & _
                     "AND Password = @PassWord " & _
                     "AND @UserName = @Compare"
    Using Command As New SqlCommand(sSQL, connection)
        Command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = Me.UserName.Text
        Command.Parameters.Add("@Password", SqlDbType.VarChar).Value = Me.Password.Text
        Command.Parameters.Add("@Compare", SqlDbType.VarChar).Value = Compare1
        Dim Reader As SqlDataReader = Command.ExecuteReader()
        Return Reader.HasRows
    End Using

Note that the where clause already compared the values for you, so all you have to do is just see if any rows are returned by the query.

edited Aug 27, 2015 at 7:17

answered Aug 27, 2015 at 6:15

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Codedin97 Over a year ago

Thanks for bringing this to my attention i'll read up on it so i can Fully understand what you have done, also thanks for clearing up my issue with my Query as well

jmcilhinney · Accepted Answer · 2015-08-27 10:06:59Z

0

This is your problem here:

Dim sSQL As String = "SELECT UserName, Password FROM [Table] WHERE UserName = 'Me.UserName.Text' AND Password = 'Me.Password.Text'"

You're telling the database to compare the values in the database to the literal text "Me.UserName.Text" and "Me.Password.Text". Nowhere are you actually getting the text the user has entered and sending it to the database. Note how, in the code you posted, those parts are red? That means they are part of the literal String. If you were using variables then they would not be red. This is what you actually meant to do:

Dim sSQL As String = "SELECT UserName, Password FROM [Table] WHERE UserName = '" & Me.UserName.Text & "' AND Password = '" & Me.Password.Text & "'"

That is now using variables and getting data from them and concatenating that data into the literal String.

That's still not very good code though. I won't go into it here because it goes beyond the scope of your original question but you should learn how to use parameterised queries, which protect you against various issues and, most importantly, SQL injection attacks.

edited Aug 27, 2015 at 10:06

answered Aug 27, 2015 at 6:14

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

4 Comments

Zohar Peled Over a year ago

Though are answers are basically the same, Why show the wrong way of doing it instead of the right way?

Codedin97 Over a year ago

Ah ok that makes sense i'll amend that immediately and let you know if it works.

jmcilhinney Over a year ago

@ZoharPeled, because I have answered the question as asked, i.e. why the code, as it was written, was not working. It's possible that this is a school assignment for which using a parameterised query would be overkill and possibly considered wrong because it was using material not yet taught.

Codedin97 Over a year ago

Yeah its not a school assignment however its a personal project but i might also i 'm in my last year of high school this year so parameterised query was a little beyond me at the moment, but i appreciate both your responses regardless you both brought to light something i did not know.

Collectives™ on Stack Overflow

Comparing String Variable to SQL table VB.Net

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related