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
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.