E-mail anonymization in Firebird

29 09 2011

I worked recently with Firebird database. I want to share my experience in one task I had to do: anonymization of e-mails.

To anonymize all e-mails in a table run in Firebird one of the following SQL expressions depending on what output is desired.

Let’s assume we have two tables MEMBRE and SOCIETE (in French: member and company). Both have an email column. MEMBRE has NOT NULL and UNIQUE clauses on that column. SOCIETE is not restricted.

In my case I wanted to be able to see if e-mails were actually sent to the anonymized addresses. That’s why I change domain names to @yopmail.com. See www.yopmail.com for more details about the service.

Scenario 1

update membre set email=substring(email from 1 for position('@' in email)) || 'yopmail.com';

update societe set email=substring(email from 1 for position('@' in email)) || 'yopmail.com' where email is not null and email like '%@%';

Will do the following change: john.doe@company.com => john.doe@yopmail.com

The simplest solution but may cause a data violation error if the same username is used in different domains, e.g. john.doe@company.com and john.doe@societe.fr

Scenario 2

create sequence anonymizator;

update membre set email='mem' || next value for anonymizator || '@yopmail.com';

update societe set email='soc' || next value for anonymizator || '@yopmail.com' where email is not null and email like '%@%';

drop sequence anonymizator;

Will do the following change: john.doe@company.com => mem1@yopmail.com

Requires a sequence which is created before and deleted after the operation. ‘1’ in the result address will be increased for subsequent rows. Completely erases any information of previous e-mail value so it really anonymizes data.

Scenario 3

update membre set email=substring(email from 1 for position('@' in email)-1) || '_' || substring(email from position('@' in email)+1) || '@yopmail.com';

update societe set email=substring(email from 1 for position('@' in email)-1) || '_' || substring(email from position('@' in email)+1) || '@yopmail.com' where email is not null and email like '%@%';

Will do the following change: john.doe@company.com => john.doe_company.com@yopmail.com

The most sophisticated but keeps information of initial e-mail value. So doesn’t really anonymize but successfully prevents sending e-mail messages to affected addresses.

Note: Anonymizing e-mails may increase its length and therefore may cause an error during execution. When the database replies

arithmetic exception, numeric overflow, or string truncation

it means that a new value probably exceeds the length of field being changed. The table can be changed with this SQL:

alter table membre alter column email type varchar(100);


27 09 2011

I didn’t post about Ubuntu for a long time. Today I decided to break the silence. I’ll explain later what made me do that.

The reason why I didn’t say a word about Ubuntu is Unity. This new default interface in Ubuntu brought many controversies to the distro’s community. My first impression was: “Ok, we’ve got something new. It looks odd by we need to move forward. Win95 looked strange in 1995 but it change our way of interacting with machines”. I gave it a chance despite of the fact that most of blog entries were somewhere between “I don’t like Unity” and “I hate it”. I used Unity since it was set the default UI in Ubuntu. I wanted to post about Unity once I have a deep enough insight in this UI.

Yesterday after a subsequent “minor annoyance” in Unity I switched to Gnome. And guess what? Suddenly I felt… just better. But to be honest here are my remarks on Unity.

The good
Lenses! I like it very much and I already miss it after switching to Gnome. It’s really a very useful feature.

The bad
Slow! Unity is just much slower than Gnome. And to be honest — I realized that speed is the most important reason why I use Linux on daily basis. But with Unity I felt like I stepped one step back.

Hiding a window top panel. I find this feature really frustrating. For example — when I have two windows in Gnome side by side I can directly select an option in the second window’s menu when the first window has focus. With Unity it require one click more. It’s especially annoying when you want to close a window. First you need to pass focus to it and then close it. I just can’t stand it. What’s even worse — this feature is buggy. E.g. WINE windows incorrectly receive mouse position when the window is maximized. It’s a nail in the coffin.

Limited configuration of sidebar. I like the sidebar it’s functional and… pretty 😉 But I missed some configuration options. E.g. how to change some icons positions? For example, I want partitions to be higher.

The ugly
Bugs! Bugs! And more bugs! They are not “critical” but highly annoying. Just to give some examples of bugs that happened to me:

  • invisible window — covers some part of the screen and prevents clicking anything under it. Nice, huh?
  • hanging — Unity just hangs from time to time.
  • exploding theme — not every time but quite often any theme, be it Ambiance or Radiance or any other, explodes and switches to a gray default theme. Icons, top panel and UI other element becomes “default” but windows keep their Ambiance decoration.
  • disappearing icons in sidebar — as I said I like the sidebar but sometime some icons disappear. E.g. “applications” and “places” (or whatever it’s called — I don’t remember).

That was enough for me. I switched back to Gnome and I’m happy. I wish all the best to Unity. I hope some issues will be solved in the incoming release. But in Unity 11.04? — No, thanks.