System Overlord

A blog about security engineering, research, and general hacking.

Migrating an Access Database to MySQL

I'm currently taking a Database class as part of my requirements for my M.S. in Computer Science. Several of our assignments are based on a database provided to us as a Microsoft Access Database. While I have a Windows 7 Virtual Machine, and could install Office in it, I prefer to use free software whenever possible, so I looked for a way to use this database with free software.

Fortunately, the database is in the earlier .mdb format, and not the newer .accdb format. I first found a glimmer of hope in an article by Niall Donegan describing the use of the MDB Tools package.

While the steps posted by Niall worked, and worked well, there are a couple of quirks in MDB Tools that took some working around. Additionally, the steps are kind of repetitive. So I decided to write a small wrapper script for mdb-tools to export the data as a MySQL script. The script takes one argument (the name of the mdb file you're working with) and outputs the SQL script on standard output. So, for example, you might use it as: mdb2mysql students.mdb|mysql students. Here's the script (I call it mdb2mysql) itself:

if [ $# -lt 1 ] ; then
        echo "Usage: $0 [mdbfile]" > /dev/stderr
        exit 1
# Extract the schema/DDL
mdb-schema $MDB mysql | sed 's/DROP TABLE/DROP TABLE IF EXISTS/'
# Extract table data
mdb-tables -1 $MDB | while read TABLE ; do
        mdb-export -I $MDB $TABLE | sed 's/$/;/'

Hopefully this helps others who just need to extract their data from an Access Database. It should be noted that this only gets the schema and data, and does not include foreign keys, views, etc.

Using an SSH Connection to Provide Remote Support (Part I)

Last week, at the ALE meeting, a question came up about using SSH to provide remote support for someone who is not especially Linux-literate.  I suggested using an SSH reverse tunnel so the end-user wouldn't need to worry about firewalls, NAT, etc.

Thinking about the problem, I realize that it's a little more complicated than that.  So in part 1, I'm going to discuss the general solution and the approach to the problem.  In Part II, I'll present a more comprehensive solution that will (I think) scale better.

Let's first talk about reverse SSH tunnels.  These tunnels allow a data stream to be carried across the SSH connection in reverse -- that is, from the server to the client.  This is useful for getting back in past a firewall/NAT router/etc. without needing to make configuration changes.

The Basic Premise

First off, let's be clear on the terminology we'll be using.  The "client machine" is the machine being used by the person receiving support.  The "server" is a machine under the control of the person providing the support.

Server Setup

  • Install OpenSSH Client & Server
  • Provide inbound access to SSH (port 22 or alternate port) (this may require firewall changes, router configuration, etc.)
  • Generate a keypair (we'll call this 'reverse.key') to be used to connect back to the client.
  • Create a 'support' account for inbound connections.
  • Set up a DNS entry (dynamic DNS is fine) for the server. We'll call it

Client Setup

  • Install OpenSSH Client & Server
  • Add '' from above to an account 'support' that has sudo access (you'll probably need sudo to provide support.)
  • Generate a private key (we'll call this key 'support.key') and copy off the public portion.  This should be added to the server's 'support' account.

The Script

Place this script, marked executable, on the user's desktop. Double clicking it will allow a support connection in.

ssh -N -R 2222:localhost:22 -i .ssh/support.key &
        echo "Support connection ready!"


After the script is run, you can ssh -p 2222 support@localhost to connect to their machine via the reverse SSH tunnel.

In the next part, we'll talk about a script to generate most of this for us and make it much easier to set up.

Boost, RSS Feeds, and Google Reader

For a while now, I've struggled with an issue on this site.  Google Reader would sometimes show items that had already been displayed in the reader.  They would be shown as new unread items, regardless of whether the "original" copy of that item had been read.  I'm sure this irritated many readers, and I tried several times to fix the issue.

  • The feed was successfully validated by the W3C Validator.  Multiple times.
  • Adding the feed freshly worked fine.
  • Adding the feed to other RSS readers showed only 1 per item.

I set up a cron job to pull a copy of my RSS feed regularly and save copies.  I figured I could see if anything changed between versions.  At first, the differing versions showed no significant changes.  (Other than new posts where expected.)

At one point, I got a clue from a fellow ALE-NW organizer that the feed was showing duplicate items.  Looking at the view that generated the feed, I realized each tag was causing a duplicate entry.  I deleted the offending relationship, and the number of entries got better.  I figured I had the Google Reader issue fixed.

This weekend, it sprung its head again -- duplicate entries!  I looked back at my cron-based RSS archive and discovered that there were differences in some of the files!  As I looked at the differences, I felt like an idiot.

The first file contained: <guid ispermalink="false">149 at</guid>.

Another file contained: <guid ispermalink="false">149 at</guid>.

I realized, as I read the differences, that Drupal bases its "base URL" on the URL that is used to access the site. (I used to use This isn't normally a problem, because the RSS reader would be accessing it via the same domain every time, but once you're running Boost, you can get different domains from the cached copies of the files! So, if the cached RSS feed expires and Boost builds a new one on an access from, the subsequent access by Google Reader returns a feed with URLs. These guids are different, and so Google Reader believes they're different articles!

I've now set $base_url = ''; in my settings.php. I believe this should finally, permanently, put the duplicate item bug to rest.

Where My Goals Lie

Lately, I've been doing a lot of thinking about my life goals.  While I realize that 26 is still comparatively young, I really feel like I'm not making enough progress towards where I want to be.  Rather than moping on about it, as I have for quite some time, I've been inspired by Sacha Chua to actually do something about it.  Sacha is all about getting things done and making the most out of life, or, to quote her blog title, "Living an Awesome Life."  Whining is not living an awesome life.

I've decided to identify (some of) my life goals, what is preventing my from achieving them, what I am doing to accomplish them, and what else I could be doing to accomplish them.

Goal #1: Achieve financial comfort.

While it may seem greedy or shallow to list a monetary goal first, financial stress can inhibit the ability to achieve other goals.  Having little to no savings makes career changes riskier and more stressful.  Many of my other goals have SOME financial requirements.

This goal is intertwined into many other aspects of my life, including my progress towards a graduate degree.  I like my current job, but I'm not sure that there's much (if any) more room for advancement there.  Eventually it will be time to move on, no matter how hard it may be.

Goal #2: Do original work in Information Security.

I have long been fascinated by the technical aspects of information security.  Securing applications, penetration testing, IDSs, firewalls, and other security technologies are just a few of the things I want to get into more.  I'd like to do original research and development in Information Security.

In order to do that, I need to find a more specific niche to work on, and devote some time to really working on it.  I think part of that involves reading more, but even more so, DOING more.  I need to stop just reading and use what I've learned to develop the deeper understanding needed for original work.  I'm hoping to incorporate some of this into my M.S. in Computer Science, but again, I'll need a more specific topic.

Goal #3: Engage more with like-minded individuals.

I fairly regularly attend meetings like Atlanta Linux Enthusiasts, DC404, etc.  I do this less for the presentations (although lately I've been a frequent speaker at ALE meetings) than for the interaction with the other individuals.  While there are a couple of people at work who have similar interests, it's a rare opportunity to have an in-depth conversation about the topics that interest me.

I have yet to figure out how to fix this one.  The only idea I've had so far involves career change, which might be a bit much to meet this goal.  I guess my meetings will have to do for now.

Goal #4: Speak at a 'major' conference.

I've done quite a bit of speaking at minor conferences, monthly meetings, etc., and really enjoy the level of preparation necessary to teach others.  I don't think I could teach professionally, but I feel like being accepted to speak at a large conference somehow validates your work, be it original research or just presenting a topic to an audience unfamiliar with it.

I'm working my way up there.  I've got a couple of topics I'm thinking about developing for a talk at SELF next year.  Eventually, I'd like to get up to SCALE, Defcon, etc.

Goal #5: Find my niche.

This goal intertwines with all of the above.  Right now, I'm somewhat of a generalist in the realm of information technology.  While that's certainly valid, I'd like to specialize more into security, but even that is very general.  Moxie Marlinspike is known for his SSL research, Dan Bernstein for DNS, Bruce Schneier for Cryptography, etc.  All of them interest me, and I don't have the time and/or energy to get into all those fields.

I hope that one day I'll stumble upon my niche, or -- at least -- develop something within one of those fields.  Again, it comes down to "doing" rather than "talking."

More minor goals in my life:

  • Attend Defcon, Black Hat, SCALE, OSCON, LinuxCon, DerbyCon, and the other top-shelf *cons.
  • Obtain a CISSP and LPIC-3
  • Get over my health-related anxieties.
  • Find something good to do with an Arduino.
  • Have fun!


This list is not comprehensive, and even writing it out has led to some internal revelations regarding what I should be doing to achieve my goals.  This, being a blog, is also an invitation to input on what I'm missing from achieving my goals.

Git On Your Web Server: A Security Reminder

Earlier this month, I wrote about managing a Drupal site with git.  What I neglected to remember, of course, is this places a full copy of your git repository within your web server's document root.  This has the potential to expose any data in your git repository -- a malicious attacker could (depending on your configuration) clone the entire repository, thus exposing source code, configuration files, database dumps, and other sensitive data.

Adam Baldwin did an interesting study on exposed repositories, and shows that the problem is widespread, even among very large-scale websites.  He also offers points on how to protect your git repositories, but the Nginx directions didn't exactly work for me.  No matter what I tweaked, I couldn't get "deny all;" to actually deny anyone!  I ended up using "return 403;" and that worked quite well.  You could even return 404 if you wanted to hide the repository entirely.  For completeness, here's my configuration for protecting git:

location ~ /\.git {
    return 403;