Home
Do you parameterize all input to your database?
  v1.0 Posted at 19/03/2020 10:19 AM by Christian Morford-Waite
It is important to parameterize all input to your database and it’s easy to implement.
Doing so will also reduce a lot of headaches down the track.

 ParameterizeSqlInputsXKCD.png
Figure: What can happen if you don’t parameterize your inputs
Source: xkcd.com​

​​

​Advantages
  • ​Prevents SQL injection attacks
  • Preserves types being sent to the database
  • Increased performance by reducing the number of query plans
  • Makes your code more readable

​SELECT Id, CompanyName, ContactName, ContactTitle
FROM dbo.Customers
WHERE CompanyName = 'NorthWind';

​Figure: Bad Example - Using a dynamic SQL query

SELECT Id, CompanyName, ContactName, ContactTitle
FROM dbo.Customers
WHERE CompanyName = @companyName;

​Figure: Good Example - Using a parameterized query​

​​Should I use Parameters.AddWithValue()?

Using Parameters.AddWithValue() can be a bit of a shortcut as you don’t need to specify a type. However shortcuts often have their dangers and this one certainly does.

For most cases Parameters.AddWithValue() will guess correctly, but sometimes it doesn’t which can lead to the value being misinterpreted when sent to the database. This can be avoided using Parameters.Add() and specifying the SqlDbType, this will ensure the data will reach the database in the correct form.​

When using dates, strings, varchar and nvarchar it is strongly recommended to use Parameters.Add() as there is a possibility of Parameters.AddWithValue() to incorrectly guess the type. 

​​​Implementing parameterized queries using Parameters.Add()

cmd.Parameters.Add("@varcharValue", System.Data.SqlDbType.Varchar, 20).Value = “Text”;

​​Figure: Good Example – Using VarChar SqlDbType and specifying a max of 20 characters (-1 for MAX)

cmd.Parameters.Add("@decimalValue", System.Data.SqlDbType.Decimal, 11, 4).Value = decimalValue;

​Figure: Good Example – Using decimal(11,4) SQL Parameter

​​cmd.Parameters.Add("@dateTimeValue", System.Data.SqlDbType.DateTime2).Value = DateTime.UtcNow;

​Figure: Good Example - C#, VB .NET SQL DateTime Parameter

​$SqlCmd.Parameters.Add("@dateTimeValue", [System.Data.SqlDbType]::DateTime2).Value = $dateTime2Value

​Figure: Good Example - PowerShell SQL DateTime Parameter

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: