Return to HomePage

---

Using Parameterized Queries for Secure Database Access (C#)

Applies to

* ASP.NET 2.0
* C#
* 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 njection 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

static void ParameterizedDBQuery(string sParam)
{

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

		    		// Create a new SQL Command object with our query
		    		// Note the syntax for our parameter field, "first"
		    		[SqlCommand] 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();
		    		[SqlDataReader] reader = [sqlCommand.ExecuteReader();]
	

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

		    		// Close Reader and Connection.
		    		reader.Close();
		    		cn.Close();
	

}

Problem Example

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

string sParam = Request"first_name";

// Create a new database connection using Integrated Security
string connectionString = "Initial Catalog=snippets;Data Source=vm-win2003 \\sqlexpress;Integrated Security=SSPI;";
SqlConnection cn = new SqlConnection(connectionString);

// Create a new SQL Command object with our query
// Dynamically generate SQL query using passed parameter
SqlCommand 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();
SqlDataReader reader = sqlCommand.ExecuteReader();

while (reader.Read())

		    		Console.WriteLine("Result: " + [reader.GetString(0)] + " " + [reader.GetString(1));]
	

// 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:

using System.Data;
using 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.
static void Main(string[] args)
{

		    		ParameterizedDBQuery("Elvin");
	

}

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, C#
* Category: Data Access
* Author: Jonathan Bailey

---

Return to HomePage