{"id":2609,"date":"2018-07-12T12:04:03","date_gmt":"2018-07-12T10:04:03","guid":{"rendered":"https:\/\/www.sqlnethub.com\/?p=2609"},"modified":"2024-12-12T14:36:17","modified_gmt":"2024-12-12T12:36:17","slug":"using-the-csharp-sqlparameter-object-writing-more-secure-code","status":"publish","type":"post","link":"https:\/\/www.sqlnethub.com\/blog\/using-the-csharp-sqlparameter-object-writing-more-secure-code\/","title":{"rendered":"Using the C# SqlParameter Object for Writing More Secure Code"},"content":{"rendered":"

C# SqlParameter is a handy feature allows you to safely pass a parameter to a SqlCommand<\/a> 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<\/a> attacks.<\/p>\n

As described in OWASP<\/a>,\u00a0a SQL injection attack consists of insertion or “injection” of a SQL query via the input data from the client to the application.<\/p>\n

\n
\n

We Can Help you Get Started with .NET Programming Fast and Easy!<\/strong><\/span><\/em><\/p>\n

Enroll to our online course titled <\/em>“.NET Programming for Beginners: Windows Forms (C#)<\/a><\/strong>”
\n(special limited-time discount included in link).<\/span><\/em><\/p>\n

\".NET<\/a>
(Lifetime Access)<\/span><\/figcaption><\/figure>\n

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!\u00a0<\/em><\/p>\n

Many live demonstrations and downloadable resources included!<\/em><\/p>\n

Learn More<\/span><\/a><\/p>\n<\/blockquote>\n

\n

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.<\/p>\n

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.<\/p>\n

A Bad Example – High Risk Code (Code A)<\/h3>\n

First, let’s see a bad example<\/span><\/strong><\/span>\u00a0of 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:<\/p>\n

using System;\r\nusing System.Collections.Generic;\r\nusing System.Linq;\r\nusing System.Text;\r\nusing System.Threading.Tasks;\r\nusing System.Data.SqlClient;\r\nnamespace Test_Database_Connection\r\n{\r\n    class Program\r\n    {\r\n        static void Main(string[] args)\r\n        {\r\n            string connString = @\"Server=.\\SQL2K17;Database=master;Trusted_Connection = True;\";\r\n            using (SqlConnection conn = new SqlConnection(connString))\r\n            {\r\n                \/\/set the command to execute against SQL Server (this is where you set your query)\r\n                string query = @\"SELECT[fileid],[filename] FROM sysfiles WHERE name = '\" + args[0].ToString() + \"'\";\r\n\r\n                Console.WriteLine(\"\");\r\n                Console.WriteLine(\"Informational\");\r\n                Console.WriteLine(\"-------------\");\r\n                Console.WriteLine(\"Query to execute: \" + query);\r\n                Console.WriteLine(\"\");\r\n                Console.WriteLine(\"\");\r\n\r\n                \/\/set SqlCommand\r\n                SqlCommand cmd = new SqlCommand(query, conn);\r\n\r\n                \/\/open connection\r\n                conn.Open();\r\n\r\n                \/\/the actual command execution\r\n                SqlDataReader dr = cmd.ExecuteReader();\r\n\r\n                \/\/if reader has any rows retrieve them\r\n                if (dr.HasRows)\r\n                {\r\n                    Console.WriteLine(\"Query Results\");\r\n                    Console.WriteLine(\"-------------\");\r\n                    while (dr.Read())\r\n                    {\r\n                        \/\/handle the retrieved record (i.e. display it)\r\n                        Console.WriteLine(dr.GetInt16(0) + \" \u2013 \" + dr.GetString(1));\r\n                    }\r\n                }\r\n                else\r\n                {\r\n                    Console.WriteLine(\"Query Results\");\r\n                    Console.WriteLine(\"-------------\");\r\n                    Console.WriteLine(\"Error: Not data found.\");\r\n                }\r\n\r\n                dr.Close();\r\n            }\r\n        }\r\n    }\r\n}<\/pre>\n
As you can see, in the above code, line 17-18 <\/strong>builds up the query string by concatenating to the static text, the user input (args[0]). This is completely wrong and dangerous<\/span><\/strong>. This is like saying: “come and inject some malicious code!” \ud83d\ude42<\/em><\/p>\n
Here’s the output of the above code:<\/p>\n
\"C#<\/a><\/p>\n
 <\/p>\n
Turning the High Risk Code (Code A) into Secure Code with the use of C# SqlParameter (Code B)<\/h3>\n
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!<\/p>\n
Based on the above example, the code would be changed as below:<\/p>\n
using System;\r\nusing System.Collections.Generic;\r\nusing System.Linq;\r\nusing System.Text;\r\nusing System.Threading.Tasks;\r\nusing System.Data.SqlClient;\r\nusing System.Data;\r\n\r\nnamespace Test_Database_Connection\r\n{\r\n    class Program\r\n    {\r\n        static void Main(string[] args)\r\n        {\r\n\r\n            string connString = @\"Server=.\\SQL2K17;Database=master;Trusted_Connection = True;\";\r\n            using (SqlConnection conn = new SqlConnection(connString))\r\n            {\r\n                \/\/set the command to execute against SQL Server (this is where you set your query)\r\n                string query = @\"SELECT[fileid],[filename] FROM sysfiles WHERE name = @dbName\";\r\n\r\n                \/\/set SqlCommand\r\n                SqlCommand cmd = new SqlCommand(query, conn);\r\n\r\n                \/\/Set SqlParameter\r\n                SqlParameter param = new SqlParameter();\r\n                param.ParameterName = \"@dbName\";\r\n                param.SqlDbType = SqlDbType.VarChar;\r\n                param.Value = args[0].ToString();\r\n\r\n                \/\/Add SqlParameter to SqlCommand\r\n                cmd.Parameters.Add(param);\r\n\r\n                Console.WriteLine(\"\");\r\n                Console.WriteLine(\"Informational\");\r\n                Console.WriteLine(\"-------------\");\r\n                Console.WriteLine(\"Query to execute: \" + cmd.CommandText);\r\n                Console.WriteLine(\"\");\r\n                Console.WriteLine(\"\");\r\n\r\n\r\n                \/\/open connection\r\n                conn.Open();\r\n\r\n                \/\/the actual command execution\r\n                SqlDataReader dr = cmd.ExecuteReader();\r\n\r\n                \/\/if reader has any rows retrieve them\r\n                if (dr.HasRows)\r\n                {\r\n                    Console.WriteLine(\"Query Results\");\r\n                    Console.WriteLine(\"-------------\");\r\n                    while (dr.Read())\r\n                    {\r\n                        \/\/handle the retrieved record (i.e. display it)\r\n                        Console.WriteLine(dr.GetInt16(0) + \" \u2013 \" + dr.GetString(1));\r\n                    }\r\n                }\r\n                else\r\n                {\r\n                    Console.WriteLine(\"Query Results\");\r\n                    Console.WriteLine(\"-------------\");\r\n                    Console.WriteLine(\"Error: Not data found.\");\r\n                }\r\n                dr.Close();\r\n            }\r\n        }\r\n    }\r\n}<\/pre>\n
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:<\/p>\n
    \n
  1. In the initial query text, we write our query and in the WHERE clause, we are making use of the parameter @dbName<\/li>\n
  2. We then create the SqlParameter object, we define the parameter’s name, in this case “@dbName” and the type (i.e. VarChar)<\/li>\n
  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()<\/li>\n
  4. Now that the SqlParameter object is properly created and defined, we just add it to the SqlCommand object with the command <SqlCommandObject>.Parameters.<\/span>Add<\/span>(<\/span>param<\/span>)<\/span>;<\/span><\/li>\n<\/ol>\n
    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):<\/p>\n
    \"C#<\/a><\/p>\n
    That’s it! Now, you have a more secure code with the help of the SqlCommand parameter!<\/p>\n
     <\/p>\n
    A Useful Advice<\/h3>\n
    In software development and generally in IT, there are always two options available for all the tasks that you do:<\/p>\n