Tip/Trick: Guard Against SQL Injection Attacks

SQL Injection attacks are really nasty security vulnerabilities, and something all web developers (regardless of platform, technology or data layer) need to make sure they understand and protect themselves against.  Unfortunately developers too often neglect putting focused time on this - and leave their applications (and worse their customers) extremely vulnerable.

Michael Sutton recently published a very sobering post about just how widespread this issue is on the public web.  He built a C# client application that uses the Google Search API to look for sites vulnerable to SQL Injection Attacks.  The steps to achieve this were simple:

  1. Look for sites that have querystring values (example: search for URLs with "id=" in the URL)
  2. Send a request to the sites identified as dynamic with an altered id= statement that adds an extra quote to attempt to cancel the SQL statement (example: id=6')
  3. Parse the response sent back to look for words like "SQL" and "query" - which typically indicate that the app is often sending back detailed error messages (also bad)
  4. Review whether the error message indicates that the parameter sent to SQL wasn't encoded correctly (in which case the site is open to SQL Injection Attacks)

Of a random sampling of 1000 sites he found via his Google search, he detected possible SQL Injection Attack vulnerability with 11.3% of them.  That is really, really scary.  It means hackers can remotely exploit the data in those applications, retrieve any unhashed/encrypted passwords or credit-card data, and potentially even log themselves in as administrators to the application.  This is bad not only for the developer who built the application, but even worse for any consumer/user of the application who has provided data to the site thinking it will be secure.

So what the heck is a SQL Injection Attack?

There are a couple of scenarios that make SQL Injection attacks possible.  The most common cause are cases where you are dynamically constructing SQL statements without using correctly encoded parameters.  For example, consider this SQL query code that searches for Authors based on a social security number provided via a querystring:

Dim SSN as String
Dim SqlQuery as String

SSN = Request.QueryString("SSN")
SqlQuery = "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + SSN + "'"

If you have SQL code like the snippet above, then your entire database and application can be hacked remotely.  How?  Well in the normal scenario users will hit the site using a social security number which will be executed like so:

' URL to the page containing the above code
http://mysite.com/listauthordetails.aspx?SSN=172-32-9999

' SQL Query executed against the database 
SELECT au_lname, au_fname FROM authors WHERE au_id = '172-32-9999'

This does what the developer expected, and searches the database for author information filtered by the social security number.  But because the parameter value hasn't been SQL encoded, a hacker could just as easily modify the querystring value to embed additional SQL statements after the value to execute.  For example:

' URL to the page containing the above code
http://mysite.com/listauthordetails.aspx?SSN=172-32-9999';DROP DATABASE pubs --

' SQL Query executed against the database 
SELECT au_lname, au_fname FROM authors WHERE au_id = '';DROP DATABASE pubs --

Notice how I was able to add the ';DROP DATABASE pubs -- clause to the SSN querystring value and use it to terminate the current SQL statement (via the ";" character), and then add my own malicious SQL statement to the string, and then comment out the rest of the statement (via the "--" characters).  Because we are just manually concatenating the SQL statement in our code, we will end up passing this to the database - which will execute first the query against the authors table, and then delete our pubs database table.  Bang - it is now gone.

In case you think the idea of anonymous hackers deleting your database tables is bad, that is unfortunately actually one of the better scenarios when a SQL Injection Attack is involved.  Rather than just destroy data, a hacker could instead use the above code vulnerability to perform a JOIN that retrieves all of the data within your database and displays it on the page (allowing them to retrieve username/passwords/credit-cards).  They could also add UPDATE/INSERT statements to modify product prices, add new admin users, and really screw up your life (imagine auditing your inventory at the end of the month, only to discover that the actual number of products in your warehouse is different then what your accounting system reports...). 

How do you protect yourself?

SQL Injection Attacks are something you need to worry about regardless of the web programming technology you are using (all web frameworks need to worry about it).  A couple of very basic rules you must always follow:

1) Don't construct dynamic SQL Statements without using a type-safe parameter encoding mechanism.  Most data APIs (including ADO + ADO.NET) have support for allowing you to specify the exact type of a parameter being provided (for example: string, integer, date) and can ensure that they are escaped/encoded for you to avoid hackers trying to exploit it.  Always use these features. 

