MySQL Login problem 2

[jayemsee]

I am attempting to create a Login form which compares the info that the user enters against a MySQL database for verification. However, when entering the correct information, I still get a failed login.

Here is the code:

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        Dim user As String
        Dim pass As String
        Dim tstUsr As String
        Dim tstPss As String

        'Database Info
        conn = New MySqlConnection()
        conn.ConnectionString = "server=[URL unfurl="true"]www.sqlserver.com;user[/URL] id=sqllogin;password=sqlpassword;database=sqldb"

        'Holds login values
        user = txtUsr.Text
        pass = txtPss.Text

        conn.Open()

        'Gets SQL values from server and stores as String
        tstUsr = "SELECT `users`.`loginname` FROM 'users'"
        tstPss = "SELECT 'users'.'password' FROM 'users'"

        If user = tstUsr And pass = tstPss Then
            frmMain.Show()
            Me.Hide()
            Me.Close()
            conn.Close()
        Else
            conn.Close()
            lblStatus.Text = "Incorrect Login Information!"
        End If
    End Sub

The SQL info is not as it appears here, but is valid when I do a Try.

 

[jayemsee]

If there is a better (easier) way of verifying a login (serverside or not), I would be very interested as well.

I have heard of hashing, but I am very unfamiliar with it. I do not want to use an unsecure way either if possible (i.e. storing the login stuff in the program).

 

[TipGiver]

1. You use ' and `. You dont need them. Write like:
tstUsr = "SELECT users.loginname FROM users"
Also the users is not necessary as you get results from one table only. So tstUsr = "SELECT loginname FROM users" is also correct.

2. The If user = tstUsr And pass = tstPss Then is wrong, and will be always false. You compare the txtUsr text with this string --> "SELECT `users`.`loginname` FROM 'users'".

You have your query for the database to execute, but you didnt tell it to do so! I see that you have missunderstood maaaaany thing on databases.


Try this:
(i do it by formating the query and not using parameters.)


(Wrote it on the fly.... it may have mistakes)

    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click

        ' Holds login values
        Dim user As String = txtUsr.Text
        Dim pass As String = txtPss.Text

        'Database Info
        conn = New MySqlConnection()
        conn.ConnectionString = "server=[URL unfurl="true"]www.sqlserver.com;user[/URL] id=sqllogin;password=sqlpassword;database=sqldb"

        Try
            ' Error may occur
            conn.Open()

            'Gets SQL values from server (database query)
            Dim qy As String = "SELECT loginname FROM users WHERE loginname = '{0}' AND password = '{1}'"

            ' Format it
            qy = String.Format(qy, user, pass)

            ' New command object
            Dim cm As New MySqlCommand(qy, conn)
            Dim r As Object = cm.executescalar
            cm.dispose() : cm = Nothing

            If Not (r Is Nothing) Then 'User found
                Me.Close()
                frmMain.Show()
            Else
                lblStatus.Text = "Incorrect Login Information!"
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        Finally
            If Not (conn.state = onnectionState.Open) Then
                conn.close()
                conn = Nothing
            End If

        End Try

    End Sub

 

[jayemsee]

Thanks so much! I am about to test it out.

Would this also be the reason as to why I am unable to get data to display in a DataGridView? (the 'users'.'blah')?

 

[jayemsee]

Sorry to ask again, just been trying to get this working for a while hehe. Thanks for the help in advance...

Imports MySql.Data.MySqlClient
Imports System.Data

Public Class frmMain
    Private conn As String
    Public WriteOnly Property connectionString() As String
        Set(ByVal value As String)
            Dim conn As String

            conn = "server=[URL unfurl="true"]www.bloodmourne.net;user[/URL] id=bartal_reality;password=k0rg;database=bartal_linkshell"

        End Set
    End Property

    Private Sub BMDB_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub btnRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefresh.Click

        Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim myAdapter As New MySqlDataAdapter
        Dim myData As New DataTable
        Dim qy As String

        conn = New MySqlConnection()
        conn.ConnectionString = "server=[URL unfurl="true"]www.mysqlserver.com;user[/URL] id=user;password=password;database=db"

        'I tried this as you did last time...because it didn't work either heh.
        Try
            conn.Open()
            qy = "SELECT Member Name,Points FROM Points WHERE Member Name = '{0}' AND Points = '{1}'' ORDER BY Member Name ASC"

            Try
                myCommand.Connection = conn
                myCommand.CommandText = qy

                myAdapter.SelectCommand = myCommand
                myAdapter.Fill(myData)

                dgvStatus.DataSource = myData
                dgvStatus.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells

                conn.Close()

            Catch myerror As MySqlException
                MsgBox("There was an error reading from the database: " & myerror.Message)
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try

    End Sub
End Class

 

[jayemsee]

Oh well, the server DB is posted (accidentally), but it only allows 4 specific domains to connect anyway .

 

[earthandfire]

Field [tt]Member Name[/tt] contains a space, I don't know MySQL syntax, but in SQL Server it would be necessary to enclose it in square brackets as: [tt][Member Name][/tt]


Hope this helps.

 

[TipGiver]

I think whatever the database is, the brackets are necessary if the field has a space. Also, '{1}'' should be '{1}'.

So:
qy = "SELECT [Member Name], Points FROM Points WHERE [Member Name] = '{0}' AND Points = '{1}' ORDER BY [Member Name] ASC"

Have a search also for the parameters. I use both the string.format but sometimes i use parameters to complete me query.

 

[jayemsee]

Thank you guys so much for the input.

Well, every time I have tried to correct my mistake, as well as when people give suggestions, I get this...

 

[TipGiver]

GREAT MISTAKE #1
> you showed the username and the password. Any one can harm you. Change it ASAP

 

[TipGiver]

MISTAKE #2

In the try block, you used myCommand.CommandText = qy, but you did not format it, in order to replace {0} and {1}. Try this:

myCommand.CommandText = String.format(qy, txtUsr.text.trim, txtPss.text.trim)

 

[TipGiver]

What is the purpose of the Public WriteOnly Property connectionString() As String

?

 

[jayemsee]

Hmm...Still the same error message.

 

[TipGiver]

Try removing the brackets [] and put `` (not '')

I am sorry but i do not use MySql.

qy = "SELECT `Member Name`, `Points` FROM `Points` WHERE `Member Name` = '{0}' AND `Points` = '{1}'' ORDER BY `Member Name` ASC"

My guess (?) is that you had correctly put ``

 

[jayemsee]

Hmm...Ok...

qy = "SELECT `Member Name`, `Points` FROM `Points` WHERE `Member Name` = '{0}' AND `Points` = '{1}' ORDER BY `Member Name` ASC"

Worked...however, the data which is already in the db does not show up heh...

 

[jayemsee]

LMAO...sorry, nevermind...

Thanks for all the help guys...I got it working now

 

[TipGiver]

Nice
and sorry for the bracket trouble