Preventing SQL Injection in ASP.NET

I, and many other contributors to the forums at www.asp.net find examples of code posted on a daily basis that are vulnerable to SQL Injection attacks. We continue to exhort beginners (and some more experienced programmers) to code against them. This article examines just how serious a flaw vulnerable coding can be, and what you should do about it.
What is SQL Injection?

To answer this, let's look at a typical piece of code used in millions of web sites which attempts to validate a user who has tried to log in to a protected area of a web site:

  
protected void Button1_Click(object sender, EventArgs e)
{
  string connect = "MyConnString";
  string query = "Select Count(*) From Users Where Username = '" + UserName.Text + "' And Password = '" + Password.Text + "'";
  int result = 0;
  using (var conn = new SqlConnection(connect))
  {
    using (var cmd = new SqlCommand(query, conn))
    {
      conn.Open();
      result = (int)cmd.ExecuteScalar();
    }
  }
  if (result > 0)
  {
    Response.Redirect("LoggedIn.aspx");
  }
  else
  {
    Literal1.Text = "Invalid credentials";
}
  

This is a commonly found piece of code that runs as the result of a ButtonClick event. It connects to the database and executes some SQL against a SQL Server database that returns the number of rows where the username and password combination supplied by the user matches a row in the database. If the result is at least one matching row, the user is logged in. At runtime, the values entered by the user are merged dynamically with the SQL string, to create a valid SQL command which is then executed against the database:

The values supplied above were "Admin" for the user name, and "Let_Me_In" for the password. The image illustrates that the merging of those values with the core SQL has worked rather nicely. You will only get logged in if there is a matching row in the database. Simples. Now look at this:

This was achieved simply by entering ' or '1' = '1 into both the username textbox and the password textbox. If you study the SQL that has resulted from concatenating those user values with the core SQL, you will probably be able to see that it will always match at least one row. In fact, it will match all rows, so the variable result will be > 0. Sometimes, coders don't return a count. They return user's details so they can use them for allowing further permissions or similar. This SQL will return the first row that matches, which will be the first row in the table generally. Often, this is the admin account that you set up when developing the site, and has all privileges.

This is SQL Injection. Basically, additional SQL syntax has been injected into the statement to change its behaviour. The single quotes are string delimiters as far as T-SQL is concerned, and if you simply allow users to enter these without managing them, you are asking for potential trouble. Quite often, I see well meaning people advise beginners to "escape" the quotes, using a string.Replace() method such as this:

var username = UserName.Text.Replace("'", "''");
var password = Password.Text.Replace("'", "''");
string query = "Select * From Users Where Username = '" + username + "' And Password = '" + password + "'";


And indeed that will have the desired effect:

The first OR clause will never be true. Job done. However, it does not protect against all avenues of attack. Consider the very common scenario where you are querying the database for an article, product or similar by ID. Typically, the ID is stored as a number - most of them are autogenerated by the database. The code will usually look like this:

string connect = "MyConnString";
string query = "Select * From Products Where ProductID = " + Request["ID"];

using (var conn = new SqlConnection(connect))
{
  using (var cmd = new SqlCommand(query, conn))
  {
    conn.Open();
    //Process results
  }
}

Now, in this case, the value for Request["ID"] could come from a posted form, or a querystring value - perhaps from a hyperlink on a previous page. It's easy for a malicious user to amend a querystring value. In the example caught by the VS debugger below, I just put ;Drop Table Admin-- on the end of the querystring before requesting the page:

The result, again is a legitimate SQL statement that will be run against the database. And the result will be that my Admin table will be deleted. You might be wondering how a hacker will know the names of your tables. Chances are they don't. But think about how you name your database objects. They are bound to be commonsense names that reflect their purpose. It doesn't take long to guess. And of course, if you are using ASP.NET Membership out-of-the-box, the aspnetdb.mdf schema is available to anyone. But before you start changing all your database table names to something really obscure, that is not the answer. Dictionary attacks (where random strings are generated) are common. Finally, it takes just one disgruntled person (a former colleague?) who knows the obscure names you have used to undo all your effort.

So far, the examples have shown attacks that will only really cause an inconvenience. Someone might break into your Content Management System and start defacing your site, or they might make parts of the database disappear. No big deal - a database restore from a backup will put things right (you DO backup your database, don't you?). Or they might help themselves to some free shipments, or discover secret information that your client would prefer their competitors didn't know. Or it could be a lot worse. Have a look at this document, which describes a certain type of penetration test.

Penetration tests are tests designed to identify security loopholes in applications, systems and networks. This particular test makes use of the SQL Server xp_cmdshell system stored procedure. xp_cmdshell is extraordinarily powerful, and assuming that the user has the right privileges, effectively gives them total control over the machine that the SQL Server is on, as well as potentially others in the network. Now, imagine being responsible for creating the loophole through which someone was able to create an FTP site on your web server's network, and use it to store material they would not like the police to know about. Or wiped the entire server. Some people think they don't need to worry about this type of thing because their application is intended only to run on a private Intranet. I have however seen servers hosting this type of applciation being affected.

The Prevention

OK. I'm sure that by now, you can see that the only sensible thing to do is to prevent any possibility of your application being subject to successful SQL Injection attacks. So now we will turn to preventing them. I have seen plenty of advice like this: http://forums.asp.net/t/1254125.aspx, where the suggestion is to create a Black List if all T-SQL keywords and punctuation, and screen user input for the presence of it. If it exists, throw an error, or similar. It's not a good approach in my view. There are two potential problems with this that I can see. The first is that there may be legitimate reasons why users need to post values included in the blacklist. How many users will become frustrated in their attempt to post a comment that includes "At the end of the day..."? Most T-SQL keywords are also in use in every day language. What if they aren't alowed to submit an @ symbol? Or a semi-colon? The second problem is what happens if Microsoft makes changes to the syntax of T-SQL? Do you go round all the applications you have built over the years and rebuild your black list functions? You might think that the chances of Microsoft making changes are so slim that you shouldn't worry about this. However, until the middle of last year, neither "var" nor "=>" would have done anything in C# except generate a compiler error. You certainly won't get any guarantees from Microsoft against making changes to T-SQL. The real way to prevent SQL Injection attacks is the use of Parameter Queries.

Parameter Queries

Parameters in queries are placeholders for values that are supplied to a SQL query at runtime, in very much the same way as parameters act as placeholders for values supplied to a C# method at runtime. And, just as C# parameters ensure type safety, SQL parameters do a similar thing. If you attempt to pass in a value that cannot be implicitly converted to a numeric where the database field expects one, exceptions are thrown. In a previous example where the ProductID value was tampered with to append a SQL command to DROP a table, this will now cause an error rather than get executed because the semicolon and text cannot be converted to a number.

The SqlCommand class represents a SQL query or stored procedure to be executed against the database. It has a Parameters property which is a collection of SqlParameter objects. For each parameter that appears in the SQL statement, you need to add a Parameter object to the collection. This is probably simpler to explain through code, so taking the ProductID example as a starting point, here's how to rewrite the code:

protected void Page_Load(object sender, EventArgs e)
{
  var connect = ConfigurationManager.ConnectionStrings["NorthWind"].ToString();
  var query = "Select * From Products Where ProductID = @ProductID";
  using (var conn = new SqlConnection(connect))
  {
    using (var cmd = new SqlCommand(query, conn))
    {
      cmd.Parameters.Add("@ProductID", SqlDbType.Int);
      cmd.Parameters["@ProductID"].Value = Convert.ToInt32(Request["ProductID"]);
      conn.Open();
      //Process results
    }
  }
}


The connection string has been defined in the web.config file, and is obtained using the System.Configuration.ConfigurationManager class which provides acces to items in the web.config file. In this case, it retrieves the value of the item in the connectionstrings area with the name "NorthWind". The SQL query is declared with a parameter: @ProductID. All parameters are prefixed with the @ sign. The connection object is declared next, with the connection string passed into the constructor. It's in a using block, which ensures that the connection is closed and disposed of without have to explicitly type code to manage that. The same is true of the SqlCommand object.

Adding the parameter to the SqlCommand.Parameters collection is relatively straightforward. there are two methods - the Add() method and the AddWithValue() method. The first of these has a number of overloads. I've used the Add(String, SqlDbType) option and then applied the value separately. It could be written all on one line like this:


cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = Convert.ToInt32(Request["ProductID"]);

Alternatively, I could use the AddWithValue(string, object) option like this:

protected void Page_Load(object sender, EventArgs e)
{
  var connect = ConfigurationManager.ConnectionStrings["NorthWind"].ToString();
  var query = "Select * From Products Where ProductID = @ProductID";
  using (var conn = new SqlConnection(connect))
  {
    using (var cmd = new SqlCommand(query, conn))
    {
      cmd.Parameters.AddWithValue("@ProductID", Convert.ToInt32(Request["ProductID"]);

      conn.Open();
      //Process results
    }
  }
}


The choice is up to you, but most professionals prefer to use one of the Add() methods where the SQL data type (and length, where appropriate) is specified. This reduces the chance of sub-optimal conversion causing performance issues on the server. It also ensures that the value being passed is of the right type in the application, rather than getting SQL Server to have to deal with it and report back errors. Having said all that, most samples on this site use the AddWithValue() option for readability.

When ADO.NET parameterised queries are sent to SQL Server, they are executed via the system stored procedure sp_executesql:

exec sp_executesql N'Select * From Products Where ProductID = @ProductID',N'@ProductID int',@ProductID=13

This passes in the SQL statement, followed (in this case) by the data type, and finally with the value that the parameter in the SQL statement must use. If the value is a string, any SQL syntax it might contain is treated as part of the literal string, and not as part of the SQL statement, and this is how SQL injection is prevented.

If a string is provided where a numeric is expected, the application will throw an error. For this reason, you should be validating all input for type and range before even attempting to pass it to a parameter.

There is one other benefit to be had by using parameters, and that is one of performance. When SQL Server is presented with a SQL statement, it first checks its cache for an identical statement. If it finds one, it retrieves an optimised execution plan which will ensure that the statement is executed as efficiently as possible. If it cannot find an exact match, it goes through the process of creating a plan to cache prior to using that plan to execute the statement. You can see that the first part of the sp_executesql call contains the statement, and that it will always be the same. All subsequent uses of it will use the cached optimised plan. If this statement was dynamically generated using string concatenation, and the ProductID varied each time, an execution plan would need to be created and stored for every value of ProductID. "...WHERE ProductID = 13" is not the same as "...WHERE ProductID = 96".

Stored Procedures

It always interests me that whenever the subject of preventing SQL injection comes up in the www.asp.net forums, at least one person contributes the assertion that you must use stored procedures to make use of parameters. As I have demonstrated above, this is not true. However, if you do use stored procedures the code above can be used with just two amendments: you need to pass the name of the stored procedure instead of the SQL statement, and you must set the CommandType to CommandType.StoredProcedure. It's omitted at the moment because the default is CommandType.Text. Here's the revised code for a stored procedure which I shall call GetProductByID:

var connect = ConfigurationManager.ConnectionStrings["NorthWind"].ToString();
var query = "GetProductByID";

using (var conn = new SqlConnection(connect))
{
  using (var cmd = new SqlCommand(query, conn))
  {
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = Convert.ToInt32(Request["ProductID"]);
    conn.Open();
    //Process results
  }
}


LINQ to SQL, Entity Framework, OleDb and ODBC

Both LINQ to SQL and the Entity Framework generate parameterised SQL commands out-of-the-box, providing protection against SQL Injection with no additional effort. This is indeed true of many other Object Relational Mappers (nHibernate etc). If you are using MS Access, SQL injection is not such a problem, as Access is very limited in what it allows. For example, you cannot batch statements so the DROP Table example will not work. Nevertheless, the login hack will work, so you should still use parameters. A much fuller description of how OleDb parameters work with Access is given here: http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access. It should be noted that both OleDb an ODBC parameters work based on position, whereas the examples in this article that use SqlClient all work on matching the name of the parameters, and position is not important.

How other database systems are affected varies, but it is always best to check their documentation. Nevertheless, using parameterised queries where they are supported by the database system is a sure-fire way to make your application SQL Injection proof. You really have no excuse.

Date Posted: Wednesday, August 19, 2009 7:28 AM
Last Updated: Friday, October 10, 2014 9:12 PM
Posted by: Mikesdotnetting
Total Views to date: 119804

26 Comments

Wednesday, August 19, 2009 9:54 PM - bin

Great art, but again ;) (I read arts for this subject quite often) I didn't know what about String parameter? Your example is on Int type.
Can you explain if String type parameter are also safe and what happens in "background"?

Regards

Thursday, August 20, 2009 8:11 PM - Mike

@bin

Yes, string parameters are completely safe. By the time that the value of a string parameter reaches the sp_executesql call, any single quotes are escaped, so they are treated as strIng literals.

Friday, August 21, 2009 3:51 PM - Artem Gassan

use NHibernate

Friday, August 21, 2009 4:59 PM - Mike

@Artem

Talk about a sledgehammer to crack a nut....

Monday, August 24, 2009 4:39 AM - kbdotnet

Nice article..Please include a comprehensive bookmark at the end of post...

Monday, August 24, 2009 6:23 AM - Vinz

Great Post Mike!

Monday, August 24, 2009 2:59 PM - Brandon M. Hunter

Good Article.

Monday, August 24, 2009 10:07 PM - Rob

Good Article, I prefer StoredProcedures to prevent Injections:
Read it on http://xxxxxxxxxxxxxxx/preventing-sql-injection-in-aspnet

Monday, August 24, 2009 10:30 PM - Mike

@Rob

I'd much rather people didn't frame my site within theirs. It's a breach of my copyright.

Tuesday, August 25, 2009 2:10 AM - kumar

yes I agree with mike for using Stored Procedure for preventing SQL Injection

Tuesday, August 25, 2009 6:06 AM - eric

so cool~
thanks for share

Tuesday, August 25, 2009 8:02 AM - Donn Edwards

Well written article! I have found that Convert.ToInt32 crashes the site when a non-numeric value is passed, or if the number is too big.

I have written some simple functions that strip out only the valid characters I'm expecting, and then pass the result to the page code. Here is the VB.NET version of one of them:

Public Function strLong(ByVal pstrText As String) As String
'// Extract just the 0-9 chars from a string
Dim strTemp As String
Dim lngI As Long
strTemp = ""
For lngI = 1 To Len(pstrText)
If InStr(1, "1234567890", Mid$(pstrText, lngI, 1)) > 0 Then
strTemp = strTemp & Mid$(pstrText, lngI, 1)
End If
Next lngI
strLong = Left(strTemp, 20)
End Function ' strLong

This method means that a hacker could send any garbage without causing an error, because I'm only accepting what I want, and ignoring the rest. It's the firewall principle.
I have similar stripping functions for text, passwords, etc.

It's easy to do this by declaring a class and putting all these functions in one place.

Tuesday, August 25, 2009 1:47 PM - Charlie

Excellent! Finding cool code to copy is always nice, but this article was highly educational and that's far more valuable.

Tuesday, August 25, 2009 2:37 PM - Daniel

Excellent, easy to understand article. Thanks, Mike!

Tuesday, August 25, 2009 6:50 PM - Mike

@Donn

Your general point about validating user input for datatype and range is a good one. I was going to cover that in this article too, but decided to stick with just covering SQL injection.

However, there are built-in functions you can use such as Int.TryParse(), or in VB, you can test the value using IsNumeric. You can use Regular Expressions to ensure that no values outside of the range that you expect are present. If a user is attempting to input unacceptable values, I wouldn't go through them and pick out just those characters that fit what I want. I'd reject the lot.

Thursday, August 27, 2009 1:49 AM - lab

What about if the query string is encrypted and decrypted? if this prevent the SQL Injection?

Thursday, August 27, 2009 8:40 AM - Mike

@lab

Yes and No. You are still left with other potential avenues of attack such as forms and cookies. Not only that, but there will be cases where encrypting querystrings is counterproductive. For example, my "querystrings" form part of the URL, which I want to make SEO-friendly. Encryption would mess that up, so it is not a legitimate defense against SQL injection.

Saturday, August 29, 2009 6:00 PM - rtpHarry

Ah looks like I have a new link to point everyone to on the forums ;)

See you have a new design going on in the site too...! The favicon is all that remains hehe

Btw your Rob Connery [MSFT] link needs updating on the blog roll as he stopped being [MSFT] yesterday!

Saturday, August 29, 2009 10:20 PM - Sayhawatpu

Your example is vulnerable because you are not sanitizing the queryString.

The built-in ASP.Net login control is not susceptible to SQL Injection attacks.

Saturday, August 29, 2009 11:53 PM - Mike

@Sayhawatpu

I don't know which sample you are referring to. If you mean that I haven't checked the querystring value for range or datatype, that's true. But that will not make it susceptible to SQL injection. It will just blow up in an error as I have said previously.

As far as your assertion that the Login control being SQL injection proof is concerned, sorry - but you are wrong.

Sunday, October 11, 2009 5:55 PM - Supernova

Wow, nice article. Also as you said above:

"It always interests me that whenever the subject of preventing SQL injection comes up in the www.asp.net forums, at least one person contributes the assertion that you must use stored procedures to make use of parameters."

I always assumed that I had to use stored procedures to do parameters. Well anyway needless to say you have saved me ALOT of var checking in the future, so THANKS.

Consider your site bookmarked and checked from now on.

Monday, February 8, 2010 10:42 AM - Andrew

Just wondering how to prevent SQL injection when using full text search and if it is necessary?

Is it possible to use parameters when your select statement involves freetexttable?

My select c# code is:

string searchwords = Server.UrlDecode(Request.QueryString["search"].ToString());

SqlDataSource1.SelectCommand =
"SELECT articles.id, articles.title, articles.description, results.rank "
+ "FROM articles INNER JOIN FREETEXTTABLE (articles, *, '(\"" + searchwords + "\")') AS results "
+ "ON articles.id = results.[key] ORDER BY Rank DESC";

This works as I need it to but i stumbled across your article and then got concerned that i was opening myself up for SQl injection.


Can you advise?

Monday, February 8, 2010 8:24 PM - Mike

@Andrew

I am not familiar with Full Text Searching, but a quick Google suggests that you need to use a white list approach to prevent Sql Injection with it, and to escape single quotes. You might want to post a question to stackoverflow or a SQL Server forum.

Friday, March 23, 2012 12:04 AM - Mike Wassmer

My question may only be tangentially related to this forum topic, but you explain tricky concepts to beginners better than anyone else on the Web, so my fingers are crossed that you'll help me out!

I'll give you a simplified version of my scenario. I have a SQL Server CE table (called TempData) with four columns: 1) RecordID; 2) TimeStamp; 3) IndoorTemp; and 4) OutdoorTemp. I'm building an ASP.NET Web Pages app (using your book for guidance -- thanks!) that allows the user to view a table of either indoor temperatures or outdoor temperatures over a particular date range. A pair of radio buttons enables them to select which temperature they want; a pair of date pickers to specify the date range they want.

I'd like to use the following parameterized query: SELECT @tempType FROM TempData WHERE TimeStamp >= @startDate AND TimeStamp <= @endDate

My questions are threefold:
1) Should this work?
2) Is this risky in terms of SQL injection?
3) I can't find examples of column/field parameters in any books or help sites, so that tells me I am approaching this entirely wrong. Can you suggest a better way for me to do what I'm trying to do?

Thanks!

Wednesday, August 8, 2012 8:23 AM - Ramaniranjan Das

IF I write
Select Count(*) From product Where pname = '" + txtname.Text + "'
Select Count(*) From product Where pname = "@name"
but client pass value from front end . So I want to known when pass only textbox id in command then Sql injection attacks successfully when i pass parameter wise then why not Sql Injection not attacks

Friday, August 10, 2012 7:20 AM - Mike

@Ramaniranjan Das,

As I said in the article, parameter values are treated as literal strings values, not as part of the SQL that gets executed. The are passed separately from the SQL.
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.