For example, with dynamic SQL using ADO.NET you could re-write the code above like below to make it safe:

Dim SSN as String = Request.QueryString("SSN")

Dim cmd As new SqlCommand("SELECT au_lname, au_fname FROM authors WHERE au_id = @au_id")
Dim param = new SqlParameter("au_id", SqlDbType.VarChar)
param.Value = SSN
cmd.Parameters.Add(param)

This will prevent someone from trying to sneak in additional SQL expressions (since ADO.NET above knows to string encode the au_id value), and avoid other data problems (incorrectly type-casting values, etc).  Note that the TableAdapter/DataSet designer built-into VS 2005 uses this mechanism automatically, as do the ASP.NET 2.0 data source controls. 

One common misperception is that if you are using SPROCs or a ORM you are completely safe from SQL Injection Attacks.  This isn't true - you still need to make sure you are careful when you pass values to a SPROC, and/or when you escape or customize a query with an ORM that you do it in a safe way.

2) Always conduct a security review of your application before ever put it in production, and establish a formal security process to review all code anytime you make updates.  This later point is super important.  Too often I hear of teams that conduct a really detailed security review before going live, then have some "really minor" update they make to the site weeks/months later where they skip doing a security review ("it is just a tiny update - we'll code review it later").  Always do a security review.

3) Never store sensitive data in clear-text within a database.  My personal opinion is that passwords should always be one-way hashed (I don't even like to store them encrypted).  The ASP.NET 2.0 Membership API does this for you automatically by default (and also implements secure SALT randomization behavior).  If you decide to build your own membership database store, I'd recommend checking out the source code for our own Membership provider implementation that we published here.  Also make sure to encrypt credit-card and other private data in your database.  This way even if your database was compromised, at least your customer private data can't be exploited.

4) Ensure you write automation unit tests that specifically verify your data access layer and application against SQL Injection attacks.  This is really important to help catch the "it is just a tiny update so I'll be safe" scenario, and provide an additional safety layer to avoid accidentally introducing a bad security bug into your application.

5) Lock down your database to only grant the web application accessing it the minimal set of permissions that it needs to function.  If the web application doesn't need access to certain tables, then make sure it doesn't have permissions to them.  If it is only read-only generating reports from your account payables table then make sure you disable insert/update/delete access. 

How to Learn More

The Microsoft Prescriptive Architecture Guidance (PAG) team has posted a number of really good security guideline documents that you should set aside some time to read:

And these additional PAG How-To articles are useful for learning more about how to protect yourself against injection attacks:

You can also find useful ASP.NET security information from this security blog post of mine, and from my ASP.NET Tips/Tricks page here.

Updated: Bertrand pointed me at a great post he did two years ago on SQL Injection Attacks that is great to read.

Hope this helps,

Scott

