Microsoft Enterprise Library: Caching Application Block

This is a a second article on the topic of the Microsoft Enterprise Library. If you have not read the previous article titled Microsoft Enterprise Library: Data Access Application Block, I recommend you do so.

Introduction to the Caching Application Block

Amazon ImageThe Enterprise Library Caching Application Block lets developers incorporate a local cache in their applications. It supports both an in-memory cache and, optionally, a backing store that can either be the database store or isolated storage. The Caching Application Block can be used without modification; it provides all the functionality needed to retrieve, add, and remove cached data. Configurable expiration and scavenging policies are also part of the block.

If you have been working with caching outside the Enterprise Library, I believe you will find this application block extremely powerful and easy to use. If you have not taken on the subject of caching before, I believe you also will find this easy to pick up and ultimately boost the performance of your applications. The Enterprise Library Caching Application Block includes the following features:

  • You can use the graphical Enterprise Library configuration tools to manage configuration settings.
  • You can configure a persistent storage location, using either isolated storage or the Enterprise Library Data Access Application Block, whose state is synchronized with the in-memory cache.
  • Administrators can manage the configuration using Group Policy tools.
  • You can extend the block by creating custom expiration policies and storage locations.
  • You are assured that the block performs in a thread-safe manner.

Cache Manager

The cache manager serves the role of managing the cache store exactly as it sounds. It is entirely possible to have multiple cache managers to suit you business requirements. When configuring the cache manager you also have choices when it comes to the backing stores. For example, if you wish to use a database to persist the cache you can do so, but for the purpose of this article I will demonstrate the out of the box configuration which is the server’s memory.

Much like the potential vulnerabilities surrounding the Data Access Block in terms of SQL Injection, the Caching Application Block also has risk that you must understand. For example, do not store sensitive data in the cache and if you must do so, then use encryption.

Once you have the defined your cache manager settings,  you will see similar code withing your web.config:

<configSections>
	<section name="cachingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Caching.
		Configuration.CacheManagerSettings, Microsoft.Practices.EnterpriseLibrary.Caching,
		Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
		requirePermission="true" />
</configSections>
<cachingConfiguration defaultCacheManager="RadDev Cache Manager">

Design and Implementation

Turning our attention to the web project, the first step is to add the appropriate references. Add a reference to the Caching Application Block assembly. In Microsoft Visual Studio, right-click your project node in Solution Explorer, and then click Add Reference. Click the Browse tab and find the location of the Microsoft.Practices.EnterpriseLibrary.Caching.dll assembly. Select the assembly, and then click OK to add the reference.

Now that the references are in place, we need the using statements.

//Enterprise Library
using Microsoft.Practices.EnterpriseLibrary.Caching;
using Microsoft.Practices.EnterpriseLibrary.Caching.Expirations;

Now the fun begins. We will query the database and after this first round trip we will cache the data. By caching the data the next call to the database essentially does not occur. Back in your webform add a GridView.

<asp:GridView ID="GridViewContactsNoCache" runat="server">
</asp:GridView>

Now that we have the GridView established it is time to wire up the necessary code to communicate with the database and then display this data to the end user.

First step here is to create your reference to the cache manager.

ICacheManager cache = EnterpriseLibraryContainer.Current.GetInstance<ICacheManager>();

Depending upon your application’s design you can add a object to cache in the scenario that works best for you. Here I am using a Generic List to create an object type of Contact. I will not go into the details of the List nor the Contact Class, but I will share the code so you can follow the thought process.

SQL Statement

private const string sqlOne = "SELECT FirstName," +
	" MiddleName, LastName FROM Person.Contact WHERE  (LastName =" +
	" N'adams') AND (FirstName LIKE N'r%') ORDER BY LastName";

Page Load Event

Inside the page load you will notice an if/else statement. Here is where we check if the Contacts object is in cache and if so, we bypass the database round trip and bind the GridView to this cache object.

protected void Page_Load(object sender, EventArgs e)
{
	if (cache.Contains("ContactsCachKey"))
	{
		GridViewContactsNoCache.DataSource = cache.GetData("ContactsCachKey");
	}
	else
	{
		GridViewContactsNoCache.DataSource = GetContacts();
	}
	GridViewContactsNoCache.DataBind();

	// How many object are in cache?
	LabelCacheObjects.Text = string.Format("The Cache Manager contains {0} objects.",
	cache.Count.ToString());
}

