Thursday, June 19, 2014

Base64 encoding in VBA

Today's useful snippet: Base64 encoding in VBA (not VB.NET; the latter has a builtin API for that).

Encodes an array of bytes into a string. Doesn't make any assumptions about the bounds of the source array. Processes the entire array; can be trivially modified to deal with a array slice.

Public Function ToBase64(Src() As Byte) As String
    Const ALPHABET = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"

    Dim SrcLen As Long, i As Long, Remainder As Long
    Dim RLen As Long, Triad As Long, LastSrcIndex As Long
    SrcLen = UBound(Src) - LBound(Src) + 1
    Remainder = SrcLen Mod 3
    'Index of the first byte of the leftover 1- or 2-byte chunk
    RLen = LBound(Src) + SrcLen - Remainder
    LastSrcIndex = RLen - 3
    ToBase64 = ""
    'The 3 byte chunks of the block
    For i = LBound(Src) To LastSrcIndex Step 3
        Triad = (CLng(Src(i)) * &H10000) Or _
            (CLng(Src(i + 1)) * &H100) Or Src(i + 2)
        ToBase64 = ToBase64 & _
            Mid(ALPHABET, ((Triad \ &H40000) And &H3F) + 1, 1) & _
            Mid(ALPHABET, ((Triad \ &H1000) And &H3F) + 1, 1) & _
            Mid(ALPHABET, ((Triad \ &H40) And &H3F) + 1, 1) & _
            Mid(ALPHABET, (Triad And &H3F) + 1, 1)
    'The remainder, if any
    If Remainder = 1 Then
        ToBase64 = ToBase64 & _
            Mid(ALPHABET, ((Src(RLen) \ 4) And &H3F) + 1, 1) & _
            Mid(ALPHABET, ((Src(RLen) * &H10) And &H3F) + 1, 1) & "=="
    ElseIf Remainder = 2 Then
        Triad = (CLng(Src(RLen)) * &H100) Or Src(RLen + 1)
        ToBase64 = ToBase64 & _
            Mid(ALPHABET, ((Triad \ &H400) And &H3F) + 1, 1) & _
            Mid(ALPHABET, ((Triad \ &H10) And &H3F) + 1, 1) & _
            Mid(ALPHABET, ((Triad * 4) And &H3F) + 1, 1) & "="
    End If
End Function

A useful variation would use a preallocated buffer for the encoded chunk, with assignment to Mid() instead of concatenation. When encoding large pieces (>100KB), it makes sense to save on string allocation and copying.

The formula for the length of a Base64-encoded string is: ((SourceLength + 2) \ 3) * 4.

Friday, June 6, 2014

CryptoAPI issue I've found

Continuing with the crypto theme, this is a story about an issue in Microsoft CryptoAPI that I've discovered a few years ago. It was originally posted at an MSDN forum, but I thought I'd rather republish it here. I've used some pretty deep magic to get to this result.

I was debugging a problem that my customer had. He was trying to move an SSL client certificate from one WinXP SP2 box to another. He exported the certificate into a PFX file. When he was trying to import, he got the following message:

"An internal error occurred. The private key that you are importing might require a cryptographic service provider that is not installed on your system."

The private key in question was 1024-bit RSA with a SHA1 signature - very plain vanilla. We've tried the steps in KB919074 to no effect.

I've written a simple test program that would ask for certificate file path and an export password, then would try to read the certificate into a temp store by means of PFXImportCertStore(). It would fail with error 0x8009000b, NTE_BAD_KEY_STATE.

Further analysis traced the error to the following call stack:

