Tuesday, 6 November 2012

OWASP Top 10: #1 - Injection. How to prevent attack.

This is one in a series of videos and blog posts that explore the top 10 most critical web application security risks as defined by OWASP.
In my previous session, I explained OWASP’s #1 from their top 10  web application security vulnerabilities. Injection.
We saw how easy it is for an attacker to exploit a vulnerable web application.

In this session, I’m going to show you several steps that can be employed to harden your application and mitigate against the threat of injection attack.
You can read the transcript below.
HTML5 player



Transcript



The first action we can take is to apply the “Principle of least privilege”.
Every module in a system must only be able to access the information and resources that are necessary for its legitimate purpose.
As shown in the previous session, the root causes of SQL injection risk are unvalidated input data and a lack of parameterisation.
But the risk was exacerbated because the SQL account the web application used had excessive permissions.
So that’s the first thing I am going to address.
Let me open SQL Server Management Studio 2012 then browse to the database used by the application, 1-Injection.
If I drill down into Security then Users, I can find the user account used by the user account. It too is called 1-Injection.
If I right-click the user account and select Properties then click on Membership I’ll be able to see what roles this user account is a member of. In this case, the user is a member of both db_datareader and db_datawriter. In other words, this user account is able to read from and write to every single table and view in the database.
Now our application is only required to read the Products table. So I’ll uncheck both db_datareader and db_datawriter. And now instead I’ll select Securables and locate the table called Products by clicking on the Search button then choosing All objects of the types.
Once selected, I’ll give the user account the one permission that it requires. And that is Select on the Products table.
So let’s see what effect that has on the application.
If I load up the application in Internet Explorer 10, and then navigate to the Beverages list in the first session, if I attempt to attack this application by injecting some SQL such as:
http://injection/Products.aspx?CategoryID=1 OR 1=(SELECT COUNT(*) FROM Customers)

I should immediately get an error. The reason being that Select permission was denied on the Customers table.
Despite any actions that we subsequently take to prevent SQL injection we can be sure that the web application is only able to access those tables that it is supposed to.
If you recall from the first session, the attacker was able to retrieve customer data with very little difficulty.
A common flaw with web applications is that the developer often takes the contents of the query string and uses it to construct an SQL statement. It is this vulnerability that the attacker is trying to exploit.
A simple step the developer can take to mitigate against this risk is to use inline SQL parameters.
So let me launch Visual Studio 2012 and load up the web application.
The vulnerable page in this application is the Products page, so I’ll load up Products.aspx.cs.
Before we proceed, I need to add a using directive for System.Data.
The next thing I need to do is update the select statement that I am generating behind the scenes.
As you can see, at the moment, I am simply taking the query string parameter and appending it to an SQL statement without first validating it. A better alternative is to update that statement so as to include the parameter. I’ve called the parameter @CategoryID.

