CSharp Parameterized SQL
One year ago
If you've been programming in ASP.NET C# for a while now and working with databases like MS SQL Server, you'll no doubt have come across the problem seen in the following insert statement:
Insert Into Students (Name) Values ('Robert Du'Lac')
The first solution which comes to mind and which is alarmingly found all over the interweb, is to find and replace all single apostrophes with a double apostrophe. For example:
var InsertStatement = "Insert Into Students
(Name) Values ('" + "Robert Du'Lac".Replace("'","''") + "')";
This is bad! You may be solving the problem, but you'll still be vulnerable to SQL Injection Attacks. The below comic by Randall Munroe is one such Exploit which you're simple sanitization method would not have caught and also highlights why you should correctly sanitize your database inputs.
The problem is that you are trying to sanitize the inputs yourself which is noble, but wrong. What you should be doing is getting the database to do the sanitation work for you. Amazingly the .Net framework allows you to do this which makes constructing safe SQL queries in easy:
var InsertStatement = "Insert Into Students (Name) Values (@Name)";
var InsertCommand = new SqlCommand(InsertStatement);
InsertCommand.Parameters.Add("Name", "Robert Du'Lac");
InsertCommand.Connection = DatabaseConnection;
var InsertDataReader = InsertCommand.ExecuteReader();
MS SQL Server allows you define parameters and assign those parameters a value. In the above I have added the @Name parameter to the insert statement, then using the SqlCommand object I have informed the database what the value is for the @Name parameter. This simple example has shown how you can effectively sanitize your database inputs with a little extra code.
Jeff Atwood also point out that using parameterized SQL also has the following performance (as well as security) benefits:
- Fewer string concatenations
- No need to worry about any kind of manual string escaping
- A more generic query form is presented to db, so it's likely already hashed and stored as a pre-compiled execution plan
- Smaller strings are sent across the wire
So in summary, if your not using Parameterized SQL, you should be. I hope the above is enough to get you going, however; here are some extra sources of information you may find useful.
- How To Protect From SQL Injection in ASP.NET
- SQL Injection is suprisingly easy
- SQL Injection Explained