Using Parameterized Queries in ASP.Net

There are two impelling reasons everyone should learn about Parameterized Queries. One entails one keyboard character and is more of a hassle reliever than anything else. That character, in code, can become either of two different objects, the single quote and the apostrophe. When you're coding, either one can make your life truely miserable at times. The second, and MOST compelling reason to learn Parameterized Queries is to protect your database from SQL Injection Injection Attacks. If you have never heard of them, you need to hear about them now. These attacks can reak havoc on your server and, more importantly, your data. Check out these articles on SQL Injection Attacks:
http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/
http://www.tech-faq.com/sql-injection-attack.shtml
http://www.codeproject.com/aspnet/SqlInjection.asp
Simply by using Parameterized Queries, this becomes a first line of defense, and SQL Injection attacks are stopped in their tracks.
Anyone who has ever put together a long, involved SQL statement with variables, juggling single quotes, along with the double quotes (Tutorial on Single and Double Quotes), will tell you that it's not much fun. And - on top of that, when we then talk about the apostrophe, it gets even more complicated. Of course, as most of you know, in an Insert statement, if the last Name is O'Hara, the engine looks at the string, and only sees the apostrophe as a single quote, thereby truncating your perfectly structured SQL statement. Naturally, there are fixes for the latter (Replace statement to double up on the apostrophes, which tells the DataBase engine to interpret the two apostrophes as only one true apostrophe and not a single quote), but that's extra care you must take that's really not needed - but only if you use Parameterized Queries.

Let's put together a scenario - we're searching Employees of the Northwind Trading Company by last name. We have a DropDownList (DDL) with all the last names. From that, the end user will choose one, so that the rest of that employee's information will appear. This, naturally, is a fairly simple scenario to start. We don't want to get too complicated too quickly.

Since we're searching the database by the last name, The SQL statement, using single quotes would be something like this:


sql = "Select * from Employees where Lastname = '" & ddl.selecteditem.text & "'"

As you can see, this could get pretty complicated, adding AND clauses and/or OR clauses on the end of the WHERE clause.
Using a Parameterized Query, it would look like this:


sql = "Select * from Employees where Lastname =@LastName"
All we'd need for this to work is to add the following to your code, after instantiating your Command:
(assuming you have defined a Command called 'cmd')

cmd.Parameters.Add(New SQLParameter("@LastName", ddl.SelectedItem.text))
Of course, this is an example for SQL Server. For OleDb (MS Access, and others), you would use (you guessed it!):

cmd.Parameters.Add(New OleDbParameter("@LastName", ddl.SelectedItem.text))
Of course, if you wanted to do a LIKE query (Select * from tablename where fieldname LIKE, etc), you would substitute the '=' sign for 'Like'. THEN, you could add your wildcards ('%' for SQL Server and '*" for MS Access) into the actual data being used for the search. For instance, if you had a text box for the end user to enter data to do a LIKE search - just have them put the wildcards in where they want (at the end, start, or both).
Where this really comes in handy is when you create an Insert or Update SQL statement. Suppose you were inserting a FirstName, LastName, Address, City, State, Zip, Phone, and Email address. Here's the older way you would have done this with single quotes:


sql="Insert into Employees (Firstname, Lastname, City, State, Zip, Phone, Email) Values ('" & frmFirstname.text & "', '" & frmLastName & "', '" & frmCity & "', '" & frmState & "', '" & frmZip & "', '" & frmPhone & "', '" & frmEmail & "')"
As you can see, this can get pretty cumbersome, doing it by hand. Here's the way you would take care of it with a Parameterized Query:


Dim MySQL as string = "Insert into NewEmp (fname, LName, Address, City, State, Postalcode, Phone, Email) Values (@Firstname, @LastName, @Address, @City, @State, @Postalcode, @Phone, @Email)"
Then, of course, you'd need to specify the parameters:

With SQL Server:
With cmd.Parameters:
.Add(New SQLParameter("@Firstname", frmFname.text))
.Add(New SQLParameter("@LastName", frmLname.text))
.Add(New SQLParameter("@Address", frmAddress.text))
.Add(New SQLParameter("@City", frmCity.text))
.Add(New SQLParameter("@state", frmState.text))
.Add(New SQLParameter("@Postalcode", frmPostalCode.Text))
.Add(New SQLParameter("@Phone", frmPhone.text))
.Add(New SQLParameter("@email", frmemail.text))
end with

With OleDb:
With cmd.Parameters:
.Add(New OleDbParameter("@Firstname", frmFname.text))
.Add(New OleDbParameter("@LastName", frmLname.text))
.Add(New OleDbParameter("@Address", frmAddress.text))
.Add(New OleDbParameter("@City", frmCity.text))
.Add(New OleDbParameter("@state", frmState.text))
.Add(New OleDbParameter("@Postalcode", frmPostalCode.Text))
.Add(New OleDbParameter("@Phone", frmPhone.text))
.Add(New OleDbParameter("@email", frmemail.text))
end with


Here's one note I received from a reader, Randy, that I'd like to mention here:
"I found out the hard way that the delineating of parameters must be in the SAME ORDER as they appear in the SQL command."
As far as using Stored Procedures, check out this code sample on using a Stored Procedure to populate a DropDownList - naturally, using parameters.

We'll discuss wldcards and using the 'Like' operator in another Tutorial called "Parameterized Queries - Part II".

I'm sure at this point, it's easy to also see, that consistent naming, from the Form Field names, to the Parameter Names is by far the best way to go. It's much easier to see at a glance, exactly what's going on in the code.

I've seen many examples on the net and in books that go into length concerning filetypes, sizes, etc when defining parameters. And while that's a more exact way of doing it, this tutorial shows that there is not just one way to do things in the ASP.Net world. Of course it's always best to make sure the data going in is in the exact form the database needs, in all cases. Therefore, whichever way you decide to do it, make sure your validation is the best it can be.

http://aspnet101.com/aspnet101/tutorials.aspx?id=1