# Monday, 16 April 2012
Monday, 16 April 2012 16:02:00 (GMT Daylight Time, UTC+01:00)
# Friday, 13 April 2012

SQL Injection  is one of the most frequently-exploited vulnerabilities in the software world. It refers to user-entered data making its way into commands sent to back-end systems. It is common because so many developers are unaware of the risk and how to mitigate it.

Most of the applications I work with read from and write to a relational database, such as Microsoft SQL Server.  I frequently run across ADO.NET code like the following:

string lastName = "'Adams'";
string sql = "Select * from dbo.Customer where LastName = '" + lastName + "'";
string connString = ConfigurationManager.ConnectionStrings["LocalConn"].ConnectionString;
using (var conn = new SqlConnection(connString))
{
    conn.Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = sql;
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine("Bad Name: {0} {1}", reader["FirstName"], reader["LastName"]);
    }
}

This code is designed to call a stored procedure like the following:

CREATE PROCEDURE [dbo].[GetCustomersByFirstName]
    @FirstName NVARCHAR(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;

    SELECT 
            Id, 
            FirstName, 
            LastName
        FROM dbo.Customer
        WHERE FirstName = @FirstName
        ORDER BY Id
END

GO

This method of code has several disadvantages

  1. This code is not optimal because SQL Server does not have a chance to reuse a cached query plan unless the user happens to send the exact same text into SQL Server.
  2. The string concatenation opens the system to SQL Injection attacks.

A SQL Injection Attack is an attempt by an unscrupulous user to pass malicious commands to a database. In the above example, imagine that the variable x was provided by a user inputting text into a text box on a web age. An evil user might type something like

"Smith';DROP TABLE Customer;//"

If that code runs with sufficient permissions, it would wreak havoc on your database. The following query would be passed to SQL Server.
Select * from dbo.Customer where LastName = 'Smith';DROP Table Customer;//'

Clearly, dropping the customer table is not what your code is intended to do.

Many of you will read the above example and decide that you are safe because

  1. Your web code runs under a context with insufficient privileges to drop a table; and
  2. You are validating all user inputs to ensure a user cannot enter anything bad.

There are problems with this reasoning.

  1. A clever hacker can sometimes trick a user into running code under elevated privileges. Often there are multiple steps to an attack.
  2. Even if you have caught every possible injection possibility in your user interface, you cannot guarantee that every call to this API will be made only from your UI for all eternity. You may open up the API to the public or you may subcontract writing a mobile application that calls this API or you may hire a new programmer who doesn't know better.

The point is that you need to check security at every level of your application. And part of checking security is to not trust your inputs.

A far better approach than concatenating strings to form a SQL statement is to create parameter instances; set the value of each parameter; and add these parameters to a Parameters collection.

The code below shows how to do this.

string lastName = "Adams";
string sql = "Select * from dbo.Customer where LastName = @LastName";
string connString = ConfigurationManager.ConnectionStrings["LocalConn"].ConnectionString;
using (var conn = new SqlConnection(connString))
{
    conn.Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = sql;
    SqlParameter lnParam = cmd.CreateParameter();
    lnParam.ParameterName = "@LastName";
    lnParam.Value = lastName;
    cmd.Parameters.Add(lnParam);
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine("Good Name: {0} {1}", reader["FirstName"], reader["LastName"]);
    }
    Console.WriteLine();

Pass an unexpected parameter here and it will no t be executed on the end of the query because SQL Server is expecting a parameter for a specific use.

The same pattern works if I want to pass in a dynamic string of SQL. Passing Parameter instances is more secure than concatenating SQL and passing that string to SQL Server.

Below is a console application that uses the vulnerable string concatenation method to call SQL Server via ADO.NET

using System;
using System.Configuration;
using System.Data.SqlClient;

namespace PassingSql_WrongWay
{
    class Program
    {
        static void Main(string[] args)
        {
            CallSqlQuery();
            CallStoredProc();
            Console.ReadLine();
        }

        private static void CallSqlQuery()
        {
            string lastName = "'Adams'";
            //string lastName = "Adams';DROP TABLE dbo.ExtraTable;--";
            string sql = "Select * from dbo.Customer where LastName = '" + lastName + "'";
            string connString = ConfigurationManager.ConnectionStrings["LocalConn"].ConnectionString;
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("Bad Name: {0} {1}", reader["FirstName"], reader["LastName"]);
                }
            }
            Console.WriteLine();
        }

        private static void CallStoredProc()
        {
            string firstName = "James";
            string sql = "EXEC GetCustomersByFirstName '" + firstName + "'";
            string connString = ConfigurationManager.ConnectionStrings["LocalConn"].ConnectionString;
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("Bad Name: {0} {1}", reader["FirstName"], reader["LastName"]);
                }
                Console.WriteLine();
            }
        }
    }
}

Below is a similar console app, using the more secure parameters pattern

using System;
using System.Configuration;
using System.Data.SqlClient;

namespace PassingSql_RightWay
{
    class Program
    {
        static void Main(string[] args)
        {
            CallSqlQuery();
            CallStoredProc();
            Console.ReadLine();
        }