Get the Contacts

In this method you will see the use of the Generic List and immediately before exiting the method I am making another method call to populate the Contacts to cache.

private List<Contact> GetContacts()
{
	using (IDataReader rdr = db.ExecuteReader(CommandType.Text, sqlOne))
	{
		lstContact = new List<Contact>();

		while (rdr.Read())
		{
			contact = new Contact();
			contact.FirstName = rdr.GetString(0);
			contact.MiddleName = rdr.GetString(1);
			contact.LastName = rdr.GetString(2);
			lstContact.Add(contact);
		}
	}

	PopulateCacheManager(lstContact);

	return lstContact;
}

private void PopulateCacheManager(List<Contact> lstContact)
{
	cache.Add("ContactsCachKey", lstContact);
}

The Result

Contacts GridView

What If The Data Changes

Of course when one displays data, typically the counterpart is to also update the data. You will be pleased to know that in one line of code you can destroy the Contacts cached object and upon the next execution of displaying the data, the process starts all over. Assume for a moment that that Update button click has processed a database transaction.

protected void ButtonUpdate_Click(object sender, EventArgs e)
{
	cache.Remove("ContactsCachKey"); //here we remove the Contacts cache object
}

Conclusion

As you can see, using the Caching Application Block component of the Enterprise Library is not complex. Of course, this article has just begun to scratch the surface in the hopes that your interest is perked.

Examples Via MSDN

The following examples are from the Enterprise Library 5.0, Chapter 5 – A Cache Advance for Your Applications.

Proactive Cache Loading

The example, Load the cache proactively on application startup, provides a simple demonstration of proactive cache loading. In the startup code of your application you add code to load the cache with the items your application will require. The example creates a list of Product items, and then iterates through the list calling the Add method of the cache manager for each one. You would, of course, fetch the items to cache from the location (such as a database) appropriate for your own application. It may be that the items are available as a list, or—for example—by iterating through the rows in a DataSet or a DataReader.

// Create a list of products - may come from a database or other repository
List<Product> products = new List<Product>();
products.Add(new Product(42, "Exciting Thing",
                         "Something that will change your view of life."));
products.Add(new Product(79, "Useful Thing",
                         "Something that is useful for everything."));
products.Add(new Product(412, "Fun Thing",
                         "Something that will keep the grandchildren quiet."));

// Iterate the list loading each one into the cache
for (int i = 0; i < products.Count; i++)
{
  theCache.Add(DemoCacheKeys[i], products[i]);
}

Reactive Cache Loading

Reactive cache loading simply means that you check if an item is in the cache when you actually need it, and—if not—fetch it and then cache it for future use. You may decide at this point to fetch several items if the one you want is not in the cache. For example, you may decide to load the complete product list the first time that a price lookup determines that the products are not in the cache.

The example, Load the cache reactively on demand, demonstrates the general pattern for reactive cache loading. After displaying the contents of the cache (to show that it is, in fact, empty) the code attempts to retrieve a cached instance of the Product class. Notice that this is a two-step process in that you must check that the returned value is not null. As we explained in the section “What’s In My Cache?” earlier in this chapter, the Contains method may return true if the item has recently expired or been removed.

If the item is in the cache, the code displays the values of its properties. If it is not in the cache, the code executes a routine to load the cache with all of the products. This routine is the same as you saw in the previous example of loading the cache proactively.

Console.WriteLine("Getting an item from the cache...");
Product theItem = (Product)defaultCache.GetData(DemoCacheKeys[1]);

// You could test for the item in the cache using CacheManager.Contains(key)
// method, but you still must check if the retrieved item is null even
// if the Contains method indicates that the item is in the cache:
if (null != theItem)
{
  Console.WriteLine("Cached item values are: ID = {0}, Name = '{1}', "
                    + "Description = {2}", theItem.ID, theItem.Name,
                    theItem.Description);
}
else
{
  Console.WriteLine("The item could not be obtained from the cache.");

  // Item not found, so reactively load the cache
  LoadCacheWithProductList(defaultCache);
  Console.WriteLine("Loaded the cache with the list of products.");
  ShowCacheContents(defaultCache);
}