42 Comments

  • Everybody talks about SQL injection. Few tell us how to fix it . Great tip!!!

  • The Replace() function in my comment did not come out clearly. The second argument is a string of one single-quote character ('), and the second argument is a string of two single-quote characters. So the Replace() function changes every instance of a single-quote into two single-quotes. By doubling every single-quote character, each single-quote character is treated as an embedded single-quote character, rather than an end to the string.

  • Hi Speednet,

    I'd recommend always using the parameter encoding mechanism I described above. The approach you are using above won't work for non-string types. For example, if someone was doing a select based on a number:

    SELECT * FROM Products WHERE Category=" & Request.Querystring("categoryID")

    You'd be in trouble - since someone could pass in:

    ?categoryid=9;drop database pubs --

    Using the ADO.NET parameter encoding mechanism will always keep you safe.

    Hope this helps,

    Scott

  • as Scott already mentioned, double quoting isnt the right aproach
    i described this in a earlier post

    http://weblogs.asp.net/hpreishuber/archive/2004/04/30/123901.aspx

    the worst case is that the developer have the connection string in the code and not switched off the raw error message.

  • I totally agree Scott, my technique is a stop-gap measure, and it's most useful for very large websites where you need to get something in there quickly. Recoding with parameters not only takes time, but when you start going through old code and refactoring, you always want to take a little extra time to make it "perfect" while you're in there, so it can take a long time.

    For numerics I agree that's an issue, but for me personally it is not, because I always test QueryString and Form values with ISNUMERIC() before proceeding. If they don't pass that test I bomb out with a graceful error message and the query never gets executed. Then, after testing with ISNUMERIC(), I assign it to a variable with a conversion function, like: lngCategoryID = CLng(Request.QueryString("categoryID")). That gets rid of any junk that may appear after the starting number.

    Regardless, I am in complete agreement with you, and I would also encourage anyone to go with a paramterized approach, and better yet, recoding using stored procedures. My approach is good if you know there's a problem and want to put in an instant fix while you're refactoring. I wish I knew how to insert line breaks into a comment; it would make this more readable.

  • By the way, one more thing you can do to avoid harmful injection is to create a SELECT-only SQL login, and use it for all read-only queries. It can further limited to just the tables and columns you want to access.

    It is very easy to create and use a single God-like login for everything, but it only takes a little extra time to plan more carefully and create task-oriented logins.

  • A lot of people argue about dynamic queries vs. parameterized SQL. In reality, there's no reason you can't use dynamic SQL and still use parameters.

    You just have to add the parameters dynamicly as you build the SQL string.

  • I think using Stored Procedures instead dynamic Sql queries and Adding parameters to sp using ADO.NET could be a good solution to sql injection.

  • Hello Scott,

    I believe that a good choice in ASP.NET is Context.Items for parameters transfer between pages. It's more light that Session and more secure that QueryString.

    There are scenes that QueryStrings are important, how when you allow the users to copy the URL and reference it in another place. But many people (developers) forget to validate these parameters, so "SQL Injection" is allowed and application is vulnerable.

  • We did develop simple function that helps us a lot in dynamic sql builds.
    It's called FixParam.

    It gets one parameter and returns string.
    If it's string I do replace "'" with "''".
    If it's number I do simply convert it to string.
    If it's date I return Date.ToString("yyyyMMdd")
    and so and so.

    Developer only need to remember to use it.

  • Good trick. I larned about SQl Injecting about a year ago. And from that Time onward have always talk about sql injection because that is one way to stop. SQL Injection. Also the fact that URL Rewriting is very help ful. With URL Rewriting u never get URLs with ID or so on.

  • Quick comment: one thing I ran into with parameterized queries in my youth with them was the in-list problem. I wanted to do something like:

    sql.command_text = "select * from table where id in :list"
    sql.add_parameter("list", [3, 4, 5])
    sql.execute()

    That didn't work too well.

    However, you can usually use ? to denote anonymous parameters:

    sql.command_text = "select * from table where id in (?,?,?)"
    sql.add_parameter(3)
    sql.add_parameter(4)
    sql.add_parameter(5)
    sql.execute()

    And you can build the list of question marks dynamically based on the size of your array.

    It's not completely pretty, but it works, and it keeps things parameterized, and it doesn't rely on stored procedures.

  • I have a friend who does this for a hobby. If you saw the list of major sites he's managed to break.. mein gott.

    He only uses SQL injection and he pokes around until he's able to retrieve the login cookies for all the users from the database. He then just takes the cookie for the highest-priveleged account he can find, sets it in his browser and just like magic, he's now logged in as a superuser.

    Scary stuff.

    If I could recommend one thing, I would make sure your login cookies for superusers have the IP encrypted into them, so they are not useable from any other IP. Maybe ScottGu can show how to do this with ASP.NET Membership Providers?

  • You're an old fox Scott...

  • First off, I'm glad that this is getting some airing - too many people don't realise how easy this is.
    Secondly, I don't believe that you need to anything type-safe to prevent this type of attack.  In my app (C#, .NET 2) I *always* use SqlParameters passed into a SqlCommand, but I don't necessarily ensure they are the correct type.  For example:
    SqlCommand cmd = new SqlCommand("", conn);
    cmd.CommandText = "SELECT * FROM Users WHERE UID = @id";
    cmd.Parameters.AddWithValue("@id", Request["id"]);
    The UID column in the database is numeric, but the SqlParameter doesn't care that I've passed in a string object (which normally contains a number).  If a malicious user enters a string on the URL, it just generates a database type conversion error, and does not allow any sort of SQL injection attack.
    I know it's being a little bit more lazy than specifying a parameter with the correct type, checking the value of the ID on the request line and then putting it into the parameter, but apart from the error messages it generates being slightly less clear, I can't see any disadvantages.
    Richard

  • Speednet: another comment about non-parameterized solutions... they're far harder to audit. We audit our code for a number of problems regularly, but its only economic to do if the flaws are rare & easy to find: our tools can ignore constant prepared statements, string appends need checked manually.

  • It seems the best way to prevent against SQL Code injection is to use typed parameters. But what about scenarios where you need a query builder that has to be flexible enough that you can't use parameters. What's the guideline there?
    Thanks.

  • I've always used SQL Parameters for input which seems to resolve most issues however one thing I'm not sure on is the best way to pass in multiple parameters.

    The typical scenario is a .Net Checked List Box where the user may select 1 or more options and this these input needs to be passed to a Store Proc for use in a SQL IN clause ie

    Select some, fields,
    from someTable
    Where field1 in (23, 24, 25, 26, 27)

    How do you guys normally get around this without using string concatenation?

  • Let's say you have a bunch of values already stored in a list of strings.



    SqlCommand cmd = new SqlCommand(conn);
    StringBuilder cmdText = new StringBuilder();
    cmdText.Append("SELECT * FROM foo WHERE bar IN (");
    for (int i = 0; i < inValues.Count; i++)
    {
    string pname = "@p" + i;
    cmd.Parameters.AddWithValue(pname, inValues[i];
    if (i != 1) cmdText.Append(", ");
    cmdText.Append(pname);
    }

  • Using parameterized queries in stored procedures does not always work, either. Sometimes stored procedures use user input data to concatenate an inline sql statement. While that's not usually a good idea in a stored procedure, it is sometimes necessary for very complicated searches where there are many conditions in the search criteria and T-SQL is very difficult to perfect... such instances should be rare but they do happen.

    Thanks,
    Shawn

  • Is it possible to do Sql Injection in ConnectionStrings? Short of character-by-character parsing through each parameter read from a config file, how can we protect against this kind of attack?

  • Hi Rob,

    I don't think you can do any SQL Injection's via connectionstrings (since they don't contain any SQL queries). You do want to be careful about not varying the connectionstring based on user-input (since there are probably other bad things you can do), but you shouldn't need to worry about SQL injection attacks.

    Hope this helps,

    Scott

  • Thanks Scott -Thats why your paid the big bucks.

  • Wow! Who knew? Thanks for illuminating another aspect in database security shortcomings.

  • Something missing from this post was data partitioning, and for Shawn's comment (in particular), data partitioning is an answer.

    Data should be divided into partitions. Stored Procedures are used to access Data; data is never accessed directly from outside the database process. The database connects with a user that has access only through predefined stored procedures.

    When you need Dynamic SQL, what you do is create a new partition. You grant that partition the minimum access (usually read) it needs to the data, usually via a view that provides the minimum column set the procedure needs.

    Then you expose the stored procedure that contains the dynamic SQL.

    This provides isolation -- no matter what flaw your dynamic SQL might contain, no matter what SQL the attacker might be able to inject, they cannot get out of that partition. Nothing they do will get them access to anything other than what is in the partition the DSQL has access to.

    If you really need to be secure, you split the DSQL in half. One half builds a temporary table, view or cursor and provides that information to the other half, which then runs the dynamic SQL against it.

    In general, the dynamic SQL approach is used for adhoc reporting. Usually, you can construct a temporary table containing only the data the procedure needs in the procedure's partition, and then run the DSQL against that temporary table.

  • Refering to the replace instead of paameterized.

    I use a number of functions, one for each data type, string/number/boolean, that handles the input.

    astr() For strings I just replace ' with double '

    nstr() For numbers I test if it is a number and if not return NULL as string.

    This may cause an error when executing the SQL but eliminates any injection.

    bstr() For boolean I use a simple if param return 1 else 0

    And then I use these functions at EVERY concatenation, not before.

    Maybe I could spare some performance by pre parsing parameters but if someone else cuts and pastes they might loose the protection.

  • I see these problems happening quite a bit as I bounce around companies with different contracts. If you have SQL in your code behind, you have alsorts of problems; Security, Extendability, code reuse, readability of code. With the slew of usefull Code gen tools available today, you should be able to program in a far more effective way and, more than likey, drive your development time down!

  • 1. The app&#39;s database login should only be allowed to execute the stored procedures.
    2. Use stored procedures.
    2a. If you must do dynamic SQL in stored procedures, use the sp_executesql system stored proc, which allows you to pass parameters - it&#39;s the T-SQL equivalent of parameterized queries in .NET. Also be sure to use the new EXECUTE AS option when defining stored procedures that do dynamic SQL.
    By following these rules in SQL Server 2005, you pretty much rule out any SQL Injection attack since you never concatenate a statement using user-passed values. Break any part of the rules and you could be exposing yourself to attack.
    --Peter DeBetta
    --Microsoft MVP - SQL Server

  • Excellent post. I hadn't really given this much thought before stumbling on this post.

  • i have a session["user"] which has values like(userid field of table coil1 values 1,2,3...).i have to count and split
    the values and pass it to select sql query

    ex:select * from coil1 where userid in (1,2,3...)

  • i have doubt in how to do coding :splitin values from session["user"] and pass it to select query.................can u correct my codin

    string s =(string) Session["user"];
    char[] ser = new char[] {' ',','};
    //session has values accordin to checkbx value(1,2,3..
    foreach (string ss in s.Split(ser))
    {
    Response.Write(ss);



    try
    {
    SqlConnection conn = new SqlConnection("Data source=(local); initial catalog=dhatchu; integrated security=SSPI");
    SqlCommand cmd1 = new SqlCommand();
    conn.Open();
    cmd1.Connection = conn;
    cmd1.CommandType = CommandType.Text;
    cmd1.CommandText = "select * from fancoil1 where userid in '" + ss + "'";
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd1;
    DataSet ds1 = new DataSet();
    ds1.Clear();
    da.Fill(ds1, "fancoil1");
    cmd1.ExecuteNonQuery();
    conn.Close();
    gview2.DataSource = ds1;
    gview2.DataMember = "fancoil1";
    gview2.DataBind();

  • Hi Senthil.

    You want to be very, very careful with the code above. You have a potential SQL Injection attack with how you are doing it today - because of this code:

    cmd1.CommandText = "select * from fancoil1 where userid in '" + ss + "'";

    You should instead make sure that you use the strongly-typed parameter approach I described above to encode the ss parameter as a string.

    Hope this helps,

    Scott

  • Is injection a threat for request.form data?
    I've avoided all query stringsd
    thanks

  • Hi Chuck,

    Yes - you still need to worry about SQL Injection attacks with Request.Form values as well. Basically, anytime you take input from users in anyway you need to protect yourself.

    Hope this helps,

    Scott

  • I can see where this technique will be useful in making a pure parametrized SQL statement, it is not necassarily the most efficient SQL statement.

    try the following SQL statement

    DECLARE @p1 int, @p2 int

    select customerid from customers where customerid in (@p1, @p2)

    If you look at the execution plan, its way overfilled with scalar scans and grows exponentially if you keep on adding parameters.

    On the other hand, the execution plan for following query is perfect:

    select customerid from customers where customerid in (1, 2)

    The dynamic parametrised query is a good option but it does not work in all scenarios and does not always give the most optimised query.

    One can always argue that you can use temp tables or table variable or udfs to solve the above issue but then again it won't be as good as static values in the query.

    So, the question is still open... what is the best way to parametrise the IN clause?

  • prevent SQL Injection Attacks,you can change the Web-UI sytle,sometimes can build it with *.ascx or more methods,to avoid dispose the url params to user,this may be uncomfortable for us

  • Another approach I have used in the past has been validating the variable being utilised in the SQL statement.
    It would be interesting to hear your feedback on this.
    Quite often if I know the form/type of the expected variable then I can use Regular Expressions to validate the SQL variable BEFORE the SQL statement is executed.

    e.g. all variables passed through querystring or Form are checked using a regular expression function.
    Therefore all non-expected characters would produce a match and hence allow you to trigger an event for logging event/error generating / input warning etc.

    i.e. As long as you do not expect characters like ; or * or % etc in your SQL statement then these can all be filtered out BEFORE the SQL is executed.

  • Hi Fregus,

    I'd be careful about doing your own validation, and instead recommend using the validation support that you get for free from ADO.NET.

    The challange with doing your own validation is that you can pretty easily miss something - in which case an automatic hacker tool will get by pretty easily.

    Hope this helps,

    Scott

  • Dumb question, Does the membership.vaidateUser method use typed parameters and stored procedures? How do you see the code for the validateUser Method?

  • Hi Neal,

    Yep - the Membership API (and all of the other ASP.NET application services) use typed params to ensure that SQL Injection Attacks can't happen.

    Thanks,

    Scott

  • Scott,

    When I read about SQL Injection Attacks, there's often a recommendation to use SQL Authentication to restrict DB access. On the other hand, there are lots of postings from MS that say "Use Integrated Security wherever possible". So on the surface it seems these are opposing recommendations.

    I'm having difficulty getting SQL Authentication to work; newbie problem, hopefully will get resolved soon, and not the issue here. However, it's causing me to shy away from SQL Authentication, which I'm only motivated to use (at this point) to mitigate consequence of potential SQL injection attacks (which I can see that my app isn't going to be particularly vulnerable to...no dynamic sql for starters...can easily block html injections...)

    So my questions are
    1) What's the balance between these two recommendations?
    2) Can I get my asp.net app's "Integrated Security = True" connection string to have restricted access to the DB tables?
    3) If 2) is "yes" what are some pointers to how to make that work?

    Thanks!

  • Hi Jim,

    In general I recommend reducing the permissions of database account that the web-server uses as much as possible. This means removing support for dropping tables, creating new tables, and disabling access to all stored procedures that aren't required for the web to work. That helps lower your attack surface.

    In terms of using windows auth vs. sql authentication, I don't think either one necessarily improves your security risk with regard to SQL Injection Attacks.

    Windows auth is sometimes preferrable if possible for security reasons, since it means you don't have a password lying around anywhere on disk (instead it will use Windows' process account password store).

    Here are two articles to check out about how to connect to SQL:

    Connect using Windows Auth: http://msdn.microsoft.com/en-us/library/ms998292.aspx

    Connect using SQL Auth: http://msdn.microsoft.com/en-us/library/ms998300.aspx

    Hope this helps,

    Scott

Comments have been disabled for this content.