locked
How to verify if login name and password are match using a ms access 2010 as DB in VB.Net 2010 RRS feed

  • Question

  • User553892048 posted

    Good day,

    Pls help on the codes that im working on..

    I am biginner regarding Database in vb.net

     

    I have a ms access as db

    Field names as follows..

    Uname and UPassword, the table is named as accdb and saved as useracc.mdb in 2k3 format...

    While in my vb.net my main page form has the followings

    • 2 label boxes for Name and Password
    • 2 text boxes namely loginid.text and loginpass.text
    • A button named Login

    Here are the codes that i am working on

    ________________

    Imports System.Data.OleDb

    Public Class Form1

        Private Sub login_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles login.Click

            Dim connection As OleDb.OleDbConnection

            Dim mydb, mystr As String

     

            mystr = ("Provider=Microsoft.JET.OLEDB.4.0;" & _

                     "Data Source=..\useracc.mdb")

            connection = New OleDb.OleDbConnection(mystr)

            connection.Open()

    mydb = "SELECT * FROM accdb where [Uname] =  '" & idtext.Text & "' AND [UPassword] =  '" & passtext.Text & "'"

    I am stock here..............

    dont know how to compare correctly..

    i also try some code but it bypasses the password, meaning if the username is exsisting on the db it will not confirm the password anymore..

    pls help....tnx alot

     

    Tuesday, March 8, 2011 9:52 PM

Answers