Microsoft Enterprise Library: Data Access Application Block

For those of you who have been using the Enterprise Library from Microsoft then I tip my hat to you. I admit that I have not used this library for a number of years and in most cases the reason is because I have honestly not been in a position to do so. It is a long story so don’t ask. There are a number of reason why you should seriously consider the use of the Enterprise Library and I cannot think of any better reason than those provided directly from Microsoft.

The goals of Enterprise Library are the following:

  • Consistency. All Enterprise Library application blocks feature consistent design patterns and implementation approaches.
  • Extensibility. All application blocks include defined extensibility points that allow developers to customize the behavior of the application blocks by adding their own code.
  • Ease of use. Enterprise Library offers numerous usability improvements, including a graphical configuration tool, a simpler installation procedure, and clearer and more complete documentation and samples.
  • Integration. Enterprise Library application blocks are designed to work well together or individually.

Now that the groundwork has been laid let us get started.

Introduction to the Data Access Library

Amazon ImageThe Data Access Application Block includes a small number of methods that simplify the most common techniques for accessing a database. Each method encapsulates the logic required to retrieve the data and manage the connection to the database. The methods exposed by the block allow you to execute queries, return data in a range of different formats, populate a DataSet, update the database from a DataSet, perform data access asynchronously (against SQL Server databases), and return data as objects in a suitable format for use with client-side query technologies such as LINQ.

As with anything good there is always its counterpart. For example, if you are going to work with data in a a mechanism that is unique to your application or if your are going to work with a particular database that provides unique features then you may not want to use the Data Access Library. It is also important to understand the level of support. For example, if you are targeting an Oracle database you should not use this library since Microsoft has depreciated the Oracle Client. In this case you should turn to Oracle’s ODP.NET which is native to an Oracle Database.

The Database Connection

The beauty of the Data Access Library is the simplicity of its use. For example, to establish a connection to a database, SQL Server in this case all it takes is a single line of code. First things first is to add the appropriate references to the Enterprise Library Assemblies.

//Enterprise Library
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;

Then comes the database object creation.

Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>(“MyDatabase”);

The string value MyDatabase represents the connection to a SQL Server. Yes, it actually is that simple. In fact the instance of database could in fact be defined in a single class so your development team would never have to waste any time in defining a database object again. It is also worth saying that this database object is not yet in an open state. This is good news because so many developers often forget to close out their database connections. Another important benefit is the Data Access Library also will log events to the application event log, but this is a subject that I will go into in a future article.

Inline SQL Statement Example

I want to first say that you should exercise extreme caution when using inline SQL because this type of database interaction is vulnerable to SQL Injection. For the purpose of this example, I will utilize inline SQL.

In this example we will employ two SQL statements. One will retrieve the contact details to include first, middle, and last names. The second statement will return the total number of contacts.

private const string sqlOne = "SELECT FirstName," +
" MiddleName, LastName FROM Person.Contact WHERE  (LastName = N'adams') AND" +
" (FirstName LIKE N'r%') ORDER BY LastName";

private const string sqlTwo = "SELECT COUNT(*) AS TotalContacts FROM Person.Contact WHERE (LastName = N'adams') AND" +
" (FirstName LIKE N'r%')";

Build the WebForm

Now turning our attention to the web form we will need to server controls. First is a Gridview and second is a Label.

<asp:GridView ID="ContactsGridView" runat="server"></asp:GridView>
<asp:Label ID="TotalContactsLabel" runat="server" Text=""></asp:Label>

At this point if you have been working with ADO.NET you will find the code behind the webform very familiar. The only thing we are doing different in this example is working with the database object that was covered earlier in this article.

/// <summary>
/// Gets the contacts.
/// </summary>
/// <returns>DataSet of contacts</returns>
private DataSet GetContacts()
{
	DataSet dsContacts = db.ExecuteDataSet(CommandType.Text, sqlOne);
	return dsContacts;
}

