As a general rule, parameters should always be used when inserting values into SQL statements. Many people, generally new developers, don't follow this rule because they either don't realise that parameters exist or they don't understand the issues that their use helps to avoid. When I was new to .NET programming, I fell into the former category. Not having read extensively on ADO.NET at the time, I didn't know that parameters could be used to insert field values into SQL code. I started out writing code like this:
C#string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
"VALUES ('" + this.firstNameField.Text +
"', '" + this.lastNameField.Text +
"', '" + this.dateOfBirthPicker.Value.ToString("yyyy-MM-dd") +
"', " + this.childrenSpinner.Value + ")";
SqlCommand myCommand = new SqlCommand(sql);
VB
Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
"VALUES ('" & Me.firstNameField.Text & _
"', '" & Me.lastNameField.Text & _
"', '" & Me.dateOfBirthPicker.Value.ToString("yyyy-MM-dd") & _
"', " & Me.childrenSpinner.Value & ")"
Dim myCommand As New SqlCommand(sql)
N.B. I will initially write all examples using the SqlClient ADO.NET provider for SQL Server and later I will discuss the adjustments required when using other providers and other data sources.
The previous example demonstrates various issues associated with this type of code but let’s start with the most obvious: it’s hard to read. With all the various literals and property values being concatenated it’s a bit hard to immediately pick out what’s what. As a result, it’s very easy to make mistakes in such code. It’s very easy to miss a single quote here or a comma there. Of course, it’s not too hard to actually view the end result to see if it is what you intended it to be. I’m constantly surprised, though, by the number of people who build such strings and still don’t actually look at their contents, even when an error occurs.
C#string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
"VALUES ('" + this.firstNameField.Text +
"', '" + this.lastNameField.Text +
"', '" + this.dateOfBirthPicker.Value.ToString("yyyy-MM-dd") +
"', " + this.childrenSpinner.Value + ")";
MessageBox.Show(sql);VB
Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
"VALUES ('" & Me.firstNameField.Text & _
"', '" & Me.lastNameField.Text & _
"', '" & Me.dateOfBirthPicker.Value.ToString("yyyy-MM-dd") & _
"', " & Me.childrenSpinner.Value & ")"
MessageBox.Show(sql)
While there are other, possibly better ways, this is the first thing that should occur to a new developer: a simple message box to display the string that was constructed. This will make it much easier to see if you’ve simply missed some punctuation. This is a lesson that should be taken into all development: always view your data and don’t just assume that it is what it’s supposed to be. If an error occurs then you’ve done something wrong and invalid or incorrect data is always a likely candidate.
Now, I’ve always been a big fan of the String.Format method in preference to straight string concatenation when I need to join more than two or three substrings together. Building SQL statements was one of the first scenarios in which I put it to work. It makes such code easier to read and, therefore, less error-prone.
C#string sql = string.Format("INSERT INTO User (FirstName, LastName, " +
"DateOfBirth, ChildCount) " + "VALUES ('{0}', '{1}', '{2:yyyy-MM-dd}', {3})", this.firstNameField.Text, this.lastNameField.Text, this.dateOfBirthPicker.Value, this.childrenSpinner.Value);SqlCommand myCommand = new SqlCommand(sql);
VB
Dim sql As String = String.Format("INSERT INTO User (FirstName, LastName, " & _
"DateOfBirth, ChildCount) " & _ "VALUES ('{0}', '{1}', '{2:yyyy-MM-dd}', {3})", _ Me.firstNameField.Text, _ Me.lastNameField.Text, _ Me.dateOfBirthPicker.Value, _ Me.childrenSpinner.Value)Dim myCommand As New SqlCommand(sql)
As you can see, it’s much easier to tell where each single quote and comma is and, therefore, whether any are missing. It also makes formatting values such as dates a bit neater.
I thought that this was an improvement, and it was, but it brings me to the next issue: inserting dates and times into SQL code. As you can see in the last two examples, I’ve had to explicitly format the date of birth. That’s because you cannot simply rely on a date and/or time value to be formatted by the .NET Framework in a way that is valid for your database. Even if the format is valid you may end up using a different value to the one you expected because of regional settings. If your month and day values get transposed between your application and your database then you may end up saving incorrect data, which is even worse than your application failing with an error message. We can always do as I’ve done above and use a format that will always work. It’s not a big deal to find out how each database you use formats date literals and then explicitly create that format, but wouldn’t it be better to not have to worry about format at all? When you use parameters you don’t have to.
Before I provide a code example that does use parameters I will touch on another common issue: strings containing apostrophes. This is a very common problem which, again, can be diagnosed by actually viewing the string you build. Doing so should make it obvious that you end up with mismatched single quotes. The question is, how do you prevent the issue. To include a single quote in a text literal you escape it with another single quote. As string concatenation is creating a SQL statement containing literals, that’s what needs to be done:
C#string sql = string.Format("INSERT INTO User (FirstName, LastName, " +
"DateOfBirth, ChildCount) " + "VALUES ('{0}', '{1}', '{2:yyyy-MM-dd}', {3})",this.firstNameField.Text.Replace("'", "''"),
this.lastNameField.Text.Replace("'", "''"),
this.dateOfBirthPicker.Value, this.childrenSpinner.Value);SqlCommand myCommand = new SqlCommand(sql);
VB
Dim sql As String = String.Format("INSERT INTO User (FirstName, LastName, " & _
"DateOfBirth, ChildCount) " & _ "VALUES ('{0}', '{1}', '{2:yyyy-MM-dd}', {3})", _Me.firstNameField.Text.Replace("'", "''"), _
Me.lastNameField.Text.Replace("'", "''"), _
Me.dateOfBirthPicker.Value, _ Me.childrenSpinner.Value)Dim myCommand As New SqlCommand(sql)
Now, that’s not a big deal but why do it if it’s not necessary? Also, what about other unusual characters like line breaks? The simple fact is that, if you use parameters, all these considerations go away. The values never become part of the literal string so there’s no need to escape single quotes. Values remain in binary form rather than being converted to strings, so there’s no need to worry about format. There’s no need to worry about what values need to be wrapped in single quotes or other delimiters and which don’t. None of that is a concern.
C#string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
"VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)";SqlCommand myCommand = new SqlCommand(sql);
SqlParameterCollection parameters = myCommand.Parameters;parameters.AddWithValue("@FirstName", this.firstNameField.Text);
parameters.AddWithValue("@LastName", this.lastNameField.Text);
parameters.AddWithValue("@DateOfBirth", this.dateOfBirthPicker.Value.Date);
parameters.AddWithValue("@ChildCount", Convert.ToInt32(this.childrenSpinner.Value));
VB
Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
"VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)"Dim myCommand As New SqlCommand(sql)
With myCommand.Parameters.AddWithValue("@FirstName", Me.firstNameField.Text)
.AddWithValue("@LastName", Me.lastNameField.Text)
.AddWithValue("@DateOfBirth", Me.dateOfBirthPicker.Value.Date)
.AddWithValue("@ChildCount", CInt(Me.childrenSpinner.Value))
End With
The first point to note here is how much easier the SQL code is to read. There’s no need for any string concatenation (other than what I’ve used to fit the code within the width of this page), punctuation or formatting, so the opportunity for errors is greatly reduced. In this case (but not all), each value has a name that makes its purpose obvious. Those names match up with the names of the parameters that are added to the command, so it’s easy to trace exactly what’s happening.
Now, let’s examine how the parameters are added to the command. In cases where the SQL code will be executed only once the parameters will only ever have one value. In such cases it’s advisable to call the AddWithValue method as it’s the simplest way to add a parameter and set its value.
The SqlClient ADO.NET provider supports named parameters, so notice that the parameter names in the SQL code match the parameter names passed to AddWithValue. We’ll look at positional parameters later but, in this case, the names must match or you’ll either send the wrong data to the wrong place or an exception will be thrown, which are obviously both poor outcomes.When calling AddWithValue, the data type of the parameter is inferred from the type of the specified value. That’s why, in the case of the parameter named @ChildCount, the Value property of the NumericUpDown control is converted to an Int32 before being assigned. If the Value property was used as is then the type of the parameter would be inferred from a Decimal instead of an Int32 and a data type mismatch would occur at the database.
Note also that, in the case of the parameter named @DateOfBirth, a DateTime is retrieved from the DateTimePicker’s Value property and then the Date property of that value is used. Because the code is not using formatting to create a date-only string, if we want only the date and not the time then we must explicitly assign only the date and not the time.
As mentioned previously, if the SQL code is being executed only once then the value of each parameter doesn’t need to change. In such cases it is easiest to specify the value when creating the parameter and let the data type be inferred. In cases where the SQL code will be executed multiple times with different parameter values each time we need to take a different approach. In such cases we need to specify the data type when creating the parameters, then set their Value properties each time we want to use new data.
C#string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
"VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)";SqlCommand myCommand = new SqlCommand(sql, myConnection);
SqlParameterCollection parameters = myCommand.Parameters;parameters.Add("@FirstName", SqlDbType.VarChar, 50);
parameters.Add("@LastName", SqlDbType.VarChar, 50);
parameters.Add("@DateOfBirth", SqlDbType.DateTime);
parameters.Add("@ChildCount", SqlDbType.Int);
foreach (ListItem item in myList)
{ parameters["@FirstName"].Value = item.FirstName; parameters["@LastName"].Value = item.LastName; parameters["@DateOfBirth"].Value = item.DateOfBirth; parameters["@ChildCount"].Value = item.ChildCount;myCommand.ExecuteNonQuery();
}
VB
Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
"VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)"Dim myCommand As New SqlCommand(sql, myConnection)
With myCommand.Parameters .Add("@FirstName", SqlDbType.VarChar, 50) .Add("@LastName", SqlDbType.VarChar, 50) .Add("@DateOfBirth", SqlDbType.DateTime) .Add("@ChildCount", SqlDbType.Int)For Each item As ListItem In myList
.Item("@FirstName").Value = item.FirstName .Item("@LastName").Value = item.LastName .Item("@DateOfBirth").Value = item.DateOfBirth .Item("@ChildCount").Value = item.ChildCountmyCommand.ExecuteNonQuery()
NextEnd With
The parameters are created and added to the command once only and then their Value properties are set each time new values are needed. The data source-specific data types of the parameters are specified when they are created because they cannot be inferred. Finally, the size is also specified for those parameters whose type requires it. Scale and precision can also be set if required.
There is another way to set parameter values also. Using a DataAdapter you might save data directly from a DataTable. In that case you won’t be looping through the records and setting parameter values yourself. It still needs to be done but the DataAdapter will do it for you. In that case though, you need to tell it exactly where to get the value for each parameter. You do this by specifying the SourceColumn of each parameter, which is the name of the column from which the parameter’s Value will be taken for each DataRow.
C#string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
"VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)";SqlCommand myCommand = new SqlCommand(sql);
SqlParameterCollection parameters = myCommand.Parameters;parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName");
parameters.Add("@LastName", SqlDbType.VarChar, 50, "LastName");
parameters.Add("@DateOfBirth", SqlDbType.DateTime, 0, "DateOfBirth");
parameters.Add("@ChildCount", SqlDbType.Int, 0, "ChildCount");
SqlDataAdapter myAdapter = new SqlDataAdapter();
myAdapter.InsertCommand = myCommand;
VB
Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
"VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)"Dim myCommand As New SqlCommand(sql)
With myCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName")
.Add("@LastName", SqlDbType.VarChar, 50, "LastName")
.Add("@DateOfBirth", SqlDbType.DateTime, 0, "DateOfBirth")
.Add("@ChildCount", SqlDbType.Int, 0, "ChildCount")
End With
Dim myAdapter As New SqlDataAdapter
myAdapter.InsertCommand = myCommand
In this case the fourth parameter of the Add method is the parameter’s SourceColumn. Note that this is the name of a DataColumn in the DataTable that will be passed to the DataAdapter’s Update method, i.e. it is the name of the column the data is coming from, not the name of the column in the database the data is going to. In the majority of cases those two column names will be the same but certainly not in all.
It’s also important to note that, if we are passing a fourth argument to the Add method, we must be passing a third argument too. That third argument is the maximum size of the data. For variable-size data types, like VarChar, this value is important but for fixed-size data types, like Int, it is not. If you know the specific size of a fixed-size data type, e.g. 4 bytes for type Int, then you can specify that value but there’s really no need. All values of a fixed-size data type will be the same size so there’s no need to specify the maximum size. As such you can just pass zero for fixed-size data types.
Now, let’s look at how using parameters differs amongst ADO.NET providers and data sources. First up, let’s look using OleDb and an Access database. The code should look almost exactly the same, except using OleDb classes instead of SqlClient classes.
C#string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
"VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)";OleDbCommand myCommand = new OleDbCommand(sql);
OleDbParameterCollection parameters = myCommand.Parameters;parameters.AddWithValue("@FirstName", this.firstNameField.Text);
parameters.AddWithValue("@LastName", this.lastNameField.Text);
parameters.AddWithValue("@DateOfBirth", this.dateOfBirthPicker.Value.Date);
parameters.AddWithValue("@ChildCount", Convert.ToInt32(this.childrenSpinner.Value));
VB
Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
"VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)"Dim myCommand As New OleDbCommand(sql)
With myCommand.Parameters.AddWithValue("@FirstName", Me.firstNameField.Text)
.AddWithValue("@LastName", Me.lastNameField.Text)
.AddWithValue("@DateOfBirth", Me.dateOfBirthPicker.Value.Date)
.AddWithValue("@ChildCount", CInt(Me.childrenSpinner.Value))
End With
It’s important to understand that there’s a subtle difference in the way the code behaves though. As I said earlier, SqlClient supports named parameters. That is to say that when you add each parameter to a SqlCommand its name is used as a key to identify it. When it comes time to insert the values into the SQL code, every instance of that name is replaced with the value of the parameter with that name. Even though the code in the preceding example uses the same parameter names in the SQL code and the same names when adding the parameters, those names are not actually related.
The Jet OLEDB provider uses the position of each parameter to decide what values to substitute into the SQL code. All the parameter names in the SQL code are numbered and all the parameters added to the OleDbCommand are numbered. Values are then substituted based on those positions, whether or not the names match. This leads to two important considerations when using OleDb and Access. Firstly, while it makes no difference what order the parameters are added to a SqlCommand, you must ensure that parameters are added to an OleDbCommand in the order they appear in the SQL code. Secondly, while the value of a single SqlParameter can be substituted into SQL code as many times as required, even if the same value is to be used more than once in an OleDbCommand, you must add a discrete parameter for each place it will be used.
Next, let’s look at using OleDb with a SQL Server database. This should rarely be done, given that SqlClient provides more features and better performance, but there may be occasions where it’s necessary. Like the Jet OLEDB provider, the SQL Server OLEDB provider supports only positional parameters. Unlike the Jet provider though, it also doesn’t support pseudo-names for the parameters in the SQL code. In this case you must use “?” symbols in the code to represent a parameter.
C#string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
"VALUES (?, ?, ?, ?)";OleDbCommand myCommand = new OleDbCommand(sql);
OleDbParameterCollection parameters = myCommand.Parameters;parameters.AddWithValue("FirstName", this.firstNameField.Text);
parameters.AddWithValue("LastName", this.lastNameField.Text);
parameters.AddWithValue("DateOfBirth", this.dateOfBirthPicker.Value.Date);
parameters.AddWithValue("ChildCount", Convert.ToInt32(this.childrenSpinner.Value));
VB
Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
"VALUES (?, ?, ?, ?)"Dim myCommand As New OleDbCommand(sql)
With myCommand.Parameters.AddWithValue("FirstName", Me.firstNameField.Text)
.AddWithValue("LastName", Me.lastNameField.Text)
.AddWithValue("DateOfBirth", Me.dateOfBirthPicker.Value.Date)
.AddWithValue("ChildCount", CInt(Me.childrenSpinner.Value))
End With
The Jet OLEDB provider also supports this notation, as do many others.
There are various other ADO.NET providers that support named parameters however. One such example is the Connector/Net provider available for MySQL. It supports a similar notation to the SqlClient provider except that parameter names must be prefixed with “?” instead of “@”.
C#string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
"VALUES (?FirstName, ?LastName, ?DateOfBirth, ?ChildCount)";MySqlCommand myCommand = new MySqlCommand(sql);
MySqlParameterCollection parameters = myCommand.Parameters;parameters.AddWithValue("?FirstName", this.firstNameField.Text);
parameters.AddWithValue("?LastName", this.lastNameField.Text);
parameters.AddWithValue("?DateOfBirth", this.dateOfBirthPicker.Value.Date);
parameters.AddWithValue("?ChildCount", Convert.ToInt32(this.childrenSpinner.Value));
VB
Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
"VALUES (?FirstName, ?LastName, ?DateOfBirth, ?ChildCount)"Dim myCommand As New MySqlCommand(sql)
With myCommand.Parameters.AddWithValue("?FirstName", Me.firstNameField.Text)
.AddWithValue("?LastName", Me.lastNameField.Text)
.AddWithValue("?DateOfBirth", Me.dateOfBirthPicker.Value.Date)
.AddWithValue("?ChildCount", CInt(Me.childrenSpinner.Value))
End With
You should check the documentation for other providers and data sources to see what notation they support.
I should also point out that the use of parameters is no different when using stored procedures. Whether your CommandType is Text or StoredProcedure, if your SQL code contains parameters then you add them to your command in exactly the same way.
Finally, let’s look at one of the most important reasons to use parameters in the first place: security. When you use string concatenation to build SQL statements you can insert anything into the code at any position. You may intend to insert just a single value but, if the data is provided by the user, it’s often difficult or impossible to ensure that that’s all you’re getting. This can allow malicious users to perform what’s called “SQL injection”, where they inject a chunk of SQL code into your statement where you intended to use only a value. For example, let’s consider a situation where you want to allow the user to search for an employee record by payroll number:
C#string sql = "SELECT * " +
"FROM Employee " + "WHERE PayrollNumber = '" + this.payrollNumberField.Text + "'";VB
Dim sql As String = "SELECT * " & _
"FROM Employee " & _ "WHERE PayrollNumber = '" & _ Me.payrollNumberField.Text & _ "'"In that case you expect the user to enter a payroll number and the system will find the matching record if such a record exists. For instance, if the user enters the value “12345” then the SQL statement that will eventually be executed will be:
SELECT * FROM Employee WHERE PayrollNumber = ‘12345’
That’s exactly as you intended, but what happens if a malicious user enters the value “12345’; DELETE FROM Employee; SELECT * FROM Employee WHERE PayrollNumber = ‘12345”? The actual SQL code that eventually gets executed will be:
SELECT * FROM Employee WHERE PayrollNumber = ‘12345’
DELETE FROM Employee
SELECT * FROM Employee WHERE PayrollNumber = ‘12345’
Congratulations, you just deleted the entire contents of your Employee table. By using a parameter to insert the payroll number you ensure that whatever the user enters gets treated as a value and cannot be interpreted as SQL code.
There will be situations where SQL injection will not be possible but you should still use parameters all the time. Doing so helps you to avoid various issues and is just a good habit to get into. If you always use parameters then you never have to decide whether you should or not and you can never make the wrong choice.
