Destin Smith Destin Smith - 5 months ago 16
MySQL Question

MySQL commands in vb code are not working

I've been trying to create a login page that will check if you're an administrator or a customer in my SQL data source. I am not sure why it can't understand the

MySQLCommand
s. I added
MySql.Data
in the references but this doesn't seem to work.

ASPX

<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
<div id="registrerenbody">
<asp:Login ID="wcAanmelden" runat="server" DestinationPageUrl="~/default.aspx" style="margin: 0 auto;" >

<InstructionTextStyle Font-Italic="True" ForeColor="Black" />
<LayoutTemplate>

<div class="box" style="margin-left:auto;margin-right:auto;">
<div id="boxlogo"></div>
<div class="contentLogin">
<h3>Gebruiker Aanmelden</h3>
<asp:textbox class="field" placeholder="Gebruikersnaam" onfocus="this.placeholder = ''" onblur="this.placeholder = 'Gebruikersnaam'" id="UserName" runat="server"></asp:textbox>
<asp:RequiredFieldValidator ID="UserNameRequired" runat="server" ControlToValidate="UserName" ErrorMessage="User Name is required." ToolTip="User Name is required." ValidationGroup="wcAanmelden">*</asp:RequiredFieldValidator>
<br>
<asp:textbox class="field" placeholder="Paswoord" onfocus="this.placeholder = ''" onblur="this.placeholder = 'Paswoord'" id="Password" runat="server" textmode="Password"></asp:textbox>
<asp:RequiredFieldValidator ID="PasswordRequired" runat="server" ControlToValidate="Password" ErrorMessage="Password is required." ToolTip="Password is required." ValidationGroup="wcAanmelden">*</asp:RequiredFieldValidator>
<br>
<asp:button class="btn" id="LoginButton" runat="server" commandname="Login" text="Aanmelden" validationgroup="wcAanmelden"></asp:button>
<br>
<asp:Literal ID="FailureText" runat="server" EnableViewState="False"></asp:Literal>
</div>
</div>
</LayoutTemplate>


</asp:Login>
</div>
</asp:Content>


CodeBehind ASPX.VB

This is where for example:
MySqlConnection
and
MySqlCommand
have blue underlinement.

Public Class WebForm3
Inherits System.Web.UI.Page
Protected Sub wcAanmelden_Authenticate(sender As Object, e As AuthenticateEventArgs) Handles wcAanmelden.Authenticate
Dim strUser As String = wcAanmelden.UserName
Dim strPassword As String = wcAanmelden.Password
Dim strSQL, strSQL2, strEmail As String
Dim strNiveau As String
Dim con As MySqlConnection = New MySqlConnection()
con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("LoginConnectionString").ConnectionString

strSQL = "SELECT name FROM employer WHERE (login= '" &
strUser & "' AND password = '" & strPaswoord & "')"
Dim cmd As MySqlCommand = New MySqlCommand '(strSQL, con)
cmd.Connection = con
cmd.CommandText = strSQL
con.Open()
If cmd.ExecuteScalar() = Nothing Then
strNiveau = ""
Else
strNiveau = "W"
End If
con.Close()
If strNiveau = "" Then
strSQL = "SELECT name FROM customers WHERE (login= '" &
strUser & "' AND password = '" & strPassword & "')"
Dim cmd2 As MySqlCommand = New MySqlCommand '(strSQL, con)
cmd2.Connection = con
cmd2.CommandText = strSQL
con.Open()
If cmd2.ExecuteScalar() <> Nothing Then
strNiveau = "K"
Else
End If
End If
If strNiveau = "K" Then
Dim con2 As MySqlConnection = New MySqlConnection()
con2.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("LoginConnectionString").ConnectionString
strSQL2 = "SELECT email FROM customers WHERE (login= '" &
strUser & "' AND password = '" & strPaswoord & "')"
Dim cmd3 As MySqlCommand = New MySqlCommand(strSQL2, con2)
con2.Open()
strEmail = cmd3.ExecuteScalar().ToString
Session("Niveau") = strNiveau
Session("Username") = strUser
Session("Password") = strPassword
Session("email") = strEmail
con2.Close()
FormsAuthentication.RedirectFromLoginPage(strUser, False)
ElseIf strNiveau = "W" Then
Dim con2 As MySqlConnection = New MySqlConnection()
con2.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("LoginConnectionString").ConnectionString
strSQL2 = "SELECT email FROM employers WHERE (login= '" &
strUser & "' AND password = '" & strPassword & "')"
Dim cmd3 As MySqlCommand = New MySqlCommand(strSQL2, con2)
con2.Open()
strEmail = cmd3.ExecuteScalar().ToString
Session("Niveau") = strNiveau
Session("Username") = strUser
Session("Password") = strPassword
Session("email") = strEmail
con2.Close()
FormsAuthentication.RedirectFromLoginPage(strUser, False)
Else
wcAanmelden.FailureText = "Try again! Wrong input."
End If
con.Close()
End Sub
End Class

Answer

Password is a reserved word in MySql. If you want to use a field with that name then everytime you use it in your code you should remember to put it between backticks:

 `password` = ...

Said that your code has serious problems. You should never concatenate strings coming from the user input to form a sql text. This leads to syntax errors caused by parsing problem and to Sql Injection attacks. You shoul use a parameterized query like this

   strSQL = "SELECT name FROM employer WHERE (login=@login AND `password`=@pwd"
   Dim cmd As MySqlCommand = New MySqlCommand(strSQL, con)
   cmd.Parameters.Add("@login", MySqlDbType.VarChar).Value = strUser 
   cmd.Parameters.Add("@pwd",MySqlDbType.VarChar).Value = strPaswoord
   con.Open()
   If cmd.ExecuteScalar() = Nothing Then
      ....

Finally you should also change the way you get your data because you want to minimize the trips to access the database for performance reason. You should SELECT both the Name and the EMail with a single query and use an MySqlDataReader to get the data.

Other problems present in your code are the lack of appropriate using statement around the connection and the security problem caused by a possible clear text password stored in the database.

Comments