Working with parameterized SQL operations part 2
Overview
Working off part 1, this code sample provides steps a developer can take to secure their SQL SELECT statements from hacker attacks such as SQL-Injection. Rather than focus on simple WHERE conditions this article will start off by showing the very basics then move on to focus on parameterizing WHERE IN (Transact-SQL) SELECT statements.
Description
A common task is to load data from a SQL-Server database table where one field meets a specific condition. For example, from customer orders, the user wants all orders placed on a specific date.
The developer creates a connection, a command object, sets the command object’s command text as shown below where the data is passed through from a DateTimePicker then loads the results into a DataTable or a list.
The statement below would have been created in a query built in Visual Studio or SQL-Server Management Studio.
SELECT OrderID
,CustomerIdentifier
,EmployeeID
,RequiredDate
,ShippedDate
,ShipVia
,Freight
,ShipAddress
,ShipCity
,ShipRegion
,ShipPostalCode
,ShipCountry
FROM dbo.Orders
WHERE OrderDate = '7-4-2015'
The query would be setup as follows. The value for DateTimePicker1.Value can be manipulated by a hacker.
Dim selectStatement As String = "SELECT OrderID,CustomerIdentifier," &
"EmployeeID,RequiredDate,ShippedDate,ShipVia," &
"Freight,ShipAddress,ShipCity,ShipRegion," &
"ShipPostalCode,ShipCountry " &
"FROM dbo.Orders " &
"WHERE OrderDate = '" & DateTimePicker1.Value.ToString() & "'"
The statement above is prone to SQL-Injection while using parameters prohibits SQL-Injection as the date is not available.
Dim selectStatement As String = "SELECT OrderID,CustomerIdentifier," &
"EmployeeID,RequiredDate,ShippedDate,ShipVia," &
"Freight,ShipAddress,ShipCity,ShipRegion," &
"ShipPostalCode,ShipCountry " &
"FROM dbo.Orders " &
"WHERE OrderDate = @OrderDate"
Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlCommand With {.Connection = cn}
cmd.Parameters.AddWithValue("@OrderDate", DateTimePicker1.Value)
End Using
End Using
What the hacker sees is
SELECT OrderID ,
CustomerIdentifier ,
EmployeeID ,
RequiredDate ,
ShippedDate ,
ShipVia ,
Freight ,
ShipAddress ,
ShipCity ,
ShipRegion ,
ShipPostalCode ,
ShipCountry
FROM dbo.Orders
WHERE OrderDate = @OrderDate;
Now that you can see the benefit of using parameters the next step is learning how to parameterize multiple conditions such as the following.
SELECT OrderID ,
CustomerIdentifier ,
EmployeeID ,
OrderDate ,
RequiredDate ,
ShippedDate ,
ShipVia ,
Freight ,
ShipAddress ,
ShipCity ,
ShipRegion ,
ShipPostalCode ,
ShipCountry
FROM dbo.Orders
WHERE OrderDate IN ( '7-4-2015', '7-21-2015' );
The following methods will be the core to create a parameterized SELECT statement. The first, BuildWhereInClause takes a SELECT statement and creates the IN part of the SELECT statement without values which is then passed off from the command object to create actual parameters and fill in their values.
Note the private function StripFunction which looks for functions used in the WHERE condition e.g. YEAR(Birthday) where the intent is to allow the user of the application to get birthdays for specific years. If a function accepts arguments StripFunction is not designed to handle them, instead, a runtime exception would be thrown because the SELECT statement would end up malformed.
Imports System.Data.SqlClient
Imports System.Runtime.CompilerServices
Imports System.Text.RegularExpressions
''' <summary>
''' Contains methods to create dynamic WHERE IN conditions on a single field
''' </summary>
''' <remarks>
''' These methods are not meant to handle every single condition, they are
''' for simple IN clauses e.g.
'''
''' WHERE SomeField IN (1,2,3)
''' WHERE SomeField IN ('June','April')
''' WHERE YEAR(SomeField) IN (2008,2007,2018)
''' WHERE SomeField IN ('4-4-1960','9-22-1989')
'''
''' If a function is year that has arguments these methods will not handle
''' them "as is".
'''
''' </remarks>
Public Module SqlWhereInParamterBuilder
''' <summary>
''' Create a SQL SELECT statement which is then passed off to
''' AddParamsToCommand to create a parameter for each value.
''' </summary>
''' <typeparam name="T">SELECT Statement with WHERE condition</typeparam>
''' <param name="partialClause">Base SQL SELECT statement</param>
''' <param name="paramPrefix">WHERE IN field</param>
''' <param name="parameters">Value list for WHERE IN</param>
''' <returns>SELECT Statement with WHERE condition ready to populate values</returns>
Public Function BuildWhereInClause(Of T)(
partialClause As String,
paramPrefix As String, parameters As IEnumerable(Of T)) As String
paramPrefix = StripFunction(paramPrefix)
Dim parameterNames = parameters.
Select(Function(paramText, paramNumber) $"@{paramPrefix}{paramNumber}").ToArray()
Dim whereInClause = String.Format(partialClause.Trim(), String.Join(",", parameterNames))
Return whereInClause
End Function
''' <summary>
''' Create a parameter for each value in parameters
''' </summary>
''' <typeparam name="T">Command with paramers setup</typeparam>
''' <param name="cmd">Command object</param>
''' <param name="paramPrefix">Field name for the WHERE IN</param>
''' <param name="parameters">Values for the WHERE IN</param>
<Extension>
Public Sub AddParamsToCommand(Of T)(
cmd As SqlCommand,
paramPrefix As String,
parameters As IEnumerable(Of T))
paramPrefix = StripFunction(paramPrefix)
Dim parameterValues = parameters.Select(Function(paramText) paramText).ToArray()
Dim parameterNames() As String = parameterValues.
Select(Function(paramText, paramNumber) $"@{paramPrefix}{paramNumber}").ToArray()
For index As Integer = 0 To parameterNames.Length - 1
cmd.Parameters.AddWithValue(parameterNames(index), parameterValues(index))
Next
'
' Display what a hacker would see
'
If Debugger.IsAttached Then
Console.WriteLine(cmd.CommandText)
End If
End Sub
''' <summary>
''' Used to get a field name from a function e.g. YEAR(ActiveDate)
''' which will return ActiveDate.
''' </summary>
''' <param name="pValue"></param>
''' <returns></returns>
Private Function StripFunction(ByVal pValue As String) As String
If pValue.Contains("(") Then
Dim regularExpressionPattern As String = "(?<=\()[^}]*(?=\))"
Dim re As New Regex(regularExpressionPattern)
Return re.Matches(pValue)(0).ToString()
Else
Return pValue
End If
End Function
End Module
Example 1 working with integers
In this example, the task is to provide a list of suppliers (using a modified version of Microsoft NorthWind database) by showing supplier names in a CheckedListBox along with the primary key for each supplier.
The following class is responsible for reading data for the CheckedListBox and creating the SELECT statement.
Imports DataConnections
Imports System.Data.SqlClient
Imports ParameterHelpers
Imports PeekerForCommands
Public Class SuppliersOperations
Inherits BaseSqlServerConnection
Public Function GetSuppliersByIdentifier() As List(Of Supplier)
Dim suppliers As New List(Of Supplier)
Dim selectStatement As String = "SELECT SupplierID, CompanyName " &
"FROM dbo.Suppliers " &
"ORDER BY CompanyName;"
Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlCommand With {.Connection = cn}
Try
cn.Open()
cmd.CommandText = selectStatement
Dim reader = cmd.ExecuteReader
While reader.Read
suppliers.Add(New Supplier With
{
.SupplierID = reader.GetInt32(0),
.CompanyName = reader.GetString(1)
})
End While
Catch ex As Exception
mHasException = True
mLastException = ex
End Try
End Using
End Using
Return suppliers
End Function
Public Function Countries() As List(Of String)
Dim names As New List(Of String)
Dim selectStatement As String = "SELECT Country FROM dbo.Suppliers GROUP BY Country"
Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlCommand With {.Connection = cn}
Try
cn.Open()
cmd.CommandText = selectStatement
Dim reader = cmd.ExecuteReader
While reader.Read
names.Add(reader.GetString(0))
End While
Catch ex As Exception
mHasException = True
mLastException = ex
End Try
End Using
End Using
Return names
End Function
Private _ActualStatement As String
Public ReadOnly Property ActualStatement As String
Get
Return _ActualStatement
End Get
End Property
Public Function GetSuppliersByIdentifier(pSupplierIdentifiers As List(Of Integer)) As DataTable
Dim dt As New DataTable
Dim parameterPrefix As String = "SupplierID"
Dim selectStatement As String =
"SELECT SupplierID, " &
"CompanyName, ContactName,ContactTitle,[Address], City, PostalCode, Country, Phone " &
"FROM dbo.Suppliers " &
"WHERE " & parameterPrefix & " IN ({0})"
Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlCommand With {.Connection = cn}
Dim commandText As String = BuildWhereInClause(selectStatement, parameterPrefix, pSupplierIdentifiers)
cmd.CommandText = commandText
cmd.AddParamsToCommand(parameterPrefix, pSupplierIdentifiers)
Try
cn.Open()
dt.Load(cmd.ExecuteReader)
'
' Usually we need the primary key but unseen
'
'dt.Columns("SupplierID").ColumnMapping = MappingType.Hidden
_ActualStatement = cmd.ActualCommandTextByNames
Catch ex As Exception
_ActualStatement = ""
mHasException = True
mLastException = ex
End Try
End Using
End Using
Return dt
End Function
Public Function GetSuppliersByCountryName(pCountryNames As List(Of String)) As DataTable
Dim dt As New DataTable
Dim parameter_Prefix As String = "Country"
Dim cmdText As String =
"SELECT SupplierID, " &
"CompanyName, ContactName,ContactTitle,[Address], City, PostalCode, Country, Phone " &
"FROM dbo.Suppliers " &
"WHERE " & parameter_Prefix & " IN ({0})"
Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlCommand With {.Connection = cn}
Dim commandText As String = BuildWhereInClause(cmdText, parameter_Prefix, pCountryNames)
cmd.CommandText = commandText
cmd.AddParamsToCommand(parameter_Prefix, pCountryNames)
Try
cn.Open()
dt.Load(cmd.ExecuteReader)
'
' Usually we need the primary key but unseen
'
'dt.Columns("SupplierID").ColumnMapping = MappingType.Hidden
_ActualStatement = cmd.ActualCommandTextByNames
Catch ex As Exception
_ActualStatement = ""
mHasException = True
mLastException = ex
End Try
End Using
End Using
Return dt
End Function
End Class
GetSuppliersByIdentifier function is used to populate the CheckedListBox while GetSuppliersByIdentifier function is responsible for creating the WHERE in clause using checked items from the CheckedListBox.
In form shown event the CheckedListBox is loaded with supplier names and their primary key. The button first determines if there are any checked items if there are checked items create a list of integer and pass the list to the GetSuppliersByItentifier method which in turn creates a parameterized SELECT statement.
Public Class SupplierIdDemoForm
Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
Dim ops As New SuppliersOperations
Dim supplierList = ops.GetSuppliersByIdentifier
For Each sup As Supplier In supplierList
clbSuppliers.Items.Add(sup)
Next
End Sub
Private Sub cmdGenerate_Click(sender As Object, e As EventArgs) Handles cmdGenerate.Click
DataGridView1.DataSource = Nothing
txtSelectStatement.Text = ""
If clbSuppliers.CheckedItems.Count > 0 Then
' get primary key for each checked Supplier
Dim identifiers = clbSuppliers.
CheckedItems.Cast(Of Supplier).
Select(Function(supplier) supplier.SupplierID).
ToList
Dim ops As New SuppliersOperations
' display results from a DataTable
DataGridView1.DataSource = ops.GetSuppliersByIdentifier(identifiers)
' Show the generated SQL statement
txtSelectStatement.Text = ops.ActualStatement
End If
End Sub
End Class
The following screen shot shows the user interface after making several selections.
Note the TextBox shows the SQL SELECT statement which is passed to the database while the hacker sees the following.
SELECT SupplierID,
CompanyName,
ContactName,
ContactTitle,
[Address],
City,
PostalCode,
Country,
Phone
FROM dbo.Suppliers
WHERE SupplierID IN (
@SupplierID0,
@SupplierID1,
@SupplierID2,
@SupplierID3,
@SupplierID4,
@SupplierID5
)
Example 2 working with Year part of Date
In the following example, the task is to get years for active members in a database table. This complicates matters as in this example the WHERE condition contains a function while in the first example a simply list was needed to create the IN clause. This is where the private method mentioned before determines if there is a function and if so obtains the field name which in turn is used to create the WHERE IN clause.
Imports System.Data.SqlClient
Imports DataConnections
Imports ParameterHelpers
Imports PeekerForCommands
Public Class MembersOperations
Inherits BaseSqlServerConnection
Public Function GetRandomYears() As List(Of Integer)
Dim yearList As New List(Of Integer)
Dim selectStatement As String = "SELECT TOP 100 YEAR(ActiveDate) " &
"FROM WorkingWithDataTips_1.dbo.MemberList " &
"GROUP BY ActiveDate " &
"ORDER BY NEWID()"
Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlCommand With {.Connection = cn}
Try
cn.Open()
cmd.CommandText = selectStatement
Dim reader = cmd.ExecuteReader
While reader.Read
yearList.Add(reader.GetInt32(0))
End While
Catch ex As Exception
mHasException = True
mLastException = ex
End Try
End Using
End Using
yearList = yearList.Distinct().ToList()
yearList.Sort()
Return yearList
End Function
Private _ActualStatement As String
Public ReadOnly Property ActualStatement As String
Get
Return _ActualStatement
End Get
End Property
Public Function GetMembersByActiveYear(ByVal yearList As List(Of Integer)) As DataTable
Dim dt As New DataTable
Dim parameterPrefix As String = "YEAR(ActiveDate)"
Dim selectStatement As String =
"SELECT id,FirstName,LastName, ActiveDate FROM dbo.MemberList " &
"WHERE " & parameterPrefix & " IN ({0})"
Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlCommand With {.Connection = cn}
Dim commandText As String = BuildWhereInClause(selectStatement, parameterPrefix, yearList)
cmd.CommandText = commandText
cmd.AddParamsToCommand(parameterPrefix, yearList)
Try
cn.Open()
dt.Load(cmd.ExecuteReader)
'
' Usually we need the primary key but unseen
'
'dt.Columns("id").ColumnMapping = MappingType.Hidden
_ActualStatement = cmd.ActualCommandTextByNames
Catch ex As Exception
_ActualStatement = ""
mHasException = True
mLastException = ex
End Try
End Using
End Using
Return dt
End Function
End Class
Form code
Public Class MembersYearsDemoForm
Private Sub MembersYearsDemoForm_Shown(sender As Object, e As EventArgs) Handles Me.Shown
Dim ops As New MembersOperations
Dim yearList = ops.GetRandomYears()
For Each item As Integer In yearList
clbActiveYears.Items.Add(item)
Next
End Sub
Private Sub cmdGenerate_Click(sender As Object, e As EventArgs) Handles cmdGenerate.Click
DataGridView1.DataSource = Nothing
txtSelectStatement.Text = ""
If clbActiveYears.CheckedItems.Count > 0 Then
Dim years = clbActiveYears.
CheckedItems.
Cast(Of Integer).
Select(Function(n) n).
ToList()
Dim ops As New MembersOperations
DataGridView1.DataSource = ops.GetMembersByActiveYear(years)
' Show the generated SQL statement
txtSelectStatement.Text = ops.ActualStatement
End If
End Sub
End Class
The screenshot above uses the exact same method to create the SQL WHERE condition as in the first example accept for different data along with dealing with the YEAR method on the database side. A hacker, like in the first example can not alter the parameters.
Example 3 working with strings
Working with string values is no different than working with integers. In the following code sample, a list of countries is loaded into a CheckedListBox, pressing the button a check is done to ensure there are checked items and if so create the WHERE IN condition and is shown in the TextBox at the bottom of the window. As with prior examples, a hacker can not manipulate the SQL statement.
Summary
Using parameters prevents SQL-Injection attacks, for instance without parameters a hacker can inject their condition into your WHERE statement by providing methods to create parameters for WHERE IN conditions.
Parameterized SQL performs better, no string concatenation, as mentioned above, no escaping of data. A more generic query form is presented to the database, so it's likely already hashed and stored as a pre-compiled execution plan.
Having this information provides fewer errors and more secure application. Ignoring parameters opens the doors to attacks from hackers and error-prone applications. Take the time to work parameters into your data-centric applications which in the end is what knowledgeable developer do no matter if they are just starting out to the season developer.
Source code
https://code.msdn.microsoft.com/Parameterized-SQL-7f283c3c
See also
SqlCommand.Parameters Property Jump
Configuring Parameters and Parameter Data Types Jump
IN (Transact-SQL)