VB.Net parameterized query example and why you should care

I’m going to provide a simple example and explain the benefits (and need). Let’s assume that you have table called “user_table” with one field that is “username”. This table resides in a database called “WebServices”. The below would be a way to execute a parameterized query that searches the table for a specific user, returns a data reader and then iterates over it message boxing the username (in a WinForms app).

Reasons why this is important to you:

1. Using a parameterized query helps protect you from SQL injection exploits. If you allow a user to enter a parameter you string concat onto the query, they will be able to enter single quotes which at best will cause your query to fail and at worse will allow a sophisticated user to then append additional SQL onto your query and execute it. If the user you’re executing the query with has read/write privileges (or worse, dbo) then that user will be able to delete data, truncate tables, drop tables, change permissions, etc.etc.. The parameter will escape the data provided by the user to protect against this. This is the single most important reason to always use parameterized queries in my opinion.
2. Using parameters can allow you to specify the exact field type. Example, you can tell the parameter that it is a varchar with a length of 32. That way, if you try to put more than 32 characters into the field, the parameter knows it cannot be that long. You should probably be doing checks on this anyway this will stop an exception from occurring (I believe the default behavior will truncate the data, which may or may not be desirable for you).
3. Using a parameterized query will allow you to call “Prepare” on the command if you specifically set field lengths on your parameters. On a query that runs 1 time, this may not have a huge benefit. However, on a query that is run over and over, in a loop or throughout the program it can have a huge impact. Using “Prepare” will allow the SQL Server to cache the execution plan/compile it and reuse it which will give you a performance benefit.

' 1.) Create and open the connection, the using will handle the "Dispose" call.
Using conn As New System.Data.SqlClient.SqlConnection("server=localhostSQLExpress;database=WebServices;trusted_connection=yes;")
    conn.Open()
    ' 2.)  Create a SQL command that's assigned to the connection, again, the using will handle the "Dispose" call when your done.
    Using cmd As System.Data.SqlClient.SqlCommand = conn.CreateCommand
        ' The SQL statement we want to execute with the parameter.  @ is the parameter prefix for SQL Server/Express/Compact
        cmd.CommandText = "select * from user_table where username=@username"

        ' We can add this two ways, one where we give the command as much detail about @username as possible
        ' and one where we just provide a value and the command interprets what it should be
        cmd.Parameters.AddWithValue("@username", "blake")

        ' Method two, specifically create a parameter, doing this is more code but will give you more flexibility especially
        ' when defining parameters that require precision on numeric values, etc.
        '    Dim param As New System.Data.SqlClient.SqlParameter("@username", SqlDbType.NVarChar, 100)
        '    param.Value = "blake"
        '    cmd.Parameters.Add(param)
        Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader

        While dr.Read
            MessageBox.Show(dr("username").ToString)
        End While

        ' Close the data reader, we always want to do this when we're done.
        dr.Close()
    End Using

    conn.Close()
End Using

The prefix of a parameter with SQL Server is @. This may change depending on what provider you use (for instance, MySql uses a ?).