        private static void CallSqlQuery()
        {
            string lastName = "Adams";
            //string lastName = "Adams;DROP TABLE dbo.ExtraTable;--";
            string sql = "Select * from dbo.Customer where LastName = @LastName";
            string connString = ConfigurationManager.ConnectionStrings["LocalConn"].ConnectionString;
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                SqlParameter lnParam = cmd.CreateParameter();
                lnParam.ParameterName = "@LastName";
                lnParam.Value = lastName;
                cmd.Parameters.Add(lnParam);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("Good Name: {0} {1}", reader["FirstName"], reader["LastName"]);
                }
                Console.WriteLine();
            }
        }

        private static void CallStoredProc()
        {
            string firstName = "James";
            string storedProcName = "GetCustomersByFirstName";
            string connString = ConfigurationManager.ConnectionStrings["LocalConn"].ConnectionString;
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = storedProcName;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlParameter lnParam = cmd.CreateParameter();
                lnParam.ParameterName = "@FirstName";
                lnParam.Value = firstName;
                cmd.Parameters.Add(lnParam);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("Good Name: {0} {1}", reader["FirstName"], reader["LastName"]);
                }
                Console.WriteLine();
            }
        }
    }
}

If you wish to use the above code, create a new database named TestData and run the following SQL DDL to create the database objects.

USE [TestData]
GO

