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

Question

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

Tomcat · Accepted Answer · 2013-02-22 23:14:25Z

2

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"

answered Feb 22, 2013 at 23:14

Tomcat

6146 silver badges18 bronze badges

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

2 Comments

Kismet Agbasi Over a year ago

Thanks Tomcat, unfortunately it didn't work for me. While no exceptions are thrown, the DO WHILE statement is being skipped when I modify the query as you suggested. I tried doing this instead and got the same results: LIKE CONCAT('%', @search_string, '%') with the parameter define as: dbCmd.Parameters.AddWithValue("@search_string", TextBoxSearch.Text). Any other suggestions?

Kismet Agbasi Over a year ago

please accept my apologies. I made a mistake in my variable declaration. I did Dim parameter AS String = instead of what you provided. All is good now and it works, thanks.

Fionnuala · Accepted Answer · 2013-02-22 23:26:16Z

0

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

answered Feb 22, 2013 at 23:26

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

3 Comments

Kismet Agbasi Over a year ago

Remou, thanks for your response. I haven't mastered Stored Procedures yet so haven't tried that approach. In any case, your suggestion didn't work for me - not sure why. I actually got an exception telling me to check my query.

Fionnuala Over a year ago

It was tested with SQL Server, not MySQL, so that might be the problem.

Kismet Agbasi Over a year ago

thanks, I really do appreciate the assistance. Tomcat's suggestion did the trick.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related