How can i change sql queries to parameterized?

Question

I want to change this parameterized query

On Error Resume Next
        Dim timex As String
        Dim isigroup As DataTable
        objdata = New clsMSSQL
        isigroup = objdata.QueryDatabase("SELECT * FROM Userx WHERE Username='" & txtuser.Text & "' AND Userpass ='" & txtpassword.Text & "'")
        If isigroup.Rows.Count > 0 Then
            For i = 0 To isigroup.Rows.Count - 1
                If isigroup.Rows(i)("username") <> txtuser.Text Or isigroup.Rows(i)("userpass") <> txtpassword.Text Then
                    MsgBox("Access denied username and password !!!", MsgBoxStyle.Information, "Attention.....")
                    xcountx = xcountx + 1
                    If xcountx >= 3 Then
                        MsgBox("You have reach the maximum time of login !!", MsgBoxStyle.Exclamation, "Προσοχή.....")
                        End
                    End If
                    Exit Sub
                End If
            Next
            username = isigroup.Rows(0)("Username")
            xUser_ID = isigroup.Rows(0)("User_id")
            xUser_Access = isigroup.Rows(0)("Access_Type")
            timex = TimeOfDay
            isigroup = objdata.QueryDatabase("INSERT INTO Audit_Log (User_ID, Login) VALUES(" & xUser_ID & ", '" & timex & "')")
            isigroup = objdata.QueryDatabase("SELECT * FROM Audit_Log ORDER BY LOG_ID DESC")
            LOGID = isigroup.Rows(0)("LOG_ID")
            Audit_Trail(xUser_ID, TimeOfDay, "Login to system ")

I tried a lot but i can't make it please help

This is the class

Imports System.Data.SqlClient

Public Class clsMSSQL

    Public Shared con As New SqlConnection(constring)
    Private DbSwtable As DataTable

    Public Function QueryDatabase(ByVal Query As String) As DataTable

        Try
            Dim objDataSet As New DataSet
            Dim objDataTable As New DataTable
            Dim objDataAdapter As New SqlDataAdapter(Query, con)
            objDataAdapter.Fill(objDataSet, "DefaultTable")
            objDataTable = objDataSet.Tables("DefaultTable")
            con.Close()

            Return objDataTable
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Λάθος", MessageBoxButtons.OK, MessageBoxIcon.Error)

            Return DbSwtable
        End Try
    End Function

Get rid of the On Error Resume Next - use Try Catch blocks. You need to post the code for QueryDatabase, as that is where your query appears to be executed and where you will do the parameterization. — Tim
– Tim, Commented Jun 29, 2013 at 14:58
Something is wrong in objDataAdapter.SlectCommand = selectCmd — jomachi
– jomachi, Commented Jun 29, 2013 at 15:28

Fabian Bigler · Accepted Answer · 2013-06-29 15:26:54Z

3

Edit the function QueryDataBase like this:

Add parameters username and password and use the SelectCommand Property of your DataAdapter. Also change the name of the Function from QueryDatabase to GetUserData.

Public Function GetUserData(username as string, password as string) As DataTable
        Try
            Dim objDataSet As New DataSet
            Dim objDataTable As New DataTable
            Dim sql As String = "SELECT * FROM Userx WHERE Username=@Username AND Userpass=@Userpass"
            Dim objDataAdapter As New SqlDataAdapter()
            Dim selectCmd as new SqlCommand(sql, con)
            selectCmd.Parameters.Add("@Username", SqlDbType.Varchar).Value = UserName 
            selectCmd.Parameters.Add("@UserPass", SqlDbType.Varchar).Value =Password 
            objDataAdapter.SelectCommand = selectCmd;
            objDataAdapter.Fill(objDataSet, "DefaultTable")
            objDataTable = objDataSet.Tables("DefaultTable")
            con.Close()

            Return objDataTable
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Λάθος", MessageBoxButtons.OK, MessageBoxIcon.Error)

            Return DbSwtable
        End Try
    End Function

Then from the UI, call the function like this:

isigroup = objdata.GetUserData(txtuser.Text, txtpassword.Text)

edited Jun 29, 2013 at 15:26

answered Jun 29, 2013 at 15:01

Fabian Bigler

10.9k6 gold badges52 silver badges74 bronze badges

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

6 Comments

jmoreno Over a year ago

Never use password, use password hash instead.

Fabian Bigler Over a year ago

@jmoreno I agree. But I can't include everything in one answer, that would result in being too broad.

jmoreno Over a year ago

I know, but your example can do the right thing, even if you don't go into the reason WHY it is the right thing.

Fabian Bigler Over a year ago

Your assumption could be wrong. If the question owner has no password hashes on his database, we cant help him with little effort. Thats why i rather focus on answering the question appropriately, rather than ending up with an answer which covers all possible variants. ;)

jmoreno Over a year ago

If there's no hash, that is a bigger problem than not being able to use parameters.

|

jmoreno · Accepted Answer · 2013-06-30 03:58:04Z

1

Modified version of Fabian's answer: Edit the function QueryDataBase like this:

Add parameters for the username and the hash of the password and use the SelectCommand Property of your DataAdapter. Also change the name of the Function from QueryDatabase to GetUserData.

Public Function GetUserData(username as string, PassHash as string) As DataTable
    Try
        Dim objDataSet As New DataSet
        Dim objDataTable As New DataTable
        Dim sql As String = "SELECT * FROM Userx WHERE Username=@Username AND PassHash =@PassHash"
        Dim objDataAdapter As New SqlDataAdapter()
        Dim selectCmd as new SqlCommand(sql, con)
        selectCmd.Parameters.Add("@Username", SqlDbType.Varchar).Value = UserName 
        selectCmd.Parameters.Add("@PassHash", SqlDbType.Varchar).Value =PassHash 
        objDataAdapter.SelectCommand = selectCmd;
        objDataAdapter.Fill(objDataSet, "DefaultTable")
        objDataTable = objDataSet.Tables("DefaultTable")
        con.Close()

        Return objDataTable
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Λάθος", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Return DbSwtable
    End Try
End Function

Then from the UI, call the function like this:

isigroup = objdata.GetUserData(txtuser.Text, gethash(txtpassword.Text))

edited Jun 30, 2013 at 3:58

answered Jun 29, 2013 at 17:07

jmoreno

13.7k6 gold badges66 silver badges103 bronze badges

2 Comments

jomachi Over a year ago

Hello my friend I found BCrypt for hash I can store password to database but I don't know how to use it to cmdLogin. Can you help?

Andrew Barber Over a year ago

I know you've edited to include that you took your answer from Fabian's, but it's an extremely large amount of code you took from his. I'm not sure how comfortable the community would be with that.

Collectives™ on Stack Overflow

How can i change sql queries to parameterized?

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related