0x77ab0b9c CRYPT32.dll+0x30b9c - CryptProtectData()
0xffeb7ad rsaenh.dll+0x1b7ad - MyCryptProtectData()
0xffebda2 rsaenh.dll+0x1bda2 - TryDPAPI()
0xffdd599 rsaenh.dll+0xd599 - OpenUserKeyGroup()
0xffdeb3e rsaenh.dll+0xeb3e - NTagLogonUser()
0xffded6e rsaenh.dll+0xed6e - CPAcquireContext()
0x77de8307 ADVAPI32.dll+0x18307 - CryptAcquireContextA()
0x77de8675 ADVAPI32.dll+0x18675 - CryptAcquireContextW()
0x77a866c6 CRYPT32.dll+0x66c6 - HCryptProv_Query_Func()
0x77af5609 CRYPT32.dll+0x75609 - ???
0x77aef215 CRYPT32.dll+0x6f215 - CryptImportPKCS8()
0x77af5af3 CRYPT32.dll+0x75af3 - CertImportSafeContents()
0x77aef800 CRYPT32.dll+0x6f800 - PFXImportCertStore()
0x401193 ImpCert.exe+0x1193 (that's my code)

So CryptProtectData fails with NTE_BAD_KEY_STATE, which causes CryptAcquireContext to fail, which causes the import to fail. And that's even before the CryptoAPI starts to do anything about the certificate we're importing. Why would it call CryptProtectData during context acquisition - beats me.

CryptProtectData() calls, via RPC, into the Protected Storage service, which resides in the LSASS. From the disassembly of lsasrv.dll: SPCryptProtect() calls into GetSpecifiedMasterKey(), which can return NTE_BAD_KEY_STATE.

Finally, the answer: The file
c:\Documents and Settings\(username)\Application Data\Microsoft\Protect\CREDHIST
was read-only. Once they cleared the read-only flag, everything was fine, and the certificate imported as expected.

A similar issue is described in the Intuit knowledge base.
Original write-up at the MSDN forum is here.

Thursday, June 5, 2014

OpenSSL vs. the Microsoft crypto stack

I'd like to publish a Microsoft Word VBA macro for my co-workers. I would also like to have it digitally signed, so that Word doesn't complain about scary insecure macros.

I know that my organization runs a certificate authority (CA) on a Microsoft platform. Every corporate computer trusts that CA. The CA has a Web-based UI for requesting certificates, but doesn't explicitly allow for requesting code signing certificates. It has, however, an "Advanced certificate request" form, where you can paste a Base64-encoded certificate signing request. The problem is creating one that the Microsoft crypto stack (including VBA) would play nicely with.

There are numerous tutorials on the Web regarding code signing certificate generation, but those mostly deal with either commercial certificate vendors, or with self-signed certificates. This case is somewhere in between.

By now, I've found a way to generate an arbitrtary CSR using Microsoft tools. But back at the time, I've decided to go all low-level with OpenSSL under Cygwin. OpenSSL has all the necessary commands for creating RSA keys, certificate signing requests (CSRs), and signed certificates.

First, you need to create a private/public RSA key pair:

openssl genrsa -out Key.pem 2048

This generates an unencrypted, 2048-bit RSA key pair and stores it in Key.pem.

Now you need to put together a CSR with your information. Some of the parameters can't be set from the command line, so a config file is in order. It would looks like this:


CN=John Doe
OU=Software Engineering
O=Acme Software
L=New York


The contents of the DN ("distinguished name") section will go into the "Subject" line in the final certificate. The 1.OU line has to do with the fact that the config file syntax doesn't allow for several OU lines ("organizational units") in a section, but the certificate/CSR syntax does. Also, the DN should be written in reverse order; in the final certificate, the order of fields will be from bottom to top.

Save this as req.txt, and run the following command:

openssl req -new -config req.txt -key Key.pem -out SignReq.pem

SignReq.pem now contains the CSR in Base64 form. Paste its contents into the CA's Web form (or use whatever submission mechanism your CA uses), and wait for approval from whoever runs the CA.

Once the approval comes, you'll receive a signed certificate file. Depending on the CA, you may get it either in PEM format (text, Base64-encoded), or in DER (binary). In the latter case, you can use OpenSSL to convert (assuming SignedCert.der is the binary file you've got from the CA):

openssl x509 -in SignedCert.der -inform DER -out SignedCert.pem -outform PEM

Microsoft tools assign the .cer extension to certificates in both PEM and DER format. You can view either by double-clicking in Windows Explorer. But the following OpenSSL command requires the certificate as PEM.

Now you need to convert the private key and the signed certificate to PFX form, so that Windows may import it into the certificate store. For that, use the following command:

openssl pkcs12 -export -in SignedCert.pem -inkey Key.pem -out CertWithKey.pfx

It will prompt for an export password; memorize it, as you'll need it to import the certificate/key pair into Windows' store. It will generate a file called CertWithKey.pfx that would contain both the signed certificate and the private key.

Now double-click the CertWithKey.pfx file in Windows Explorer (or right-click and choose "Install PFX"). The import UI will pop up; click through it, and provide the export password from the step above when prompted. On the same screen, I'd recommend marking the key as exportable and enabling private key protection.

That's it. Now the certificate should appear whenever you're choosing one in VBA.

All the OpenSSL commands I've mentioned are documented here.

Thursday, May 1, 2014

Yet another reverse proxy

The general idea of reverse proxy is quite simple. Sometimes, one wants to designate a portion of a website  to return a copy of another website. Not redirection; instead, whenever a request comes for, the server-side code at foo performs an HTTP request to and returns the page to the client. The client doesn't even know that the response originated at In this scenario, the directory acts as a reverse proxy for

