Convert Dynamic SQL to Use SQLCommand Parameters Automatically

Dynamic SQL Can Die Automatically

by Les Smith

Now you can convert your antiquated and dangerous Dynamic SQL to use Named Parameters automatically.  Stop Injection Attacks and SQL failures from the use of aposthrophes in string variables.

One of my partners, Bill Ryan, wrote an excellent article on Dynamic SQL Needs to Die.I won't go into all of the problems he discussed that are associated with this dangerous methodology of creating SQL statements.  As Bill pointed out, stored procedures are preferable to SQL, but sometimes it is not always possible to use a stored procedure.

However it is possible to never use Dynamic SQL.  What we mean by Dynamic SQL is to create the SQL statement at run-time by concatenating strings and variables together.

Consider the following code.  There are five potential bugs in it.  Two of them are extremely likely to be hit.  In the two lines colored Red, the code is concatenating text data from a text box.  If the user happens to name the product or it's description with an aposthrophe (single quote) in it, which happens all the time, either on purpose or by accident, the SQL is bad and will cause a syntax error at run-time.  The careless programmer's first question is, "why did you put a single quote in the product name?"  The user's valid answer is, "because I could, what's wrong with it?"  If the programmer has no concept of user interfaces and the way users think, which, sad to say, describes more developers than we wish to acknowledge, he or she will say, "you can't do that, Structured Query Language (as if the user has any idea what Structured Query Language is) doesn't allow aposthrophes in names!"  No need to carry this discussion on, you get the picture.

SQL = "update products "   SQL &= "set productname = '" & .txtProductName.Text & "', "   SQL &= "productdescription = '" & .txtProductDescription.Text & "', "   SQL &= "priceperunit = " & UnFormat(.txtPricePerUnit.Text) & ", "   SQL &= "Unitsinstock = " & .txtUnitsInStock.Text & ", "   SQL &= "reorderlevel = " & .TextBox1.Text & " "   SQL = SQL & "where productid = '" & .txtProductID.Text & "'"

So the careless programmer makes a quick fix, as shown below.  Since the user input a "'" in the product name, the code shown below won't allow them do that any more, HA!  Yeah, guess what, the user puts an aposthrophe in the product description TextBox and here we go again, another call to Software Development.

SQL = "update products "   SQL &= "set productname = '" & Replace(.txtProductName.Text, "'", "''") & "', "   SQL &= "productdescription = '" & .txtProductDescription.Text & "', "   SQL &= "priceperunit = " & UnFormat(.txtPricePerUnit.Text) & ", "   SQL &= "Unitsinstock = " & .txtUnitsInStock.Text & ", "   SQL &= "reorderlevel = " & .TextBox1.Text & " "   SQL = SQL & "where productid = '" & .txtProductID.Text & "'"

Why would anyone want to keep fighting this battle everytime a text field is written to database?  Ridiculus, eh?  Not if you read News Groups!  I'll not mention any names, but one guy on the News Groups, when told to use SQL Command Parameters, replied, "I'm a professional, I won't forget to replace the single quotes."  The problem is, that a few days later, the professional is found posting to another news group and apparently has forgotten.

Code shown below will solve the problem for all of the parameter fields in the SQL statement.

Dim sqlCmd As New SqlClient.SqlCommand   SQL = "update products "   SQL &= "set productname = @dbParam1, "   sqlCmd.Parameters.Add("@dbParam1", Replace(.txtProductName.Text, "'", "''"))   SQL &= "productdescription = @dbParam2, "   sqlCmd.Parameters.Add("@dbParam2", .txtProductDescription.Text)   SQL &= "priceperunit = @dbParam3, "   sqlCmd.Parameters.Add("@dbParam3", UnFormat(.txtPricePerUnit.Text))   SQL &= "Unitsinstock = @dbParam4, "   sqlCmd.Parameters.Add("@dbParam4", .txtUnitsInStock.Text)   SQL &= "reorderlevel = @dbParam5 "   sqlCmd.Parameters.Add("@dbParam5", .TextBox1.Text)   SQL = SQL & "where productid = @dbParam6"   sqlCmd.Parameters.Add("@dbParam6", .txtProductID.Text)   sqlCmd.CommandText = SQL   sqlCmd.CommandType = CommandType.Text

We have replaced Dynamic SQL with SQL Command Parameters and they handle the single quote problem automatically.  Not only can the single quote cause a client server program to crash, but on the Web, Dynamic SQL opens your site to hacking and injection attacks.

Now, for a little commercial.  The code transformation was done automatically by the new SQL Parameter feature of NET Refactor.  This powerful new feature is now available in a 30 Day Free Trial.

VB.NET Code is shown above, but here is C# code being transformed.  The old code is shown below.

sql ="select * ";       sql += "from table ";// comment       sql += "where a = '" + lname + "' "; /* comment */       sql += "and b = '" + fname + "' ";       sql += "and c = " + citycode +           " and d = 'a' " +           " and e = " + citycode + " and f = '" +           fname;       sql += "' and datev = '" + DateAdd(1,1,Today) & "' ";

Now, I will select the code shown above and click the SQL Parameters Menu Option in NET Refactor and get the following C# code.

string sqlCmd = new SqlClient.SqlCommand();       sql ="select * ";       sql += "from table ";       sql += "where a = @dbParam1 ";       sqlCmd.Parameters.Add("@dbParam1", lname);       sql += "and b = @dbParam2 ";       sqlCmd.Parameters.Add("@dbParam2", fname);       sql += "and c = @dbParam3 " +         " and d = 'a' " +         " and e = @dbParam4 and f = '" +         "@dbParam5";       sqlCmd.Parameters.Add("@dbParam3", citycode);       sqlCmd.Parameters.Add("@dbParam4", citycode);       sqlCmd.Parameters.Add("@dbParam5", fname);       sql += "' and datev = @dbParam6 ";       sqlCmd.Parameters.Add("@dbParam6", DateAdd(1,1,Today));       sqlCmd.CommandText = sql;       sqlCmd.CommandType = CommandType.Text;

You don't have to be plagued with Dynamic SQL Problems anymore.  The reason I built this tool is so that I could get rid of Dynamic SQL in my own applications.