What is a simple example of using prepared statements in vb net using Provider=Microsoft.Jet.OLEDB.4.0

Question

Hallo good people of Stack overflow. For some reason i want to keep working with ms Access as the database using vb net.

My code works well but only if i work directly with user inputs. Which makes my system prone to sql injections.

I have seen some examples on google that show use binding of parameters in prepared statements using vbn.et.

Unfortunately those examples do not support "Provider" key word in my connection string for ms Access table.

Can someone who understands this help me modify my vb code below to work with binding of parameters using Provider=Microsoft.Jet.OLEDB.4.0 as connection string?

I want to avoid saying "where password = " & supplier_password but say something like "where password = @supplier_password " and later provide the supplied password text.

Here is my vb net code.

    Private Sub test_password()


    Dim myConnection As OleDbConnection = New OleDbConnection
    myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =C:\books\author.mdb"

    Dim counter As Int16

    myConnection.Open()
    Dim str As String
    str = "SELECT * FROM users where password =  '" & pass.Text & "'"


    Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)

    counter = cmd.ExecuteScalar
    myConnection.Close()

    If counter > 0 Then
        MsgBox("password was found")
    Else
        MsgBox("password was NOT found")

    End If

End Sub

I have used the appropriate

          Imports System.Data.OleDb
          Imports System.Data.SqlClient

Many thanks

Answer 1

This is a version of your code with parameters and correct use of disposable objects

Private Sub test_password()
    ' Do not return all the fields, just count....'
    ' ...and password is a reserved keyword in access...'
    ' need square brackets around it otherwise ...syntax error...'
    Dim str = "SELECT COUNT(*) FROM users where [password] =  @pass"
    Using myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;....")
    Using cmd = New OleDbCommand(str, myConnection)

        myConnection.Open()
        cmd.Parameters.Add("@pass", OleDbType.VarWChar).Value = pass.Text
        ' ExecuteScalar returns the first column of the first row from your query'
        Dim counter = Convert.ToInt32(cmd.ExecuteScalar)
        If counter > 0 Then
             MsgBox("password was found")
        Else
             MsgBox("password was NOT found")
        End If
    End Using 
    End Using    
End Sub

The Using Statement ensures that every disposable object (connection and command in this context) are correctly disposed when you have finished to use them.

The parameter placeholder @pass is inserted in place of the actual value and a parameter is added to the command parameter collection with the matching datatype expected by the field and the correct value.

Note that in OleDb, parameters placeholders are usually represented by a question mark ? but Access accepts also parameters placeholders expressed with the @xxxx format. However, the parameters should be added in the parameter collection, following the same order in which their placeholders appears in the command text.