The business cases for this kind of functionality are several. I, specifically, want to move a set of Web services from one host to another while keeping the old URLs working. Since those are services, meant to be invoked by programs and not people, redirection won't work. Another possible scenario involves exposing an HTTP service from behind a firewall without exposing the whole host.

There are built-in means for creating reverse proxies both in Apache and in Internet Information Server. The problem is, leveraging those requires administrative rights, and with certain Web hosting packages, one might not have them.

On the other hand, there's nothing magical about forwarding an HTTP request. A piece of server side code (PHP in my case) is perfectly capable of issuing an outgoing HTTP request, passing the incoming request headers along, then sending the response headers and data back. So I went ahead and wrote one.

The reverse proxy script is done, it works (for me) and I don't mind sharing.

Setting up

If you're interested in this script, here are the installation steps.
  1. Download this archive from Dropbox
  2. Upzip into a folder on a Web server
  3. Open proxy_config.php, describe the location(s) under which the proxy is sitting and which targets they should invoke
  4. Open .htaccess,  change the value of RewriteBase to reflect the URI location where the proxy is sitting (multiple lines might or might not work, test it)
  5. Make log.txt writable to the world
That's the basic setup, and it assumes that the server is Apache, that it has mod_rewrite, that mod_rewrite is enabled, and that overriding rewrite rules on folder level is allowed. It's a reasonable assumption in this day and age; many CMS's out there depend on rewriting functionality.

If rewrite is not available, there's still a way to run the proxy. I won't go into that here, but the idea is either establishing symlinks to proxy.php all over the proxy folder, or placing renamed copies of rproxy.php all over the folder. It's not pretty, but it'll work. Static content needs to be duplicated outright (or a handler needs to be established).


