Friday, July 06, 2012

Formatting XML using MSXML

Here's another useful bit of code used to generate formatted XML.  This particular code - in C++ form - will eventually be incorporated into the Platypus API Service, when logging API calls is enabled.

Function PrettyPrintXML(strXML)

    Dim objReader, objWriter
    Set objReader = CreateObject("MSXML2.SAXXMLReader.6.0")
    Set objWriter = CreateObject("MSXML2.MXXMLWriter.6.0")

    objWriter.indent = True
    objWriter.standalone = False
    objWriter.omitXMLDeclaration = False
    objWriter.encoding = "utf-8"

    Set objReader.contentHandler = objWriter
    Set objReader.dtdHandler = objWriter
    Set objReader.errorHandler = objWriter

    objReader.putProperty _
        "", _
    objReader.putProperty _
        "", _

    objReader.parse strXML

    PrettyPrintXML = objWriter.output


The credit for this actually goes to Daniel Rikowski on StackOverflow.  All I did was convert it into usable VBScript.

Wednesday, May 09, 2012

SQL Challenge - Part 1 - How to Find Contact Email Addresses in Platypus That May Cause an SMTP 550 Error.

Occasionally, if I am very lucky, someone will confront me with what I call the "SQL Challenge".  Way back in the day of the early 2000's, one of my coworkers and I would try to come up with ways to stretch our SQL knowledge.  Thanks to the "SQL Challenge", I am now capable of some fun acrobatics using just a little SQL.

The rules are fairly simple, once you have the actual challenge.  The SQL must be encapsulated into a single SQL statment - be that select, delete, insert or update.  It can have all the joins, subclauses, derived tables, and whatever else SQL provides, as long as it is included in a single statement. If inserting, updating or deleting, you are allowed a separate SQL statement for each table, but that is the only exception to the one statement rule. So, no cursors or loops. You get a bonuses for using ANSI SQL syntax and for how quickly you finish.

Today, I got one of those challenges.  Let's start with the givens.

1.  We have one or more email addresses stored in
2.  These emails can be comma delmited or semicolon delimited.
3.  There may be spaces embedded before or after commas/semicolons.
4.  The emails may be hosted internally or by a 3rd party provider, or a combination of the two.
5.  A list of domains is stored in domain_item.domain.  These domains are hosted internally.
6.  A list of email addresses is stored in email_data.emailaddr.  These emails are hosted internally.
7.  When sending email messages to the email addresses stored on, if one of the email addresses is on a hosted domain but the email address is not in the list of hosted emails, the SMTP server will return a 550 (mailbox not found) error.

Now, the challenge.  Given all of the above, we want to find a list of customers with email addresses on that will generate a 550.  This means we want to find a list of email addresses attached to a hosted domain but are not a hosted email address.  After a little less than an hour, I made this...

/* The unique customer id */,
/* The delimited list of contact email addresses */,
/* A hosted domain matching one of the email addresses */
/* The number of email addresses in */
    len( - len(replace(, '@', '')) as email_count,
/* The number of emails that match the hosted domain */
    len(replace(';'+replace(,' ','')+';',',', ';'))
    - len(replace(replace(';'+replace(,' ','')+';',',', ';'), '@'+domain_item.domain+';',''))
) / len('@' + domain_item.domain + ';') as domain_count,
/* The number of hosted email addresses that match the hosted domain  */
    select count(*)
      from email_data
     where = email_data.d_custid
       and ';'+replace(replace(,' ',''),',',';')+';' like '%;'+email_data.emailaddr+';%'
       and email_data.emailaddr + ';' like '%@' + domain_item.domain + ';'
) as match_count
  from customer
 inner join domain_item
    on replace(';'+replace(,' ','')+';',',',';') like '%@'+domain_item.domain+';%'