All replies

  • User1836848059 posted

    Hi,

    try this

    mydb = "SELECT count(*) FROM accdb where [Uname] =  '" & idtext.Text & "' AND [UPassword] =  '" & passtext.Text & "'"

    Dim MyCmd As New OleDbCommand("mydb",connection)

    int count=cint( MyCmd.ExecuteScalar());

    if count=1

    //go for login and redirect the apge

    else 

     // login faild

     

    (Note:-Check all vb syntax)

    http://www.daniweb.com/web-development/aspnet/threads/6028

    Wednesday, March 9, 2011 12:51 AM
  • User-1199946673 posted

    Be aware of SQL injections. To avoid them, always use parameterized queries:

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    To create a simple login system using Access, read this:

    http://www.mikesdotnetting.com/Article/75/Simple-Login-and-Redirect-for-ASP.NET-and-Access

    But instead of reinventing the wheel, you can also use ASP.NET Forms Authentication with Access (or any other DataSource):

    http://imar.spaanjaars.com/404/using-the-microsoft-access-providers-to-replace-the-built-in-sql-server-providers
    http://imar.spaanjaars.com/560/using-the-microsoft-access-providers-for-membership-roles-and-profile-under-aspnet-4

    But instead of using Access, which isn't really designed for web environments, you can use SQL Server Compact Edition 4.0:

    http://www.microsoft.com/downloads/en/details.aspx?FamilyID=033cfb76-5382-44fb-bc7e-b3c8174832e2

    Wednesday, March 9, 2011 4:22 AM
  • User3866881 posted

    Hi, you can also try this:

     mystr = ("Provider=Microsoft.JET.OLEDB.4.0;" & _

                     "Data Source=..\useracc.mdb")

            connection = New OleDb.OleDbConnection(mystr)

            connection.Open()

    mydb = "SELECT * FROM accdb where [Uname] =  '" & idtext.Text & "' AND [UPassword] =  '" & passtext.Text & "'"

    Dim com As New Command(mydb,connection)

    Dim flag As Boolean = com.Read()

    if(flag)

    {

        //Login successfully....

    }

    else

    {

       //Login failing……

    }

    Thursday, March 10, 2011 8:30 PM
  • User-1199946673 posted

    Hi, you can also try this

    Although your example might work, it also opens the door for a SQL injection attack. An attacker will be able to login without knowing a username/password combination!

    Friday, March 11, 2011 7:24 AM
  • User-1561814533 posted

     mydb = "SELECT * FROM accdb where [Uname] =  '" & idtext.Text & "' AND [UPassword] =  '" & passtext.Text & "'"

     

    As other people have said, if I were to type something like this in the password field:

    ' OR '1'='1

    Then I would have succeeded in bypassing your login check with some sql injection.

    It may mean following links and reading a lot more than you want to but hans_v gives the best advice.

     

    Friday, March 11, 2011 7:43 AM
  • User3866881 posted

    Many thanks for hans_v and frez. Their detailed sample tells you that altough combining splits will work. However it's not safe and will cause the problem of SQL injection. Compared with the solutions. Hans_v is your choice

    :)

    Friday, March 11, 2011 7:21 PM
  • User553892048 posted

    Good Day Sir;

    I do have a follow up question..

    What if i have 2 tables in my ms access db namely accdb and adminaccdb.

    the AIM is will search in the firs table (accdb) and if no account found will search to the second table (adminaccdb)

    ive tried the try and catch, if statement but it fails..

    what is the best to use??..

     

    Thnks for ur help..

    sorry for the late update in my threads..

    been busy in the past days..

    Saturday, March 12, 2011 12:50 AM
  • User3866881 posted

    Hey, have a nice day man!

    Hello, you can try this:

    mystr = "Provider=Microsoft.JET.OLEDB.4.0;" & _

                     "Data Source=..\useracc.mdb"

            connection = New OleDb.OleDbConnection(mystr)

            connection.Open()

    mydb = "SELECT * FROM accdb where [Uname] = @UName and [Password]=@Password

    Dim com As New Command(mydb,connection)

    com.Parameters.AddWithValue("@UserName",YourName)
    com.Parameters.AddWithValue("@Password",YourPassword)

    Dim rd As SqlDataReader = com.Read()

    Dim flag As Boolean = rd.Read()

    If(flag) Then

     'Login successfully....

     

    Else

     'Login failing……
     
     rd.Close()
     
     mydb = "SELECT * FROM adminaccdb where [Uname] = @UName and [Password]=@Password

    Dim com As New Command(mydb,connection)

    com.Parameters.AddWithValue("@UserName",YourName)
    com.Parameters.AddWithValue("@Password",YourPassword)

    Dim rd As SqlDataReader = com.Read()

    Dim flag As Boolean = rd.Read()
     
     If(flag) Then

      'Login successfully....
     Else
      'Do Login fails things……
     End If
    End If

     

    Saturday, March 12, 2011 6:18 AM
  • User-1199946673 posted

    Hello, you can try this:

    Decker. Maybe you should take more time to answer. In your example code your using a OleDbConnection (which is OK), a Command and a SQLDataReader.

    Furthermore, you don't close and dispose the connection, which is very important when using Access. Also, you; r using a datareader to check if a record is retrieved, which is unnecessary. Instead of SELECT *, SELECT Count(*) will return the amount of records that match the search and can be retrieved using ExecuteScalar. And instead of 2 calls to the database, 1 call with will do the job also with only a few lines of code which will also take care of properly closing and disposing the connection:

     

            Dim Authenticated As Boolean
            Using cn As New OleDbConnection(mystr)
                Using cmd As New OleDbCommand("SELECT Count(*) + (SELECT Count(*) FROM adminaccdb where [Uname] = @UName and [Password]=@Password) FROM accdb where [Uname] = @UName and [Password]=@Password", cn)
                    cmd.Parameters.AddWithValue("Uname", "some_username")
                    cmd.Parameters.AddWithValue("Password", "xyz")
                    cn.Open()
                    Authenticated = (cmd.ExecuteScalar > 0)
                End Using
            End Using
    

     

    Saturday, March 12, 2011 9:11 AM
  • User-1199946673 posted

    What if i have 2 tables in my ms access db namely accdb and adminaccdb

    I think you need to rethink your design, because most likely eventually will not work. What if in both tables you've users with the same username. How could you tell which user you're dealing with?

    I really think you should first learne more about how to deal with data before you design a login system. Perhaps you better start using forms authentication, read my first reply in this thread....

    Saturday, March 12, 2011 9:16 AM
  • User553892048 posted

    Good day Sir Hans;

    any suggestion for that??..

    the reason why i separate the table is that different form will appear for a regular user and for an admin.

    basically the reason is that for the restrictions...

     

    Tnx for ur help..

    Saturday, March 12, 2011 11:20 AM
  • User-1199946673 posted

    the reason why i separate the table is that different form will appear for a regular user and for an admin

    That's what I thought already. When you're using forms authentication, you can assign roles to users and secure some pages to be accesible by specific users or users in a role.

    More on Forms authentication:

    http://www.asp.net/web-forms/security

    But by default, this works with SQL Server, but as I said, you can use Access also:

    http://imar.spaanjaars.com/404/using-the-microsoft-access-providers-to-replace-the-built-in-sql-server-providers
    http://imar.spaanjaars.com/560/using-the-microsoft-access-providers-for-membership-roles-and-profile-under-aspnet-4

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 12, 2011 2:12 PM
  • User553892048 posted

    Good day...

     

    Now i redesign my project..ive created a radio button to differentiate whats the standard from admin..

    but i encountered a new problem...if ever there are same password from different user..

    is does not log in for both of user that has same password..

    Help...

    is there a way that i can minimize the code..tnx

    Here is my code:

        Legend:

                 standarduser - name of the table for standard user

                 AdminTable - for the admin user

     

    _________________________________________________________________

     

    Dim myprovider As String

            Dim mydbstandard As String

            Dim mydbadmin As String

     

            myprovider = ("Provider=Microsoft.JET.OLEDB.4.0;" & _

                          "Data Source=..\accountdb.mdb")

            myconnection = New OleDb.OleDbConnection(myprovider)

            mydbstandard = "Select * From standarduser Where UserName = '" & Loginid.Text & "' AND UPassword = '" & loginpass.Text & "'"

            mydbadmin = "Select * From AdminTable Where UserAdmin = '" & Loginid.Text & "' AND PassAdmin = '" & loginpass.Text & "'"

            myconnection.Open()

     

            If suser.Checked = True Then

                Try

                    Dim mycommands As OleDb.OleDbCommand

                    mycommands = New OleDbCommand(mydbstandard, myconnection)

                    Dim sdr As OleDb.OleDbDataReader = mycommands.ExecuteReader

                    If (sdr.Read() = True) Then

                        Me.Hide()

                        MsgBox("Welcome: " & Loginid.Text)

                        profilepage.userpofilename.Text = Me.Loginid.Text

                        profilepage.Show()

                    Else

                        MsgBox("No exsisting User")

                        Loginid.Text = ""

                        loginpass.Text = ""

                    End If

                Catch ex As Exception

                End Try

            ElseIf (auser.Checked = True) Then

                Try

                    Dim mycommanda As OleDb.OleDbCommand

                    mycommanda = New OleDbCommand(mydbadmin, myconnection)

                    Dim sdr As OleDb.OleDbDataReader = mycommanda.ExecuteReader

                    If (sdr.Read() = True) Then

                        Me.Hide()

                        MsgBox("Welcome: " & Loginid.Text)

                        AdminPage.Adminname.Text = Me.Loginid.Text

                        AdminPage.Show()

                    Else

                        MsgBox("No exsisting Adminastrator")

                        Loginid.Text = ""

                        loginpass.Text = ""

                    End If

                Catch

                End Try

     

            ElseIf (suser.Checked = False And auser.Checked = False) Then

                    MsgBox("Choose type of user")

            Else

                    MsgBox("User name and password did not match")

                    Loginid.Text = ""

                    loginpass.Text = ""

            End If

            myconnection.Close()

     

    _______________________________________________________________________________________

     

    Tnx for the help..

    Monday, March 14, 2011 10:47 AM
  • User-1199946673 posted

    You have been directed to many links already explaining how to write better and more secure code, but you don't seem to read them. I suggest you firts start to read them!

    Monday, March 14, 2011 1:29 PM