Using the C# SqlParameter Object for Writing More Secure Code

Using the C# SqlParameter Object for Writing More Secure Code

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.

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 19 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.

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 (1 votes, average: 5.00 out of 5)

Loading...

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

© 2018 SQLNetHub

Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.