/* Strip the hosted domain from the delimited list */
/* Comparing the difference divided by the length of the hosted domain name */
/* This will let us know how many emails _should_ be hosted for this domain */
    len(replace(';'+replace(,' ','')+';',',',';'))
    - len(replace(replace(';'+replace(,' ','')+';',',',';'), '@'+domain_item.domain+';', ''))
) / len('@'+domain_item.domain+';')
/* Compare the _should_ total against the _actual_ total */
/* If they don't match exactly, include the customer in the result */
<> (
/* Find the number of hosted email addresses for this customer */
/* That match one of the email addresses on the delimited list */
/* This will let us know how many emails are _actually_ hosted for this domain */
    select count(*)
      from email_data
     where = email_data.d_custid
       and replace(';'+replace(,' ','')+';',',',';') like '%;'+email_data.emailaddr+';%'
       and email_data.emailaddr+';' like '%@'+domain_item.domain+';'

Sunday, May 06, 2012

Base64 Encoding using MSXML

At the time we were developing the base64 encoder - covered in my last post, there already existed a quick and dirty way to perform base64 encoding/decoding through MSXML.  Even though we have decided not to pursue this for any Platypus development, it might benefit someone.  Here are examples for using MSXML for base64 encoding/decoding from within VBScript.

    Function Base64Encode (strData)
        Set objDocument = CreateObject("MSXML2.DOMDocument")
        Set objNode = objDocument.createElement("document")
        objNode.dataType = "bin.base64"
        objNode.nodeTypedValue = strData
        Base64Encode = objNode.text
    Function Base64Decode (strData)
        Set objDocument = CreateObject("MSXML2.DOMDocument")
        Set objNode = objDocument.createElement("document")
        objNode.dataType = "bin.base64"
        objNode.text = strData
        Base64Decode = objNode.nodeTypedValue

This is covered in more detail in Microsoft's Knowledge Base Article named "How To Create XML Documents with Binary Data in Visual Basic", along with examples for hex encoding and date encoding  (ISO-8601).

Saturday, May 05, 2012

Base64 Encoding in Platypus

Base64 encoding was originally designed for transmitting binary data over SMTP.  When email servers were first created, the SMTP protocol restricted the allowed characters to the first 7bits of ASCII's 8bit character set. Among other things, this prevented binary files from being sent over email.

Then came MIME and with it, base64 encoding, which converted binary files into readable text suitable for sending over email. With base64, for every 3 bytes of binary data input there are 4 bytes of encoded text returned. Compared with hex (base16) encoding, the other dominant encoding mechanism at the time, base64 encoding is much more efficient in terms of storage by lowering the amount of space needed for attachments from 2:1 (for hex) to 4:3 (for base64).  Since its creation, base64 encoding has been included in a variety of internet protocols, including SMTP authentication, HTTP authentication, XML and is used in various subsystems of the Platypus Billing System.

While Visual FoxPro - the base language for the Platypus Billing System - includes features for encoding and decoding using base64 through STRCONV(), that function does not follow the line length requirement in the RFC 2045 specification, which limits encoded lines to a maximum of 76 characters. For example, if data were encoded into 100 characters, two lines of encoded data would result.  The first line 76 characters, followed by CRLF and then the remaining 24 characters.

At first, this limitation was not a problem.  We used 3rd party ActiveX/COM libraries, named EncodeX and SmtpX from Mabry Software, for encoding email attachments and sending emails, respectively.  The other use of base64 within Platypus - our own attachment feature first included in Platypus v3 - did not have to interact with any external systems, so RFC 2045 compliance was not a requirement.

Eventually, we began work on creating a shared library that could wrap all our SMTP functionality for Platypus v5.  Up until this point, there were separate classes and libraries for the Platypus client and API.  For one, the Platypus client used ActiveX forms of the libraries, while the Platypus API used the COM forms.  Of course, this made maintaining that code doubly difficult and it was prone to inconsistent behavior between the Platypus client and API.  Because of that inconsistency, limitations in Visual FoxPro, and compatibility problems, we dropped the ActiveX form of SmtpX and completely dropped the EncodeX libraries.

Unfortunately, because FoxPro's STRCONV() function did not strictly follow MIME, sending attachments encoded using this function would often generate errors.  The strange thing was that these errors were not universal across all mail servers.  Some were more strict than others.  Anyway, to move forward, we decided to develop our own library for base64 encoding.

This was my first C++ project with Platypus.  Up until this point, my development experience - excluding school - was limited to Visual FoxPro, Visual Basic, and SQL.  After an excessive amount of research, we found a decent example in the public domain which performed encoding quickly enough and adapted it into a COM library using Visual C++ 6.0.  That library has been in use since that time for a majority of Platypus v4 and all of Platypus v5 and v6.  Not bad for a piece of of code 10 years old.  With the release of Platypus v7, that COM library has been phased out in favor of the Mailbee SMTP COM library, which handles encoding internally; but the old base64 COM library is still included in our installation sets as part of a fallback feature.

The original C++ source for the encoder has been phased out of our other components, as well; being replaced with a much improved library.  This new library takes advantage of Microsoft's SecureCRT (secure C runtime) guidelines to prevent buffer overflows, now includes an efficient decoder, and has been fuzz tested to ensure stability.  It is currently in use within the updated Mailpopper for re-encoding email attachments that have been decoded by the Mailbee POP3 COM library, which pulls emails into the Helpdesk features in Platypus.

Monday, April 30, 2012

Configuring DEP on Windows Server 2008 R2 from a 32bit NSIS Installer

If the title is hard to understand, let me just shorten it to this.  The woes of compatibility testing!

Included with the Platypus Billing System is a number of 3rd party ActiveX libraries.  Most of the time, these libraries are wondrous things.  Unfortunately, one, in particular, has a problem with Windows DEP.

Now, upon installation of the Platypus client, we can get around this by configuring the Application Compatibility settings of our executable to bypass DEP.  So, whenever the exe is launched, the OS will not trap DEP problems for our process.  We do all of this by simply writing to the appropriate location in the registry during the installation process.

HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers
All the way from Windows XP up to Windows Server 2008, we simply had to write to the registry.  It didn't even matter whether the OS is x86 or x64.  It has just worked.

Anyway, one of the more ingenious and infamous features from Windows Vista is redirection.  You see, whenever a 32bit application writes to HKEY_LOCAL_MACHINE in the registry on 64bit Windows, it is actually writing to HKEY_LOCAL_MACHINE\Software\Wow6432Node. So, whenever our software was installed, because the installation set is a 32bit application itself, when it writes to the registry, it is actually writing to the 32bit subset of the registry.  This includes those pesky Application Compatibility settings I mentioned.  Up until the latest version of Windows Server, the OS didn't care whether you configure the compatibility settings in the 32bit or 64bit registry.  It checked both when the process started.  Here's an example of code we used for configuring DEP*.
!include LogicLib.nsh
!include WinVer.nsh

StrCpy $NSISRegPath "SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers"

${IF} ${AtLeastWinXP}
    WriteRegStr HKLM "$NSISRegPath" "$INSTDIR\program.exe" "DisableNXShowUI"

Starting with Windows Server 2008 R2, Application Compatibility settings in the 32bit registry now appear to be ignored.  Meaning the Platypus client will now crash whenever our troublesome ActiveX library rears its head, because our installation set can't get to the 64bit registry.  Not easily, anyway.

To get around this problem, the creators of NSIS - who provide us with the software for making installation sets - were kind enough to take advantage of some features in 64bit Windows that allows us to get around redirection.  Mostly.

Unfortunately, whenever redirection is disabled, it only changes file redirection - not registry redirection.  Since our installation set is writing directly to the registry, disabling file redirection doesn't help us.  So, we have to find a way to write to the 64bit registry through file redirection.  This leads us to reg.exe - a nifty little utility that came with Windows XP that allows the registry to be accessed from the command line.  Since 64bit Windows has a 32bit reg.exe and a 64bit reg.exe, disabling file redirection should allow us to call the 64bit copy directly, which doesn't have that pesky 32bit registry limitation.

All we need to do is to check for 64bit Windows, disable file redirect, run reg.exe and then reenable file redirection. That gives us code that looks something like this, which actually does work*.
StrCpy $NSISRegPath "SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers"

var /GLOBAL EXERegPath
StrCpy $EXERegPath "HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers"

${IF} ${AtLeastWinXP}
    WriteRegStr HKLM "$NSISRegPath" "$INSTDIR\program.exe" "DisableNXShowUI"
    ${IF} ${RunningX64}
        ExecWait '$SYSDIR\reg.exe add "$EXERegPath" /v "$INSTDIR\program.exe" /d "DisableNXShowUI"'

Finally! It works!  Total time of this endeavor is a little over an hour.  Now I'm off for more compatibility testing.  Windows 8 and Windows 2012 up next!