Preventing SQL Injection in BizTalk

I was reviewing a solution recently using the BizTalk WCF-OracleDB Adapter and detected an interesting security vulnerability.

Before you dismiss this as “I’m using SQL Server, I’ll be right mate”, this issue probably also affects the BizTalk WCF-SQL Adapter and while I haven’t addressed fixes for WCF-SQL specifically in this article, there may be helpful overlap.

In our case we were using plenty of code in Construct/Assign shapes to create new Oracle Select message instances. Something like this:

// Create message
InvSelectMsg = Utility.CreateMessage("OurSchemas.InvoiceSelect"); 
InvSelectMsg.COLUMN_NAMES = "INVOICE_ID"; // SELECT
InvSelectMsg.FILTER = System.String.Format("REFERENCE = '{0}'", reference); // WHERE

This looks innocuous enough, but for anyone who’s worked on web apps without an ORM (including the performance-constrained or those simply caught in the crossfire of an ORM battleground) this stands out as a clear-cut SQL Injection nightmare.

Sure enough, testing confirmed there was a vulnerability, which so far I’ve seen when:

Counter-intuitively, SQLEXECUTE messages are less likely to be affected by this issue than the “strongly-typed” message schemas.

As a side note, one of the more concerning things for me is that I’ve regularly heard lines such as “Oracle prevents SQL Injection” by people who when pushed don’t seem to know what SQL injection is. What I think they mean is that Oracle prevents multiple statement execution, which while preventing a section of SQL Injection attacks, does not make Oracle immune.

SQL Injection?

The above sample renders to some SQL like this:

SELECT INVOICE_ID FROM OracleInvoiceView WHERE REFERENCE = 'Some Invoice Ref'

