Archive

Posts Tagged ‘C#’

SQL to LINQ Cheat Sheet

September 27, 2009 2 comments

If you are already working with SQL and are familiar with SQL queries then you may find you at time are thinking of converting SQL syntax to LINQ syntax when writing LINQ. Following cheat sheet should help you with some of the common queries

 

SQL

LINQ

Lambda

SELECT *

FROM HumanResources.Employee

from e in Employees

select e

Employees
   .Select (e => e)

SELECT e.LoginID, e.JobTitle

FROM HumanResources.Employee AS e

from e in Employees

select new {e.LoginID, e.JobTitle}

Employees
   .Select (
      e =>
         new 
         {
            LoginID = e.LoginID,
            JobTitle = e.JobTitle
         }
   )

SELECT e.LoginID AS ID, e.JobTitle AS Title

FROM HumanResources.Employee AS e

from e in Employees

select new {ID = e.LoginID, Title = e.JobTitle}

Employees
   .Select (
      e =>
         new 
         {
            ID = e.LoginID,
            Title = e.JobTitle
         }
   )

SELECT DISTINCT e.JobTitle

FROM HumanResources.Employee AS e

(from e in Employees

select e.JobTitle).Distinct()

Employees
   .Select (e => e.JobTitle)
   .Distinct ()

SELECT e.*

FROM HumanResources.Employee AS e

WHERE e.LoginID = ‘test’

from e in Employees

where e.LoginID == "test"

select e

Employees
   .Where (e => (e.LoginID == "test"))

SELECT e.*

FROM HumanResources.Employee AS e

WHERE e.LoginID = ‘test’ AND e.SalariedFlag = 1

from e in Employees

where e.LoginID == "test" && e.SalariedFlag

select e

Employees
   .Where (e => ((e.LoginID == "test") && e.SalariedFlag))

SELECT e.*
FROM HumanResources.Employee AS e

WHERE e.VacationHours >= 2 AND e.VacationHours <= 10

from e in Employees

where e.VacationHours >= 2 && e.VacationHours <= 10

select e

Employees
   .Where (e => (((Int32)(e.VacationHours) >= 2) && ((Int32)(e.VacationHours) <= 10)))

SELECT e.*

FROM HumanResources.Employee AS e
ORDER BY e.NationalIDNumber

from e in Employees

orderby e.NationalIDNumber

select e

Employees
   .OrderBy (e => e.NationalIDNumber)

SELECT e.*

FROM HumanResources.Employee AS e

ORDER BY e.HireDate DESC, e.NationalIDNumber

from e in Employees

orderby e.HireDate descending, e.NationalIDNumber

select e

Employees
   .OrderByDescending (e => e.HireDate)
   .ThenBy (e => e.NationalIDNumber)

SELECT e.*
FROM HumanResources.Employee AS e

WHERE e.JobTitle LIKE ‘Vice%’ OR SUBSTRING(e.JobTitle, 0, 3) = ‘Pro’

from e in Employees

where e.JobTitle.StartsWith("Vice") || e.JobTitle.Substring(0, 3) == "Pro"

select e

Employees
   .Where (e => (e.JobTitle.StartsWith ("Vice") || (e.JobTitle.Substring (0, 3) == "Pro")))

SELECT SUM(e.VacationHours)

FROM HumanResources.Employee AS e

 

Employees.Sum(e => e.VacationHours);

SELECT COUNT(*)

FROM HumanResources.Employee AS e

 

Employees.Count();

SELECT SUM(e.VacationHours) AS TotalVacations, e.JobTitle

FROM HumanResources.Employee AS e

GROUP BY e.JobTitle

from e in Employees

group e by e.JobTitle into g

select new {JobTitle = g.Key, TotalVacations = g.Sum(e => e.VacationHours)}

Employees
   .GroupBy (e => e.JobTitle)
   .Select (
      g =>
         new 
         {
            JobTitle = g.Key,
            TotalVacations = g.Sum (e => (Int32)(e.VacationHours))
         }
   )

SELECT e.JobTitle, SUM(e.VacationHours) AS TotalVacations

FROM HumanResources.Employee AS e

GROUP BY e.JobTitle

HAVING e.COUNT(*) > 2

from e in Employees

group e by e.JobTitle into g

where g.Count() > 2

select new {JobTitle = g.Key, TotalVacations = g.Sum(e => e.VacationHours)}

Employees
   .GroupBy (e => e.JobTitle)
   .Where (g => (g.Count () > 2))
   .Select (
      g =>
         new 
         {
            JobTitle = g.Key,
            TotalVacations = g.Sum (e => (Int32)(e.VacationHours))
         }
   )

SELECT *

FROM Production.Product AS p, Production.ProductReview AS pr

from p in Products

from pr in ProductReviews

select new {p, pr}

Products
   .SelectMany (
      p => ProductReviews,
      (p, pr) =>
         new 
         {
            p = p,
            pr = pr
         }
   )

SELECT *

FROM Production.Product AS p

INNER JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID

from p in Products

join pr in ProductReviews on p.ProductID equals pr.ProductID

select new {p, pr}

Products
   .Join (
      ProductReviews,
      p => p.ProductID,
      pr => pr.ProductID,
      (p, pr) =>
         new 
         {
            p = p,
            pr = pr
         }
   )

SELECT *

FROM Production.Product AS p

INNER JOIN Production.ProductCostHistory AS pch ON p.ProductID = pch.ProductID AND p.SellStartDate = pch.StartDate

from p in Products

join pch in ProductCostHistories on new {p.ProductID, StartDate = p.SellStartDate} equals new {pch.ProductID, StartDate = pch.StartDate}

select new {p, pch}

Products
   .Join (
      ProductCostHistories,
      p =>
         new 
         {
            ProductID = p.ProductID,
            StartDate = p.SellStartDate
         },
      pch =>
         new 
         {
            ProductID = pch.ProductID,
            StartDate = pch.StartDate
         },
      (p, pch) =>
         new 
         {
            p = p,
            pch = pch
         }
   )

SELECT *

FROM Production.Product AS p

LEFT OUTER JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID

from p in Products

join pr in ProductReviews on p.ProductID equals pr.ProductID

into prodrev

select new {p, prodrev}

Products
   .GroupJoin (
      ProductReviews,
      p => p.ProductID,
      pr => pr.ProductID,
      (p, prodrev) =>
         new 
         {
            p = p,
            prodrev = prodrev
         }
   )

SELECT p.ProductID AS ID

FROM Production.Product AS p

UNION

SELECT pr.ProductReviewID

FROM Production.ProductReview AS pr

(from p in Products

select new {ID = p.ProductID}).Union(

from pr in ProductReviews

select new {ID = pr.ProductReviewID})

Products
   .Select (
      p =>
         new 
         {
            ID = p.ProductID
         }
   )
   .Union (
      ProductReviews
         .Select (
            pr =>
               new 
               {
                  ID = pr.ProductReviewID
               }
         )
   )

SELECT TOP (10) *

FROM Production.Product AS p

WHERE p.StandardCost < 100

(from p in Products

where p.StandardCost < 100

select p).Take(10)

Products
   .Where (p => (p.StandardCost < 100))
   .Take (10)

SELECT *

FROM [Production].[Product] AS p

WHERE p.ProductID IN(

    SELECT pr.ProductID

    FROM [Production].[ProductReview] AS [pr]

    WHERE pr.[Rating] = 5

    )

from p in Products

where (from pr in ProductReviews

where pr.Rating == 5

select pr.ProductID).Contains(p.ProductID)

select p

Products
   .Where (
      p =>
         ProductReviews
            .Where (pr => (pr.Rating == 5))
            .Select (pr => pr.ProductID)
            .Contains (p.ProductID)
   )

 

Also, here is an excellent LINQ query comprehension diagram http://www.albahari.com/nutshell/linqsyntax.emf

Advertisements
Tags: , , ,

Creating a generic CLR audit trigger

November 9, 2008 1 comment

There’s an interesting article at SqlJunkies http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk which shows how to create a generic CLR audit trigger. The audit trigger works great and includes tracking of:

  • insertions of new records
  • deletions of existing records
  • and modifications of fields in existing records.

But there is just one small problem with this trigger code. The PerformedBy column of the Audit table in the sample code is set to the UserID of the connection string which in most applications would be the same UserID for all connections because of connection pooling. This means the trigger will log all operations performed by the application but it will not log the real user (application user) who made the change.

