A while ago i needed a way to look up addresses from zip codes in Brazil for a weekend tinker project (Zip codes are called "CEP" here, "Código de Endereçamento Postal", or Postal Addressing Code). Turns out such a database is pretty hard to get hold of. Zip code data is created and maintained by the Brazilian Post (Correios), they have a copyright on it and are selling it for a hefty price tag. There are two versions for sale, "DNE Básico" for R$1.100 (complete dataset in text format, probably CSV), and "DNE Máster" for R$2.500. As far as i aware, the "Máster" version is just the same as the "Básico" version, but additionally contains the database in MS Access format. Okay, both are a bit above my budget.
Luckily there is another option, called "Guia Postal Brasileiro Eletrônico", or GPBe. This is an executable that you can run on your PC to manually query zip codes and addresses. And to be able to do that it must somehow contain the entire database in some form. It costs R$50, which is affordable (about US$15), so i bought a copy.
So what you get when you buy this GPBe is a Windows executable, something like this: GPBe_2018_1801.exe. Sad trombone. I'm on a Mac and too lazy to install a Windows VM.
Reverse Engineering time!
The executable is a self-extracting RAR, which can be unpacked with Unarchiver.app. Yay!
You'll get:
- Instala.exe
- SetupEnderecador.exe
- setup.exe
- Leia_me.txt
The database is located in setup.exe, which is an Inno Setup executable. You can unpack such an exe with the tool innoextract, available via Homebrew: brew install innoextract, then do innoextract setup.exe.
You'll find the database in app/Access/dbgpbe.mdb. This is a Microsoft Access database. You can export tables to CSV (or to other databases) with mdbtools, also available via Homebrew: brew install mdbtools, then get the list of tables with mdb-tables dbgpbe.mdb, and export each table to CSV with mdb-export dbgpbe.mdb <table-name>.
Done!
No, wait.
Look at those CEP tables:
Yeah that's right, that looks like base64 encoded garbage. They encrypted the actual CEP in the database. I read somewhere that they use TwoFish encryption with a SHA-1 hashed key "gpbe", but somehow didn't succeed in my decryption attempts. Maybe they changed the encryption method, or i am just doing something wrong. In fact, i looked at some CEP values and found that incrementing CEPS in most cases lead to incrementing encoded CEP values, so i don't know if this is actually TwoFish. It might be something much simpler.
Anyhow, turns out we don't need that CEP column at all. All tables that have CEP columns also have LOG_KEY_DNE columns, which contains the same CEP, just encoded a bit differently. I assume to be able to search the database for parts of the CEP?
There are freely downloadable CEP databases at http://cep.la Zipped tsv form. Collected by enthusiasts recently and years ago.