Now as my query is being executed by a SQLCommand, I need to notify the command that there is a parameter.
So I put in an additional line of code to add in the @CategoryID parameter to the SQLCommand object and set its value to be the parameter categoryID.
What we are doing here is called parameterisation.
Parameterisation is the practice of separating the query itself from the query parameters. When we do this, it ensures that untrusted data remains data and cannot break out into the query context.
We can parameterise SQL using a number of different approaches. At the moment we are using in-line SQL parameters.
Let me build the project and return to the browser.
I reload the web application and follow the beverages link to navigate to the products page.
If I try to attack this application by updating the query string to CategoryID=1 OR 1=1, unlike in the previous session where no error was returned, this time an error IS returned.
The error occurred as SQL Server will not allow data to enter into the query context and instead will treat the entire string  as a CategoryID and then fail when comparing it to the integer column type .
Another approach when mitigating from the threat of injection is Whitelisting. Implementing a whitelist of acceptable values is a critical mitigation for many security risks.
Whitelisting is the process of saying “This is what I trust”. Blacklisting, on the other hand, says “This is what I don’t trust”.
Blacklisting tends to protect against the risks we know today, but not tomorrow.
White listing is always preferred as its explicit; it says “This is what we know to be safe” rather than “This is what we presently know to be dangerous”.
So let’s implement whitelisting for untrusted data.
Back in Visual Studio 2012, I need to add another using directive to the Products page code behind. This time for System.Text.RegularExpressions.
On the line following that which retrieves the categoryID from the query string, I declare a new variable and assign it an instance of a RegEx regular expression. @"^0*[1-9][0-9]*$". This one designed to identify positive integers.
Below that I can add a test to call the IsMatch method of my regular expression variable passing it the categoryID. And if the categoryID retrieved from the query string does not match the regular expression then we can throw an ApplicationException.
So I’ll build the application and return to Internet Explorer 10. I’ll then click on the Beverages link once again to navigate to the Products page.
This time, if I attack the site by attempting to add in 1=1 to the query string, I’ll see my ApplicationException thrown because the categoryID does not match the regular expression. i.e. it is not a positive integer.
So that’s an example of whitelisting in action. By using a regular expression in this case we have been able to specify exactly what is valid for categoryID.
Let’s try another form of parameterisation. Instead of the inline SQL parameter that we used earlier, this time we’ll use a stored procedure.
So I’ll return to SQL Server Management Studio 2012 and create a new query.
The first thing I need to do is set the database context to be 1-Injection which is the database that the web application uses. And then I can create a stored procedure called GetProducts which can be called by an application rather than it having to create and execute a SELECT statement.
I’ll define a parameter called @CategoryID of type INT and use that parameter in a SELECT statement that is defined within the stored procedure.
One last step that I have to carry out in this script is to grant the Execute permission on the GetProducts stored procedure to the user account named 1-Injection.
In fact before I run this script, I need to add a Go statement between each of those steps.
And finally, I can execute the query to create the stored procedure and grant the execute permission.
Time to return to Visual Studio 2012.
Back in the Products page code behind, I’ll add a statement to parse the parameter passed in from the query string into an integer. We have already established that it is a positive integer thanks to the regular expression.
Now I have written a stored procedure, I no longer require all this code to define an SQL query and execute it via a SQLCommand. So I’ll delete it.
In its place, I’ll put in a new connection and then another SQLCommand object but this time initialised with the name GetProducts.
I’ll set the CommandType to StoredProcedure and remember to add in the parameter @CategoryID.
Once the connection is open, I can execute the command, take the result and place it in the data source of the DataGridView.
All I need to do now is build the project, return to Internet Explorer 10 and load the web application once again.
If I follow the Beverages link, the Products page loads up with a query string containing a CategoryID =1 and displays the results.
Thanks to the combination of actions that I have taken, I can be sure that the risk of SQL injection has been greatly diminished.
Staying with the theme of parameterisation, I could take a third approach. Instead of inline SQL parameters, or stored procedures, I could instead use an Object Relational Mapper – ORM such as Entity Framework or nHibernate.
ORMS generally abstract away the parameterisation.
They represent classes that are added to your program which amongst other things generate safe SQL strings without the need for you to explicitly construct your own.
Let me get back into Visual Studio 2012.
I’ll right-click on the project and select Add New Item.
As I’m choosing to use Entity Framework, I’ll select ADO.NET Entity Data Model.
I’ll name my model, InjectionModel. When the Entity Data Model Wizard appears, I select Generate from database and click Next.
The wizard realises that the application already has a connection string and gives me the option to use that.
For the purposes of the demonstration, I’ll allow sensitive data to be maintained in the connection string. I’ll save the connection string with the name InjectionEntities and once again, click Next.
The wizard then presents me with a list of tables and views that I am able to build my ORM classes from. As the user account only has permission to select from the Products table, that is the only table that I am offered.
I finally provide a meaningful namespace for my classes, in this case InjectionModel and click Finish.
Once Visual Studio 2012 has finished building the ORM classes, a diagram is displayed. In my case, because I was only offered the Products table, only the Product class is shown. In addition to the Product class, Visual Studio 2012 has also created a class named InjectionEntities which represents the database.
Back in the Products page code behind, I can once again remove any unwanted code. This time, I can remove the code that retrieves the connection string from the web.config file and all of the code that I added previously to use the SQLCommand.
In their place, I’m going to declare a new instance of type InjectionEntities, my entity model which is analogous to the database. I then declare a variable called products to which I assign the result of a LINQ statement querying the Products property of the InjectionEntities object. In other words, I am querying the Products table. Albeit indirectly.
I pass into the Where method a lambda expression, selecting the CategoryID that matches the parameter that I retrieved from the query string. As before, the results of the query are simply fed into the DataSource property of the DataGridView control.
So let me build the project one more time, and return back to Internet Explorer 10.
I follow the Beverages link and once again, I am viewing products with a CategoryID of 1. And because Entity Framework uses parameterisation I can be sure that I have mitigated against the threat of SQL injection.
Now in this session we have looked at three ways of implementing parameterisation. Only one would be required. Arguably, it is much easier to implement an ORM such as Entity Framework as it does much of the heavy lifting for you. However, many feel that ORMs suffer from poor performance and query optimisation.
We have looked at a number of approaches in this session. From whitelisting to SQL parameterisation and applying the principle of least privilege. No single approach will mitigate against the threat of injection on its own. Rather it’s a combination of these things. i.e. a layered approach.
In this session we have seen how simple it is to mitigate against the threat of Injection. The #1 in OWASPs top 10 web application security vulnerabilities.

