Code Focused

Create Parameterized Queries in ADO.NET

Squash the dangers lurking in your SQL queries by setting parameters by way of parameterized queries.

• By Tim Patrick
• 07/27/2017

So much of modern programming is about string manipulation. Whether it’s parsing XML content, building HTML for the browser or trying to understand what the user just typed into that text entry field, developers have no choice but to deal with text all the time. Today’s programming languages have adequate tools for working with strings, but an incorrectly formatted chunk of text can still ruin your whole day, especially when user data is involved.

Such is the case with SQL queries. They seem so innocent, and so useful. But dangers lurk within every space-separated keyword. Consider a basic query crafted using ordinary C# string concatenation:

// ----- Code specific to Windows Forms/ADO.NET/SQL Server.
//       Assumes: "using System.Data.SqlClient;"
string sqlText = "UPDATE Customer " +
  "SET Name = '" + NameField.Text + "', " +
  "Age = " + AgeField.Text + " " +
  "WHERE ID = " + CustomerID;
SqlCommand dbCommand = new SqlCommand(sqlText, existingSqlConnection);
dbCommand.ExecuteNonQuery();

This query works, sometimes. Because it’s built from basic strings with little in the way of data validation, it suffers from the following issues, among others:

• If NameField.Text contains a single quote mark, as with the name "O’Reilly," the quote marks will become unbalanced and the query will fail.
• If AgeField.Text is blank, or contains a non-numeric value, the Age database field will not be set correctly, and the query will fail.
• If a hacker types "0 AND Password = 'abc123'" in the AgeField input field, it will change the intent of the query, and open the database up to misuse. This is the dreaded "SQL Injection Attack" you keep hearing about on the nightly news.

Beyond these data issues, the query as defined brings with it some performance issues, especially at the database level. Some databases, like SQL Server, send incoming queries through a compilation process. The database caches each query, and reuses the compiled version when a request for the identical query arrives. By embedding user-supplied text in the body of the query, there is little or no chance that a previous query will be reused, increasing the need for query parsing and compiling.

Obviously, there’s a solution to these problems: parameterized queries. Such queries separate the body of the query from the data values managed by that query, and enforce data type expectations on those values. The process begins by replacing hardcoded values in the query text with placeholders, each starting with an "@" sign:

string sqlText = @"UPDATE Customer
  SET Name = @NewName, Age = @NewAge
  WHERE ID = @RecordID";
SqlCommand dbCommand = new SqlCommand(sqlText, existingSqlConnection);

Right away, things look simpler. The next step is the new part, using parameters to provide the source data values that the database engine will eventually insert in the placeholder positions. Each parameter is added to the ADO.NET command object through its Parameters collection. The easiest way to append a value to this set is through the AddWithValue method:

dbCommand.Parameters.AddWithValue("@RecordID", CustomerID);

A more explicit format instantiates a new SqlParameter instance manually, adding it to the collection after its members have been set appropriately:

dbCommand.Parameters.Add(new SqlParameter("@RecordID", CustomerID));

Parameters give you plenty of ways to hand massage the data before being sent to the database. Here’s some code that manages nullable fields based on the quality of the user input:

SqlParameter oneParam = new SqlParameter("@NewAge", SqlDbType.Int);
if (int.TryParse(AgeField.Text, out int age) == true)
  oneParam.Value = age;
else
  oneParam.Value = System.DBNull.Value;
dbCommand.Parameters.Add(oneParam);

Parameterized queries can also be used with database-level stored procedures, although you need to warn the query in advance:

// ----- Placeholders are not required, just the procedure name.
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "ProcedureNameGoesHere";

If a stored procedure parameter will only return data, the parameter object’s Direction property provides guidance:

oneParam.Direction = ParameterDirection.Output;
// ----- Then, when running the query:
dbCommand.ExecuteReader();
long newID = Convert.ToInt64(dbCommand.Parameters["@OutputID"].Value);

ADO.NET sends the query text and the collection of parameters to the database as separate components of the overall processing request, leaving it to the database how best to merge them together for safety and efficiency. And it frees you up from having to worry about stray apostrophes and stray hackers.