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

LarsTech · Accepted Answer · 2014-05-15 21:44:39Z

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 (?).

answered May 15, 2014 at 21:44

LarsTech

81.9k14 gold badges161 silver badges237 bronze badges

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

Collectives™ on Stack Overflow

Adding Parameters to OleDBConnection

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related