/// <summary>
/// Gets the total contacts.
/// </summary>
/// <returns>Total number of contacts</returns>
private int GetTotalContacts()
{
	//because ExecuteScalar returns an object we must cast to an int
	int totalContacts = (int)db.ExecuteScalar(CommandType.Text, sqlTwo);
	return totalContacts;
}
  1. Internal is for assembly scope (i.e. only accessible from code in the same .exe or .dll)
  2. Private is for class scope (i.e. accessible only from code in the same class)

Now that we have the necessary methods established the next step is to bind the data to the server controls back on the webform. We will do this in the page load event.

/// <summary>
/// Handles the Load event of the Page control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
protected void Page_Load(object sender, EventArgs e)
{
	ContactsGridView.DataSource = GetContacts();
	ContactsGridView.DataBind();

	TotalContactsLabel.Text = string.Format("There are a total of {0} contacts", GetTotalContacts().ToString());
}

Now upon execution of the web application you will be presented the following:

Contacts Data Displayed

Conclusion

As you can see, using the Data Access Library component of the Enterprise Library is not complex. Of course, this article has just begun to scratch the surface in the hopes that your interest is perked.

Examples Via MSDN

The following examples are from the Enterprise Library 5.0, Chapter 2 – Much ADO about Data Access.

Reading Rows Using a Query with No Parameters

Simple queries consisting of an inline SQL statement or a stored procedure, which take no parameters, can be executed using the ExecuteReader method overload that accepts a CommandType value and a SQL statement or stored procedure name as a string.

// Call the ExecuteReader method by specifying just the stored procedure name.
using (IDataReader reader = namedDB.ExecuteReader("MyStoredProcName"))
{
  // Use the values in the rows as required.
}

Reading Rows Using an Array of Parameter Values

While you may use simple no-parameter stored procedures and SQL statements in some scenarios, it’s far more common to use queries that accept input parameters that select rows or specify how the query will execute within the database server. If you use only input parameters, you can wrap the values up as an Object array and pass them to the stored procedure or SQL statement. Note that this means you must add them to the array in the same order as they are expected by the query, because you are not using names for these parameters—you are only supplying the actual values. The following code shows how you can execute a stored procedure that takes a single string parameter.

// Call the ExecuteReader method with the stored procedure
// name and an Object array containing the parameter values.
using (IDataReader reader = defaultDB.ExecuteReader("ListOrdersByState",
                                      new object[] { "Colorado" }))
{
  // Use the values in the rows as required - here we are just displaying them.
  DisplayRowValues(reader);
}

Retrieving XML Data

The Data Access block provides the ExecuteXmlReader method for querying data as XML. It takes a SQL statement that contains the FOR XML statement and executes it against the database, returning the result as an XmlReader. You can iterate through the resulting XML elements or work with them in any of the ways supported by the XML classes in the .NET Framework. However, as SQLXML is limited to SQL Server (the implementations of this type of query differ in other database systems), it is only available when you specifically use the SqlDatabase class (rather than the Database class).

The following code shows how you can obtain a SqlDatabase instance, specify a suitable SQLXML query, and execute it using the ExecuteXmlReader method.

// Resolve a SqlDatabase object from the container using the default database.
SqlDatabase sqlServerDB
    = EnterpriseLibraryContainer.Current.GetInstance<Database>() as SqlDatabase;

// Specify a SQL query that returns XML data.
string xmlQuery = "SELECT * FROM OrderList WHERE State = @state FOR XML AUTO";

// Create a suitable command type and add the required parameter
// NB: ExecuteXmlReader is only available for SQL Server databases
using (DbCommand xmlCmd = sqlServerDB.GetSqlStringCommand(xmlQuery))
{
  xmlCmd.Parameters.Add(new SqlParameter("state", "Colorado"));
  using (XmlReader reader = sqlServerDB.ExecuteXmlReader(xmlCmd))
  {
    // Iterate through the elements in the XmlReader
    while (!reader.EOF)
    {
      if (reader.IsStartElement())
      {
        Console.WriteLine(reader.ReadOuterXml());
      }
    }
  }
}

Retrieving Single Scalar Values

