Microsoft Urlscan Helps to Filter SQL Injection Attacks

by

Microsoft recently re-released an improved version of a security filter for IIS that is designed to help thwart SQL injection attacks by restricting the types of HTTP requests that IIS will process. UrlScan 3.0 is an IIS add-on that provides real-time validation of HTTP server requests, potentially blocking SQL injection exploits.

UrlScan has actually been available for several years, but Microsoft added some new features in this 3.0 release, including support for query string scanning.

Determining Poorly Performing Queries for Tuning from SQL Server Workload Trace Files

by

Whenever you gather workload traces to identify poorly performing queries, you need to import this data into a database table, and to “normalise” and aggregate this information to identify the worst offenders. This can be done in a variety of ways. One way is to define a regular expression such as this SQL CLR method based on work done by Itzik Ben-Gan and modified by Adam Machanic:

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
public static SqlString sqlsig(SqlString querystring)
{
    return (SqlString)Regex.Replace(
       querystring.Value,
       @"([\s,(=!](?![^\]]+[\]]))(?:(?:(?:(?:(?# expression coming
       )(?:([N])?(')(?:[^']'')*('))(?# character
       )(?:0x[\da-fA-F]*)(?# binary
       )(?:[-+]?(?:(?:[\d]*\.[\d]*[\d]+)(?# precise number
       )(?:[eE]?[\d]*)))(?# imprecise number
       )(?:[~]?[-+]?(?:[\d]+))(?# integer
       )(?:[nN][uU][lL][lL])(?# null
       ))(?:[\s]?[\+\-\*\/\%\&\\^][\s]?)?)+(?# operators
       )))",
       @"123#4");
}

Recently I’ve been trying out ClearTrace, a free tool based around Read80Trace (described and downloadable here). Read80Trace was originally part of a Microsoft PSS engineer’s internal toolkit, but was released to the public in Dec 2007 (RML Utilities for SQL Server). ClearTrace is extremely simple to use, imports files (including rollover) very quickly and the results are good. The project is being supported so if you find a SQL statement that isn’t normalised/parameterised correctly, you can click a button and report it.

The larger RML Utilities toolkit for Microsoft SQL Server was released here.

The RML Utilities can help you answer the following questions:

  • Which application, database or login is consuming the most resources, and which queries are responsible for that.
  • Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed.
  • What queries are running slower in today’s data as compared to a previous set of data.

You can also test how the system will behave with some change (different service pack or hot fix build, changing a stored procedure or function, modifying or adding indexes, and so forth) by using the provided tools to replay the trace files against another instance of SQL Server. If you capture trace during this replay you can use the tools to directly compare to the original baseline capture.

If you decide to install and experiment with the RML Utilities toolkit, be warned that the tools are provided as is, and the install process is neither easy nor particularly pleasant!

All your volcano are belong to us!

by

Brian Madsen recently blogged enthusiastically about Diskeeper ‘rocking’ the virtualisation world. Did you know that the CEO of Diskeeper Corporation, Craig Jensen is an active member of the Church of Scientology? In fact, Craig attributes the success of Diskeeper Corporation to the management system created by L Ron Hubbard.

Scientology have been notoriously heavy-handed in silencing any criticism, they even got Amazon to (temporarily) take down a link to a book critical of Scientology, before free speech prevailed. Head over to xenu.net if you’re interested in finding out about some of the facts surrounding the CoS.

Premature Optimisation

by

Scott Allen has recently posted a blog entry on the pitfalls of premature optimisation; in this specific case, optimizing LINQ queries, but the advice is timeless and technology agnostic:

“The first step in optimizing any code is to take some measurements and
make sure you really have a problem.”

It’s worth reading just for the image! (Now, if only that was Donald Knuth in the devil suit…If you’re curious, check out Knuth’s story of optimising the system idle loop…).

Donald Knuth popularised a quote which is sometime attributed to him, but is actually due to Sir Tony Hoare (probably best known for the development of Quicksort), “…premature optimization is the root of all evil.” The Fallacy of Premature Optimization by Randall Hyde is worth a look over at the ACM site.