Using Parameters/Parameterized Queries in database interactions with C# and VB.NET

When writing ASP.NET applications, you need to be careful with your code to protect from misuse by would be hackers. One of the best ways to do this is to parametrize your queries. Not only will using parameters help against SQL injection attacks, but it’s best practice to do so and avoid large string concatenation. It also types the values being passed in which helps with proper coding.

Typically someone will write some code like this on button click

C#
SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=database;User ID=uid;Password=pass");
conn.Open();

// assuming a textbox with id txtUserId exists on the aspx page
string userId = txtUserId.Text;
string sql = "SELECT name, password FROM users WHERE id=" + userId;

SqlCommand cmd = new SqlCommand(sql, conn);

SqlDataReader dr = cmd.ExecuteReader();

...

VB.NET
Dim conn as SqlConnection = new SqlConnection("Data Source=localhost;Initial Catalog=database;User ID=uid;Password=pass")
conn.Open()

' assuming a textbox with id txtUserId exists on the aspx page
Dim userId as String = txtUserId.Text
Dim sql as String = "SELECT name, password FROM users WHERE id=" & userId

Dim cmd as SqlCommand = new SqlCommand(sql, conn)

Dim dr as SqlDataReader = cmd.ExecuteReader()

...

Thie code above just grabs some data from the users table based on a textbox and puts it into the datareader. This is pretty basic code most people would use. The SQL injection risk is in the concatenation of the sql query. Hackers could send malicious code through the textbox to hack your database. The proper way to do this kind of call would be to setup the command object with parameters.

C#
SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=database;User ID=uid;Password=pass");
conn.Open();

// assuming a textbox with id txtUserId exists on the aspx page
string userId = txtUserId.Text;
string sql = "SELECT name, password FROM users WHERE id=@userid";

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("userid", userId);

SqlDataReader dr = cmd.ExecuteReader();

...

VB.NET
Dim conn as SqlConnection = new SqlConnection("Data Source=localhost;Initial Catalog=database;User ID=uid;Password=pass")
conn.Open()

' assuming a textbox with id txtUserId exists on the aspx page
Dim userId as String = txtUserId.Text
Dim sql as String = "SELECT name, password FROM users WHERE id=@userid"

Dim cmd as SqlCommand = new SqlCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommendText = sql
cmd.Parameters.AddWithValue("userid", userId);

Dim dr as SqlDataReader = cmd.ExecuteReader()

...

As you can see above, the SQL query itself is now using a parameter value for userid which we define with the Parameters collection inside the SqlCommand object. This works the same with OleDB objects as well. This will automatically filter out malicious SQL injection type attacks on your database.

One thing to note when using parameters, you may get an error like below

Parameterized Query ‘(@userid nvarchar(4000))SELECT name, password FROM users WHERE id=@userid’ expects parameter @userid, which was not supplied.

This error occurs because the parameter being passed in is empty (C# null, VB.NET nothing). You actually need to pass in DBNull if it’s empty, so add this to your code

C#
if (userId != "")
cmd.Parameters.AddWithValue("companyid", userid);
else
cmd.Parameters.AddWithValue("companyid", DBNull.Value);

VB.NET
If (userId <> "") Then
cmd.Parameters.AddWithValue("companyid", userid)
Else
cmd.Parameters.AddWithValue("companyid", DBNull.Value)
End If

That’s it, hope this helps you.

3 Responses to "Using Parameters/Parameterized Queries in database interactions with C# and VB.NET"

  1. Fabian

    I’m having a problem with this, working with vb.net.

    Using this:
    cmd.Parameters.AddWithValue(“@insc”, data)

    Returns this error:
    AddWithValue not a member of System.Data.IDataParameterCollection

    Thanks in advance

  2. Fabian

    Searching with Google, looks like the error (in English) is:
    System.Data.IdataParameterCollection doesnot contain a definition for ‘AddWithValue’

  3. Fabian

    I solved the problem.
    I had this:
    Dim cmd As IDbCommand

    Instead of this:
    Dim cmd As New SqlCommand

    Thanks again. I apologize for the other posts.

Leave a Reply