Using the C# SqlParameter Object for Writing More Secure Code

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!

Enroll to our online course titled .NET Programming for Beginners: Windows Forms (C#)
(special limited-time discount included in link).

.NET Programming for Beginners - Windows Forms with C# (Online Course)
(Lifetime Access)

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!

Enroll Now with Discount!


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:

C# SqlParameter Example - SQLNetHub Blog

 

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:

  1. In the initial query text, we write our query and in the WHERE clause, we are making use of the parameter @dbName
  2. We then create the SqlParameter object, we define the parameter’s name, in this case “@dbName” and the type (i.e. VarChar)
  3. 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()
  4. 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):

C# SqlParameter Example - SQLNetHub Blog

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 🙂

Learn more about how you can protect your code from SQL Injection by checking OWASP’s SQL Injection Prevention Cheat Sheet.

 

Featured Online Courses:

 

Read Also:

 

Did you find this article useful and interesting?

Interested in SQL Server Administration? Find hundreds of useful SQL Server administration and troubleshooting articles in 9 Times Microsoft Data Platform MVP, Artemakis’ Artemiou eBook: “Administering SQL Server (Second Edition)“.

Check our other related .NET articles.

Check out our latest software releases!

Subscribe to our newsletter and stay up to date!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 4.57 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub