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

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!

Learn More

---

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:

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):

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.

 

Recommended Online Courses:

• AI Essentials: A Beginner’s Guide to Artificial Intelligence
• SQL Server 2022: What’s New – New and Enhanced Features
• Working with Python on Windows and SQL Server Databases
• Introduction to Azure Database for MySQL
• Boost SQL Server Database Performance with In-Memory OLTP
• Introduction to Azure SQL Database for Beginners
• Essential SQL Server Administration Tips
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Development Tips for SQL Developers
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
  
• SQL Server 2019: What’s New – New and Enhanced Features
• Entity Framework: Getting Started – Complete Beginners Guide
  
• Data Management for Beginners – Main Principles
  
• A Guide on How to Start and Monetize a Successful Blog

 

Read Also:

• How to Set Filters for OpenFileDialog and SaveFileDialog in C#
• .NET Programming for Beginners – Windows Forms (C#)
• What is Abstraction in Object Oriented Programming?
• Using ClickOnce for Deploying your .NET Windows Forms Apps
• There is no argument given that corresponds to the required formal parameter
• How to Establish a Simple Connection from a C# Program to SQL Server
• The timeout period elapsed prior to obtaining a connection from the pool
• Closing a C# Application (including hidden forms)
• Changing the startup form in a C# project
• Cannot implicitly convert type ‘string’ to ‘System.Windows.Forms.DataGridViewTextBoxColumn
• The type or namespace name ‘Office’ does not exist in the namespace ‘Microsoft’ – How to Resolve
• How to Connect to SQL Server from Visual C++.
• How to Build a Simple Image Viewer with .NET WinForms and C# in Visual Studio

 

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

Loading...

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

© SQLNetHub