A common requirement when working with a database is to extract a single scalar value based on a query that selects either a single row or a single value. This is typically the case when using lookup tables or checking for the presence of a specific entity in the database. The Data Access block provides the ExecuteScalar method to handle this requirement. It executes the query you specify, and then returns the value of the first column of the first row of the result set as an Object type. This means that it provides much better performance than the ExecuteReader method, because there is no need to create a DataReader and stream the results to the client as a row set. To maximize this efficiency, you should aim to use a query that returns a single value or a single row.

The ExecuteScalar method has a set of overloads similar to the ExecuteReader method we used earlier in this chapter. You can specify a CommandType (the default is StoredProcedure) and either a SQL statement or a stored procedure name. You can also pass in an array of Object instances that represent the parameters for the query. Alternatively, you can pass to the method a Command object that contains any parameters you require.

The following code demonstrates passing a Command object to the method to execute both an inline SQL statement and a stored procedure. It obtains a suitable Command instance from the current Database instance using the GetSqlStringCommand and GetStoredProcCommand methods. You can add parameters to the command before calling the ExecuteScalar method if required. However, to demonstrate the way the method works, the code here simply extracts the complete row set. The result is a single Object that you must cast to the appropriate type before displaying or consuming it in your code.

// Create a suitable command type for a SQL statement.
// NB: For efficiency, aim to return only a single value or a single row.
using (DbCommand sqlCmd
       = defaultDB.GetSqlStringCommand("SELECT [Name] FROM States"))
{
    // Call the ExecuteScalar method of the command.
    Console.WriteLine("Result using a SQL statement: {0}",
                       defaultDB.ExecuteScalar(sqlCmd).ToString());
}

// Create a suitable command type for a stored procedure.
// NB: For efficiency, aim to return only a single value or a single row.
using (DbCommand sprocCmd = defaultDB.GetStoredProcCommand("GetStatesList"))
{
    // Call the ExecuteScalar method of the command.
    Console.WriteLine("Result using a stored procedure: {0}",
                       defaultDB.ExecuteScalar(sprocCmd).ToString());
}

Updating Data

The following code from the example application for this chapter shows how you can use the ExecuteNonQuery method to update a row in a table in the database. It updates the Description column of a single row in the Products table, checks that the update succeeded, and then updates it again to return it to the original value (so that you can run the example again). The first step is to create the command and add the required parameters, as you’ve seen in earlier examples, and then call the ExecuteNonQuery method with the command as the single parameter. Next, the code changes the value of the command parameter named description to the original value in the database, and then executes the compensating update.

string oldDescription
    = "Carries 4 bikes securely; steel construction, fits 2\" receiver hitch.";
string newDescription = "Bikes tend to fall off after a few miles.";

// Create command to execute the stored procedure and add the parameters.
DbCommand cmd = defaultDB.GetStoredProcCommand("UpdateProductsTable");
defaultDB.AddInParameter(cmd, "productID", DbType.Int32, 84);
defaultDB.AddInParameter(cmd, "description", DbType.String, newDescription);

// Execute the query and check if one row was updated.
if (defaultDB.ExecuteNonQuery(cmd) == 1)
{
  // Update succeeded.
}
else
{
    Console.WriteLine("ERROR: Could not update just one row.");
}

// Change the value of the second parameter
defaultDB.SetParameterValue(cmd, "description", oldDescription);

// Execute query and check if one row was updated
if (defaultDB.ExecuteNonQuery(cmd) == 1)
{
  // Update succeeded.
}
else
{
    Console.WriteLine("ERROR: Could not update just one row.");
}

Be sure to check out Chapter 2 – Much ADO about Data Access for much more details and examples.

Creating Charts With Microsoft Chart Controls

There is no shortage of charting controls for the Microsoft .NET framework and while many do a great job, they may be overkill and costly to any project. If you’re not familiar with the Microsoft Chart Controls then you may find that you’re in for a pleasant surprise for two reasons;

  1. They are free
  2. Quickly render charts

Quick Walkthrough

My purpose here is simple and to the point. I want to demonstrate just how easy it is to return a chart to the end user. In this case I will be using an XML data source rather than a database which is typical in most cases.

Line Chart

line chart

ASPX:

<asp:Chart ID="Line" runat="server" Width="600px" Height="400px">
<Series>
<asp:Series Name="Series1" ChartType="Line">
</asp:Series>
</Series>
<ChartAreas>
<asp:ChartArea Name="ChartArea1">
</asp:ChartArea>
</ChartAreas>
</asp:Chart>

Code Behind:

internal void BindCharts()
{
string dataPath = MapPath(".") + "\App_Data\books.xml";

DataSet ds = new DataSet();
ds.ReadXml(dataPath);

DataTable dt = ds.Tables[0];
DataView dataView = new DataView(dt);

Line.Series[0].Points.DataBindXY(dataView, "title", dataView, "price");
}

Take note that the real magic happens on “DataBindXY” which represents that data point to display and in this case is title and price. As a bonus, if I add the following to the “BindCharts” method then I also have a stacked bar and column chart.

StackedBar.Series[0].Points.DataBindXY(dataView, "title", dataView, "price");
Column.Series[0].Points.DataBindXY(dataView, "title", dataView, "price");

Stacked Bar Chart

stacked bar chart

Column Chart

column chart

Conclusion

Keep in mind that these examples are basic in nature and serve as an introduction. To really see first hand the power and options available be sure to check out the links in the references.

Security Development Lifecycle: SQL Injection Attacks

In an earlier post titled Security Development Lifecycle: Introduction I begun introducing what the Security Development Lifecycle (SDL) represents and as I continue this series I will focus on the SDL model that Microsoft has so graciously provided to the community.

Introduction

In part 2 of this series I want to focus of SQL Injection and for those of you just getting started it is important to understand what a SQL Injection attack is. Here is what Microsoft has stated:

  • SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parametrized data can be manipulated by a skilled and determined attacker.
  • The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.
  • The injection process works by prematurely terminating a text string and appending a new command. Because the inserted command may have additional strings appended to it before it is executed, the malefactor terminates the injected string with a comment mark “–”. Subsequent text is ignored at execution time.

sql injection model

The two root causes for SQL Injection attacks are the use of dynamic SQL and ineffective or even all together lacking input validation. The best advice here is:

  1. Use parameterized queries or stored procedures
  2. Validate all input. Always assume that the data coming in is corrupt and possibly malicious.

Breaking The Bank

We are all too familiar with the login process and much like your favorite social network or even your banking website the typical login process includes the collection of a username and password. Assume for a moment that your favorite website does not address SQL Injection and therefore uses dynamic SQL. Consider the following:

