TomBecker.net

Back to Basics: Parameterizing Inline SQL

Things have been a bit slow with any interesting project work and I don't want this blog to fall even more stagnant . So I am going to be writing a new series called "Back to Basics" containing best practices and some basic code references aimed at newer coders.

There are some situations in which you cannot use stored procedures in order to handle all of your data access. Some cases it is just because of the architecture where you are at, other times it is because you are building the SQL at run time (this still can be done in both P/L or T-SQL but in this case we are going to write it inline).

But no matter what the circumstance, it is still good practice to parameterize your statements. This can help with style as well as helping to protect against SQL Injection (Wikipedia).

We will take a normal inline SQL statement and convert it to a parameterized statement. We will start with this.

public void Save() {

  string sql = "INSERT INTO People (First_Name, Last_Name, Age) VALUES (\"" + this.FirstName + "\", \"" + this.LastName +
   "\", \"" + this.Age + "\");";

  using (OracleConnection conn = new OracleConnection("connstring"))
   using (OracleCommand comm = new OracleCommand(conn, sql)) {
  comm.CommandType = CommandType.Text;

  comm.ExecuteNonQuery();

  }
}

This will work as is, and technically is correct. However, the readability goes out the window. With all the escape characters and everything in that statement it becomes hard to read very quickly. This is also a very small insert statement. Imagine a larger table with 15-20 rows. The statment would become huge and debugging would become hard if you mistyped something, so along with the readability, scaleability is bad as well.

So lets take that statement and parameterize it. This adds to the number of lines of code, but in this case it is well worth it. We take out all of the items in the VALUES clause and replace them with identifiers, much like you would if you were writing a full stored procedure. Then you can add the parameters to the command object. With this you can specify data types and sizes to even further restrict the data entered to help make sure no bad data gets through. This also allows you to significantly shorten your SQL statement, and allows you to add another value much easier than in the previous example.

public void Save() {

  string sql = "INSERT INTO People (First_Name, Last_Name, Age) VALUES (:FirstName, :LastName, :Age);";

  using (OracleConnection conn = new OracleConnection("connstring"))
   using (OracleCommand comm = new OracleCommand(conn, sql)) {
  comm.CommandType = CommandType.Text;

  comm.Parameters.Add(":FirstName", OracleType.VarChar, 50).Value = this.FirstName;
  comm.Parameters.Add(":LastName", OracleType.VarChar, 50).Value = this.LastName;
  comm.Parameters.Add(":Age", OracleType.Int32).Value = this.Age;

  comm.ExecuteNonQuery();

  }
}

This will work for both Oracle and MS SQL, you would just have to change from and Oracle connection and command to a SQL one. Also I think the preferred syntax when using MS SQL would be to use the @ symbol rather than the colon.

Labels: .NET, Back to Basics, SQL

posted by Tom Becker at 11/05/2009