Flash player

Training?

If you are interested in OWASP training, we offer the following courses:


See you soon

Phil Stirpé
"I don't do average!"





OWASP Top 10: #1 - Injection. The risk explained.

This is one in a series of videos and blog posts that explore the top 10 most critical web application security risks as defined by OWASP.
OWASP - the Open Web Application Security Project, was formed in 2001 and is a not for profit charitable organisation.
It is an open community dedicated to enabling organisations to conceive, develop, acquire, operate and maintain applications that can be trusted.
In this session, I will deal with the OWASP #1 risk. Injection.
You can read the transcript below.
HTML5 player


Transcript


I will define Injection and provide several examples of injection at work. In a follow on session, I will demonstrate various techniques that can be employed to mitigate against the risk of injection.
So let’s begin with a definition:

“Injection flaws, such as SQL, OS, and LDAP injection, occur when untrusted data is sent to an interpreter as part of a command or query. The attacker’s hostile data can trick the interpreter into executing unintended commands or accessing unauthorised data.” -OWASP Top 10, 2010

A key message to take from this definition is that although many people assume that Injection refers to SQL Injection, it needn’t be so. Although for the purposes of this session I will focus on SQL Injection.

OWASP’s top 10 doesn’t restrict itself to just .NET technologies. Therefore QA do have two other courses:

and


For the purpose of these videos and blog posts however, I will focus on .NET technologies.

