dcsimg
Results 1 to 6 of 6

Thread: [RESOLVED] How can I make these mysql queries parameterized?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2014
    Posts
    428

    Resolved [RESOLVED] How can I make these mysql queries parameterized?

    Hello,

    I want to parameterize all my mysql queries of my application for protection against SQL Injection. I have done turning almost all my old mysql queries to parameterized queries. But I get stuck on two of the queries and was unable to convert them.

    #1
    Code:
    frm.lv_OnlineUsers.Items.Clear()
                    ds = New DataSet
                    da = New MySqlDataAdapter("SELECT * FROM members WHERE status=1", conn)
                    da.Fill(ds, "members")
                    If ds.Tables("members").Rows.Count > 0 Then
                        For i As Integer = 0 To ds.Tables("members").Rows.Count - 1
                            With frm.lv_OnlineUsers.Items.Add(ds.Tables("members").Rows(i).Item(0).ToString)
                                .SubItems.Add(ds.Tables("members").Rows(i).Item(2).ToString)
                                .SubItems.Add("")
                            End With
                        Next
                    End If
                    conn.Close()
    How can I turn the above thing to parameter and also fill my items as per the conditions above???


    #2
    Code:
    Try
                    ds = New DataSet
                    da = New MySqlDataAdapter("SELECT username, password, status FROM members WHERE username='" & username.Text & "' AND password='" & password.Text & "'", conn)
                    da.Fill(ds, "members")
                    If ds.Tables("members").Rows.Count > 0 Then
                        If CDbl(ds.Tables("members").Rows(0).Item("status").ToString) = 0 Then
                            If ds.Tables("members").Rows(0).Item("username").ToString = username.Text Then
                                If ds.Tables("members").Rows(0).Item("password").ToString = password.Text Then
                                    Try
                                        Using command As New MySqlCommand("UPDATE members SET status=?Status WHERE username=?LoggedUser", conn)
                                            command.Parameters.AddWithValue("@Status", 1)
                                            command.Parameters.AddWithValue("@LoggedUser", username.Text)
                                            conn.Open()
                                            command.ExecuteNonQuery()
                                        End Using
                                        conn.Close()
                                    Catch ex As Exception
                                        conn.Close()
                                        MsgBox("ERROR #112: Error in login database query. Please report the error number to the developer.", MsgBoxStyle.Critical, "ERROR")
                                    End Try
                                End If
                            End If
                        Else
                            conn.Close()
                            MsgBox("No multiple logins allowed!!!", MsgBoxStyle.Information, "Multiple Login")
                        End If
                    Else
                        conn.Close()
                        MsgBox("Login Failed. Incorrect Credentials. Please try again with correct credentials.", MsgBoxStyle.Critical, "ERROR")
                    End If
                Catch ex As Exception
                    conn.Close()
                    MsgBox("ERROR #113: Problem in database query!!! Please report the error number to the developer.", MsgBoxStyle.Critical, "ERROR")
                End Try
    The above code is a login system having no multiple logins and username password wrong system along with connectivity problem msg. I have successfully done parameter query at the updating member status from 0 to 1. But wasn't able to do parameter query at the first portion of datatable rows count and username password matching. Please help me doing parameter query on these things.


    Thanks in advance...

  2. #2
    Hyperactive Member
    Join Date
    Mar 2012
    Posts
    311

    Re: How can I make these mysql queries parameterized?

    For #1, since the entire query is hard-coded (ie you don't have any user input to alter the query), you don't need to use parameters. For #2, however, you should be using parameters for the very first SELECT query; I think this is what your 2nd question is about... Essentially change that query to be in the parameterized format as the UPDATE one for changing the status. Then to add the parameters, use:
    Code:
    da.SelectCommand.Parameters.AddWithValue(@username, username.Text)
    da.SelectCommand.Parameters.AddWithValue(@password, password.Text) '

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2014
    Posts
    428

    Re: How can I make these mysql queries parameterized?

    Hello Pyth,

    I totally understand your answer #1 and also applied that thing. But I didn't understand what method you asked me to use on the #2 query method. The STATUS query at #2 have 4 jobs to do. First job is to count the rows of the table using ds.Tables("members").Rows.Count > 0 , the second job is to verify that if the value of status is 0 or not using ds.Tables("members").Rows(0).Item("status").ToString) = 0 , the third job is matching the username with the database one using ds.Tables("members").Rows(0).Item("username").ToString = username.Text and the 4th one is matching the password of the selected username if username found on the database using the command ds.Tables("members").Rows(0).Item("password").ToString = password.Text .

    Code:
    da.SelectCommand.Parameters.AddWithValue(@username, username.Text)
    da.SelectCommand.Parameters.AddWithValue(@password, password.Text)
    This is the command provided by you for parameter query which seems only of the username and password matching. But this code seems also uses my normal mysql data adapter declared above in da. I want that these 4 jobs on the SELECT query command should use parameter system. So, how can I row count, check status=0, match username and password of the matched username using parameter?

    Please help me guys.
    Thanks in advance.....

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2014
    Posts
    428

    Re: How can I make these mysql queries parameterized?

    Actually have done the system using your method. Thanks pyth for the suggestion.

  5. #5
    Hyperactive Member
    Join Date
    Mar 2012
    Posts
    311

    Re: [RESOLVED] How can I make these mysql queries parameterized?

    I'm glad you got it working... Yeah, the parameters are just how the query gets set up and sent to the database; all of those jobs you listed are done after the database has returned the table of records and the parameters no longer have a bearing on the result. So you'd just handle the results the same way. A few other things I noticed too... Firstly, you can .Fill a DataTable instead of a DataSet if you want; since you are only retrieving one table, you might want to do that instead. This would save from having to navigate from the DataSet to the Table. So instead of "ds.Tables("members").Rows(0)..." you'd just have to use "dt.Rows(0)..." (assuming dt is the variable name of the DataTable that you .Filled). The 2nd thing I noticed is that your SELECT query is only returning those records WHERE the username field = username.Text, and likewise for password. So your code is being redundant when you later check for those in your If-statements. If the username and passwords in the database matches those typed in the TextBoxes, then that row will be returned, which is what your first "If ds.Tables("members").Rows.Count > 0 Then" is checking. Thus you could shorten your code by just checking 1) if the returned DataSet / DataTable has any rows and 2) check if the status = 0. You could even shorten it further were you to also include "status = 0" in the WHERE-clause of your query, but doing so would not allow you to determine whether the login failed due to an incorrect login credentials vs trying to login multiple times, so because of that reason I'd just stick with what you already have. Finally, you may want to change the MsgBox for multiple logins from MsgBoxStyle.Information to MsgBoxStyle.Exclamation or even .Critical since those are more often used for alerting the user to an error vs. just providing some useful data.

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2014
    Posts
    428

    Re: [RESOLVED] How can I make these mysql queries parameterized?

    Thanks for the suggestion. I have applied dataTable instead of dataset and reduces the code further.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width