Return to HomePage

---

Using Parameterized Queries for Secure Database Access (VB.NET)

Applies To

* ASP.NET 2.0
* VB
* Server-side
* SQL Server

Summary

The purpose of this code snippet is to illustrate the construction of database queries using parameterized arguments to add additional security to database operations that rely upon user-supplied application input.

Objectives

* Incorporate user input into database statements while protecting against potential SQL injection attacks
* Define and fix query logic during implementation of database operations

Scenarios

* Application needs to make use of user input of any form in database queries
* Application needs to make use of output from other database or code operations in database queries

Solution Example

Sub ParameterizedDBQuery(ByVal sParam As String)

		    		' Create a new database connection using Integrated Security
		    		Dim connectionString As String =  "Initial Catalog=snippets;Data Source=vm-win2003\sqlexpress;Integrated Security=SSPI;" 
		    		Dim cn As [SqlConnection] =  New [SqlConnection(connectionString)] 
	

		    		' Create a new SQL Command object with our query
		    		' Note the syntax for our parameter field, "first"
		    				Dim sqlCommand As [SqlCommand] =  New SqlCommand("SELECT first, last FROM people WHERE first = @first",cn) 
	

		    		' Create a new [SqlParameter] object, which will contain the argument value that we wish to query
		    		' When creating the parameter we specify the parameter name, type of column in the database
		    		' and length of the argument we are passing to the query.
		    		sqlCommand.Parameters.Add("@first", [SqlDbType.VarChar,] sParam.Length).Value = sParam
	

		    		' Open connection to the server and execute query, returning a data reader
		    		cn.Open()
		    		Dim reader As [SqlDataReader] =  [sqlCommand.ExecuteReader()] 
	

		    		While reader.Read()
		    			Console.WriteLine("Result: " + [reader.GetString(0)] + " " + [reader.GetString(1))]
		    		End While
	

		    		' Close Reader and Connection.
		    		reader.Close()
		    		cn.Close()
	

End Sub

Problem Example

The following example demonstrates the use of string concatenation to dynamically create a database query.

Dim sParam As String = Request("first_name")

' Create a new database connection using Integrated Security
Dim connectionString As String = "Initial Catalog=snippets;Data Source=vm-win2003\sqlexpress;Integrated Security=SSPI;"
Dim cn As SqlConnection = New SqlConnection(connectionString)

' Create a new SQL Command object with our query
' Dynamically generate SQL query using passed parameter
Dim sqlCommand As SqlCommand = New SqlCommand("SELECT first, last FROM people WHERE first = '" + sParam + "'",cn)

' Open connection to the server and execute query, returning a data reader
cn.Open()
Dim reader As SqlDataReader = sqlCommand.ExecuteReader()

While reader.Read()
Console.WriteLine("Result: " + reader.GetString(0) + " " + reader.GetString(1))
End While

' Close Reader and Connection.
reader.Close()
cn.Close()

* Code does not first validate the "first_name" CGI data before incorporation into the database query
* SqlCommand object makes use of a dynamically-generated string containing user input, which makes this operations vulnerable to SQL injection. For instance, the following sParam value could result in the deletion of the entire application database from the environment:
abcxyz'; drop database snippets; --
* This example illustrates an inline SELECT statement. SQL injection may also be possible when stored procedures are used, if user input is incorporated dynamically, as above

Test Case

The following classes must be included in any project making use of the sample code provided above:

Imports System.Data
Imports System.Data.SqlClient

The test case requires performance of the following two steps:

1. Run the following CREATE table command to add the "people" table

		   	used in this query to your SQL server.
	

CREATE TABLE dbo.People(
first varchar(50) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
last varchar(50) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
email varchar(max) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
phone nchar(15) COLLATE SQLLatin1GeneralCP1CI_AS NULL
)

2. Execute the following test case code.

Sub Main(ByVal args() As String)
ParameterizedDBQuery("Elvin")
End Sub

Expected Result

* Result: Elvin Jones

More Information

* User input should always be considered "tainted" and validated against a set of known and expected values or characters before acceptance by an application for processing in any type of operation, including database queries.

Additional Resources

* Security Guidelines (ASP.NET Data Access): http://msdn.microsoft.com/library/en-us/dnpag2/html/PAGGuidelines0001.asp?frame=true#pagguidelines0001_dataaccess
* Security Guidelines (ADO.NET SQL Injection): http://msdn.microsoft.com/library/en-us/dnpag2/html/PAGGuidelines0002.asp?frame=true#pagguidelines0002_sqlinjection
* Security Guidelines (.NET Data Access): http://msdn.microsoft.com/library/en-us/dnpag2/html/PAGGuidelines0003.asp?frame=true#pagguidelines0003_dataaccess

Attributes

* Applies To: .NET Framework 2.0, VB
* Category: Data Access
* Author: Jonathan Bailey

---

Return to HomePage