EntLib extension to execute queries with less code

For internal security training of SDX developers I’m currently preparing a really small web-application with different vulnerabilities. The goal is to have an application that does have all of the OWASP Top 10, so that I can use it to make my colleagues search them and discuss mitigations.

One of the problems I want to include is of course SQL injection. Since my favorite way of accessing data is the DAAB of the Enterprise Library, I did use it in this project, too. When I came to SQL injection, I realized that I cannot use SPROCs as usual, but need to include the SQL statements into my code – using SPROCs does usually include the usage of SQL parameters, which does protect you (as long as you don’t construct SQL manually inside the SPROC) from injections issues.

This way I came across the issue of using parameters with SQL statements in DAAB. This is not really an “issue” as long as you accept to create a command object, then use the database object to add the parameters (one line of code per parameter) and finally execute the command to get the result. But I don’t like to repeat such steps and I wanted to use something more “elegant”, so I wrote a little extension method for the Database object of EntLib:

/// <summary>

/// The same as <see cref="Database.ExecuteScalar(System.Data.Common.DbCommand)"/>, but adds the properties of 

/// <paramref name="parameters"/> as parameters to the query.

/// </summary>

/// <param name="db"> The database. </param>

/// <param name="sql"> The SQL statement containing parameters. </param>

/// <param name="parameters"> The parameters object (might be of anonymous type). </param>

/// <typeparam name="T">The type of the result value.</typeparam>

/// <returns>The result of the query.</returns>

public static T ExecuteScalarWithParameters<T>(this Database db, string sql, object parameters)

{

    var cmd = db.GetSqlStringCommand(sql);

    AddParameters(db, cmd, parameters);

 

    return (T)db.ExecuteScalar(cmd);

}

 

/// <summary>

/// Adds parameters to the <paramref name="cmd"/> based on the properties of <paramref name="parameters"/>.

/// </summary>

/// <param name="db"> The database. </param>

/// <param name="cmd"> The command to add the parameters to. </param>

/// <param name="parameters"> The parameters object (might be of anonymous type). </param>

private static void AddParameters(Database db, DbCommand cmd, object parameters)

{

    foreach (var parameter in parameters.GetType().GetProperties())

    {

        var propertyType = parameter.PropertyType;

        var type = propertyType == typeof(string) ? DbType.String

                 : propertyType == typeof(int) ? DbType.Int32

                 : propertyType == typeof(Guid) ? DbType.Guid 

                 : DbType.Object;

 

        var name = parameter.Name;

        var value = parameter.GetValue(parameters);

 

        db.AddInParameter(cmd, "@" + name, type, value);

    }

}

As you can see it inspects a value of type “object” in order to use its properties to build up the parameters. This way you can use anonymous types to pass the parameters to the SQL statement:

private string CustomerIdByName(string customerName)

{

    const string Sql = "SELECT TOP 1 Id AS AspNetUserId " + 

                       "FROM AspNetUsers " + 

                       "WHERE UserName = @userName";

    var database = new SqlDatabase(this.ConnectionString);

    return database.ExecuteScalarWithParameters<string>(

        Sql, new { userName = customerName });

}

I don’t think this is a really new invention, but it may be useful for you, so I want to share it.

Be aware that this code is far from being perfect: currently there’s no error handling, no input validation to guide the developer, it only handles strings, integers and GUIDs, etc., so you might not use it “as is” in production code. But since I did not find an example how to do this with Google, it might be the code snippet giving you an idea on how to keep your application code even smaller.

Advertisements

One Response to EntLib extension to execute queries with less code

  1. ajdotnet says:

    Just the other days I had to look into two (independent) projects to provide some review and coaching. One a commercial 3rd party application, the other provided by people actually providing guidance for other developers.
    Both contained (among other code smells) code that was littered with details on how to achive certain tasks, many of those lines repeating itself. The net result is that these methods may do their job, but they make it very hard to determine what their job is in the first place.

    While your particular helper method may not be rocket science, it shows what I call “the power of helper methods” quite clearly: It makes the calling method readable! Instead of hiding what is actually “semantically” done under a bunch of lines dealing with each detail at once, it clearly states what it is doing, without cluttering the method – I can read this method and understand it immediately.

    Given that said applications cannot be dismissed with “what do you expect?”, this is a lesson far too many developers still have to learn.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Sylvio's Infobox

Aktuelle Themen rund um SQL Server, BI, Windows, ...

Meredith Lewis

Professional Digital Portfolio

Vittorio Bertocci

Just another WordPress.com weblog

ScottGu's Blog

Just another WordPress.com weblog

AJ's blog

Thoughts and informations I think worthwhile to share...

Outlawtrail - .NET Development

Architecture & Design

SDX eXperts Flurfunk

Just another WordPress.com weblog

%d bloggers like this: