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.

Secure Development Series: Input Validation

Many websites today collect data from the user community which includes but not limited to an email address, address, or even a phone number. The single golden rule everyone must follow is never trust the data input. In order to mitigate the risk to ensure that the data received and processed by your application is acceptable you must first define what data your application should accept, what its syntax should be and the minimum and maximum lengths. This information will allow you to define a set of “acceptable” values for every entry data point that is captured.

Foundation of Security

Foundation of Security

  1. Authentication: Addresses the question: who are you? It is the process of uniquely identifying the clients of your applications and services.
  2. Authorization: Addresses the question: what can you do? It is the process that governs the resources and operations that the authenticated client is permitted to access.
  3. Auditing:Effective auditing and logging is the key to non-repudiation. Non-repudiation guarantees that a user cannot deny performing an operation or initiating a transaction.
  4. Confidentiality: Referred to as privacy, is the process of making sure that data remains private and confidential, and that it cannot be viewed by unauthorized users or eavesdroppers who monitor the flow of traffic across a network.
  5. Integrity: The guarantee that data is protected from accidental or deliberate (malicious) modification. Like privacy, integrity is a key concern.
  6. Availability: From a security perspective, availability means that systems remain available for legitimate users.

Two main approaches exist for input validation which are called whitelisting and blacklisting.

Whitelist

A term used to describe a list or register of entities that, for one reason or another, are being provided a particular privilege, service, mobility, access or recognition. As a verb, to whitelist can mean to authorize access or grant membership. Conversely, blacklist is a term used to describe a list or compilation that identifies entities that are denied, unrecognised, or ostracised.

Blacklist

A list or register of entities who, for one reason or another, are being denied a particular privilege, service, mobility, access or recognition. As a verb, to blacklist can mean to deny someone work in a particular field, or to ostracize a person from a certain social circle. Conversely, a whitelist is a list or compilation identifying entities that are accepted, recognized, or privileged.

Both blacklisting and whitelisting are valuable tools when it comes to data validation. Typically the best approach is to adopt whitelisting and define what type of data is acceptable where blacklisting is defining data types that are unacceptable. Think about it, if you’re collecting an email address does it not make sense to address the acceptable data entry rather than what is unacceptable?

A simple regular expression used for whitelisting an email address:

^[w-]+(.[w-]+)*@([a-z0-9-]+(.[a-z0-9-]+)*?.[a-z]{2,6}|(d{1,3}.){3}d{1,3})(:d{4})?$

Matches a valid email address including ip’s which are rarely used. Allows for a-z0-9_.- in the username, but not ending in a full stop i.e user.@domain.com is invalid and a-z0-9- as the optional sub domain(s) with domain name and a 2-7 char (a-z) tld allowing for short tld’s like ca and new ones like museum.

The blacklisting approach is often avoided where possible because it only protects against threats the developer could think of at the time of its creation. This means the blacklist might miss new attack vectors and have higher maintenance costs when compared to a whitelist.

Input Validation best practices:

  • Apply whitelists (known good values) where possible.
  • Check for content (i.e. 0-9), minimum and maximum lengths and correct syntax of all inputs.

Obviously, there is a great deal of different kinds of data types that must be validated. But where does this data get into your program? The answer is from a surprising number of places; in fact, your application may be collecting data from a would be attacker in ways you weren’t prepared for or maybe have not even considered.

Related Books

Amazon ImageAmazon ImageAmazon ImageAmazon Image

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)
Pages:1234567»