Security is a hot topic in any field relating to computers and the internet. When you're writing a website that will face the public internet, you have to realize that 1.3 billion people (at the writing of this tutorial) can access your web page. Of these people, a sizable number would like nothing more than to take advantage of poorly written code and hijack your page to serve malware, spyware, viruses, make political statements, display horrible ascii art, or any number of other things.

The page we've coded so far, does it look secure? It's not. The problem is that we take user input and just blindly stick it into a database. Why does that matter? Try this. For the name enter anything you want. For the comment, try entering this:

blah', getdate(), ''); truncate table comments; --

It's a bit of a strange command perhaps, but everything appears to work correctly. Right? Go back to SQL Server Management Studio Express and try doing a "select * from comments". Assuming nothing went wrong, you may notice that all your rows of data are gone. What happened? Congratulations, you have just used SQL Injection on your server to delete all the data in your table. SQL injection basically takes a poorly written web page and exploits it to execute arbitrary SQL code on the server. In the case above, I took the comments part of the query, finished up the insert command so it executes properly, put in a semicolon (;) to run that query, then put in another query to truncate (empty) the comments table. I execute that command, then put in a sql comment to keep the query from erroring out. It's not important that you understand the mechanics of SQL injection so much as the effect.

This may seem somewhat contrived. After all, I had to know what the query looks like in order to craft this injection. However, that's not exactly as difficult as you might think. In fact, as you code web pages and put in debugging code, you may very well give an attacker all the ammunition they need to do something just like I did. You may, for instance, have problems on a page with the SQL query, so you code in a try/catch block that automatically outputs the SQL statement to the screen if it doesn't work right. This is why you should always remove your publically accessible debug code before deploying a public web application.

More important than how it works is how to fix it. In this case it's really not that hard... we just need to check for single quotes and escape them. Escaping is something we do to let SQL server know that the character following the escape should be ignored as a control character. Basically, we're going to tell it to ignore any single quotes within our data. Allow me to demonstrate.


Escaping Single Quotes
I've appended .Replace("'","''") to the end of line 107. Replace is a method that replaces the first character with the second. So basically I'm taking the result of Request.Form.Get(input), looking for any occurence of a single quote (') and replacing that with two single quotes (''). Thus if I have a name like John O'Neal, it will come back as John O''Neal. The two single quotes tells SQL server to ignore one single quote and insert it as text. I stuck it into my getVariable method but you could just as easily have stuck it anywhere else up to when you build the select statement. Just keep in mind that the replace method will throw an exception if you try to use it on a string that's null.

The lesson here is to never trust what a user inputs. Any time there's any input that comes from the user, including things like cookies and sessions (we'll hit those later), don't trust them. Make sure to verify all input at all times. Otherwise you're leaving yourself open for attack. Please keep in mind that different databases have different methods for escaping quotes. In mySQL, for instance, you use a slash instead of a quote. For some more info on SQL injection, check out securiteam.com or Wikipedia.

There's one other thing we need to fix. On line 50 we have our connection string variable. Having it in code like this isn't very secure. The server name, username and password are all contained within this string. If a user were able to see this information they would have complete control of our database server and, consequently, our website. The trick is to take this connection string and put it in a more secure place. That place is the web.config file. Go ahead and open this file. It's available on the right side of Visual Studio Express, in the Solution Explorer window.


The Web.config File
You'll notice a section in the web.config that is specifically meant for connection strings. Yours may look like <connectionStrings />. This just means that it has no entries. Having a tag that looks like that, ending in />, opens and closes the tag in one place. It's like having <connectionStrings> </connectionStrings>. Feel free to build an entry similar to mine above. Then back in our code, you can call it like this.


Using a Connection String
Basically you use System.Configuration.ConfigurationManager.ConnectionStrings["guestbookDB"].ConnectionString to access the connection string, with "guestbookDB" being whatever the name is. You can have as many different connection strings as you may need.

Now that we're recording comments and names, the only thing we need to do is display existing comments. That's next.