Adding Parameters to OleDBConnection

Question

I'm executing the below to run a query from an Access DB.

  Dim search As String = txtUnitCode.Text
    Dim sText As String = String.Empty
    Dim aClients As String = My.Settings.ClientDB
    Dim sConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & aClients & ""

    Using cn As New OleDb.OleDbConnection(sConnString)
        cn.Open()
        If txtUnitCode.Text = "" Then Exit Sub
        Dim cmd As New OleDb.OleDbCommand("SELECT Name FROM Units WHERE (Code = " & search & ") ", cn)


        Dim r As OleDb.OleDbDataReader = cmd.ExecuteReader()
        If Not r.HasRows Then Exit Sub

        Do While r.Read()
            sText = sText & r.GetString(0)
        Loop

    End Using
txtUnitName.Text = sText

When i run the code analysis in VS it indicates a vulnerability in this line

Dim cmd As New OleDb.OleDbCommand("SELECT Name FROM Units WHERE (Code = " & search & ") ", cn)

and basically I think its suggesting that the search part of the code should ideally be a Parameter. I have got these to work with another code using OleDbDataAdapter but can't fathom it with a OleDbConnection

Any pointers

Thanks

Answer 1

Connections don't have parameters. You could use the OleDbConnectionStringBuilder class to build your connection string.

But for the Command object, yes, always use parameters to avoid SQL injection:

Dim cmd As New OleDb.OleDbCommand("SELECT Name FROM Units WHERE Code = @code", cn)
cmd.Parameters.AddWithValue("@code", search)

Do note that the OleDb library doesn't actually use the @code name signature, it will fill in the parameters in index order, so you could replace @code with just a question mark (?).