SELECT OrderID, CustomerID, ShipAddress, ShipCity, ShipPostalCode
FROM Orders
WHERE (CustomerID = '" + customerid + "'";)

Do you spot the problems? The issue is the customer id is passed as literal string values and they may contain malicious input which can allow the end user to perform actions that you never thought that they could. The user may be able to access data they should not, drop tables, or even worse drop the entire database for example. Let us assume for a moment that the end user enters the id of VINET’. Now paying attention to the single quote the SQL is now constructed as:

SELECT OrderID, CustomerID, ShipAddress, ShipCity, ShipPostalCode
FROM Orders
WHERE (CustomerID = 'VINET'')

When this statement executes, the SQL parser will locate the extra quote mark and throws an error. How this error is captured and the user alerted is a topic in itself but let us assume that the raw error is being returned to the user. This error is a dead giveaway that user input is not being sanitized and that the application is wide open to SQL injection attacks. With this information the user can begin to probe the database. Never provide to much details to the user via error messages.

sql exception

SQL Explained

Since we are talking about merely constructing essentially a string that will represent a query that is in turn to be executed against a given database if we construct the following you begin to understand what is occurring. So assume the user enters VINET;drop table Orders– , what do you think would happen? If you said the statement is valid and would not only execute the query but also drop the Orders table then you are correct. This is not the fault of the database nor should one think it is. In fact the SQL syntax is 100% valid.

SELECT        OrderID, CustomerID, ShipAddress, ShipCity, ShipPostalCode
FROM            Orders
WHERE CustomerID = 'VINET';DROP TABLE Orders--'

So the lesson here is simple, never trust a user’s data input to perform database actions.

Validate Input

Sanitize the user input at all times to insure that they do not inject malicious values. You may begin to consider building a blacklist to prevent unacceptable characters, such as quotes or semicolons or escapes, but I strongly urge you not to take this approach. Though it may be simple to point out some dangerous characters, it’s harder to capture all of them. Rather establish a whitelist and define what is acceptable and anything else is rejected.

Conclusion

SQL injection attacks are nothing new and not difficult at all to address. All it really takes is attention to to detail and a few extra minutes of time where in the long run your efforts will protect your database, otherwise you may find yourself in a precarious position. If you find that you have inherited a project and you run across this vulnerability then by all means bring it to the attention of the team.

References

  1. OWASP SQL Injection
  2. OWASP SQL Injection Cheat Sheet
  3. SQL Injection FireFox Plugin
  4. SQL Injection Basics Demonstration
  5. HP Scrawlr
  6. Microsoft Security Development Lifecycle (SDL)

Ten Most Critical Web Application Security Risks

I thought it was time to sit down and begin writing a series of articles on the subject of application security for a number of reasons. First, because security should and must be foremost in everyone’s mind and second I have decided to return to school to obtain a Master’s Degree in CyberSecurity. I can think of no better reasons to both educate myself and other surrounding to topic of security.

application security

Introduction

If you’re not familiar with The Open Web Application Security Project (OWASP) then I strongly urge you to take time to visit them. The amount of information you will find there can be a little overwhelming at first but if you continue to soak up the material that they provide you will soon find yourself becoming much more knowledgeable about security than you may have ever wish to have learned. A great starting point is what OWASP has coined as the Top 10. For a number of years know they research the industry and report to the general public the top ten security risk that they find. I must admit that the really frightening part is a number of vulnerabilities remain on the list year after year and it makes one stop and consider why this is. I personally have been working as a software developer since the mid 1990′s and I never really became aware of security until early 2000 and to date I am still learning and in no way consider myself an expert, for now that is. Now that we have broken the ice and introduced what is considered to Top 10 Security Risk take a moment and review them.

2010 OWASP Top 10 Application Security Risks
A1-Injection 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 unauthorized data.
A2-Cross Site Scripting (XSS) XSS flaws occur whenever an application takes untrusted data and sends it to a web browser without proper validation and escaping. XSS allows attackers to execute scripts in the victim’s browser which can hijack user sessions, deface web sites, or redirect the user to malicious sites.
A3-Broken Authentication and Session Management Application functions related to authentication and session management are often not implemented correctly, allowing attackers to compromise passwords, keys, session tokens, or exploit other implementation flaws to assume other users’ identities.
A4-Insecure Direct Object References A direct object reference occurs when a developer exposes a reference to an internal implementation object, such as a file, directory, or database key. Without an access control check or other protection, attackers can manipulate these references to access unauthorized data.
A5-Cross Site Request Forgery (CSRF) A CSRF attack forces a logged-on victim’s browser to send a forged HTTP request, including the victim’s session cookie and any other automatically included authentication information, to a vulnerable web application. This allows the attacker to force the victim’s browser to generate requests the vulnerable application thinks are legitimate requests from the victim.
A6-Security Misconfiguration Good security requires having a secure configuration defined and deployed for the application, frameworks, application server, web server, database server, and platform. All these settings should be defined, implemented, and maintained as many are not shipped with secure defaults. This includes keeping all software up to date, including all code libraries used by the application.
A7-Insecure Cryptographic Storage Many web applications do not properly protect sensitive data, such as credit cards, SSNs, and authentication credentials, with appropriate encryption or hashing. Attackers may steal or modify such weakly protected data to conduct identity theft, credit card fraud, or other crimes.
A8-Failure to Restrict URL Access Many web applications check URL access rights before rendering protected links and buttons. However, applications need to perform similar access control checks each time these pages are accessed, or attackers will be able to forge URLs to access these hidden pages anyway.
A9-Insufficient Transport Layer Protection Applications frequently fail to authenticate, encrypt, and protect the confidentiality and integrity of sensitive network traffic. When they do, they sometimes support weak algorithms, use expired or invalid certificates, or do not use them correctly.
A10-Unvalidated Redirects and Forwards Web applications frequently redirect and forward users to other pages and websites, and use untrusted data to determine the destination pages. Without proper validation, attackers can redirect victims to phishing or malware sites, or use forwards to access unauthorized pages.

Tooling

Now that we have the foundation to in place to understand these risk, the next step is understanding the options surrounding tooling to began tackling these problem areas. While the following list serves only as a starting point, the goal is to provide options relevant to these risks.

Application Security Risk Tooling
SQL Inject Me SQL Inject Me is the Exploit-Me tool used to test for SQL Injection vulnerabilities. The tool does not attempt to compromise the security of the given system, rather it looks for possible entry points for an attack against the system. There is no port scanning, packet sniffing, password hacking or firewall attacks done by the tool. You can think of the work done by the tool as the same as the QA testers for the site manually entering all of these strings into the form fields.
Microsoft Anti-Cross Site Scripting Library The Microsoft Anti-Cross Site Scripting Library V3.1 (Anti-XSS V3.1) is an encoding library designed to help developers protect their ASP.NET web-based applications from XSS attacks. It differs from most encoding libraries in that it uses the white-listing technique — sometimes referred to as the principle of inclusions — to provide protection against XSS attacks. This approach works by first defining a valid or allowable set of characters, and encodes anything outside this set (invalid characters or potential attacks). The white-listing approach provides several advantages over other encoding schemes. New features in this version of the Microsoft Anti-Cross Site Scripting Library include:- An expanded white list that supports more languages- Performance improvements- Performance data sheets (in the online help)- Support for Shift_JIS encoding for mobile browsers- A sample application- Security Runtime Engine (SRE) HTTP module – HTML Sanitization methods to strip dangerous HTML scripts.
OWASP WebScarab WebScarab is a framework for analysing applications that communicate using the HTTP and HTTPS protocols. It is written in Java, and is thus portable to many platforms. WebScarab has several modes of operation, implemented by a number of plugins. In its most common usage, WebScarab operates as an intercepting proxy, allowing the operator to review and modify requests created by the browser before they are sent to the server, and to review and modify responses returned from the server before they are received by the browser. WebScarab is able to intercept both HTTP and HTTPS communication. The operator can also review the conversations (requests and responses) that have passed through WebScarab.
Burp Burp Suite is an integrated platform for performing security testing of web applications. Its various tools work seamlessly together to support the entire testing process, from initial mapping and analysis of an application’s attack surface, through to finding and exploiting security vulnerabilities.

Cost, Who, How and What

Face it, any company is in the business of making money and understandably so. This being said, security should and must be job #1! Training is more often than not a pain point with many companies and in reality there are very few companies out there that focus on security from the perspective of training and education. It has been my experience that no one wishes to cut into the bottom line when it comes to the profits and the fact that software may or may not be developed for an enterprise or a group of five makes no difference. While I’m always surprised to hear from customers that that want a quality product, more often than not when it comes down to requirements gathering and eventually cost estimates one of the first thing that typically gets cut is security.

To further drive home the point of what occurs when security is not addressed all you need to do is read the 2010 Verizon Data Breach Investigation Report. This reporting provides very interesting insight into the types of security breaches and answers who, how and what.

Who

Who is behind data breaches?

How

How do breaches occur?

What

What commonalities exist?

Conclusion

Technology continues to advance and security must advance much more quickly if we are ever to succeed and protecting all involved. It is not enough to just sit back and wait for breaches to occur and then address them as they do. Stop for a moment to really consider the recent security incidents that have happened in 2011 alone and the scale of these breaches. My goal is to hopefully engage others in addressing security at every level of business.

Do you have any success stories or opinions that you would like to share? It is always interesting in hearing about what works for others under what conditions and why. If this is a subject that perks your interest then please leave a comment.

References

  1. OWASP Top 10 Introduction
  2. OWASP Developer’s Guide
  3. OWASP Testing Guide
  4. OWASP Code Review Guide
  5. Microsoft Security Development Lifecycle
  6. Verizon’s 2010 Data Breach Report