Return to HomePage

---

Using Stored Procedures 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 stored procedures to add additional security to database operations for .NET applications.

Objectives

* Define and fix query logic during implementation of database operations
* Protect against potential SQL injection attacks

Scenarios

* Application database operations can be defined and implemented at compile/deployment time
* 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 StoredProcedureDBQuery()
{

		    		// 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 a query to execute the stored procedure
		    		[SqlCommand] sqlCommand = new SqlCommand("exec spFullNames", 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));]
	

		    		// 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
* Inline SQL code may violate certain corporate information security policies

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 three 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. Create the stored procedure used in this example to query names from the people table.

CREATE PROCEDURE spFullNames
AS
BEGIN
SELECT first + ' ' + last FROM people ORDER BY last
END
GO

3. Execute the following test case code.

static void Main(string[] args)
{
StoredProcedureDBQuery();
}

Expected Result

Result: Elvin Jones
Result: Gene Krupa
Result: Tony Williams

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