How to parameterise sql statments in vb.net?

Question

i have the following SQL statement to search for records in a table and store them in a listview called 'lvw'. how should i convert it into a parameterised statement to prevent sql injection attacks? thanks

            con.Open()
                Dim da As New SqlDataAdapter("Select * from Students " & _
                   "where student_id like '%" & Me.srcTxt.Text.Trim & "%' " & _
                   "or " & _
                 "student_firstname like '%" & Me.srcTxt.Text.Trim & "%' " & _
                 "or " & _
               "student_lastname like '%" & Me.srcTxt.Text.Trim & "%'", con)

               da.Fill(ds)
               con.Close()

        For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
              Dim lvi As New ListViewItem
              lvi.Text = ds.Tables(0).Rows(i)(0).ToString()
                   For j As Integer = 1 To ds.Tables(0).Rows(i).ItemArray.Length - 1
                       lvi.SubItems.Add(ds.Tables(0).Rows(i)(j).ToString())
                   Next
               lvw.Items.Add(lvi)
         Next

Which RDBMS do you use? The way you parameterize queries depends on the database system you use. — Markus, Feb 21 '14 at 07:28
possible duplicate of [How do I create a parameterized SQL query? Why Should I?](http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i) — gsharp, Feb 21 '14 at 07:28
@Markus - if they're managing to connect to any database system other than SQL Server with the `SqlDataAdapter` class, I'm impressed. — Damien_The_Unbeliever, Feb 21 '14 at 07:33

score 1 · Accepted Answer · answered Feb 21 '14 at 07:35

Just place parameters in the string and then add the appropriate values to the adapter's SelectCommand:

Dim da As New SqlDataAdapter("Select * from Students " & _
               "where student_id like @searchTerm " & _
               "or " & _
             "student_firstname like @searchTerm " & _
             "or " & _
           "student_lastname like @searchTerm", con)
da.SelectCommand.Parameters.AddWithValue("@searchTerm", _
            "%" + Me.srcTxt.Text.Trim + "%")

How to parameterise sql statments in vb.net?

1 Answers1