But consider the case where REFERENCE includes a single quote character (e.g. Invoice for Joey's Pianos). The query is now:

SELECT INVOICE_ID FROM OracleInvoiceView WHERE REFERENCE = 'Invoice for Joey's Pianos'

You can probably see from the syntax highlighting that the query ends at Joey' and then has the random extra text Pianos' sitting after the complete query which simply causes a parse error (the best-case in a SQL Injection scenario).

But what if that query was less innocuous? What if, Joey decided to send his REFERENCE as Joey' OR 1=1 --? The query would now return every INVOICE_ID in the system, letting Joey see his competitor’s data:

--// This query shows Joey all invoices. 
--// Note the end -- to comment out the end single-quote and avoid an error 
SELECT INVOICE_ID FROM OracleInvoiceView WHERE REFERENCE = 'Joey' OR 1=1 --' 

Even worse, Joey might send '; DELETE FROM BillsOutstanding; -- which could (depending on configuration as Oracle will usually prevent multiple statement execution preventing this particular extreme case) lead to both a SELECT and DELETE being executed against the database and all of your outstanding bills being wiped from the database.

SELECT INVOICE_ID FROM OracleInvoiceView WHERE REFERENCE = ''; 
DELETE FROM BillsOutstanding; --'

If Joey was subtle enough in what was modified or deleted you might never discover there was an issue.

Exploits of a Mom

[Source: xkcd]

Why hasn’t everyone noticed this already?

I’m not sure, I have a couple of theories:

  • Everyone else was smart enough to fix this issue and thought it too obvious to mention.
  • There’s a really good article out there already that I haven’t come across yet.
  • Everyone else is using the SQLEXECUTE schemas, and only using user-input via the PARAMETERSET.
  • BizTalk is often used for integration of controlled systems where this issue might not arise. (I’d still expect it to cause errors with standard non-malicious data though).

Why doesn’t this (usually) affect SQLEXECUTE messages?

It appears that SQLEXECUTE schemas circumvent this issue by using Oracle Bind Arguments, so the filter clause sent to Oracle looks like this WHERE REFERENCE = :variable, where the :variable value is set from the PARAMETERSET of the SQLEXECUTE message. For this reason SQLEXECUTE messages are most easily created in a map:

SQLEXCEUTE BizTalk Map

These PARAMETERSET values are treated specially by Oracle to prevent injection (you’ve created a parametrised query, so the values are never executed as PL/SQL). Of course, if you generate the actual SQLSTATEMENT section of your SQLEXECUTE message using user input (for example, System.String.Format("SELECT Id FROM SomeView WHERE Column='{1}'", filterValue)) then you’re back to square-one and facing SQL Injection issues. One interesting case I haven’t tested yet is SQLEXECUTE messages built using the PARAMETERSET using wildcard/LIKE filters.

So what do I do?

There’s a range of advice out there on how to address SQL Injection in your language of choice, including:

Usually this boils down to making sure that user-input characters are always treated as user-input and not part of the actual SQL query syntax. This is usually achieved by:

  • Parametrising the query.
  • Escaping non-parametrised special characters (e.g. single quotes)
  • Ensuring non-string values match their expected data type
  • Implementation specific details (depending on the specific SQL database and client used)

For Oracle in particular some resources I came across included:

Specifically?

My solution for resolving Oracle SQL Injection in BizTalk, which is by no means fool-proof or applicable to every scenario, relies upon:

  1. Calling a utility function (to be detailed below) when setting FILTER values
  2. Ensuring that the format string provided to the utility function places single-quotes around string parameters (WHERE val = '{0}' rather than WHERE val = {0})

Some of the known shortcomings of this method, which could be addressed with some further thought are:

  1. Does not ensure that numerics (or other non-string values?) match their expected data type (relies on the calling developer to enforce this)
  2. Relies on developers to ensure their query format-string has quotes around string parameters
  3. Relies on the ToString method of your provided parameters to return the desired representation for your query value (can’t see this being an issue, but it bears noting)

At least one other alternative to my method is enforcing developers to create FILTERs with a fully parametrised class – something similar in usage to System.Data.SqlClient.SqlCommand – although you would need to ensure the implementation fully supports your target database and usage (in our case Oracle).

Out with the code already!

The most primitive bit of code is a simple string escape for single-quotes. Something like:

static string EscapeParameter(object unescapedParameter)
{
    if (unescapedParameter == null)
        return String.Empty;

     // Escape quotes
     return unescapedParameter.ToString().Replace("'", "''"); 
}

You can then wrap this up with something like:

static string EscapeWhere(string format, params object[] unescapedParameters) 
{ 
    /* Escape parameters */
    object[] escapedParameters = unescapedParameters
      .Select((param, index) => EscapeParameter(param))
      .ToArray();

    /* Return filter string with escaped parameters */
    return String.Format(format, escapedParameters);
}

This allows simple usage like this:

SelectMsg = Utility.CreateMessage("OurSchemas.InvoiceSelect"); // Create message
SelectMsg.COLUMN_NAMES = "INVOICE_ID"; // SELECT
SelectMsg.FILTER = Utility.EscapeWhere("REFERENCE = '{0}'", reference); // WHERE

However, this only supports your standard equivalence-style filters. What about wildcard filtering (WHERE val LIKE '%input%')?

Why is LIKE special?

Wildcards/LIKE statements in your WHERE clause are a special case for SQL Injection, at least in Oracle.

Imagine the case where you want to search for products starting with 20% Off:

WHERE Title LIKE '20% Off%' --// This doesn't give us what we want!

Because percent (%) is a special wildcard character when using LIKE, we won’t get the results we are after (it will match 20 Special Things About Falling Off a Horse for instance).

To address this, Oracle (like many languages) allows you to escape special characters like % with another special character. This escape character is usually a backlash (\) although in Oracle you can define your escape character by following your LIKE statement with an ESCAPE '\' statement.

You can include the actual characters % or _ in the pattern by using the ESCAPE clause, which identifies the escape character. If the escape character precedes the character % or _ in the pattern, then Oracle interprets this character literally in the pattern rather than as a special pattern-matching character. You can also search for the escape character itself by repeating it. For example, if @ is the escape character, then you can use @@ to search for @.

Source: Pattern-matching Conditions, Oracle Database SQL Reference

This means we can get what we were after like this:

WHERE Title LIKE '20\% Off%' ESCAPE '\' --// What we were after!

This is great, except now our customers have a whole new set of special characters they can provide that change the behaviour of our queries to Oracle. While this probably doesn’t leave your database open to all of the SQL Injection attacks we’ve discussed, it probably still allows your customers access to data they probably shouldn’t have.

This means if we have a parameter used in a LIKE filter we need to:

  1. Escape all special Oracle wildcard characters
  2. Escape whichever ESCAPE character we’re using
  3. Ensure all LIKE statements have a corresponding ESCAPE clause (this has to be applied per-LIKE which is something that I didn’t find clear from the docs)

The Oracle documentation lists the special wildcard characters as:

An underscore (_) in the pattern matches exactly one character (as opposed to one byte in a multibyte character set) in the value.

A percent sign (%) in the pattern can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. The pattern ‘%’ cannot match a null.

This meant I needed to update my EscapeParameter function to:

  1. Escape existing ESCAPE characters (e.g. \)
  2. Escape LIKE wildcard characters (% and _)
  3. Only do this when the parameter was being used in a LIKE clause

I also needed to make sure the EscapeWhere function was:

  1. Smart enough to work out which parameters need wildcard escaping or not.
  2. Adding in an ESCAPE clause for every LIKE clause (or rely on calling developers to remember this, and be consistent in their selection of an ESCAPE character).

Final Code

Incorporating these new requirements led to the code below.

Before you jump in remember THIS CODE PROBABLY HAS ISSUES, TEST THOROUGHLY! (this is true for any code you find online).

Updated EscapeWhere method

/// String parameters should be enclosed in single-quotes
/// Parameters of other data types (e.g. numerics) should be validated 
/// by the caller before calling this method.
/// USE WITH CAUTION AND EXTENSIVE TESTING
static string EscapeWhere(string format, params object[] unescapedParameters) 
{ 
    /* Regex for LIKE clauses
      * LIKE (Whitespace) 
      * (Single Quote) (Optional Anything But Single Quote)
      * (Start Brace) (Number) (End Brace)
      * (Optional Anything But Single Quote) (Single Quote) */ 
    string likeRegex = @"LIKE\s+'[^']?{(\d+)}[^']?'";

    /* Extract indices of parameters that need wildcard escaping */
    List<int> wildcardParameterIndices = Regex.Matches(format, likeRegex)
      .Cast<Match>()
      .Select(m => m.Groups[1]) // Select the "Decimal" group of each Match
      .Select(g => int.Parse(g.Value)) // Extract the parameter number
      .ToList();

    /* Append ESCAPES to LIKE statements. */
    format = Regex.Replace(format, likeRegex, @"$0 ESCAPE '\'");

   /* Escape parameters, including wildcard escaping where applicable */
   object[] escapedParameters = unescapedParameters
     .Select((param, index) 
        => EscapeParameter(param, wildcardParameterIndices.Contains(index)))
     .ToArray();

   /* Return WHERE filter string with escaped parameters */
   return String.Format(format, escapedParameters);
}

Updated EscapeParameter method

(Which Prettify struggles with due to C# String Literals)

/// If escapeWildcards is true, assumes parameter used in a LIKE clause
/// and also assumes an "ESCAPE '\'" statement will follow the parameter.
/// USE WITH CAUTION AND EXTENSIVE TESTING
static string EscapeParameter(object unescapedParameter, bool escapeWildcards)
{
    if (unescapedParameter == null)
        return String.Empty;

    // Escape quotes
    string escapedParameter = unescapedParameter.ToString().Replace("'", "''"); 

    // Escape wildcards when used in a `LIKE` clause
    if (escapeWildcards)
    {
        escapedParameter = escapedParameter
          .Replace(@"\", @"\\") // Escape escaping slashes (before adding more)
          .Replace("_", @"\_") // Escape underscore for LIKE statements
          .Replace("%", @"\%"); // Escape percentage for LIKE statements
    }

    return escapedParameter;
}

This allows the following usage

SelectMsg = Utility.CreateMessage("OurSchemas.InvoiceSelect"); // Create message
SelectMsg.COLUMN_NAMES = "INVOICE_ID"; // SELECT
SelectMsg.FILTER = Utility.EscapeWhere("NAME LIKE '%{0}%' AND REFERENCE = '{1}'", 
    name, reference); // WHERE

Now as I’ve mentioned, this isn’t tested for every possible scenario. Ideally I’d upload a Visual Studio Test Project with some sample tests, but for the purposes of this blog post I have instead thrown a quick script through LINQPad as Joseph Albahari has promised me an island I really like LINQPad and it’s amazing.

EscapeWhere("WHERE Col = {0}", 1).Dump(); // Test non-string
EscapeWhere("WHERE Col = '{0}'", "Test").Dump(); // Test string
EscapeWhere("WHERE Col = '{0}'", "Test'").Dump(); // Test single-quote
EscapeWhere("WHERE Col = '{0}'", "Test''").Dump(); // Test 2x single-quote
EscapeWhere("WHERE Col LIKE '{0}'", "Test").Dump(); // Test LIKE
EscapeWhere("WHERE Col LIKE '%{0}%'", "Test").Dump(); // Test LIKE %
EscapeWhere("WHERE Col LIKE '%{0}_'", "Test").Dump(); // Test LIKE _
EscapeWhere("WHERE Col LIKE '%{0}%'", "Test%").Dump(); // Test Escapes %
EscapeWhere("WHERE Col LIKE '%{0}%'", "Test_").Dump(); // Test Escapes _
EscapeWhere("WHERE Col LIKE '%{0}%'", @"Test\%").Dump(); // Test Escapes \
// Test 2x LIKE
EscapeWhere("WHERE Col LIKE '%{0}%' AND Col2 LIKE '%{1}%'", "Test", "Test2").Dump(); 
// Test LIKE + non-LIKE
EscapeWhere("WHERE Col LIKE '%{0}%' AND Col2 = '{1}'", "Test", "Test2").Dump(); 

Which prints the following output:

/*
WHERE Col = 1
WHERE Col = 'Test'
WHERE Col = 'Test'''
WHERE Col = 'Test'''''
WHERE Col LIKE 'Test' ESCAPE '\'
WHERE Col LIKE '%Test%' ESCAPE '\'
WHERE Col LIKE '%Test_' ESCAPE '\'
WHERE Col LIKE '%Test\%%' ESCAPE '\'
WHERE Col LIKE '%Test\_%' ESCAPE '\'
WHERE Col LIKE '%Test\\\%%' ESCAPE '\'
WHERE Col LIKE '%Test%' ESCAPE '\' AND Col2 LIKE '%Test2%' ESCAPE '\'
WHERE Col LIKE '%Test%' ESCAPE '\' AND Col2 = 'Test2'
*/

For peace of mind I’d like someone to point me to a reliable and well-tested alternative I can leverage in a similar manner. In the meantime if you do happen to find any issues, or have some feedback, please make sure to let me know either in the comments or via email.

Comments

What’s your IDE look like?

A couple of recent releases have prompted me to spend a little time trying a new style for my IDE.

Specifically:

The Tomorrow Theme

The Tomorrow Theme is a colour palette (actually series of colour palettes) from Chris Kempson that emerged out of 5 years of personal use and revision.

I’ve chosen to go with the Tomorrow Night palette:

For my use in Visual Studio I downloaded and begun adapting the Tomorrow Night Visual Studio Theme from Chris’s GitHib repository.

I think the VS theme can use some work, but the palette itself is pretty cool and any issues are to be expected as this wasn’t designed for Visual Studio.

You can download the theme as a compressed repository from GitHub.

Adobe Source Code Pro

Source Code Pro is a new font designed by Paul D. Hunt, a typeface designer and developer at Adobe.

It’s the eagerly awaited monospace companion to the recently released Source Sans Pro.

You can download the font from SourceForge or grab the source/unbuilt files on GitHub.

The Outcome So Far

After putting these two together this is what I have so far:

So far I’m liking the new theme, but it’s still very much a work in progress and I’m already spotting things I want to change:

  • Consider a bolding font weight as per the Tomorrow Night TextMate sample.
  • Distinguish between keywords (e.g. class, public) and type aliases (e.g. lowercase string and object). This will probably required a plugin such as ReSharper’s enhanced Syntax Highlighting
  • Distinguish between objects/methods/properties
  • Revise the highlighting in more advanced cases (e.g. selection, debugging, breakpoints)
  • Extend the colouring consistency throughout the UI (e.g. already had to change the line number colouring to be consistent just so I wouldn’t go crazy)
  • Review Razor, HTML, and JavaScript colouring
  • Check out Base16 colours (described as the “next evolution of Tomorrow Theme”)

In the meantime I’m keen to hear feedback and see what everyone else is using.

Comments

How to Change the Commands FtpWebRequest Sends

Below I discuss how I’ve worked around some limitations of the System.Net.FtpWebRequest to allow low-level customisation of the actual commands sent by the FtpWebRequest class.

This allows resolution of a couple of issues, including:

If you’re here for the workaround click here to see the solution, and for everyone else I’m going to be as self-indulgent as usual and detail the background of my workaround.

Background and security exploit in FtpWebRequest

My particular discovery of this workaround was unintentional – I was looking to change the USER/PASS sequence sent by FtpWebRequest (including sending an ACCT command after the USER and PASS). To achieve this I tried sending an embedded command as my PASS by setting the password of my FtpWebRequest‘s NetworkCredential.

NetworkCredential n = new NetworkCredential("SomeUser", "SomePass\nACCT SomeAcct");

Surprisingly, this actually (almost) worked! It actually sent ACCT SomeAcct as an FTP command (and this is a whole new space for a security vulnerability).

Unfortunately I was then (seemingly) getting errors on the OPTS command, which led me down the path below. What I found out later was that injecting the command caused internal miscounts on command processing, which meant the responses from the server were out of alignment with the commands sent and couldn’t be properly correlated.

How FtpWebRequest Sends Commands

It was time to decompile System.Net.FtpWebRequest and I decided to start at the GetResponse method, which seemed to be the method where actual commands were sent to the server.

Looking through the method, it seemed that the basic structure was:

  1. If using a HTTP Proxy, delegate to HttpWebRequest / HttpWebResponse
  2. If using asynchronous methods “do some stuff I didn’t understand at the time”
  3. If using synchronous methods call the private method SubmitRequest

Checking out SubmitRequest, it seemed that that there was some stuff happening with a System.Net.FtpControlStream – an internal class which apparently was the actual type of the m_connection variable being used throughout FtpWebRequest.

 FtpControlStream ftpControlStream = this.m_Connection;

I jumped into FtpControlStream and decided to do a quick search for “OPTS”. Sure enough there were a couple of references:

The first reference was in a PipelineCallback method, which seemed to be doing some pretty hacky stuff using string evaluation after each command to work out what to do. This didn’t look good.

if (entry.Command == "OPTS utf8 on\r\n")
{
  if (response.PositiveCompletion)
    this.Encoding = Encoding.UTF8;
  else
    this.Encoding = Encoding.Default;
  return CommandStream.PipelineInstruction.Advance;
}

The second reference was in a BuildCommandsList method, which looked to be building the list of commands run as part of an FTP request. This looked much more promising, and sure enough there was a line adding the OPTS command to the queue:

  arrayList.Add((object) new CommandStream.PipelineEntry(this.FormatFtpCommand("OPTS", "utf8 on")));

This explained why Thomas Levesque’s answer on StackOverflow was that OPTS is hardcoded.

Changing the Commands FtpWebRequest Sends

I was starting to get a bit of a picture of how this worked:

  1. A queue of commands (a CommandStream) is built (BuildCommandsList)
  2. Each command is processed in sequence
  3. A callback (PipelineCallback) is fired as each command is processed allowing actual functionality

The next question was – how could I modify this commands list before it was sent to the server?

Unfortunately Monkeypatching (rewriting existing code) isn’t currently possible in C#, but maybe I could make my own CommandStream / FtpControlStream and replace (using reflection) the m_Connection variable in FtpWebRequest with my new class?

Unfortunately Eric Lippert says this is impossible and Jon Hanna’s attempts on StackOverflow were stopped by TypeLoadExceptions. My own quick attempts ran into the same issues so this seemed a no-go.

I read through the code of CommandStream, FtpControlStream, and FtpWebRequest a bit more, and realised there were quite a few callbacks around the place – if just one of these happened to fire at the right time (after the command list was built, but before a given command was sent to the server) I might be able to hook in my own callback!

Unfortunately most of the callbacks were implemented as direct method references, and thanks to “no monkeypatching” I wouldn’t be able to change these. However, a couple of callbacks used member AsyncCallback variables, and these I could repoint to my own code!

After running a few LINQPad tests (injecting a basic “Hello World” callback of my own into these various callback options) to work out when these different callbacks fired, I’d narrowed down my target: m_WriteCallbackDelegate in System.Net.CommandStream.

m_WriteCallbackDelegate seemed to fire after each command was sent to the server – as long as I didn’t want to have a command other than USER sent first (or AUTH TLS if SSL was enabled) this was the callback I was after.

My basic strategy would be to inject a callback that would:

  1. Check if the position in the command list is at the position expected to make a change (so that modifications are not applied twice, as the callback fires for every command)
  2. If so, modify the built command list (for instance, remove the command if I don’t want it sent)
  3. Hand back to the original callback (so everything can continue as normal)

With some reflection this part was actually surprisingly easy once I worked out what was happening.

Gotchas

Unfortunately there were two gotchas:

  1. The callback was only fired when the asynchronous methods of FtpWebRequest were used (BeginGetResponse / EndGetResponse).
  2. The callback variable (m_WriteCallbackDelegate) was static – this would affect every FtpWebRequest in the current AppDomain!

Resolving the first point wasn’t the end of the world – I could rewrite my code a little (and deal with the terrible asynchronous FtpWebRequest interface) to call the asynchronous methods but still work synchronously (or asynchronously if I so chose).

The second point was however a little trickier. What I was after was to only apply the callback on specific FtpWebRequest instances (those that I needed my modifications to apply to).

My first thought was to store a thread-safe list of IDs for the requests that I wanted to apply the special callback for. I could then add the ID of new requests to this list on demand, and when the callback fired it would check the current request’s instance ID against that list.

Unfortunately something like GetHashCode collides really quickly (my quick LINQPad tests showed collisions after around 10,000 objects), and there was no way for me to add a new variable to an internal class (I could add an Extension Method but these wouldn’t allow me to add a member-level property). If only there were some way to dynamically attach data to an object instance.. oh wow there is!

The ConditionalWeakTable<TKey, TValue> class enables language compilers to attach arbitrary properties to managed objects at run time.

Source: http://msdn.microsoft.com/en-us/library/dd287757.aspx

The ConditionalWeakTable meant that I could keep a thread-safe dynamic collection (with weak references removing GC issues!) of the FtpWebRequest instances that should use my new callback, and the callback could at run-time check if the current instance should apply the new rules (or simply hand back to the original callback).

The Final Workaround

So what I had worked out at this point was:

  1. FtpWebRequest uses a FtpCommandStream to store a queue of commands (each represented by a PipelineEntry) to send to the FTP server.
  2. This command stream generates a series of commands to execute per request.
  3. This means if the command stream is modified for each request, the commands sent by FtpCommandStream can be modified.
  4. Facilitating this, when using the asynchronous methods of FtpWebRequest, a callback method is fired (by FtpCommandStream‘s parent class ControlStream) after each command is sent to the server.
  5. This allows us to inject/remove/change a command in the stream then hand back to the standard callback.
  6. This callback is static so any replacement will need to happen once per AppDomain and ensure that changes are only applied to the desired instances of `FtpWebRequest

Wrapping this all up gave me an injected custom write callback which I chose to setup with a static initializer, and I’m hoping you can adapt to your own needs. It also required a couple of extension methods which I’ve included below for changing an array generically (without design-time declaration of its type, as we cannot declare the internal PipelineEntry type).

In my particular case I wanted to inject additional FTP commands, so my callback when fired would run through a Queue of commands to inject. Each item in this queue had a condition to check against the “last run command” (stored as Func<string, bool>) and if this condition was true, a “command” to inject as the next command. However my example below shows how to perform both a command removal and insertion.

Remember this relies on leveraging internal behaviour of the framework, which could (though I doubt it for minor releases) change without warning at any time.

If you have any questions or trouble adapting this to your particular needs feel free to leave a comment or get in touch.

// In custom class FtpCallbackInjector
// NB: Requires usage of the asynchronous FtpWebRequest methods (Begin/End)
// NB: In this sample, your request must be added to MarkedRequests

// Could strong-type "object" here - it's the associated per-request "state"
private readonly static ConditionalWeakTable<FtpWebRequest, object> MarkedRequests = new ConditionalWeakTable<FtpWebRequest, object>();

// Static Initializer
static FtpCallbackInjector()
{
    // Get access to (post)-write callback delegate
    Type commandStreamType = typeof(FtpWebRequest).Assembly.GetType("System.Net.CommandStream");
    FieldInfo commandStreamWriteCallbackField = commandStreamType.GetField("m_WriteCallbackDelegate", BindingFlags.NonPublic | BindingFlags.Static);

    // Store original delegate for chaining
    AsyncCallback originalDelegate = (AsyncCallback)commandStreamWriteCallbackField.GetValue(null); 

    // Inject our own delegate (which in turn calls the original delegate)
    commandStreamWriteCallbackField.SetValue(null, new AsyncCallback((asyncResult) =>
    {
      /* Get field/method references via reflection */
      Type commandStreamPipelineEntryType = typeof(FtpWebRequest).Assembly.GetType("System.Net.CommandStream+PipelineEntry");
      FieldInfo commandStreamCommandsField = commandStreamType.GetField("m_Commands", BindingFlags.NonPublic | BindingFlags.Instance);
      FieldInfo commandStreamIndexField = commandStreamType.GetField("m_Index", BindingFlags.NonPublic | BindingFlags.Instance);
      FieldInfo commandStreamRequestField = commandStreamType.GetField("m_Request", BindingFlags.NonPublic | BindingFlags.Instance);
      FieldInfo pipelineEntryCommandField = commandStreamPipelineEntryType.GetField("Command", BindingFlags.NonPublic | BindingFlags.Instance);
      MethodInfo formatFtpCommandMethod = typeof(FtpWebRequest).Assembly.GetType("System.Net.FtpControlStream").GetMethod("FormatFtpCommand", BindingFlags.NonPublic | BindingFlags.Instance);

      // asyncResult.AsyncState is the System.Net.CommandStream
      object commandStream = asyncResult.AsyncState; 

      // Reference to executing request
      FtpWebRequest request = (FtpWebRequest)commandStreamRequestField.GetValue(commandStream); 

      /* Check if executing request is marked */
      // Associate anything you like here with the request
      object markedState = null; 
      if (!MarkedRequests.TryGetValue(request, out markedState))
      {
        // If not marked simply chain and return
        originalDelegate(asyncResult);
        return;
      }

      // Array of PipelineEntry
      Array commands = (Array)commandStreamCommandsField.GetValue(commandStream); 
      // Current (just-executed) index in CommandStream
      int commandStreamIndex = (int)commandStreamIndexField.GetValue(commandStream); 
      // Current (just-executed) command of type System.Net.CommandStream+PipelineEntry
      object currentPipelineEntry = commands.GetValue(commandStreamIndex); 
      // Current (just-written) command string
      string currentCommand = (string)pipelineEntryCommandField.GetValue(currentPipelineEntry); 

      // If the just executed command passes some kind of filter
      // Example removing "OPTS" after "PASS"
      // (Could have also got "next command" and checked it was "OPTS")
      if (currentCommand.StartsWith("PASS")
      {
          // Remove next "OPTS" command
          commands = RemoveAt(commands, commandStreamIndex + 1);

          // Update CommandStream's command (PipelineEntry) array with the new array 
          commandStreamCommandsField.SetValue(commandStream, commands);
      }

      // Example adding "ACCT" after "PASS"
      if (currentCommand.StartsWith("PASS"))
      {
        // Create "ACCT" command
        // Get the Pipeline Entry constructor which takes a string parameter (the command)
        ConstructorInfo pipelineEntryConstructor = commandStreamPipelineEntryType.GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null, new[] { typeof(string) }, null);

        // Call the FormatFtpCommand method, which takes a command and parameter and formats them for FTP submission
        string formattedCommand = (string)formatFtpCommandMethod.Invoke(commandStream, new[] { "ACCT", "someAcct" });

        // Create new Pipeline Entry using formatted command as parameter
        // Of type System.Net.CommandStream+PipelineEntry
        object newPipelineEntry = pipelineEntryConstructor.Invoke(new [] { formattedCommand }); 

        // Create new merged command stream array including injected command
        commands = InsertAt(commands, commandStreamIndex + 1, newPipelineEntry);

        // Update CommandStream's command (PipelineEntry) array with the new array
        commandStreamCommandsField.SetValue(commandStream, commands);
      }
    }

    // Chain back to original delegate
    originalDelegate(asyncResult);
  }));
}

public static Array RemoveAt(Array source, int index)
{
    if (source == null) 
        throw new ArgumentNullException("source"); 

    if (0 > index || index >= source.Length) 
    throw new ArgumentOutOfRangeException("index", index, "index is outside the bounds of source array"); 

    Array dest = Array.CreateInstance(source.GetType().GetElementType(), source.Length - 1); 
    Array.Copy(source, 0, dest, 0, index); 
    Array.Copy(source, index+1, dest, index, source.Length - index - 1); 

    return dest;
 }

 public static Array InsertAt(Array source, int index, object o) 
 {  
    if (source == null) 
        throw new ArgumentNullException("source"); 

    if (0 > index || index > source.Length) 
        throw new ArgumentOutOfRangeException("index", index, "index is outside the bounds of source array"); 

    Array dest = Array.CreateInstance(source.GetType().GetElementType(), source.Length + 1); 
    Array.Copy(source, 0, dest, 0, index); 
    dest.SetValue(o, index);
    Array.Copy(source, index, dest, index+1, source.Length - index); 

    return dest;
 }
2 Comments

FtpWebRequest is Broken

I’m going to take a brief break from helpful solutions to have a bit of a rant about FTP support in the .NET Framework.

When all you have is hammers..

FTP as a protocol is pretty lousy, but its support in .NET (see System.Net.FtpWebRequest) is especially terrible. If you can use an alternative, I suggest you do.

Here’s just a small list of alternatives (evaluate each based on community feedback, release/update activity, user support, and your own testing):

Why not just use FtpWebRequest?

Why not System.Net.FtpWebRequest you say?

I’ve talked before about a small framework issue when using FtpWebRequest (and have others still to post) but I’ll let that one slide in my criticism here (it’s probably System.Net.NetworkCredential‘s fault that another framework class called its internal methods).

So let’s focus on a few of my other criticisms (most of which apply equally to both FtpWebRequest and HttpWebRequest):

I’ve personally dealt with these “underlying connection was closed” issues in rare circumstances where a connection is lost and therefore “breaks” protocol without properly closing the connection, which:

  1. Leads to a series of connections stuck in TIME_WAIT just in case there’s more traffic to come.
  2. Which leads to an exhaustion of the ServicePointManager connection pool count
  3. Which means all new connections encounter the “underlying connection was closed” issue
  4. Which, requires a restart of the AppDomain (i.e. your application)

Usually someone will suggest the largely hit-and-miss idea of changing KeepAlive to false (or true if you’ve already tried that). The more effective but hacky solution seems to be trying to subvert .NET connection handling and leveraging reflection hacks.

Should a mature framework really have such a leaky abstraction?

It can’t be that bad?

The sheer fact that there are so many successful commercial libraries for something as seemingly fundamental as FTP connectivity confirms to me that the standard libraries are simply inadequate.

The cherry on top of this rather unappetising cake is that Microsoft is not only well-aware of the library’s inadequacies, but has given up on its own FTP library:

Hopefully we can see Microsoft either buy (and integrate, particularly as many of the commercial libraries are fairly low-level) or build an adequate FTP library in a future version of the framework. I’m not holding my breath.

2 Comments

Detecting Duplicate XML data in SQL Server

Had the interesting problem today of trying to detect duplicate XML values in a SQL Server table (stored in a column of field type XML).

I’m not the first to cover this issue:

However, my “requirements” as they were differed a little from the existing solutions:

  1. Only compare a subset of the XML value (a timestamp header would still differ on XML values we considered duplicates)
  2. Only apply the duplicate checking to some of the rows
  3. Keep the first row of a set of duplicates
  4. Support large XML values
  5. Develop this solution ASAP (this was needed in a critical support situation)

A single application had populated the table, so fortunately I could assume all XML was equally processed.

My Solution

Now in the heat of the moment I went with a hashed solution as StackOverflow proposed. Upon reflection however I don’t see a real reason (beyond perhaps performance) I couldn’t have followed Matt Woodward’s method and performed a simple string conversion and comparison (well a comparison of substrings given the requirement to ignore the header). Indeed it’s the improved solution I list below.

However, given the hashing direction the HASHBYTES function seemed a good start.

HASHBYTES (Transact-SQL)

Returns the MD2, MD4, MD5, SHA, SHA1, or SHA2 hash of its input.

Unfortunately, while this is a decent enough function for hashing, it led me on a bit of a wild goose chase as I tried to work out why I couldn’t seem to cast my XML field to varchar without getting an error. It turned out that this was because I was passing my cast value straight to HASHBYTES which was in turn throwing an error.

See much like a past post I had missed something pretty crucial on the MSDN pageHASHBYTES has an 8000 character limit!

HASHBYTES Remarks

At least this time the implications were glaringly obvious, and I had noone else to blame but myself. In the process at least I learned a bit more about XML support in SQL, and found some pretty cool resources including this series of SQL XML Workshops from Jacob Sebastian found while searching on StackOverflow.

It turned out that conversion to XML was as easy as I’d first tried:

CONVERT(varchar(max), XmlColumn)

Once I got past this problem the query I ran with was pretty easy to put together.

First, I would only hash content from after the header, which I could identify by the character index of either the header closing tag or content start tag (found using CHARINDEX). I could have also used SQL Server’s XML query functions, but I was short on time and this seemed suitable for a throwaway task.

CHARINDEX('<Content>', CONVERT(varchar(max), XmlColumn))

Then I could use a SUBSTRING to grab the next 8000 characters (to support HASHBYTES) from this point:

SUBSTRING(
 CONVERT(varchar(max), XmlColumn), --// Data
 CHARINDEX('<Content>', CONVERT(varchar(max), XmlColumn)), --// Start Index
 8000) --// Length

This ultimately gave me the following query, which suited our purposes:

--// WARNING: THIS CODE HAS A MAJOR ISSUE (SEE BELOW)
UPDATE OurTable 
SET IsDuplicate = 1
WHERE 
  OtherFilters = 'SomeValue' --// Other filters, so only checking certain rows
  AND Id NOT IN ( --// ID is either unique or matches the first 'duplicate'
    SELECT MIN(Id) FROM OurTable --// Pick the lowest ID for a given hash
    WHERE OtherFilters = 'SomeValue' --// Same as the OtherFilters above!
    GROUP BY --// Group by hash (i.e. duplicates grouped)
      HASHBYTES('md5', --// Hash Function 
       SUBSTRING( --// Hash Data (the substring of 8000 chars after the header)
         CONVERT(varchar(max), XmlColumn), --// Substring Data
         CHARINDEX('<Content>', CONVERT(varchar(max), XmlColumn)), --// Index
         8000))) --// Length

Review

There is a major issue with this query! Can you spot it?

The problem is that it assumes the first 8000 characters of the XML (well, after the trimmed header) are enough for unique identification. While this may be a valid assumption (as it was in my case), if it’s wrong you run the risk of falsely labeling some XML a duplicate, which could be disastrous.

At the time I considered resolving this by appending the hashes of the next 8000 characters, possibly repeating if necessary to increase the character coverage (one of my less bright ideas).

In retrospect simple string comparison was not only sufficient, but a better (if not as performant) solution (the goose chase of believing that conversion of large XML to varchar had issues put this idea out of my mind). It also has the added benefit of avoiding admittedly unlikely MD5 collisions.

However, if performance is a concern (or depending on the number of rows and size of XML values involved) hashing may still be a good fallback option (perhaps even with my terrible ‘concatenated hashes’ approach). Alternatively I would probably look to point LINQPad at the database and write some pretty basic code to perform duplicate detection (which would have additional advantages if the XML was processed in different ways with regard to whitespace and self-closing tags).

Improved Solution

Assuming performance isn’t a serious bottleneck, something like the following works (and is what I’ve used subsequently):

UPDATE OurTable 
SET IsDuplicate = 1 
WHERE 
  OtherFilters = 'SomeValue' --// Other filters, so only checking certain rows
  AND Id NOT IN ( --// ID is either unique or matches the first 'duplicate' 
    SELECT MIN(Id) FROM OurTable --// Pick the lowest ID for a given hash 
    WHERE OtherFilters = 'SomeValue' --// Same as the OtherFilters above!
    GROUP BY --// Group by content (i.e. duplicates grouped) 
      SUBSTRING( 
        CONVERT(varchar(max), XmlColumn), --// Substring Data 
        CHARINDEX('<Content>', CONVERT(varchar(max), XmlColumn)), --// Start Index 
        LEN(CONVERT(varchar(max), XmlColumn)))) --// Length, excess is handled

Since originally publishing this I did a little more research on the issues with hashing, and should you go down this direction there’s a great article by Thomas Kejser on SQL hash functions and performance. For one, it makes sense to use something like SHA1 instead of the MD5 in my example as it reduces the likelihood of collision issues without a noticeable performance difference.

I have the feeling there’s much room for others optimisations and approaches here, so if anyone has run into this problem before or has some ideas on what to do next time I’m curious to hear more!

Comments

Zebras in System.Net.NetworkCredential

Ran into an interesting problem recently where some pretty straightforward FTP code was throwing a WebException on GetResponse when using correct server details.

Looking a little further, the error indicated there was an Object reference not set to an instance of an object (NullReferenceException).

If you’re here for the fix, feel free to jump to Workaround.

Diagnosis

Normally a WebException is pretty informative (usually credential or connectivity issues) but in this case I was getting something unusual:

  • Status: UnknownError
  • Message: Object reference not set to an instance of an object.

Well that’s NullReferenceException text, but this was thrown from framework code and there’s no way the .NET Framework is throwing NullReferenceExceptions on relatively straightforward code right?

Think Horses not Zebras (or its development equivalent Select Isn’t Broken) told me there was something wrong with my code.

We worked on a project where a senior engineer was convinced that the select system call was broken on Solaris. No amount of persuasion or logic could change his mind (the fact that every other networking application on the box worked fine was irrelevant). He spent weeks writing workarounds, which, for some odd reason, didn’t seem to fix the problem. When finally forced to sit down and read the documentation on select, he discovered the problem and corrected it in a matter of minutes. We now use the phrase “select is broken” as a gentle reminder whenever one of us starts blaming the system for a fault that is likely to be our own.

Source: The Pragamtic Programmer via Coding Horror

I reduced my code to the simple test-case below and ran it through LINQPad.

NetworkCredential credentials = new NetworkCredential();
credentials.UserName = "user";
credentials.Password = "pass";
FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create("ftp://localhost");
request.Credentials = credentials;
request.Method = WebRequestMethods.Ftp.ListDirectoryDetails;

// Throws error
using (FtpWebResponse response = (FtpWebResponse)request.GetResponse())
{
}

But I continued to get the WebException, and noticed that sure enough it was wrapping an inner NullReferenceException

Object reference not set to an instance of an object.  
   at System.Net.NetworkCredential.InternalGetDomainUserName()
   at System.Net.FtpControlStream.BuildCommandsList(WebRequest req)
   at System.Net.CommandStream.SubmitRequest(WebRequest request, Boolean async, Boolean readInitalResponseOnConnect)
   at System.Net.FtpWebRequest.TimedSubmitRequestHelper(Boolean async)
   at System.Net.FtpWebRequest.SubmitRequest(Boolean async)

Checking my code for null values revealed no obvious cause and all signs pointed to a framework bug, but surely someone else would have noticed? I did a search and … found I wasn’t alone!

Unfortunately (at the time) there was no clear explanation for what was happening, so it was time to open the hood with my favourite decompiler and check out System.Net.NetworkCredential.InternalGetDomainUserName

internal string InternalGetDomainUserName()
{
  string str = this.InternalGetDomain();
  if (str.Length != 0)
    str = str + "\\";
  return str + this.InternalGetUserName();
}

Hmm, clearly str must be null to cause the exception I was seeing. What didn’t make sense was why framework code was leaving a potentially null parameter unguarded.

Looking deeper, str was set to InternalGetDomain() which simply returned the local m_domain member.

internal string InternalGetDomain()
{
  return this.m_domain;
}

This meant that m_domain must be null, but checking the property setter for Domain (the public interface to m_domain) I discovered it guards against null values (using string.Empty instead).

public string Domain
{
    set
    {
      if (value == null)
        this.m_domain = string.Empty;
      else
        this.m_domain = value;
    }
}

This explained why the framework code wasn’t guarding (it assumed that the setter property would prevent null values!) but it didn’t explain why m_domain was null.

Hmm, given it was impossible for me to set Domain to null (through the public interface), it was time to see where Domain / m_Domain was initialised (I hadn’t changed it from its default after all).

System.Net.NetworkCredential has a few constructors, most of which chain to a constructor that initialises the username, password, and domain.

public NetworkCredential()
{
}

public NetworkCredential(string userName, string password)
  : this(userName, password, string.Empty)
{
}

public NetworkCredential(string userName, SecureString password)
  : this(userName, password, string.Empty)
{
}

public NetworkCredential(string userName, string password, string domain)
{
  this.UserName = userName;
  this.Password = password;
  this.Domain = domain;
}

public NetworkCredential(string userName, SecureString password, string domain)
{
  this.UserName = userName;
  this.SecurePassword = password;
  this.Domain = domain;
}

However, the default (parameterless) constructor does not chain, and sure enough that’s the one we’re using!

Actually, the default constructor does nothing at all, leaving private members such as Domain uninitialised (and therefore null) – a state that we’ve established for Domain at least is otherwise unachievable through the public interface.

This leads to the following weird behaviours:

// Unaffected: Non-default constructor
new NetworkCredential(
"user", "pass"
); // Domain == String.Empty

// Unaffected: Non-default constructor with named parameters
new NetworkCredential(
userName: "user", password: "pass"
); // Domain == String.Empty

// Impacted: Default constructor with object initializer
new NetworkCredential()
{
Username = "user",
Password = "pass"
}; // Domain == null

// Impacted: Default constructor then property assignment
var creds = new NetworkCredential();
creds.Username = "user";
creds.Password = "pass";
// Domain == null 

Interestingly enough, this doesn’t just impact Domain (UserName and Password are also null). For instance, if you were to use the default constructor and not set a Password, a call to credentials.SecurePassword will throw a NullReferenceException.

NetworkCredential credentials = new NetworkCredential();
// Throws NullReferenceException
object password = credentials.SecurePassword; 

Workaround

Our workaround is therefore:

  • Use another constructor (NetworkCredential credentials = new NetworkCredential("user", "pass");)
  • Or, set Domain to a value (credentials.Domain = null; would do)

Reflections

Now to be fair, the MSDN documentation does remark that the default constructor does not initialise properties.

System.Net.NetworkCredential Default Constructor MSDN Remarks

This being the case though, the framework is not excused from throwing unhandled exceptions and should handle the null properties internally rather than make invalid assumptions.

On Microsoft’s part, this could be fixed by either:

  • Chaining the default constructor
  • Or, initialising members in the default constructor
  • Or, having internal methods such as InternalGetDomainUserName checking members are not null before dereferencing

Outcomes

I lodged a Microsoft Connect ticket for this one and the team was able to verify the issue pretty quickly before advising a fix would be planned in a future release.

I also updated the MSDN Forums question I came across with the solution (the OP had fortunately already arrived at the workaround by accident).

Comments