mysql - 如何重写此 MySQL 查询以包含参数？

Question

晚上好

我今天整天都在为此工作，但无济于事。我正在使用下面的代码在文本框中获取用户输入并搜索数据库(作为用户类型)并将结果显示在 ListView 控件中。它按原样工作，但我知道它不安全，因为它没有参数化。我知道如何将参数添加到 MySQLCommand 对象，但不知道如何将它添加到查询中。非常感谢任何帮助，谢谢。

这是我的代码:

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

Answer 1

我假设您的问题是通配符无法正常工作。这不起作用，因为参数不能用引号引起来。

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"

要解决这个问题，您可以先将通配符连接到您的参数中，然后再将其插入到您的查询中。

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"