CODEWITHSUNDEEP
mysqlvb.net
Kismet Agbasi
Kismet Agbasi

Reputation: 567

How Do I Re-Write This MySQL Query to Include a Parameter?

Good evening,

I've been working at this all day today to no avail. I'm using the code below to take user input in a textbox and search through the database (as the user types) and present the results in a ListView control. It works as-is, but I know it's insecure because it's not parametized. I know how to add the parameter to the MySQLCommand object but can't figure out how to add it to the query. Any help would be greatly appreciated, thanks.

HERE'S MY CODE:

Private Sub TextBoxSearch_TextChanged(sender As Object, e As System.EventArgs) Handles TextBoxSearch.TextChanged

    Dim dbConn As New MySqlConnection(String.Format("Server={0};Port={1};Uid={2};Password={3};Database=accounting", FormLogin.ComboBoxServerIP.SelectedItem, My.Settings.DB_Port, My.Settings.DB_UserID, My.Settings.DB_Password))
    Dim dbQuery As String = ""
    Dim dbCmd As New MySqlCommand
    Dim dbReader As MySqlDataReader
    Dim a, b, c, d, f, g

    Try
        dbQuery = "SELECT * FROM cc_master INNER JOIN customer ON customer.accountNumber = cc_master.customer_accountNumber " & _
            "WHERE nameCOMPANY OR accountNumber LIKE '%" & TextBoxSearch.Text & "%' " & _
            "ORDER BY nameCOMPANY ASC"
        dbConn.Open()
        dbCmd = New MySqlCommand(dbQuery, dbConn)
        dbReader = dbCmd.ExecuteReader()

        ListViewRecords.Items.Clear()

        Do While dbReader.Read()

            a = (dbReader.Item("ccID").ToString())
            b = (dbReader.Item("accountNumber").ToString())
            c = (dbReader.Item("nameCOMPANY").ToString())
            d = DecryptCard(dbReader.Item("ccNumber").ToString())
            f = (dbReader.Item("ccExpireMonth").ToString())
            g = (dbReader.Item("ccExpireYear").ToString())

            Dim item As ListViewItem = ListViewRecords.Items.Add(a)
            item.SubItems.Add(b)
            item.SubItems.Add(c)
            item.SubItems.Add(d)
            item.SubItems.Add(f)
            item.SubItems.Add(g)
        Loop
        dbReader.Close()
    Catch ex As Exception
        MessageBox.Show("A DATABASE ERROR HAS OCCURED" & vbCrLf & vbCrLf & ex.Message & vbCrLf & _
                            vbCrLf + "Please report this to the IT/Systems Helpdesk at Ext 131.")
    End Try
    dbConn.Close()
    dbCmd.Dispose()

End Sub

Upvotes: 1

Views: 636

Answers (2)

Fionnuala
Fionnuala

Reputation: 91316

Did you mean something like:

    dbQuery = "SELECT * FROM cc_master INNER JOIN customer " & _
        "ON customer.accountNumber = cc_master.customer_accountNumber " & _
        "WHERE nameCOMPANY OR accountNumber LIKE '%' + ? + '%' " & _
        "ORDER BY nameCOMPANY ASC"

    dbConn.Open()
    dbCmd.Connection = dbConn
    dbCmd.CommandType = CommandType.Text
    dbCmd.CommandText = dbQuery
    dbCmd.Parameters.AddWithValue("?", TextBoxSearch.Text)
    dbReader = dbCmd.ExecuteReader()

However, I think you would probably be better with a stored procedure and pass parameters to that http://www.mysqltutorial.org/stored-procedures-parameters.aspx

Upvotes: 0

Tomcat
Tomcat

Reputation: 614

I'm assuming your issue is the wildcards not working correctly. This does not work as the parameter cannot be enclosed in quotes.

dbQuery = "SELECT * FROM cc_master INNER JOIN customer ON customer.accountNumber = cc_master.customer_accountNumber " & _
        "WHERE nameCOMPANY OR accountNumber LIKE '%?ParameterName%' " & _
        "ORDER BY nameCOMPANY ASC"

To fix, you can concat the wildcards into your parameter first and then insert it into your query.

Dim parameter = "%" & TextBoxSearch.Text & "%"

dbQuery = "SELECT * FROM cc_master INNER JOIN customer ON customer.accountNumber = cc_master.customer_accountNumber " & _
        "WHERE nameCOMPANY OR accountNumber LIKE ?ParameterName " & _
        "ORDER BY nameCOMPANY ASC"

Upvotes: 2

Related Questions