So the first step was to make sure that the application logged in user’s UserID is passed in to all the CRUD stored procedures from my web application.

CREATE PROCEDURE spSomeProc(
    ...
    @PerformedByUserId VARCHAR(32))
AS
BEGIN
    SET NOCOUNT ON;
    ...
    RETURN;
END
GO

You can get the application logged in user’s UserID from HttpContext.Current.User.Identity.Name or Thread.CurrentPrincipal.Identity.Name and pass this value from the application to the CRUD stored procedure.

Now that we have a way to pass in the UserID to our stored procedures, we need to somehow pass this parameter value to the trigger code. Although there is no direct way of passing a parameter down to the trigger, the forums at SqlJunkies suggested to create a temporary table in your procedure, insert the UserID as the only row in the temporary table and then retrieve this value in the CLR trigger by executing a query against this temporary table. Since the trigger uses the same connection session we don’t need to create a global temporary table.

This solution works fine but it uses the tempdb which is a bit of a concern as a load test scenario performing CRUD operations could easily bloat up the tempdb.

Another option is to pass in the UserID via the CONTEXT_INFO, which frees up tempdb for other tasks. We can do so with the following code snippet added at the beginning of every CRUD stored procedure

CREATE PROCEDURE spSomeProc(
    ...
    @PerformedByUserId    VARCHAR(32))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @BinaryUserId VARBINARY(128);
    SET @BinaryUserId = CAST(@PerformedByUserId AS VARBINARY(128));

    SET CONTEXT_INFO @BinaryUserId;

    ...

    SET CONTEXT_INFO 0x0;
    RETURN;
END
GO

and in the CLR trigger you can get the CONTEXT_INFO as:

...
oCmd.CommandText  = "SELECT CAST(CONTEXT_INFO() AS VARCHAR(128))";
string userid = (string)oCmd.ExecuteScalar();
... 

We can also add an additional check here to get the UserID from “SELECT CURRENT_USER” just to make sure that if the CONTEXT_INFO is not set or returns NULL as in the case where some DBA made a change to any of the tables directly (for production support issue or whatever).

SqlCommand CurrentUserCmd = new SqlCommand("SELECT CAST(CONTEXT_INFO() AS VARCHAR(128))", Connection);
string CurrentUser = CurrentUserCmd.ExecuteScalar().ToString();
if (string.IsNullOrEmpty(CurrentUser))
{
    CurrentUserCmd.CommandText = "SELECT CURRENT_USER";
    CurrentUser = CurrentUserCmd.ExecuteScalar().ToString();
}

