C# SqlParameter is a handy feature allows you to safely pass a parameter to a SqlCommand object in .NET. A security best practice when writing .NET data access code, is to always use parameters in SqlCommand objects (whenever parameters are required of course). The reason for this, is that parameters help prevent SQL injection attacks.
As described in OWASP, a SQL injection attack consists of insertion or “injection” of a SQL query via the input data from the client to the application.
We Can Help you Get Started with .NET Programming Fast and Easy!
Learn how to implement Windows Forms projects in .NET using Visual Studio and C#, how to implement multithreading, how to create deployment packages and installers for your .NET Windows Forms apps using ClickOnce in Visual Studio, and more!
Many live demonstrations and downloadable resources included!
When searching the internet, you can find many examples regarding the usage of C# SqlParameter objects. However, when you try to use them the way described in some articles, it just don’t work.
The purpose of this article, is to show by example how you can properly use the C# SqlParameter object in your .NET source code and thus have a more secure communication with SQL Server.
A Bad Example – High Risk Code (Code A)
First, let’s see a bad example of a database connection via .NET with a high-risk query that does not use parameters and instead it concatenates user input with the original query:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
namespace Test_Database_Connection
{
class Program
{
static void Main(string[] args)
{
string connString = @"Server=.\SQL2K17;Database=master;Trusted_Connection = True;";
using (SqlConnection conn = new SqlConnection(connString))
{
//set the command to execute against SQL Server (this is where you set your query)
string query = @"SELECT[fileid],[filename] FROM sysfiles WHERE name = '" + args[0].ToString() + "'";
Console.WriteLine("");
Console.WriteLine("Informational");
Console.WriteLine("-------------");
Console.WriteLine("Query to execute: " + query);
Console.WriteLine("");
Console.WriteLine("");
//set SqlCommand
SqlCommand cmd = new SqlCommand(query, conn);
//open connection
conn.Open();
//the actual command execution
SqlDataReader dr = cmd.ExecuteReader();
//if reader has any rows retrieve them
if (dr.HasRows)
{
Console.WriteLine("Query Results");
Console.WriteLine("-------------");
while (dr.Read())
{
//handle the retrieved record (i.e. display it)
Console.WriteLine(dr.GetInt16(0) + " – " + dr.GetString(1));
}
}
else
{
Console.WriteLine("Query Results");
Console.WriteLine("-------------");
Console.WriteLine("Error: Not data found.");
}
dr.Close();
}
}
}
}
As you can see, in the above code, line 17-18 builds up the query string by concatenating to the static text, the user input (args[0]). This is completely wrong and dangerous. This is like saying: “come and inject some malicious code!” 🙂
Here’s the output of the above code:
Turning the High Risk Code (Code A) into Secure Code with the use of C# SqlParameter (Code B)
Now, let’s re-write the bad code and change it into more secure, thus reducing the risk of SQL injection. So, SqlParameter comes to the rescue!
Based on the above example, the code would be changed as below:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace Test_Database_Connection
{
class Program
{
static void Main(string[] args)
{
string connString = @"Server=.\SQL2K17;Database=master;Trusted_Connection = True;";
using (SqlConnection conn = new SqlConnection(connString))
{
//set the command to execute against SQL Server (this is where you set your query)
string query = @"SELECT[fileid],[filename] FROM sysfiles WHERE name = @dbName";
//set SqlCommand
SqlCommand cmd = new SqlCommand(query, conn);
//Set SqlParameter
SqlParameter param = new SqlParameter();
param.ParameterName = "@dbName";
param.SqlDbType = SqlDbType.VarChar;
param.Value = args[0].ToString();
//Add SqlParameter to SqlCommand
cmd.Parameters.Add(param);
Console.WriteLine("");
Console.WriteLine("Informational");
Console.WriteLine("-------------");
Console.WriteLine("Query to execute: " + cmd.CommandText);
Console.WriteLine("");
Console.WriteLine("");
//open connection
conn.Open();
//the actual command execution
SqlDataReader dr = cmd.ExecuteReader();
//if reader has any rows retrieve them
if (dr.HasRows)
{
Console.WriteLine("Query Results");
Console.WriteLine("-------------");
while (dr.Read())
{
//handle the retrieved record (i.e. display it)
Console.WriteLine(dr.GetInt16(0) + " – " + dr.GetString(1));
}
}
else
{
Console.WriteLine("Query Results");
Console.WriteLine("-------------");
Console.WriteLine("Error: Not data found.");
}
dr.Close();
}
}
}
}
As you can see in the above, new, more-secure code, now, we do not make use of string concatenation for constructing the final query. Instead, we are making use of the SqlParameter object in the following way:
In the initial query text, we write our query and in the WHERE clause, we are making use of the parameter @dbName
We then create the SqlParameter object, we define the parameter’s name, in this case “@dbName” and the type (i.e. VarChar)
The next step is to define the value for the newly created parameter. In this case we set as the parameter’s value, the user’s input via the command line arguments input args[0].ToString()
Now that the SqlParameter object is properly created and defined, we just add it to the SqlCommand object with the command <SqlCommandObject>.Parameters.Add(param);
Here’s the output of the new code (it is actually the same but this time the output was produced by the secure version of the code):
That’s it! Now, you have a more secure code with the help of the SqlCommand parameter!
A Useful Advice
In software development and generally in IT, there are always two options available for all the tasks that you do:
Option A: Just get things done
Option B: Get things done right
If you work based on Option A, it is highly likely that your code will always end up look like Code A (insecure code)
If you work based on Option B, there is a good chance to get your code look like Code B (secure code).
Personally, I always prefer Option B, no matter the fact that sometimes it takes a little bit more time 🙂
Artemakis Artemiou is a seasoned Senior Database and AI/Automation Architect with over 20 years of expertise in the IT industry. As a Certified Database, Cloud, and AI professional, he has been recognized as a thought leader, earning the prestigious Microsoft Data Platform MVP title for nine consecutive years (2009-2018). Driven by a passion for simplifying complex topics, Artemakis shares his expertise through articles, online courses, and speaking engagements. He empowers professionals around the globe to excel in Databases, Cloud, AI, Automation, and Software Development. Committed to innovation and education, Artemakis strives to make technology accessible and impactful for everyone.
Views:15,021
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit "Cookie Settings" to provide a controlled consent. Read More
This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Cookie
Duration
Description
cookielawinfo-checkbox-analytics
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional
11 months
The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy
11 months
The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.