SQL Injection Solution using Table-Value-Parameters

Alright, after a long… ok, really long… wait I have finally gotten around to posting the content folks were asking for from the recent IPMA security presentation. The source-code and walk-thrus are below.

First up is the SQL-Server injection problem. There are many ways that a hacker can exploit vulnerabilities, and SQL Server injection is one such area. Typically, the out-of-the-box tools available to you in the Microsoft .NET programming environment make it pretty easy to avoid SQL injection vulnerabilities in your application. However, what do you do when you’re working with complex search logic? When it comes to selecting multiple filters for lookup-data, I like to make use of Table-Value Parameters. They ensure that you will not be passing in strings and forced to perform a general SP_EXECSQL call.

Here’s a screen-shot of a generic ‘Advanced Search’ screen with some lookup-list filters that I was referring to.

App Screen

SqlInjection_AppScreenResults

As you can see, there is a panel on the right-hand side that contains several search filters. The important ones for the sake of this post are the 2 checkbox lists. When I check them, I’m expecting to limit the results to only those that are selected. When none are selected, the results returned should be anything that has an Eye or Hair color.

App Screen Filter results

SqlInjection_AppScreenResultsII

Now, these are the only records with the last name of “Stoker” that also have the ‘Hair Color’ choices that have been selected. Now here… you can see that the additional filter of several ‘Eye Colors’ results in fewer records matching…

App Screen Filter detailed results

SqlInjection_AppScreenResultsIII

What’s going on behind the scenes is that I’m using the SqlDbType ‘Structured’ as a the parameter type and passing a simple System.Data.DataTable along as a parameter-value. The code is available for download here.

Notice what the SQL-Profiler shows is occurring in the background when using Table-Value Parameters to pass collections of list data. The data is strongly-typed! In other words, you don’t have to pass a string when you mean to be passing a list of Integers. You don’t have to mess around with SQL string-parsing logic and user-defined functions.

SQL Profiler results

SqlInjection_ProfileLogII

Ok, so you may be asking… “Chad, how do you pass in a custom Table-Type from .NET?” Here is the important section of the source-code that answers that question. After doing the obligatory setup of the SqlConnection and SqlCommand objects respectively, I begin creating the first 4 filter parameters to be passed into my ‘Search’ stored-procedure. Then, I proceed to create my 2 Data Tables consisting of 1 System.Int32 data column each. I dynamically add a new row for each ‘Color’ that I want to include the search-results. Finally, I set those newly populated data tables to the ‘Value’ property of the SqlParameter object instance, and add the parameter to the SqlCommand. It’s actually pretty simple if you’re already familiar with creating SqlParameter’s manually.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
protected void btnSearch_Click(object sender, EventArgs e)
{
    //********************************************************************************
    //** NOTE **
    // MAKE CERTAIN YOU READ AND FOLLOW THE INSTRUCTIONS IN THE README.TXT FILE...
    // OTHERWISE THIS FUNCTION WILL FAIL SINCE IT IS UNABLE TO FIND THE 'searchPeople'
    // STORED PROCEDURE!
    //********************************************************************************
    DataSet searchResults = new DataSet("SearchResults");
 
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[_connKey].ConnectionString))
    {
        SqlCommand sCmd = new SqlCommand("searchPeople", conn) { CommandType = CommandType.StoredProcedure };
 
        //Now add the SQL Command Parameters to the Collection...
        sCmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 255) { Value = string.IsNullOrEmpty(this.txtFirstNameSearch.Text) ? null : string.Concat(this.txtFirstNameSearch.Text.Take(255).ToArray()) });
        sCmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 255) { Value = string.IsNullOrEmpty(this.txtLastNameSearch.Text) ? null : string.Concat(this.txtLastNameSearch.Text.Take(255).ToArray()) });
        DateTime? dob = null;
        if (!string.IsNullOrEmpty(this.txtDateOfBirthSearch.Text))
        {
            DateTime dobValue = DateTime.MinValue;
            if (DateTime.TryParse(this.txtDateOfBirthSearch.Text, out dobValue))
                dob = dobValue;
        }
        sCmd.Parameters.Add(new SqlParameter("@DateOfBirth", SqlDbType.VarChar, 255) { Value = dob });
        sCmd.Parameters.Add(new SqlParameter("@Last5Ssn", SqlDbType.VarChar, 5) { Value = string.IsNullOrEmpty(this.txtLast5SsnSearch.Text) ? null : string.Concat(this.txtLast5SsnSearch.Text.Take(5).ToArray()) });
 
        //------------------------------------------------------------------------------------------//
        //Now build the Data Tables that contain the checkbox unique identifiers...
        SqlParameter hairColorsParam = new SqlParameter("@HairColors", null);
        hairColorsParam.SqlDbType = SqlDbType.Structured; //IMPORTANT FOR TABULAR PARAMETER TYPES!!
        DataTable hairDt = new DataTable();
        hairDt.Columns.Add(new DataColumn("key", typeof(int)));
        List<int> hairIdsSelected = (List<int>)ViewState["hairColors"];
        if (hairIdsSelected == null || hairIdsSelected.Count == 0) //If no Hair-Color was selected... then select ALL.
            hairIdsSelected = _hairColors.Select(i => i.ColorId).ToList();
        foreach (int colorId in hairIdsSelected)
            hairDt.Rows.Add(colorId);
        hairColorsParam.Value = hairDt;
        sCmd.Parameters.Add(hairColorsParam);
 
        SqlParameter eyeColorsParam = new SqlParameter("@EyeColors", null);
        eyeColorsParam.SqlDbType = SqlDbType.Structured; //IMPORTANT FOR TABULAR PARAMETER TYPES!!
        DataTable eyeDt = new DataTable();
        eyeDt.Columns.Add(new DataColumn("key", typeof(int)));
        List<int> eyeIdsSelected = (List<int>)ViewState["eyeColors"];
        if (eyeIdsSelected == null || eyeIdsSelected.Count == 0) //If no Eye-Color was selected... then select ALL.
            eyeIdsSelected = _eyeColors.Select(i => i.ColorId).ToList();
        foreach (int colorId in eyeIdsSelected)
            eyeDt.Rows.Add(colorId);
        eyeColorsParam.Value = eyeDt;
        sCmd.Parameters.Add(eyeColorsParam);
        //------------------------------------------------------------------------------------------//
 
        SqlDataAdapter sAdapt = new SqlDataAdapter(sCmd);
        sAdapt.Fill(searchResults);
    }
 
    this.GridView2.DataSource = searchResults;
    this.GridView2.DataBind();
}

The final piece of the whole puzzle is to create a Stored-Procedure that can leverage this stuff… right? So here is how I accomplished that and what the SQL looks like :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE PROCEDURE [dbo].[searchPeople] 
	-- Add the parameters for the stored procedure here
	@FirstName VARCHAR(255) = NULL,
	@LastName VARCHAR(255) = NULL,
	@DateOfBirth DATE = NULL,
	@Last5Ssn VARCHAR(5) = NULL,
	@HairColors dbo.GenericTVP READONLY,
	@EyeColors dbo.GenericTVP READONLY
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	SELECT DISTINCT pd.*, hc.ColorDescription [HairColorDesc], ec.ColorDescription [EyeColorDesc] 
	FROM PeopleDetail pd
	INNER JOIN @HairColors hcs ON pd.HairColorId = hcs.ID
	INNER JOIN @EyeColors ecs ON pd.EyeColorId = ecs.ID
	LEFT OUTER JOIN Color hc ON hc.ColorId = hcs.Id 
	LEFT OUTER JOIN Color ec ON ec.ColorId = ecs.Id 
	WHERE (@FirstName IS NULL OR FirstName LIKE '%' + @FirstName + '%')
	AND (@LastName IS NULL OR LastName LIKE '%' + @LastName + '%')
	AND (@DateOfBirth IS NULL OR DateOfBirth >= @DateOfBirth)
	AND (@Last5Ssn IS NULL OR Last5Ssn LIKE '%' + @Last5Ssn + '%')
END

The stored-procedure has some funny data-types on the last 2 parameters. Those ‘funny’ data-types are basically custom data-types that I’ve actually created and they are the most important point of this whole post. I obviously wouldn’t want to leave out the final, and most important piece of the puzzle… the ‘User-Defined Table Type’… as seen here:

1
2
3
4
5
/****** Object:  UserDefinedTableType [dbo].[GenericTVP]    Script Date: 05/10/2011 13:38:43 ******/
CREATE TYPE [dbo].[GenericTVP] AS TABLE(
	[ID] [INT] NOT NULL
)
GO

Once I’ve created that custom table Data-Type as shown above, I can then proceed to create my Stored-Procedure that will accept a .NET Data Table as a parameter assuming the columns of the Data-Table are using the same data-types and in the correct ordinal position. This helps to ensure that nothing fishy is getting passed to SQL Server regarding our search-logic and it also ensures that we don’t have to fall back on writing dynamically executed SQL strings to accomplish our complex search functions!

Cool, huh?


Anyway, the source-code I refered to above is here :
SqlInjection_DemoCode

Comments are closed.

Our Capabilities Include:


Custom Software Development
Enterprise Architecture
Project Management
Systems Analysis
Performance Testing

AND THE LIST GOES ON...

These methods are vital to our work:


Agile Methodology
PMBOK
Test-Driven Development

LEARN WHY...

About CodeSmart, Inc.


CodeSmart has been locally owned and operated in the Olympia, WA area since 2002. We direct, design, develop and deliver full end-to-end information systems using leading edge Microsoft .Net technologies and recommended best practices.

LEARN MORE...