The full source code from the original article and modified as described above (converted to C#) is given below

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server; 

public partial class Triggers
{
    //This is the original template for Trigger metadata. Note that it is table-specific (i.e. it suggests that the trigger should apply to one table only).
    //<Microsoft.SqlServer.Server.SqlTrigger(Name:="Trigger1", Target:="Table1", Event:="FOR UPDATE")> _ 

    //This is our actual declaration. Note that it does not specify any particular table. We don't know if it is Microsoft's intention to allow table-agnostic trigger code, but this works and we hope that it keeps working.
    //GENERIC AUDIT TRIGGER: AuditCommon
    [Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditCommon", Event = "FOR UPDATE, INSERT, DELETE")]
    public static void AuditCommon()
    {
        try
        {
#if(DEBUG)
            EmitDebugMessage("Enter Trigger");
#endif 

            //Grab the already-open Connection to use as an argument
#if(DEBUG)
            EmitDebugMessage("Open Connection");
#endif
            SqlTriggerContext Context = SqlContext.TriggerContext;
            SqlConnection Connection = new SqlConnection("CONTEXT CONNECTION=TRUE");
            Connection.Open(); 

            //Load the "inserted" table
#if(DEBUG)
            EmitDebugMessage("Load INSERTED");
#endif
            SqlDataAdapter TableLoader = new SqlDataAdapter("SELECT * FROM INSERTED", Connection);
            DataTable InsertedTable = new DataTable();
            TableLoader.Fill(InsertedTable); 

            //Load the "deleted" table
#if(DEBUG)
            EmitDebugMessage("Load DELETED");
#endif
            TableLoader.SelectCommand.CommandText = "SELECT * FROM DELETED";
            DataTable DeletedTable = new DataTable();
            TableLoader.Fill(DeletedTable); 

            //Prepare the "audit" table for insertion
#if(DEBUG)
            EmitDebugMessage("Load AUDIT schema for insertion");
#endif
            SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM AUDIT WHERE 1 = 0", Connection);
            DataTable AuditTable = new DataTable();
            AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
            SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);
            //Create DataRow objects corresponding to the trigger table rows.
#if(DEBUG)
            EmitDebugMessage("Create internal representations of trigger table rows");
#endif
            string TableName = "";
            DataRow InsertedRow = null;
            if (InsertedTable.Rows.Count > 0)
            {
                InsertedRow = InsertedTable.Rows[0];
                TableName = DeriveTableNameFromKeyFieldName(InsertedTable.Columns[0].ColumnName);
            }
            DataRow DeletedRow = null;
            if (DeletedTable.Rows.Count > 0)
            {
                DeletedRow = DeletedTable.Rows[0];
                TableName = DeriveTableNameFromKeyFieldName(DeletedTable.Columns[0].ColumnName);
            } 

            //get the current database user
            SqlCommand CurrentUserCmd = new SqlCommand("SELECT CAST(CONTEXT_INFO() AS VARCHAR(128))", Connection);
            string CurrentUser = CurrentUserCmd.ExecuteScalar().ToString();
            if (string.IsNullOrEmpty(CurrentUser))
            {
                CurrentUserCmd.CommandText = "SELECT CURRENT_USER";
                CurrentUser = CurrentUserCmd.ExecuteScalar().ToString();
            }
            //Perform different audits based on the type of action.
            switch (Context.TriggerAction)
            {
                case TriggerAction.Update:
                    //Ensure that both INSERTED and DELETED are populated. If not, this is not a valid update.
                    if (InsertedRow != null && DeletedRow != null)
                    {
                        //Walk through all the columns of the table.
                        foreach (DataColumn Column in InsertedTable.Columns)
                        {
                            //ALTERNATIVE CODE to compare values and record only if they are different:
                            //If Not DeletedRow.Item(Column.Ordinal).Equals(InsertedRow.Item(Column.Ordinal)) Then
                            //This code records any attempt to update, whether the new value is different or not.
                            if (Context.IsUpdatedColumn(Column.Ordinal))
                            {
                                //DEBUG output indicating field change
#if(DEBUG)
                                EmitDebugMessage("Create UPDATE Audit: Column Name = " + Column.ColumnName + ", Old Value = '" + DeletedRow[Column.Ordinal].ToString() + "'" + ", New Value = '" + InsertedRow[Column.Ordinal].ToString() + "'");
#endif
                                //Create audit record indicating field change
                                DataRow AuditRow = AuditTable.NewRow(); 

                                //populate fields common to all audit records
                                long RowId = (long)InsertedRow[0]; 

                                //use "Inserted.TableName" when Microsoft fixes the CLR to supply it
                                WriteCommonAuditData(AuditRow, TableName, RowId, CurrentUser, "UPDATE"); 

                                //write update-specific fields
                                AuditRow["FieldName"] = Column.ColumnName;
                                AuditRow["OldValue"] = DeletedRow[Column.Ordinal].ToString();
                                AuditRow["NewValue"] = InsertedRow[Column.Ordinal].ToString(); 

                                //insert the new row into the audit table
                                AuditTable.Rows.InsertAt(AuditRow, 0);
                            }
                        }
                    }
                    break; 

                case TriggerAction.Insert:
                    //If the INSERTED row is not populated, then this is not a valid insertion.
                    if (InsertedRow != null)
                    {
                        //DEBUG output indicating row insertion
#if(DEBUG)
                        EmitDebugMessage("Create INSERT Audit: Row = '" + InsertedRow[0].ToString() + "'");
#endif
                        //Create audit record indicating field change
                        DataRow AuditRow = AuditTable.NewRow();
                        //populate fields common to all audit records
                        long RowId = (long)InsertedRow[0];
                        //use "Inserted.TableName" when Microsoft fixes the CLR to supply it
                        WriteCommonAuditData(AuditRow, TableName, RowId, CurrentUser, "INSERT");
                        //insert the new row into the audit table
                        AuditTable.Rows.InsertAt(AuditRow, 0);
                    }
                    break;
                case TriggerAction.Delete:
                    //If the DELETED row is not populated, then this is not a valid deletion.
                    if (DeletedRow != null)
                    {
                        //DEBUG output indicating row insertion
#if(DEBUG)
                        EmitDebugMessage("Create DELETE Audit: Row = '" + DeletedRow[0].ToString() + "'");
#endif
                        //Create audit record indicating field change
                        DataRow AuditRow = AuditTable.NewRow();
                        //populate fields common to all audit records
                        long RowId = (long)DeletedRow[0];
                        //use "Inserted.TableName" when Microsoft fixes the CLR to supply it
                        WriteCommonAuditData(AuditRow, TableName, RowId, CurrentUser, "DELETE");
                        //insert the new row into the audit table
                        AuditTable.Rows.InsertAt(AuditRow, 0);
                    }
                    break;
            } 

            //update the audit table
            AuditAdapter.Update(AuditTable);
            //finish
#if(DEBUG)
            EmitDebugMessage("Exit Trigger");
#endif
        }
        catch (Exception ex)
        {
            //Put exception handling code here if you want to connect this to your database-based error logging system. Without this Try/Catch block, any error in the trigger routine will stop the event that fired the trigger. This is early-stage development and we're not expecting any exceptions, so for the moment we just need to know about them if they occur.
            throw;
        }
    } 

    //Write data into the fields of an Audit table row that is common to all types of audit activities.
    private static void WriteCommonAuditData(DataRow auditRow, string tableName, long rowId, string currentUser, string operation)
    {
        auditRow["TableName"] = tableName;
        auditRow["RowId"] = rowId;
        auditRow["OccurredAt"] = DateTime.Now;
        auditRow["PerformedBy"] = currentUser;
        auditRow["Operation"] = operation;
    } 

    //SQL CLR does not deliver the proper table name from either InsertedTable.TableName or DeletedTable.TableName, so we must use a substitute based on our key naming convention. We assume that in each table, the KeyFieldName = TableName + "Id". Remove this routine and its uses as soon as we can get the table name from the CLR.
    private static string DeriveTableNameFromKeyFieldName(string keyFieldName)
    {
        return keyFieldName.Substring(0, keyFieldName.Length - 2); //assumes KeyName = TableName & "Id"
    } 

    //Emit debug messages. This function gives us the option to turn off debugging messages by changing one value (here).
#if(DEBUG)
    private static void EmitDebugMessage(string message)
    {
        SqlContext.Pipe.Send(message);
    }
#endif
}

Technorati Tags: ,,,,

Table-Valued parameter in SQL Server 2005

August 3, 2008 3 comments

In pre-SQL Server 2005 in order to pass in a set of values one had to create a temporary table, populate it with data using INSERT, and then just use it in the procedure or function since they are created for the current session and are available to all processes in that session.

I did a blog on how to pass in Table-Value Parameters in SQL Server 2008 but what if we have a need to pass in multiple rows of data to a T-SQL statement, or a routine such as stored procedure or function in SQL Server 2005?

Turns out the same can be done in SQL Server 2005 without using temporary tables. By using the XML data type you can pass user-defined sets between queries and also between the client and the server.

The following code shows how you can create and use XML parameters.

USE AdventureWorks; GO CREATE PROCEDURE uspEmployeeList(@EmployeeList XML) AS BEGIN SET NOCOUNT ON; SELECT E.* FROM HumanResources.Employee E INNER JOIN @EmployeeList.nodes('/Root/E') AS Tbl(C) ON E.EmployeeID = Tbl.C.value('@EmployeeID', 'INT'); RETURN; END
 

How are XML parameters supported in .NET? ADO.NET allows full support using SqlDbType.Xml type. Adding a XML as a parameter to the stored procedure from C# would have something like:

//string EmployeeXml = "<Root><E EmployeeID=\"1\" /><E EmployeeID=\"3\" /><E EmployeeID=\"5\" /><E EmployeeID=\"7\" /><E EmployeeID=\"11\" /></Root>";
 
// Create the data table
DataTable dt = new DataTable("E");
// Create the table schema
DataColumn dc = dt.Columns.Add("EmployeeID", typeof(string));
// Add a few records to the data table.
for (int i = 1; i <= 10; i++)
{
      // create a new row
      
DataRow
dr = dt.NewRow();
      // populate the fields
      
dr[0] = i.ToString();
      // add the row to the table
      
dt.Rows.Add(dr);
}
... System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("uspEmployeeList", sqlConn); cmd.Parameters.Add("@EmployeeList", System.Data.SqlDbType.Xml); //cmd.Parameters["@EmployeeList"].Value = EmployeeXml; ...
// Create a temporary MemoryStream to hold the output of the WriteXml method of the DataTable
using (MemoryStream memoryStream = new MemoryStream())
{
dt.WriteXml(memoryStream);
         UTF8Encoding encoding = new UTF8Encoding();
         cmd.Parameters["@EmployeeList"].Value = encoding.GetString(memoryStream.ToArray());
}

Now that’s cool. This is much better than passing in a comma separated list and using a dynamic query in our procedure or function.

Forms Authentication Timeout vs Session Timeout

July 17, 2008 10 comments

We have an old .NET 1.1 web application which I have to support and a recent change in the login process for a select few customers has been causing haywire with every users session. The folks at QA have been giving me a really hard time lately with this bug and I just couldn’t get around as to what was causing this weird behavior.

The problem was that if we set the forms authentication and session timeouts to 10 minutes and after the 10th minute the user clicked on any link the app would redirect the user to the login page but the session was not abandoned i.e. the forms authentication ticket had expired but not the session state timeout. To make matters worse I was unable to reproduce it on DEV or QA instance with my automated test script but was able to reproduce it by manually following the steps.

After a lot of googling I finally realized the solution was right there and I had just overlooked it. The problem was in the way timeouts work for authentication tickets vs session state.

Forms authentication ticket can time out in two ways. The first scenario occurs if you use absolute expiration. With absolute expiration, you set an expiration of 20 minutes, and a user visits the site at 2:00 PM. The user will be redirected to the login page if the user visits the site after 2:20 PM. Even if the user visited some pages in between 2:00 PM and 2:20 PM the user will still be redirected to the login page after 2:20 PM.

Now if you are using sliding expiration for forms authentication and session state the scenario gets a bit complicated. With sliding expiration the session state timeout is updated on every visit but the cookie and the resulting authentication ticket are updated if the user visits the site after the expiration time is half-expired.

For example, you set an expiration of 20 minutes for forms authentication ticket and session state and you set sliding expiration to true. A user visits the site at 2:00 PM, and the user receives a cookie that is set to expire at 2:20 PM. The authentication ticket expiration is only updated if the user visits the site after 2:10 PM. If the user visits the site at 2:08 PM, the authentication ticket is not updated but the session state timeout is updated and the session now expires at 2:28 PM. If the user then waits 12 minutes, visiting the site at 2:21 PM, the authentication ticket will be expired and the user is redirected to the login page, but guess what, the session timeout has not yet expired.

Here is the MSDN link which explains this http://support.microsoft.com/kb/910439

So, how do we synch these two timeouts? Or force the other to timeout if one of them expires? The workaround we came up with was to set the authentication timeout to double the value of session timeout and have the following code in the global.asax.cs.

protected void Application_AcquireRequestState(object sender, SystemEventArgs e) 
{

    if
(Session!= null && Session.IsNewSession)
    {
       
string szCookieHeader= Request.Headers["Cookie"];
       
if((szCookieHeader!= null)&& (szCookieHeader.IndexOf("ASP.NET_SessionId")>= 0))
        {
           
if(User.Indentity.IsAuthenticated)
            {
                FormsAuthentication.SignOut();
                Response.Redirect(Request.RawUrl);
            }
        }
    }
}

Technorati Tags: ,,

Preventing SQL injection attacks in ASP.NET

May 30, 2008 1 comment

Consider a simple web application that requires user input in some fields, lets say some search box. Suppose a user types the following string in that textbox:

'; DROP DATABASE pubs --

On submit our application executes the following dynamic SQL statement

SqlDataAdapter myCommand = new SqlDataAdapter("SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = '" + OrderNumberTextBox.Text + "'", myConnection);

Or stored procedure

SqlDataAdapter myCommand = new SqlDataAdapter("uspGetOrderList '" + OrderNumberTextBox.Text + "'", myConnection);

The intention being that the user input would be run as

SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = 'PO123'

However, the code inserts the user’s malicious input and generates the following query.

SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = ''; DROP DATABASE pubs --'

In this case, the ‘ (single quotation mark) character that starts the rogue input terminates the current string literal in the SQL statement. As a result, the opening single quotation mark character of the rogue input results in the following statement.

SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = ''

The; (semicolon) character tells SQL that this is the end of the current statement, which is then followed by the following malicious SQL code.

; DROP DATABASE pubs

Finally, the — (double dash) sequence of characters is a SQL comment that tells SQL to ignore the rest of the text. In this case, SQL ignores the closing ‘ (single quotation mark) character, which would otherwise cause a SQL parser error.

--'

Using stored procedures doesn’t solve the problem either because the generated query would be

uspGetOrderList ''; DROP DATABASE pubs--'

Or perhaps this was your login page and your query being

SELECT UserId FROM Users WHERE LoginId = <inputlogin> AND Password = <inputpwd> AND IsActive = 1

Someone could easily login by typing in the following in your login textbox

' OR 1 = 1; --

Which makes our query

SELECT UserId FROM Users WHERE LoginId = '' OR 1 = 1; --' AND Password = '' AND IsActive = 1

Viola, the attacker has now successfully logged in to your site using SQL injection attack.

SQL injection can occur, as demonstrated above, when an application uses input to construct dynamic SQL statements or when it uses stored procedures to connect to the database. Conventional security measures, such as the use of SSL and IPSec, do not protect your application from SQL injection attacks. Successful SQL injection attacks enable malicious users to execute commands in an application’s database. Common vulnerabilities that make your data access code susceptible to SQL injection attacks include:

  • Weak input validation.
  • Dynamic construction of SQL statements without the use of type-safe parameters.
  • Use of over-privileged database logins.

    So what can we do to help protect our application from such attacks? To counter SQL injection attacks, we need to:

  • Constrain and sanitize input data

    Check for known good data by validating for type, length, format, and range and using a list of acceptable characters to constrain input. Create a list of acceptable characters and use regular expressions to reject any characters that are not on the list. Using the list of unacceptable characters is impractical because it is very difficult to anticipate all possible variations of bad input.

    Start by constraining input in the server-side code for your ASP.NET Web pages. Do not rely on client-side validation because it can be easily bypassed. Use client-side validation only to reduce round trips and to improve the user experience. Check my other blog on Validation Application Block for server-side validation.

    If in the previous code example, the Order Number value is captured by an ASP.NET TextBox control, you can constrain its input by using a RegularExpressionValidator control as shown in the following.

    <%@ language="C#" %> <form id="form1" runat="server"> <asp:TextBox ID="OrderNumberTextBox" runat="server"/> <asp:RegularExpressionValidator ID="regexpPO" runat="server" ErrorMessage="Incorrect Order Number" ControlToValidate="OrderNumberTextBox" ValidationExpression="^PO\d{3}-\d{2}$" /> </form>

    If the Order Number input is from another source, such as an HTML control, a query string parameter, or a cookie, you can constrain it by using the Regex class from the System.Text.RegularExpressions namespace. The following example assumes that the input is obtained from a cookie.

    using System.Text.RegularExpressions; if (Regex.IsMatch(Request.Cookies["OrderNumber"], "^PO\d{3}-\d{2}$")) { // access the database } else { // handle the bad input }

    Performing input validation is essential because almost all application-level attacks contain malicious input. You should validate all input, including form fields, query string parameters, and cookies to protect your application against malicious command injection. Assume all input to your Web application is malicious, and make sure that you use server validation for all sources of input. Use client-side validation to reduce round trips to the server and to improve the user experience, but do not rely on it because it is easily bypassed.

     

  • Apply ASP.NET request validation during development to identify injection attacks

    ASP.NET request validation detects any HTML elements and reserved characters in data posted to the server. This helps prevent users from inserting script into your application. Request validation checks all input data against a hard-coded list of potentially dangerous values. If a match occurs, it throws an exception of type HttpRequestValidationException.

    Request validation is enabled by ASP.NET by default. You can see the following default setting in the Machine.config.comments file.

    <pages validateRequest="true" ... />

    Confirm that you have not disabled request validation by overriding the default settings in your server’s Machine.config file or your application’s Web.config file.

    You can disable request validation in your Web.config application configuration file by adding a <pages> element with validateRequest="false" or on an individual page by setting ValidateRequest="false" on the @ Pages element.

    NOTE: You should disable Request Validation only on the page with a free-format text field that accepts HTML-formatted input.

    You can test the effects of request validation. To do this, create an ASP.NET page that disables request validation by setting ValidateRequest="false", as follows.

    <%@ Language="C#" ValidateRequest="false" %> <html> <script runat="server"> void btnSubmit_Click(Object sender, EventArgs e) { // If ValidateRequest is false, then 'hello' is displayed // If ValidateRequest is true, then ASP.NET returns an exception Response.Write(txtString.Text); } </script> <body> <form id="form1" runat="server"> <asp:TextBox id="txtString" runat="server" Text="<script>alert('hello');</script>" /> <asp:Button id="btnSubmit" runat="server" OnClick="btnSubmit_Click" Text="Submit" /> </form> </body> </html>

    When you run the page, "Hello" is displayed in a message box because the script in txtString is passed through and rendered as client-side script in your browser.

    If you set ValidateRequest="true" or remove the ValidateRequest page attribute, ASP.NET request validation rejects the script input and produces an error similar to the following.

    A potentially dangerous Request.Form value was detected from the client (txtString="<script>alert(‘hello…").
    Note   Do not rely on ASP.NET request validation. Treat it as an extra precautionary measure in addition to your own input validation.

  • Constrain input by using validator controls

    To constrain input, use server-side input validation. Do not rely on client-side validation because it is easily bypassed. Use client-side validation in addition to server-side validation to reduce round trips to the server and to improve the user experience. Validate length, range, format and type. Make sure that any input meets your guidelines for known good input.

    Use the ASP.NET validator controls to constrain form field input received through server controls. For other sources of input data, such as query strings, cookies, and HTTP headers, constrain input by using the Regex class from the System.Text.RegularExpressions namespace. Or you can use the Enterprise Library Validation Application Block to check for input validation. The Validation Application Block can not be used for input validation but it can also validate your business objects. See my other blog on Validation Application Block.

  • Encode unsafe output

    If your application needs to accept a range of HTML elements—for example through a rich text input field such as a comments field—turn off ASP.NET request validation and create a filter that allows only the HTML elements that you want your application to accept. A common practice is to restrict formatting to safe HTML elements such as <b> (bold) and <i> (italic). Before writing the data, HTML-encode it. This makes any malicious script safe by causing it to be handled as text, not as executable code.

    The HtmlEncode method replaces characters that have special meaning in HTML to HTML variables that represent those characters. For example, < is replaced with &lt; and " is replaced with &quot;. Encoded data does not cause the browser to execute code. Instead, the data is rendered as harmless text, and the tags are not interpreted as HTML.

    The following page disables ASP.NET request validation by setting ValidateRequest="false". It HTML-encodes the input and selectively allows the <b> and <i> HTML elements to support simple text formatting.

    <%@ Page Language="C#" ValidateRequest="false"%> <script runat="server"> void submitBtn_Click(object sender, EventArgs e) { // Encode the string input StringBuilder sb = new StringBuilder( HttpUtility.HtmlEncode(htmlInputTxt.Text)); // Selectively allow and <i> sb.Replace("&lt;b&gt;", "<b>"); sb.Replace("&lt;/b&gt;", ""); sb.Replace("&lt;i&gt;", "<i>"); sb.Replace("&lt;/i&gt;", ""); Response.Write(sb.ToString()); } </script> <html> <body> <form id="form1" runat="server"> <asp:TextBox ID="htmlInputTxt" Runat="server" TextMode="MultiLine" Width="318px" Height="168px" /> <asp:Button ID="submitBtn" Runat="server" Text="Submit" OnClick="submitBtn_Click" /> </form> </body> </html>
  • Use type-safe SQL parameters for data access

    Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. You can use these parameters with stored procedures or dynamically constructed SQL command strings.

    Using stored procedures does not necessarily prevent SQL injection. The important thing to do is use parameters with stored procedures. If you do not use parameters, your stored procedures can be susceptible to SQL injection if they use unfiltered input.

    The following code shows how to use SqlParameterCollection when calling a stored procedure.

    using System.Data; using System.Data.SqlClient; using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet userDataset = new DataSet(); SqlDataAdapter myCommand = new SqlDataAdapter("uspGetOrderList", connection); myCommand.SelectCommand.CommandType = CommandType.StoredProcedure; myCommand.SelectCommand.Parameters.Add("@OrderNumber", SqlDbType.VarChar, 11); myCommand.SelectCommand.Parameters["@OrderNumber"].Value = OrderNumberTextBox.Text; myCommand.Fill(userDataset); }

    The @OrderNumber parameter is treated as a literal value and not as executable code. Also, the parameter is checked for type and length. In the preceding code example, the input value cannot be longer than 11 characters. If the data does not conform to the type or length defined by the parameter, the SqlParameter class throws an exception.

    You should review your application’s use of stored procedures because simply using stored procedures with parameters does not necessarily prevent SQL injection. For example, the following parameterized stored procedure has several security vulnerabilities.

    CREATE PROCEDURE dbo.uspRunQuery @var ntext AS exec sp_executesql @var GO

    The stored procedure executes whatever statement is passed to it. Consider the @var variable being set to:

    DROP TABLE ORDERS;

    If you cannot use stored procedures, you should still use parameters when constructing dynamic SQL statements. The following code shows how to use SqlParametersCollection with dynamic SQL.

    using System.Data; using System.Data.SqlClient; using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet userDataset = new DataSet(); SqlDataAdapter myDataAdapter = new SqlDataAdapter("SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = @OrderNumber", connection); myCommand.SelectCommand.Parameters.Add("@OrderNumber", SqlDbType.VarChar, 11); myCommand.SelectCommand.Parameters["@OrderNumber"].Value = OrderNumberTextBox.Text; myDataAdapter.Fill(userDataset); }

    If you concatenate several SQL statements to send a batch of statements to the server in a single round trip, you can still use parameters if you make sure that parameter names are not repeated i.e. use unique parameter names during SQL text concatenation.

    SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = 'PO123' using System.Data; using System.Data.SqlClient; using (SqlConnection oConn = new SqlConnection(connectionString)) { SqlDataAdapter oAdapter = new SqlDataAdapter( "SELECT CustomerID INTO #Temp1 FROM Customers " + "WHERE CustomerID > @custIDParm; " + "SELECT CompanyName FROM Customers " + "WHERE Country = @countryParm and CustomerID IN " + "(SELECT CustomerID FROM #Temp1);", oConn); SqlParameter custIDParm = oAdapter.SelectCommand.Parameters.Add("@custIDParm", SqlDbType.NChar, 5); custIDParm.Value = customerID.Text; SqlParameter countryParm = oAdapter.SelectCommand.Parameters.Add("@countryParm", SqlDbType.NVarChar, 15); countryParm.Value = country.Text; oConn.Open(); DataSet dataSet = new DataSet(); oAdapter.Fill(dataSet); }
  • Use a least privileged account that has restricted permissions in the database

    Ideally, you should only grant execute permissions to selected stored procedures in the database and provide no direct table access. The problem is more severe if your application uses an over-privileged account to connect to the database. For example, if your application’s login has privileges to eliminate a database, then without adequate safeguards, an attacker might be able to perform this operation.

    If you use Windows authentication to connect, the Windows account should be least-privileged from an operating system perspective and should have limited privileges and limited ability to access Windows resources. Additionally, whether or not you use Windows authentication or SQL authentication, the corresponding SQL Server login should be restricted by permissions in the database.

    Consider the example of an ASP.NET application running on Microsoft Windows Server 2003 that accesses a database on a different server in the same domain. By default, the ASP.NET application runs in an application pool that runs under the Network Service account. This account is a least privileged account.

    1. Create a SQL Server login for the Web server’s Network Service account. The Network Service account has network credentials that are presented at the database server as the identity DOMAIN\WEBSERVERNAME$. For example, if your domain is called XYZ and the Web server is called 123, you create a database login for XYZ\123$.
    2. Grant the new login access to the required database by creating a database user and adding the user to a database role.
    3. Establish permissions to let this database role call the required stored procedures or access the required tables in the database. Only grant access to stored procedures the application needs to use, and only grant sufficient access to tables based on the application’s minimum requirements. If the ASP.NET application only performs database lookups and does not update any data, you only need to grant read access to the tables. This limits the damage that an attacker can cause if the attacker succeeds in a SQL injection attack.
  • Use Character Escaping Techniques

    In situations where parameterized SQL cannot be used, consider using character escaping techniques. If you are forced to use dynamic SQL and parameterized SQL cannot be used, you need to safeguard against input characters that have special meaning to SQL Server (such as the single quote character). If not handled, special characters such as the single quote character in the input can be utilized to cause SQL injection.

    Escape routines add an escape character to characters that have special meaning to SQL Server, thereby making them harmless.

    private static string GetStringForSQL(string inputSQL) { return inputSQL.Replace("'", "''"); }

    Special input characters pose a threat only with dynamic SQL and not when using parameterized SQL. Your first line of defense should always be to use parameterized SQL.

  • Avoid disclosing database error information

    In the event of database errors, make sure you do not disclose detailed error messages to the user. Use structured exception handling to catch errors and prevent them from propagating back to the client. Log detailed error information locally, but return limited error details to the client.

    If errors occur while the user is connecting to the database, be sure that you provide only limited information about the nature of the error to the user. If you disclose information related to data access and database errors, you could provide a malicious user with useful information that he or she can use to compromise your database security. Attackers use the information in detailed error messages to help deconstruct a SQL query that they are trying to inject with malicious code. A detailed error message may reveal valuable information such as the connection string, SQL server name, or table and database naming conventions. See my other post on Exception Handling – Do’s and Dont’s.

    You can use the <customErrors> element to configure custom, generic error messages that should be returned to the client in the event of an application exception condition.

    Make sure that the mode attribute is set to "remoteOnly" in the web.config file as shown in the following example.

    <customErrors mode="remoteOnly" />

    After installing an ASP.NET application, you can configure the setting to point to your custom error page as shown in the following example.

    <customErrors mode="On" defaultRedirect="YourErrorPage.htm" />

    Conclusion

    The above list is just some points found on MSDN on how you can make your site more secure by effectively preventing SQL injection attacks. You should always be reviewing your code to find these or other security vulnerabilities; remember all type of attacks start with some input, and your first line of defense should be input validation using both client-side and server-side validation.

    Technorati Tags: ,

  • Creating AJAX Validator Control

    May 25, 2008 5 comments

    Imagine that you are creating a website registration form and you need to validate a Login ID or Email field. You want to make sure that the Login ID or Email entered does not already exist in the database. You could use the CustomValidator control to call a server-side function to check whether the field is unique in the database.

    <asp:TextBox id="txtEmail" Runat="server" /> <asp:CustomValidator id="valEmail" ControlToValidate="txtEmail" Text="Email already exists" OnServerValidate="valEmail_ServerValidate" Runat="server" />
    void valEmail_ServerValidate(Object source, ServerValidateEventArgs args) { if (EmailAlreadyExists(args.Value)) args.IsValid = false; else args.IsValid = true; }

    This of course causes a postback to the server where the server-side function is executed and the page is refreshed. This can be frustrating to the user to have to enter all the fields and submit and the page refreshes only to find out that the Email address is already in the system.

    To provide a better user experience we somehow need to call the server-side function from the client without causing a postback. Using AJAX (Asynchronous JavaScript and XML) we can create a validator control to call the server-side validation function from the client. The advantage of using AJAX is that no postback to the server is apparent to the user.

    Our control needs to inherit from the BaseValidator class and also implement the ICallbackEventHandler interface. The BaseValidator class serves as the abstract base class for validation controls and provides the core implementation for all validation controls. The BaseValidator requires you to implement a single method:

    • EvaluateIsValid – returns true when the form field being validated is valid.

    The BaseValidator class also includes several other methods that you can override or otherwise use. The most useful of these methods is the following:

    • GetControlValidationValue – enables you to retrieve the value of the control being validated.

    When you create a custom validation control, you override the EvaluateIsValid() method and, within the EvaluateIsValid() method, you call GetControlValidationValue to get the value of the form field being validated.

    public class MyAjaxValidator : BaseValidator, ICallbackEventHandler { public event ServerValidateEventHandler ServerValidate; string _controlToValidateValue; protected override bool EvaluateIsValid() { string controlToValidateValue = this.GetControlValidationValue(this.ControlToValidate); return ExecuteValidationFunction(controlToValidateValue); } private bool ExecuteValidationFunction(String controlToValidateValue) { ServerValidateEventArgs args = new ServerValidateEventArgs(controlToValidateValue, this.IsValid); if (ServerValidate != null) ServerValidate(this, args); return args.IsValid; }

    The ICallbackEventHandler interface defines two methods that are called on the server when an AJAX request is made from the client:

    • RaiseCallbackEvent – called to handle the event, passing the event argument as a parameter
    • GetCallbackResult – returns the result of the callback
    public void RaiseCallbackEvent(string eventArgument) { _controlToValidateValue = eventArgument; } public string GetCallbackResult() { return ExecuteValidationFunction(_controlToValidateValue).ToString(); }

    In the OnPreRender() method, a JavaScript include file and startup script are registered.

    protected override void OnPreRender(EventArgs e) { String eventRef = Page.ClientScript.GetCallbackEventReference(this, "", "", ""); String includeScript = Page.ResolveClientUrl("~/Scripts/MyAjaxValidator.js"); Page.ClientScript.RegisterClientScriptInclude("MyAjaxValidator", includeScript); String startupScript = String.Format(“document.getElementById('{0}').evaluationfunction = 'MyAjaxValidatorEvaluateIsValid';", this.ClientID); Page.ClientScript.RegisterStartupScript(this.GetType(), "MyAjaxValidator", startupScript, true); base.OnPreRender(e); } protected override bool DetermineRenderUplevel() { return Context.Request.Browser.SupportsCallback; }

    The JavaScript include file contains the client-side functions that are called when the MyAjaxValidator validates a form field on the client. The startup script associates the client-side MyAjaxValidatorEvaluateIsValid() function with the MyAjaxValidator control. The client-side validation framework automatically calls this JavaScript function when performing validation.

    function MyAjaxValidatorEvaluateIsValid(val) { var value = ValidatorGetValue(val.controltovalidate); WebForm_DoCallback(val.id, value, MyAjaxValidatorResult, val, MyAjaxValidatorError, true); return true; } function MyAjaxValidatorResult(returnValue, context) { if (returnValue == 'True') context.isvalid = true; else context.isvalid = false; ValidatorUpdateDisplay(context); } function MyAjaxValidatorError(message) { alert('Error: ' + message); }

    The MyAjaxValidatorEvaluateIsValid() JavaScript method initiates an AJAX call by calling the WebForm_DoCallback() method. This method calls the server-side validation function associated with the MyAjaxValidator control. When the AJAX call completes, the MyAjaxValidatorResult() method is called. This method updates the display of the validation control on the client.

    The following page illustrates how you can use the MyAjaxValidator control. This page handles the MyAjaxValidator control’s ServerValidate event to associate a custom validation function with the control. The validation function checks whether a email already exists in the database. If you enter a email that already exists, a validation error message is displayed. The message is displayed in the browser before you submit the form back to the server.

    <form id="form1" runat="server"> <asp:Label id="lbEmail" Text="Email:" AssociatedControlID="txtEmail" Runat="server" /> <asp:TextBox id="txtEmail" Runat="server" /> <custom:MyAjaxValidator id="valEmail" ControlToValidate="txtEmial" Text="Email already exists" OnServerValidate="valEmail_ServerValidate" Runat="server" /> <br /> <!--some other controls here --> <br /> <asp:Button id="btnSubmit" Text="Submit" Runat="server" OnClick="btnSubmit_Click" /> </form>

    And in code behind

    protected void valEmail_ServerValidate(object source, ServerValidateEventArgs args) { if (EmailAlreadyExists(args.Value)) args.IsValid = false; else args.IsValid = true; }

    It is important to realize that you can associate any server-side validation function with the MyAjaxValidator. You can perform a database lookup, call a web service, or perform a complex mathematical function. Whatever function you define on the server is automatically called on the client.

    Complete code for MyAjaxValidator:

    public class MyAjaxValidator : BaseValidator, ICallbackEventHandler { public event ServerValidateEventHandler ServerValidate; string _controlToValidateValue; protected override bool EvaluateIsValid() { string controlToValidateValue = this.GetControlValidationValue(this.ControlToValidate); return ExecuteValidationFunction(controlToValidateValue); } protected override void OnPreRender(EventArgs e) { String eventRef = Page.ClientScript.GetCallbackEventReference(this, "", "", ""); String includeScript = Page.ResolveClientUrl("~/Scripts/MyAjaxValidator.js"); Page.ClientScript.RegisterClientScriptInclude("MyAjaxValidator", includeScript); String startupScript = String.Format(“document.getElementById('{0}').evaluationfunction = 'MyAjaxValidatorEvaluateIsValid';", this.ClientID); Page.ClientScript.RegisterStartupScript(this.GetType(), "MyAjaxValidator", startupScript, true); base.OnPreRender(e); } protected override bool DetermineRenderUplevel() { return Context.Request.Browser.SupportsCallback; } public void RaiseCallbackEvent(string eventArgument) { _controlToValidateValue = eventArgument; } public string GetCallbackResult() { return ExecuteValidationFunction(_controlToValidateValue).ToString(); } private bool ExecuteValidationFunction(String controlToValidateValue) { ServerValidateEventArgs args = new ServerValidateEventArgs(controlToValidateValue, this.IsValid); if (ServerValidate != null) ServerValidate(this, args); return args.IsValid; } }
     
    REF:
    Sams ASP.Net 2.0 by Stephen Walther
     

    Tags: , ,

    Using Validation Application Block in ASP.NET

    May 19, 2008 3 comments

    Any web page which requires input from the user simply cant allow just any data to be entered because the data might be too long, missing or incorrect. We need to validate the data entered by the users in a number of ways; for example, First Name and Last Name might be required fields and Date of Birth cannot be greater than the current date etc. We can very well use the client side validators provided by ASP.NET like RequiredFieldValidator, CompareValidator or RegularExpressionValidtor but is that really enough? What if JavaScript is disabled by the client? What if we need to validate our business objects? It is often important to validate data several times within the same application. For example, you may need to validate object instances that you:

    • Generate in separate tiers or components of your application
    • Retrieve from a cache, a repository, or a third-party data access layer
    • Receive from a Web Service as an object instance or a set of property values
    • Reconstruct from existing values, perhaps populating them by copying properties from a similar object or using values retrieved from a database

    Enter the Enterprise Library with its Validation Application Block.

    The Validation Application Block is designed to address the most common tasks developers face when they must validate input either from a user or another system. The Validation Application Block provides a comprehensive series of validation components that you can apply through configuration, or through attributes applied directly to classes, class members, plus UI controls for use in both ASP.NET and Windows Forms applications.

    One of the major advantages with Validation Application Block is that you can define rule sets within your application configuration file, with each rule set defining the types of validation to undertake and the individual parameters for that validation process. This means that administrators can quickly and easily change the validation rules and parameters without requiring any changes to the existing code – avoiding the risk of code errors, recompilation issues, and application downtime.

    The Validation Application Block is designed to do the following:

    • Encapsulate the logic used to perform the most common validation tasks into minimal application code.
    • Relieve developers of the requirement to write duplicate code and custom code for common validation tasks.
    • Allow validation rules to be changed after the application has been deployed, and ensure that changes happen simultaneously and consistently.

    There are four ways that you can associate validators with your types:

    • You can use configuration.
    • You can use attributes.
    • You can use a combination of configuration and attributes.
    • You can use self validation, which means that you include validation logic within the object you want to validate.

    The Validation Application Block contains a wide range of validators that you can use to check almost any type of property or value. The built-in validators include:

    • Range Validators –
      • Range Validator – checks that a value falls within a specified range. The range may be either closed, which means it has both a lower and an upper bound specified, or open, which means that it only has one bound specified. The Range Validator can be used with any type that implements the IComparable interface. This includes all numeric types and strings. While it is possible, in code, to use this validator with DateTime types, the Date Time Range Validator may be a better choice because it allows you to take advantage of attributes and configuration.
      • Date Time Range Validator – checks that a DateTime object falls within the specified range.
      • String Length Validator – checks that the length of the string is within the specified range. The range may include or exclude the endpoints by omitting the lower or upper bound.
    • String Validators
      • Contains Characters Validator – checks that an arbitrary string, such as a string entered by a user in a Web form, contains any or all of the characters that are specified by the CharacterSet property.
      • Regular Expression Validator – checks that the value matches the pattern specified by a regular expression.
      • Type Conversion Validator – checks that a string can be converted to a specific type. For example, the validator can check that "6.32" can be converted to a Double type or that "2007-02-09" can be converted to a DateTime type.
    • Set Validators
      • Domain Validator – checks that a value is one of the specified values in a specified set. For example, it can check that a name is "Tom," "Dick," "Harry," or "George" or that an integer is 2, 3, 5, 7, or 11. If the set only contains one value, you can use this validator to check for equality.
      • Enum Conversion Validator – checks that a string can be converted to a value in a specified enum type. For example, it can check that "Blue" can be converted to a value in the Color enumeration.
    • Comparison Validators
      • Property Comparison Validator – compares the value to be checked with the value of a property. For example, a n AuctionItem object, the current bid is greater than or equal to the minimum bid.
      • Relative Date Time Validator – checks that the DateTime value falls within a specified range using relative times and dates.
    • Object Validators
      • Not Null Validator – checks that the value is not null.
      • Object Validator – causes validation to occur on an object reference. All validators defined for the object’s type will be invoked, just as if the Validation.Validate method had been called on the object. If the object you want to validate is null, the validation is ignored. If the reference is to an instance of a type that is not compatible with the configured target’s type, the validation fails. This validator is helpful for validating tree-structured data.
      • Object Collection Validator – checks that the object is a collection of the specified type and then invokes validation on each element of the collection. If the object you want to validate is null, the validation is ignored. If the object you want to validate is not a collection, then the validation fails and the rule set is not applied. If there are elements in the collection that are of a different type than the one you specified for the object, then the validation for these elements fails but this does not affect validation for the other elements.

    The Validation Application Block also provides the following composite validators which allow you to combine other validators by nesting them within this type of validator to create complex validation rules.

    • And Composite Validator – requires that all the validators that make up the composite validator be true. For example, you can use the And Composite Validator to require that the Not Null Validator AND the Date Time Range Validator be True. Because the Validation Application Block’s default behavior is to AND validators, you only need this validator to implement complex logic.
    • Or Composite Validator – requires that at least one of the validators that make up the composite validator be True. For example, you can use the Or Composite Validator to require that the Not Null Validator OR the Date Time Range Validator be True.

    Each validator also has a Negated property, which reverses the operation of the validator so that it returns False if the value is valid, and True if not. This is useful when you combine validators using the And Composite Validator and the Or Composite Validator.

    Assume that our customer entity is as follows and we want to validate the fields within this object for some business rules. For example, Postal Code format nnnnn[-nnnn] etc.

    public class Customer { public string Name { get; set; } public string Address { get; set; } public string City { get; set; } public string PostalCode { get; set; } public int EmployeeCount { get; set; } public DateTime LastReportDate{ get; set; } }

    The first step when using the Validation Application Block is to create rule set(s) for our type / object. To create a rule set for an object, you must first create and compile that assembly. Once you have a target object class, open the configuration file for your application in the Configuration Editor integrated within Visual Studio by right-clicking on the Web.config file in the Solution Explorer window and select Edit Enterprise Library Configuration.

    In the configuration editor, right click on the application node and select New, then click Validation Application Block to add the Validation Application Block to your application. Then right-click on the new Validation Application Block node, select New, and click Type. In the Type Selector dialog, click the Load an assembly button and navigate to the folder containing your target object. Click Open to load this assembly. Find your target assembly in the list and select the target class to which you want to attach a rule set.

    Click OK, and the class appears in the configuration under the Validation Application Block node. Now you are ready to create the rule set.

    Right-click on the new target object node in the configuration editor, select New, and click Rule Set. Change the name of the rule set as required – you will use the name in your code so you should specify a name that will help you to read and debug your code, especially if you define more than one rule set.

    The next stage is to select the members of the target object that you want to validate. Right-click on the new rule set node, select New, and click Choose Members to display a dialog containing all the public members of the target object. Select the required members and click OK to close the dialog and add these members to the rule set.

    You now have a hierarchy that contains the list of members to which you will add validators. For each target object member, right-click on it, select New, and click the type of validator you want to add. As you add each one, use the Properties window in Visual Studio to set the properties you require for each validator.

    For example, the CompanyAddress property has a String Length Validator attached with the following properties set:

    <validation> <type assemblyName="TestApp, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" name="TestApp.Customer"> <ruleset name="ExampleRuleSet"> <properties> <property name="Name" > <validator lowerBound="5" lowerBoundType="Inclusive" upperBound="100" upperBoundType="Inclusive" negated="false" messageTemplate="Customer address must be between 5 and 100 characters" messageTemplateResourceName="" messageTemplateResourceType="" tag="CustomerAddressValidator" type="Microsoft.Practices.EnterpriseLibrary.Validation.Validators.StringLengthValidator, Microsoft.Practices.EnterpriseLibrary.Validation, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" name="String Length Validator"/> </property> <property name="Address"> <validator lowerBound="5" lowerBoundType="Inclusive" upperBound="100" upperBoundType="Inclusive" negated="false" messageTemplate="Customer address must be between 5 and 100 characters" messageTemplateResourceName="" messageTemplateResourceType="" tag="CustomerAddressValidator" type="Microsoft.Practices.EnterpriseLibrary.Validation.Validators.StringLengthValidator, Microsoft.Practices.EnterpriseLibrary.Validation, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" name="String Length Validator"/> </property> <property name="City"> <validator lowerBound="2" lowerBoundType="Inclusive" upperBound="30" upperBoundType="Inclusive" negated="false" messageTemplate="City name must be between 2 and 30 characters" messageTemplateResourceName="" messageTemplateResourceType="" tag="CustomerCityValidator" type="Microsoft.Practices.EnterpriseLibrary.Validation.Validators.StringLengthValidator, Microsoft.Practices.EnterpriseLibrary.Validation, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" name="String Length Validator"/> </property> <property name="PostalCode"> <validator pattern="\d{5}(-\d{4})?" options="None" patternResourceName="" patternResourceType="" messageTemplate="Postal code must be in the form nnnnn[-nnnn]" messageTemplateResourceName="" messageTemplateResourceType="" tag="CustomerPostalCodeValidator" type="Microsoft.Practices.EnterpriseLibrary.Validation.Validators.RegexValidator, Microsoft.Practices.EnterpriseLibrary.Validation, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" name="Regex Validator"/> </property> <property name="EmployeeCount"> <validator lowerBound="1" lowerBoundType="Inclusive" upperBound="100" upperBoundType="Inclusive" negated="false" messageTemplate="Employee count must be between 1 and 100" messageTemplateResourceName="" messageTemplateResourceType="" tag="EmployeeCountValidator" type="Microsoft.Practices.EnterpriseLibrary.Validation.Validators.RangeValidator, Microsoft.Practices.EnterpriseLibrary.Validation, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" name="Range Validator"/> </property> <property name="LastReportDate"> <validator lowerBound="2007-01-01" lowerBoundType="Inclusive" upperBound="2008-03-28" upperBoundType="Inclusive" negated="false" messageTemplate="Report Date must be between 1st Jan and 31st Dec 2007" messageTemplateResourceName="" messageTemplateResourceType="" tag="LastReportDateValidator" type="Microsoft.Practices.EnterpriseLibrary.Validation.Validators.DateTimeRangeValidator, Microsoft.Practices.EnterpriseLibrary.Validation, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" name="Date Range Validator"/> </property> </properties> </ruleset> </type> </validation>

    ASP.NET integration uses a class named PropertyProxyValidator, which implements a control that you can use within an ASP.NET Web page to perform UI validation. This control inherits from the ASP.NET validation control base class named BaseValidator, and is therefore effectively an ASP.NET validation control.

    The BaseValidator class exposes properties such as ControlToValidate, Display (None, Static, Dynamic), EnableClientScript, Enabled, IsValid, SetFocusOnError, and ValidationGroup. The PropertyProxyValidator class also exposes the Validate method, which you can use to initiate validation of specific validators if required. However, all the instances of the PropertyProxyValidator class you place in a page integrate with the ASP.NET validation system, and so you can cause validation by calling the Validate method of the Page class, and check the result using the IsValid property of the Page class – this does not occur automatically.

    By integrating the Validation Application Block with your applications, you can reuse your validation rules across several levels of your system architecture. Integration allows you reuse the validators that are associated with your application classes when you perform validation at the user-interface level for ASP.NET . The integration provided by the Validation Application Block at the user-interface level does the following:

    • It provides a way to associate properties of validated application objects with user-interface controls.
    • It provides a way to convert values from an input data type to an application-specific data type. For example, you can convert a string input to a System.DateTime value.
    • It helps avoid coding errors in type names and property names. For example, it throws exceptions when type names or property names cannot be found.
    • It does not require instances of application objects to be created in order for validators to be invoked at the user-interface level. In other words, you do not need to instantiate a Customer object just to check whether an input string entered by the user meets the validation requirements of the Customer.Name property.

    To use the PropertyProxyValidator class, you simply add instances to the ASP.NET page, either by declaring them within the .aspx file or by generating instances dynamically at runtime and adding them to the Controls collection of the Page object or another container control. The process is identical to adding ASP.NET built-in validation controls such as the RequiredFieldValidator or any other ASP.NET control) to a page.

    The only real differences when working with the Validation Application Block compared to the ASP.NET validation controls are:

    • You must include an @Register directive in the ASP.NET page to register the integration assembly that contains the PropertyProxyValidator control:
      <%@ Register Assembly="Microsoft.Practices.EnterpriseLibrary.Validation.Integration.AspNet" Namespace="Microsoft.Practices.EnterpriseLibrary.Validation.Integration.AspNet" TagPrefix="EntLibValidators" %>
    • You use only one type of validation control, the PropertyProxyValidator control, for all validation operations. The actual operation and parameters appear in the configuration file or in attributes applied to the target object class and its members. You should have a target object class defined that supports validation through configuration or attributes.
    • You specify the name of the rule set, the type name of the target object, and the target object member (method or property name) in the PropertyProxyValidator control declaration. For example:
    <EntLibValidators:PropertyProxyValidator ID="CustomerNameValidator" runat="server" ControlToValidate="txtName" PropertyName="Name" RulesetName="ExampleRuleSet" SourceTypeName="TestApp.Customer" EnableViewState="false"/>

    For each validated UI input control, specify that input control’s ID property as the ControlToValidate property of the PropertyProxyValidator control. Also set the RulesetName, SourceTypeName, and PropertyName properties to the appropriate values based on your application configuration.

    Our test application displays textboxes for the six properties of the target object. If you click the Submit button, after entering values in the text boxes, the code creates an instance of the target class with valid values, performs validation on the object, and displays the property values and the validation result in the page.

    If you now edit the values in the text boxes so that they are outside the range of valid values, and click the Submit button again, the page displays a list of errors it encounters when validating the new object instance.

    For each invalid property value, the page displays the contents of the MessageTemplate for the validator, the name of the target property, the value of the Tag property of the validator, and the type name of the target object.

    To validate the UI control values, add code to the handler for the button or other control that submits the page. A separate routine named CreateLocalCompanyObject performs the task of applying the validated UI values to a new instance of the target object, and displaying its properties in the page:

    // force validators to check control values Page.Validate(); if (Page.IsValid) { // create sample object instance and set properties CreateLocalCustomerObject(); } else { lblResult.Text = "Validation errors encountered by ASP.NET Integration Validators<p />"; }
    private void CreateLocalCustomerObject() { // create sample object instance and set properties TestApp.Customer objCustomer = new TestApp.Customer(); objCustomer.Name = txtName.Text; objCustomer.Address = txtAddress.Text; objCustomer.City = txtCity.Text; objCustomer.PostalCode = txtPostalCode.Text; objCustomer.EmployeeCount = Int32.Parse(txtEmployees.Text); objCustomer.LastReportDate = DateTime.Parse(txtReportDate.Text); // display and validate property values DisplayAndValidateSampleObject(objCustomer); }

    Unlike the ASP.NET UI validation controls, which react to an event in the ASP.NET page cycle to perform validation, the Validation Application Block validators must be explicitly executed by calling the static Validate method of the block. The easiest way to do this is to use the Validation façade. The façade allows you to you to create a Validator instance and validate an object with a single line of code. This is shown in the following code example.

    Customer cust = new Customer(); ValidationResults results = Validation.Validate<Customer>(cust, "rulesetname");

    If you do not want to use the façade, you can use the following code, which is the equivalent.

    Customer cust = new Customer(); Validator<Customer> validator = ValidationFactory.CreateValidator<Customer>("rulesetname"); ValidationResults results = validator.Validate(cust);

    Use the CreateValidator method on the ValidationFactory class to first get a reference to the Validator instance, and then use the Validate method to validate the object.

    The Validate method of the Validation façade accepts as parameters the name of the object to validate, and optionally the names of the validation rule sets to apply. For example, if you have only a single rule set named RuleSetA defined in the configuration of the application, you can apply this rule set to an object named customer using the following code.

    ValidationResults results = Validation.Validate(cust, "RuleSetA");

    If you have two rule sets named RuleSetA and RuleSetB defined in the configuration of the application, you can apply the rules from both using the following code.

    ValidationResults results = Validation.Validate(cust, "RuleSetA", "RuleSetB");

    The Validate method returns an instance of the ValidationResults class, which is a collection containing a ValidationResult instance for each validation failure. The ValidationResult class exposes properties that allow you to obtain the value of the Key (the member name if available), the Message describing the validation error, the value of the validator Tag property, the type name of the Target object, and the name of the Validator. There is also the NestedValidationResults property, which exposes a ValidationResults collection containing the validation errors for any nested validators, when using a composite validator.

    Our DisplayAndValidateSampleObject() function calls the Validate method, and then checks if there were any validation errors by examining the IsValid property of the returned ValidationResults collection. If there are errors, the code iterates through the collection adding the values of the Key, Tag, and Target properties of each ValidationResults instance to the StringBuilder. If there are no errors, it adds a suitable message to the StringBuilder instead. Finally, the code displays the contents of the StringBuilder in a Label control on the page:

    ... // validate the property values using the configured rule set ValidationResults results; results = Validation.Validate(targetObject, "ExampleRuleSet"); // get validation results if (!results.IsValid) { builder.Append("<b>Validation Errors:</b>:<br />"); foreach (ValidationResult result in results) { builder.Append(String.Format("- <b>{0}</b><br />", result.Message)); builder.Append(String.Format("&nbsp; (Key: {0}, ", result.Key.ToString())); builder.Append(String.Format("Tag: {0}, ", result.Tag)); builder.Append(String.Format("Target: {0})<br />", result.Target.ToString())); } } else { builder.Append("All object property values are valid."); } // display results in Label control lblResult.Text = builder.ToString(); ...

    It is always a good idea to repeat on the server any UI validation you carry out before using posted values in your application. This protects against spoofing attacks; and against issues that may arise is client browsers do not fully support or correctly process client-side script. However, unlike the ASP.NET validation controls, the PropertyProxyValidator controls only perform validation server-side, and not through client-side script.

    Using the Enterprise Library Validation Application Block in ASP.NET applications allows concentrating on the use of rule sets that allow validation across the UI and object instances within the application tiers.

    While ASP.NET provides a fully-featured set of UI validation controls, these do not always fulfill the requirements of enterprise-level applications. The Validation Application Block supports a wider range of validators, has features to allow composition and negation of validators, and supports validation using configuration rules as well as attributes applied directly to classes and class members.

    One should combine the Validation Application Block validators with the ASP.NET built-in validators as required. The PropertyProxyValidator control that supports ASP.NET integration is itself a descendant of the ASP.NET validator base class, and so behaves and can be handled in the same way as the ASP.NET validation controls.