The best way to demonstrate this risk of injection is by way of an example.
So let me start up Internet Explorer 10 and then load up a vulnerable website at http://injection.
This website is hosted under a local instance of Internet Information Services - IIS and uses a SQL Server database at the back end to store the data.
If navigate to the Beverages list by clicking on the link in the top right hand corner of the page, I will see the URL reflect a query string of CategoryID=1 and 10 beverages listed on the page.
It is the fact that a CategoryID of 1 has been appended to the query string that dictates that it is just the beverages that are displayed.
Now if we wanted to exploit a potentially vulnerable application we might experiment with the query string. So for example if I update the query string to add  or 1=1 so that the URL now appears as http://injection/Products.aspx?CategoryID=1 or 1=1 and then press Enter to load the page again, a much larger set of results should appear.
This indicates the likely presence of a SQL injection risk.
So thus encouraged, let’s try something else.
I’ll change the query string to read CategoryID=1 or x=1.
Now this is syntactically invalid SQL and the fact that if I reload the page we have an error page returned saying “Invalid column name ‘x’”, would suggest that this web application takes the values in the query strings and uses them directly in its use of SQL.
Let’s see what else we can figure out as a potential hacker.
This time I’ll update the query string to read http://injection/Products.aspx?CategoryID=1 OR 1=(SELECT COUNT(*) FROM Customer) then press Enter to load the page again.
Once again I get an error but from an attacker’s point of view this is really useful because this once again proves that the application is taking the value entered at the query string and using it to query the database.
In this case, the database has come back and said there is no such object as Customer. So now the attacker could update the query string and rewrite Customer as Customers to see where that gets them.
And this time it’s paid off because as you can see there was no error generated and so the attacker now knows that a table called Customers exists.
You can imagine that over time an attacker could build up a picture of all the table names in the system.
OK so let’s try to see if as an attacker, we are able to chain queries. I’ll update the query string to append a second query following a semi-colon as follows: http://injection/Products.aspx?CategoryID=1; SELECT * FROM Customers then press Enter to load the page again.
The fact that the page loads without error confirms that subsequent queries can be chained.
Now that we have established that the web application literally takes the value in the query string and uses it to build queries to send to the database, the attacker could update the query string one more time and add in two hyphens at the end. This will serve to comment out any remaining SQL that the application might append to the statement.
Not only will an attacker try to figure out the names of tables and we know that they can already do that. We have proved it.
They’ll probably also want to confirm the existence of columns.
So I’ll update the query string again so that this time I am selecting from Customers where the Company = 1. I am guessing at the name Company. As follows:
http://injection/Products.aspx?CategoryID=1; SELECT * FROM Customers WHERE Company = 1--
If I reload the page, I will receive a helpful error message informing me that there isn’t a column called Company.
Now as an aside, if the ASPNET web developer had actually used the customErrors flag in their web.config file, then the attacker wouldn’t be receiving such useful information.
So the attacker now knows that there isn’t a column called Company in the Customers table.
Well they have all the time in the world. They can just update the URL and try different column names.
So let’s try CompanyName.
Well, there is no error received. So that has confirmed to the attacker that there is indeed a column called CompanyName in the Customers table.
Now that they know this, the attacker could write a query to trick the database into revealing data in the CompanyName column.
Let me add in this query to the address bar:
http://injection/Products.aspx?CategoryID =convert(int,(char(82)%2bchar(33)%2b(select top 1 cast(isnull([CompanyName],char(32)) as nvarchar(4000)) from (select top 1 [CompanyName] from [Customers] order by [CompanyName] asc) sq order by [CompanyName] desc)%2bchar(33)%2bchar(82))) and 1=1-- 
And then press Enter to load the page again.
This query attempts to retrieve the CompanyName column from the Customers table and convert it into an integer so that it can be compared with the CategoryID.
Now this query will fail because SQL Server wouldn’t be able to cast the CompanyName string into an integer for the CategoryID.
But that was the point because what the attacker was trying to do was generate an error message that would reveal some information.
Look at this error. Not only does SQL Server tell us that it cannot convert an nvarchar into an int, it also revealed the data. In this case, a CompanyName of “Alfreds Futterkiste”.
Thus encouraged, the attacker could continue to update the query placing different values in the second select top statement to change select top 1 [CompanyName] from [Customers]… to select top 2 [CompanyName] from [Customers].
And so on. Generating error message after error message revealing company name after company name.

As you can see from these simple examples, a vulnerable application can be easily exploited by an attacker using SQL Injection.
In my next video and blog post, I will show you how to quite easily mitigate against this risk and thus protect yourself from the #1 in OWASP’s top ten. Injection.


Flash player




Training?

If you are interested in OWASP training, we offer the following courses:

Developing Secure Java Web Applications – Mitigating the OWASP Top 10 Security Vulnerabilities

Developing Secure .NET Web Applications – Mitigating the OWASP Top 10 Security Vulnerabilities


 



See you soon

Phil Stirpé
"I don't do average!"