Just A Programmer We're just programmers


Knowledge Quest: Reverse engineering unmanaged DLLs

As a programmer person, I am always learning. I learn different thing in different ways, at different rates, and for different purposes. Usually, I write these blog posts about things I have learned recently. This post is about what I am currently learning.

First, a little background. Around 2006 I was programming Windows CE devices, and syncing Pocket Access databases on them with a Microsoft Access database on a desktop via ActiveSync. Since I had no need for the Microsoft Access application, but I did need to be able to write SQL queries against the Access Database, I wrote PlaneDisaster.NET.

Recently, I realized that PlaneDisaster.NET did not work on 64 bit machines. The simple fix was to compile it as a 32 bit executable. However, I wanted to make it work as a 64 bit executable. This became doubly important when I realized that I could not use OPENROWSET() on a 64 bit instance of SQL server to connect to an Access database for the same reason that PlaneDisaster.NET would not work as a 64 bit process. Of course, doubly important was still not that important in the grand scheme of things.

Eventually, I did dust off PlaneDisaster.NET and got it to work as a 64 bit executable. Running PlaneDisaster.NET as a 64 bit process requires the 64 bit version of Microsoft Office 2010 or the Microsoft Access Database Engine 2010 Redistributable to be installed. However, even after doing this, I was unable to get two features of PlaneDisaster.NET to work., compacting and repairing Access databases.

PlaneDisaster.NET makes three calls to the unmanaged function SQLConfigDataSource(). These three calls pass the commands CREATE_DB, COMPACT_DB, and REPAIR_DB respectively to the JetSQL Engine. The first one works fine with  the 64 bit driver. However,  I cannot get the second two to work. I cannot find any documentation on these calls except one MSDN document last updated in 2001 which of course was written for the 32 bit driver.

So the question is, how do I plan on figuring out how to do this? My current plan involves using Nirsoft’s DLL Export Viewer to get the addresses of the dll entry points of the unmanaged calls I am making. I will then use these addresses to step through assembly in the Visual Studio Debugger. I have only toyed with assembly. I don’t know if I will succeed in this task. However, I have already learned a lot, and I will continue to learn more. I might end up solving this problem via an alternative solution. I might simply learn enough to better articulate a question on StackOverflow that gets an answer. Or,  I might find that I am able to successfully step through the assembly and figure out this problem that way.

I will keep you all updated.


Creating an Access Database In Powershell without Access installed

Recently I stumbled across a Hey, Scripting Guy! blog post titled “How Can I Use Windows PowerShell to Create an Office Access Database?” that demonstrated how to create a Microsoft Access database in powershell. The only problem with this script was it required Microsoft Access to be installed on the machine that it ran on. I knew this dependency on Access was unnecessary, because a few years ago I wrote an open source project for manipulating Microsoft Access and SQLite databases called PlaneDisaster.NET. That program did not require Microsoft Access to be installed and it allowed you to create, compact and repair databases. (I’ve discussed PlaneDisaster.NET previously on this blog.)

So, as a matter of personal and professional pride I determined that I must write a PowerShell script that could create an Access Database without requiring Microsoft Access to be installed. After all, If you can do something in C#, you can do it in PowerShell. I did have one concern. I was making unmanaged Win32 API calls via PInvoke. However, I quickly learned that’s not a problem at all. My particular PInvoke calls involve enums, but you can create enums in PowerShell. However, I ended up replacing the enums with ints in the PInvoke declarations and no one complained since PowerShell is good at figuring that sort of thing out. The code I ended up with is:

$signature = @'
[DllImport("ODBCCP32.DLL",CharSet=CharSet.Unicode, SetLastError=true)]
public static extern int SQLConfigDataSource
    (int hwndParent, int fRequest, string lpszDriver, string lpszAttributes);
[DllImport("odbccp32", CharSet=CharSet.Auto)]
public static extern int SQLInstallerError(int iError, ref int pfErrorCode, StringBuilder lpszErrorMsg, int cbErrorMsgMax, ref int pcbErrorMsg);
Add-Type -MemberDefinition $signature -Name Win32Utils -Namespace PInvoke -Using PInvoke,System.Text;

This created two static functions I could call, [PInvoke.WIn32Utils]::SQLConfigDataSource() and [PInvoke.WIn32Utils]::SQLInstallerError().