/****** Object:  Table [dbo].[ExtraTable]    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ExtraTable](
    [foo] [nchar](10) NULL,
    [bar] [nchar](10) NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Customer]    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.Customer (FirstName, LastName) VALUES ('George', 'Washington') 
GO 
INSERT INTO dbo.Customer (FirstName, LastName) VALUES ('John', 'Adams') 
GO 
INSERT INTO dbo.Customer (FirstName, LastName) VALUES ('Thomas', 'Jefferson') 
GO 
INSERT INTO dbo.Customer (FirstName, LastName) VALUES ('James', 'Madison') 
GO 
INSERT INTO dbo.Customer (FirstName, LastName) VALUES ('James', 'Monroe') 
GO 
INSERT INTO dbo.Customer (FirstName, LastName) VALUES ('John Quincy', 'Adams') 
GO 

/****** Object:  StoredProcedure [dbo].[GetCustomersByFirstName]   
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetCustomersByFirstName]
    @FirstName NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
            Id, 
            FirstName, 
            LastName
        FROM dbo.Customer
        WHERE FirstName = @FirstName
        ORDER BY Id
END
GO

With a little bit of thought and a few lines of code, you can significantly reduce the risk of SQL injection in your ADO.NET code.

.Net | C# | SQL Server
Friday, 13 April 2012 00:13:00 (GMT Daylight Time, UTC+01:00)
# Wednesday, 11 April 2012

As always, there will be a lot of developer conferences and other events this summer in the my region. Here is a partial list of conferences that have been announced in or near the Heartland region in the coming months. Please let me know if I have missed any.

What Where When
Kalamazoo X Kalamazoo, MI Apr 21
Stir Trek Columbus, OH May 4
Great Lakes Functional Programming Conference Ann Arbor, MI May 5
Cincinnati Day of Agile Westchester, OH May 19
Pittsburgh Tech Fest North Hills, PA Jun 9
CodeStock Knoxville, TN Jun 15-16
CloudDevelop Columbus, OH Aug 3
That Conference Wisconsin Dells, WI Aug 15-16
DevLink Chattanooga, TN Aug 29-31
Windows Azure Kick-Start Multiple cities Multiple dates
Windows 8 Developer Camps Multiple cities Multiple dates
Wednesday, 11 April 2012 15:37:00 (GMT Daylight Time, UTC+01:00)
# Monday, 09 April 2012
Monday, 09 April 2012 15:03:00 (GMT Daylight Time, UTC+01:00)
# Tuesday, 03 April 2012

Saturday, I spoke at the Orlando Code Camp at Seminole State College, just east of Orlando, FL. This was the seventh year of the Code Camp but my first time attending.

The first session I attended was Memory Management Fundamentals – Garbage Collection Deep Dive by Scott Dorman.
Scott explained the way memory management works under the hood.
Key points: 
The garbage collector takes care of cleaning up objects when they are no longer needed.
It's generally not advisable to implement a finalizer.
If your machine has plenty of memory, garbage collection might not occur until the user exits the app.
Larger objects are cued up for cleanup.
Here is a list of resources
http://geekswithblogs.net/sdorman/archive/2008/09/14/.net-memory-management-ndash-resources.aspx

The next session I attended was Creating a HTML5 WinRT application by Brian Kassay
You can build Windows 8 applications in either HTML5, JavaScript, and CSS3 or in XAML. This session focused on HTML5, JavaScript, and CSS3. In order to work with this, one needs to install Windows 8 and Visual Studio 11 (both are in beta).

I planned to see Richie Rump's Entity Framework - Code First and Magic Unicorns session, but others had the same idea and the room was packed. Rather than stand for an hour, I opted to hear Greg Leonardo's Line of Business development with MVC3.
This was a basic overview of how to use MVC. It consisted of more slides than demos.
He did explain how MVC's Anti-forgery library works (issues a token to the user with a response and checks for that token in subsequent requests).
He also discussed the MVC Anti-XSS library, which is designed to protect your site against cross-site scripting errors. By default, MVC disallows HTML input by. If you decide to allow HTML input, it is important to scrub input with the Anti-XSS library.

The final session I attended was Elijah Manor on Exterminating Those Common Pesky jQuery Bugs
Elijah went through a series of common JavaScript mistakes made by developers and showed ways to correct them.

I delivered a session on Visual Studio 2010 Database Tools. It was very well received and the audience asked lots of questions.

The conference was organized by local user group leaders, including Esteban Garcia, a fellow Telerik insider (Telerik sponsored my trip, BTW). The organizers did a very good job on this conference and everything ran smoothly.
The one drawback of this event was the lack of an obvious common area, where attendees could talk and meet one another. When it was over, we regrouped at a local pub, which gave me the opportunity to meet many of the local developer community. This was particularly important to me at this conference because I only knew about 5 people among the speakers and attendees. One of the reasons I came down to Orlando was for a chance to meet people in the local Florida communities. It turns out that Orlando, Sarasota, and South Florida have very vibrant communities based on the enthusiasm of those I spoke with.

Of course I recorded a couple episodes of Technology and Friends. Elijah Manor and Max Trinidad agreed to go on camera to discuss JavaScript and Powershell respectively.

The Orlando Code Camp will take place again next year and I'm seriously considering making this an annual trip. I also heard of a few smaller events in the area which might bring me back here.

Tuesday, 03 April 2012 15:46:00 (GMT Daylight Time, UTC+01:00)
# Monday, 02 April 2012
Monday, 02 April 2012 15:49:00 (GMT Daylight Time, UTC+01:00)
# Wednesday, 28 March 2012

The Detroit Day of Azure is history and I had a blast. On March 24, a group of volunteers and I organized the first Detroit Day of Azure, with the idea of sharing knowledge about Microsoft’s cloud computing platform and related technologies.

Here are a few of the high points from the conference:

We sold out our 144 tickets and about 130 people showed up for the Saturday event in Southfield.

The speakers were amazing. They traveled from Texas, Minnesota, Indiana, Chicago, Ohio, and Kentucky to share their knowledge with us. The feedback I heard was all positive. I was fortunate to get such a talented group of presenters.

We ran 3 rooms simultaneously. The presentation in the large room was streamed live all day, so people who could not make the conference could enjoy the presentations.

We ran a Q&A Panel at lunch time titled "Ask the Experts". Nearly all the speakers participated and the audience got to ask  them questions about Windows Azure. We cut this off after about 50 minutes, but could easily have gone twice as long.

Everyone loved the food. Lunch was from a local barbeque restaurant and featured some outstanding brisket, pulled pork, and chicken, along with some tasty side dishes. We also provided continental breakfast and an afternoon snack, both of which disappeared in a hurry.

Most of the sessions were recorded, thanks to volunteers Jim Priore, Falicia Starr, and Max Friend, who manned the cameras. I'll get these online in the next few weeks.

The volunteers were fantastic. People were proactive about seeing what needed to get done and jumping in and doing it. I had very little to manage on the day of the event.

Speakers and volunteers retreated to Copper Canyon Brew Pub for dinner afterwards. It was nice to wind down with friends after a busy day.

Here are a few things we could have done better:

I couldn't find the wireless microphone for the lunchtime panel, which meant only half the room could hear them well.

I ordered way too much barbeque and we ended up with more leftovers than I would have liked.

But those are small things and I was overall pleased with how everything turned out.

I need to catch my breath before thinking about the next event.


Photos: http://giard.smugmug.com/Tech-Community/2012-Detroit-Day-of-Azure/

Wednesday, 28 March 2012 19:39:00 (GMT Daylight Time, UTC+01:00)
# Monday, 26 March 2012
Monday, 26 March 2012 17:15:00 (GMT Daylight Time, UTC+01:00)
# Friday, 23 March 2012
Friday, 23 March 2012 14:24:00 (GMT Standard Time, UTC+00:00)
# Tuesday, 20 March 2012

I have the following presentations scheduled

On March 20 (tonight), I will present Persistence In The Cloud: How to Use Azure Storage at the 24 Hours of Pass online conference. Here is a link.

On March 24, I will present Persistence In The Cloud: How to Use Azure Storage at the Detroit Day of Azure in Southfield, MI (http://DetroitDayOfAzure.com).

On March 31, I will present Using the Database Tools of Visual Studio 2010 at the Orlando Code Camp in Orlando, FL (http://orlandocodecamp.com/).

On May 4, I will present Data Visualization: The Ideas of Edward Tufte at Stir Trek in Columbus, OH. (http://stirtrek.com/).

Tuesday, 20 March 2012 23:30:00 (GMT Standard Time, UTC+00:00)