In addition to the most basic HTTP functionality, the proxy supports:
  • Passing headers back and forth as much as reasonably possible - so caching instructions, content type, user agent and such won't be lost
  • Arbitrary HTTP methods (i. e. REST)
  • POST/PUT/PATCH data in arbitrary format - not just forms
  • Cookies and sessions, unless the target uses path-specific cookies nontrivially
  • HTTPS -if you designate the target as protocol independent (with no leading http://)
  • Proxy folders that are accessible via several URIs
On the other hand, there are many ways a site can be proxy-unfriendly. The following scenarios won't work under my proxy script:
  • Absolute URLs in HTML
  • Redirection to absolute URLs within the same site
  • Domain- and path-specific cookies might break, depending on the way the target works
Some of those shortcomings may be fixed in future versions.

It probably doesn't scale well with content size. Rather than passing the bytes to the client as soon as they arrive from the target, the script stores the whole thing in memory.

The preferred environment is PHP 5 under Apache. In theory, the script should work under other environments too, but I had little chance to test it under those.

Monday, April 28, 2014

Hash of a hash of a hash

Problem: there are two MySQL databases on two different websites that should be mostly identical, except for maybe a few tables. I would like to figure out quickly which tables don't match so that I can run a manual sync.

Solution: hash every record, then hash the records together. Then compare per-table hashes by hand.

In PHP, here's how it goes:

function WrapField($f)
    return "MD5(IFNULL(`$f`,''))";

//Retrieve the list of table names
$TableList = DB_GetScalarArray('show tables');

DB_ExecuteSQL('SET SESSION group_concat_max_len = 10000000;');

//10 MB; you might need more

TableList as $Table)
    if(DB_GetScalar("select count(*) from `$Table`") > 0)

    //Skip empty tables

        //Retrieve the field names
        $OneRecord = DB_GetRecord("select * from `$Table` limit 1");
        $Fields = array_keys($

        //Compose the expression for all field hashes
        $SelectSet = implode(',', array_map("WrapField", $Fields));

        //Now the real work: hash of record hashes
        $SQL =

            "select MD5(GROUP_CONCAT(".
                "MD5(CONCAT($SelectSet)) ".
            "ORDER BY `".$Fields[0]."`)) FROM `$Table`";
        $Hash = DB_GetScalar($SQL);
        echo '<tr><th>'.$Table.'</th><td>'.$Hash.'</td></tr>';

The database helper functions DB_GetScalar(), DB_GetRecord(), DB_GetScalarArray() are thin wrappers on top of the MySQL client library (Mysqli in my case). What do they do should be clear enough. DB_GetRecord() returns an associative array with field names as keys.

The triple hashing was a necessity, I'm afraid. First, simply concatenating the field values will error out if any two the fields in a table happen to have different collations. One can probably work around that by casting to binary, but hashing each individual field works as well.

Hashing of the entire record was done to make the source set of the group_concat() smaller. If the table is wide, the concat of all field hashes would be pretty wide, too. So the total length of the group_concat argument would be MD5Size*NumRecords*NumFields. If the table is long, too, there'd be a real risk of running into the limitation of group_concat() source length. Producing a per-record hash makes the length MD5Size*NumRecords instead.

One assumption that this code makes is that the first field in the table is the primary key, or at least a somewhat identifying field. If it's not, there might be false negatives caused by mismatched record ordering. A better script would analyse MySQL metadata to retrieve the proper primary key; but then there'd be need for a fallback for keyless tables anyway.

Monday, April 14, 2014

Reverse proxy needed

HTTP reverse proxies are a Useful Thing. Sometimes it's for exposing an endpoint from behind a firewall that you don't control, sometimes it's for moving a service from one public URL to another while not leaving legacy consumers in the dark. Both Apache and IIS support them almost out of the box, but how often do you have control over server-level settings with a cheap-o hosting plan? For discussion's sake, let's assume you don't; otherwise, there'd be no discussion.

Enter PHP, libcurl and the untameable spirit of Doing Things on the Cheap (a recurring theme here). A half-assed PHP reverse proxy seems easy to write. Collect target URL and POST data (if any), fire a CURL request to the intended target, pass the response along. And indeed, a web search quickly reveals a few quick and dirty implementations. Here's one. Here's another. The need is out there, a quick and dirty implementation is entirely possible, so here they go.

And yet it seems like I'm about to put together yet another one. Here are my requirements that those Q&D proxies don't meet:
  • POST data in arbitrary format (not just forms)
  • PHP session support (general purpose cookies not necessary)
  • Methods other than GET/POST
Still quite doable. Session support, however, might require some special care. Session cookie on the proxy machine might get in the way of the target session cookie. Stay tuned.

Update: I went ahead and wrote one.

Tuesday, April 1, 2014

Splitting GIF into frames on Android via giflib

This is a followup to my answer at StackOverflow regarding animated GIFs on Android. Folks want code - I've got some. The general idea is - use giflib to get RGB(A) pixel data for each frame in a format that's compatible with Android's, feed the pixels to bitmaps, display the bitmaps.

Naturally, a starting point is a Java Android project with a native library in it. The first step is including several files from giflib 4.1.4. They're sitting in an archive over here, along with a native wrapper called Strokes.cpp. Unzip the archive into your native library's folder, and list the following sources in the
  • dgif_lib.c
  • gif_err.c
  • gifalloc.c
  • Strokes.cpp
Also, insert the following line into 


Without that, giflib won't compile. Finally, rename the Java_..._LoadGIF function in Strokes.cpp to match your package and class. The function LoadGIF() logically belongs to class MyClass; we'll discuss its Java side later. LoadGIF takes two arguments - a local file name for the GIF file (not a URL!), and a boolean called HighColor that specifies the generated pixel format. With HighColor=false, it generates ARGB_4444 bitmaps; with true, it's ARGB_8888.

ARGB_4444 used to work for me for a while, but then it was phased out in recent versions of Android, so go with high color.

Now to the Java world. There are two classes there - MovieView and the second one, the one I've called MyClass for genericity's sake. Feel free to rename.

MovieView is available here.It's fairly simple; it just displays bitmaps driven by a timer. It's a view, so it can be placed into a layout file.

MyClass is where the action takes place. In my project, it's a subclass of Dialog and it's quite involved. The key part is that it holds a reference to a MovieView instance and feeds a GIF to it. I'll paste just the relevant parts here:

class MyClass
    private MovieView m_mv; //initialized on loading

    private static s_bHighColor = true;
    //Format; hard-coded here
    private native boolean LoadGIF(String FileName, boolean bHighColor); 

    //loadLibrary() is called elsewhere
    //Called from JNI - do not mess with it.
    private void AddFrame(int Delay, int l, int t, int w, int h, int Disp, byte [] Bits)
        Bitmap bm = Bitmap.createBitmap(w, h,

        s_bHighColor ?
            Bitmap.Config.ARGB_8888 :
        m_mv.AddFrame(bm, l, t, Delay, Disp);

    //Called from JNI - do not mess with it.
    private void StartImage(int FrameCount)

//////////////////////////////// The animation starts here
    public void StartMovie(File f)
, s_bHighColor))
        //This will call Reset(), AddFrames()

The flow is: you call MyClass.StartMovie(). StartMovie() calls LoadGIF() which calls StartImage() once and AddFrame() in a loop. If everything goes fine, then MovieView.Start() is invoked.

To terminate the animation, call MovieView.Stop(). This code assumes infinite loop, but feel free to follow the Disposition parameter from the GIF.

The official home of giflib 4.1.4 is here at SourceForge.