However, a new problem emerged. I could not get the script to run in a 64 bit PowerShell process. A quick google search informed me that the only way to get a 64 bit Access driver is through the Microsoft Access Database Engine 2010 Redistributable. However, even after installing the 64 bit version of that executable, my script did not work unless I ran it through a 32 bit instance of PowerShell.

After a lot of searching and frustration I eventually had an epiphany. The provider name I was using to create the database was Microsoft Access Driver (*.mdb). This provider name refered to ODBCJT32.DLL, which is only available as a 32 bit version. However, the driver that ships with the Access 2010 redistributable is called ACEODBC.DLL. This dll has a provider name of Microsoft Access Driver (*.mdb, *.accdb).  The code for this is simple:

[string] $driver = 'Microsoft Access Driver (*.mdb)';
if ([IntPtr]::Size -eq 8) {
    $driver = 'Microsoft Access Driver (*.mdb, *.accdb)';

Yes I’m using the size of a pointer to determine if I’m running in 32 or 64 bits.

The full script is below:

That version is hosted on poshcode.org to be more searchable. However, the authoritative one will remain as a github gist.


The cost of fixing a computer, and Open Source Software

Note: I started writing this a while ago, but just finished it. This is made obvious by the time stamps of the tweets I reference.

Twitter is a great tool, but sometimes you need more than a 140 characters for a reply. This is one of those cases.

It all started with a retweet by Karen Lopez, a.k.a. @datachick:

@jlopez255: Starting today -”Name your own price computer repair”, U name the price for my labor and I will fix your computer. Parts extra.

Which lead to me putting forth the following propositions to Karen and Jennifer:

  1. It’s a problem that is is cheaper to buy a new computer than repair it for low end computers under 3 years old.
  2. We need to make repairing computers cheaper, because many individuals cannot afford what professionals rightfully charge for their skill level.
  3. OSS is better suited than closed course for bringing down the cost of computer repair.
  4. You can petition the Lord with prayer.
On point one I was preaching to the choir. On point number two I received no direct response. However, for number 3 Karen responded:
@zippy1981@jlopez255 I have not had great ease of use with OSS. For every great 1, there are 10 that require lots of Dev / tech skills.
I don’t refute this statement at face value, However, I do have the following commentary to offer backing up my third proposition.
First of all, lets limit the scope to the average personal machine owned by joe sixpack. Microsoft Office, or Open Office (or Libre Office) are power tools for these people. Other than an Office Suite, we are really talking about the web browser, and maybe the email client. For these users, a default Ubuntu install is more than enough. Most of their stuff is done on the web. Open/Libre Office are a little lacking in UX and features, but google docs makes up for the UX. Also, most of these users don’t want or need the missing features. In terms of web browsers and a window manager, the web browsers are exactly the same (yes I trained both my parents to use firefox), and the Ubuntu GUI is on par Windows 7 in terms of polish, with some interesting innovations such as how it handles full screen windows. Also, for netbooks, Ubuntu has a special install disc that tweaks the UI for the small screen.
Secondly, with Ubuntu, Linux is truly for end users. Rarely does something not work. When it fails to work, you might have to bust out the command line, but the procedures are no more complicated than what one would have to do on windows. Quite frankly, if you want a *nix OS that requires you to do a lot manually, try a BSD or Solaris.
Third, my point was OSS is better suited for reimaging hard drives cheaply than windows. Because OSS is free as in beer for all practical purposes, there are no licensing restrictions to enforce. Therefore, there are no restrictions on creative ways of deploying Linux automatically. Windows Deployment Services (WDS) and its predecessor Remote Installation Services (RIS) are expensive, and designed for enterprise deployment. Microsoft could develop a version of these aimed at mom and pop geeksquad equivalents. If such a program allowed you to entered license keys for windows, office, etc and it install fully patched versions of the licensed software, plus whatever third party stuff they wanted to add (and their was a gallery where you could pick third party free and paid software to install), that might compete with what can be achieved when your software is free as in freedom and beer. However, I honestly don’t see Microsoft doing this until someone does this with Linux first.
So my point was that OSS has better potential for automating PC repair compared to Windows. You can simplify the process with OSS which will allow less skilled people to perform PC repaired. These less skilled people will command lower fees.

Yak Shaving Digest 2011-08-13

This blog post is inspired by Brendan W McAdams (blog | twitter) for introducing me to the term yak shaving, Aaron Bertrand (blog | twitter) for his Connect Digests and Jennifer Lopez (not that one) (website | twitter) for expressing  her desire to see the intersect of who she follows on twitter and who follows her, which thereby made me want to do the same thing.

I was working on a C# console app the past week or so that would find the intersect of my following and followed by list on twitter. This Saturday was dedicated to getting it suitable for github, which it now is. However, I encountered several issues along the way. Because I had some blocking issues, I allowed myself to be distracted  by every minor issue  I encountered with the libraries and tools I was using for the task, for a brief period of time. The purpose of this was to report the issues, and if possible resolve them. This lead to bug reports, feature requests, and two patches. I listed them all here to add some context to all of them.

  • OAuth issues: This actually occurred earlier in the week. Twitter uses OAuth v1, which I discovered isn’t really suited for desktop apps because you have to share the secret that associates a request with your app with the world. That’s the equivalent of being required to share a pgp private key with the world. My workaround is to simple make users of my app generate their own app key (annoying, but something you’d probably be willing to do to get CLI access to twitter). If the app becomes popular I will request xAuth access for my application.
  • Sensitive info in config files and version control. This is related to the first issue. This app was not even stored in a local git repo despite me being a SCM nazi because I needed to figure out a strategy that would allow me to push an empty app.config to github. I store my consumer key/secret in the app.config, which I point out above I don’t want to share with anyone. I solved this problem by not checking the config file into version control and having a sanitized one with a different name in version control. Also, I used configSource so all my OAuth things would be in a separate config file from the main one.
  • Being anal retentive about KBCsv overloads. I am a huge fan of Kent Boogaart‘s CSV library. In my console app, I wanted to output the results as a CSV, so I could sort the data with excel.  However, along the way I got the crazy idea  to change some constructor parameters from string[] toIEnumerable<string>. I discovered this could lead to ambiguous overloads . However, I did submit a fully working patch for IList<string> and WIP for the IEnumerable<string> with some commentary and asking if it made sense to complete the work. See patch 101185 and 101186 in the KBCsv patchlist for the actual code. I’ll call this a partial victory.
  • Filing a feature request with HelperTrinity. HelperTrinity is Ken Boogaart’s general helper library, used by KBCsv. While hacking on the KBCsv source I thought, “Hey! This could use some more helper and extension methods.” So I submitted a feature request.
  • Finding  a bug in ReSharper. Digging through the KBCsv source code lead me to find an edge case that could lead to resharper reporting a false error. So I opened RSRP-274868.
  • Filed two codeplex feature requests. I went total meta and made two suggestions for codeplex itself. Both are the sort of simple things that I expect will be received by the codeples staff with either a “great idea” or “no thats so terrible for this reason”
  • MongoDB feature requests. I constantly find the BSON implementation in the MongDB C# driver useful outside of mongodb. It was useful here, but the time it saved me was eaten up filing two feature requests. I hope to implement patches for both of them.
  • Finally, one of my twitter calls broke. Twitterizer is the library I am using for twitter, and in the middle of debugging a call that always worked stopped working. Looking elsewhere on the forums shows this is not a problem unique to me. This is the event that convinced me that my time would be better spent shaving the yak. In the end, this is still an open issue, but reverting to an older version of the library in a different branch, and excessive use of git cherry-pick made it work.
  • Epilogue: The app works, but LibreOffice does not correctly import all rows in the spreadsheet. Since its all the import columns occur before the problems occur, and this can be cleaned in a text editor, I will investigate and file the bugs appropiatly with KBCsv and LibreOffice appropriately. Also, I need to get access to a machine with Excel installed. I expect Excel to be better at parsing CSV.  It seems that I was specifying space as a delimiter in the CVS import dialog. Once I unchecked that the CSV imported properly.

So in conclusion, my app works mostly, and between my bug reports and patches, I hope that the libraries and ReSharper will be improved. Also, by writing this blog post, I hope that having the big picture will be useful to myself as I polish of this twitter utility, and implement fixes for some of the bugs.