Monday 24 August 2015

Ashley Madison - Shaming the Innocent

The Leak

Last week a database backup containing over 36 million Ashley Madison users and associated profiles data was leaked to the Internet, and made available publicly for everyone to download via a 9.6GB peer-to-peer Torrent. As of this post over half a week later over 3,400 seeds (users willing to send the data) and over 1,400 active downloads are currently in progress for the file. In short, the data has been downloaded by an extremely large number, and more and people are getting the data every day.

Within 24 hours of the leak reports of high profile public individuals were being reported as being users on the site, associated only by their e-mail address. This information was quickly picked up by the press and articles were written shaming the public individuals behind these accounts without further validation. Were some of these individuals guilty, or were they innocent? Most of the press did point out that there were fake profiles on the site in passing, but didn't always include details that e-mail addresses that were used at registration were never properly validated by the site to prove that the account owner actually had control of that e-mail account. If you looked at any popular site database in the mid 90's when e-mail validation was rare, the number of people that used e-mail addresses such as billg@microsoft.com to register was amusingly high (amusing since it really didn't matter). To ensure fake accounts were not registered, basic validation was put in place where you had to confirm ownership of your account. Without this, the chance of a high profile e-mail address being used when thinking of an e-mail address is quite high. 

Shame on you!

One prominent example that comes to mind that I will be focusing the remainder of this article on is the public shaming of Canadian Member of Parliament, Eve Adams, who was first shamed by the press when a Huffington Post article said that her government e-mail address was one of the Ashley Madison users. The article can be read in full here: http://www.huffingtonpost.ca/2015/08/19/ashley-madison-eve-adams_n_8012694.html

At the end of the article they do mention that "Ashley Madison also does not send verification emails, meaning the accounts might not belong to actual users of the site and could simply be the work of disgruntled tricksters."but publicly linking an individual to having an account based soley on e-mail address without taking the time to at least examine the associated data to that address in the dump is completely unfair, and sloppy reporting. The issue was likely a technical one where the data to the average computer user appeared to be unnavigable gobblygook, where in reality that is far from the truth. Lets look at the raw data ourselves, looking for this particular e-mail address.
Searching the raw data dump
1,0,0,0,1,0,1),(2149120,2....???? Ahh whatever we have an e-mail address. To Twitter, Facebook, Pastebin, and the press!


Going in

The data that was leaked is actually in the perfect format to search and query. The .dump files are all MySQL/MariaDB database backups (which is what Ashley Madison would have been running as their back-end database server), which means with the installation of the database software (free) you can restore the backups and navigate them as if you were on the Ashley Madison servers yourself.

Data restoration

With the data downloaded, and the MySQL or MariaDB (recommended) database software installed, the data can be imported using only 6 commands:
mysql -u root -p create schema am 
mysql -u root -p 
am <  member_login.dump
mysql -u root -p am <  member_details.dump
mysql -u root -p 
am <  aminno_member.dump
mysql -u root -p 
am  <  aminno_member_email.dump
mysql -u root -p 
am <  am_am.dump 
The import could take a significant time depending on the hardware you have, but with enough RAM (16-32GB) and a fast SSD hard disk, you can have the data imported in a matter of hours. Once imported you can navigate the database using the Structure Query Language (SQL).

Data structure

To properly query the database we must first understand the structure of the database. Each imported dump file corresponds to a table in the database. Using simple commands you can navigate the database and its structure.

Navigating table structure

  •  show tables: This command lists the tables in the database. You'll notice they are the same names are the .dump files from the leaked data.
  • describe table_name: This command will output the structure of the data. Think of it as a heading on a spreadsheet. 'field' represents the name of the field, which we will need to execute our query. The 'type' represents the type of data that can be stored in the field. int(1) means only an integer 1 characters in length can be stored, and varchar(128) means text up to 128 characters can be stored. The key 'PRI' represents the primary table key, which in the above example is the user identifier which links all data from other tables together.
Querying unique identifier based on e-mail address

The above query grabs the unique identifier from the member e-mail table. This query is essentially where the majority of search sites and press stops. If the e-mail exists, the owner of that address is assumed guilty and is probably staying in a hotel right now, but what about the other data?

describe aminno_member
Looking at the structure of the other tables, you'll quickly notice that there is a LOT of information about the account in the database. If the user had a profile, all data associated with that profile is available to search. This is extremely relevant because it allows you to determine if the account details match the individual named in the address, and if the account was ever used. There are a significant number of accounts in the database which don't even have an associated profile, which means the user signed up, but never actually used the site to do anything.

The Data

select * from aminno_member where pnum = '26254553'
Performing a query on the remainder of the tables using the unique identifier obtained from the first query, we are able to pull all of the profile data associated with this account. As you can see the data can still be a challenge to figure out how to navigate, but instead of boring you with the low level details I'll just provide the results of the various queries and mappings for the data on the account which has been reported to belong to Eve Adams.
  • Unique Identifier: 26254553
  • Sign up IP Address: 184.151.246.90
  • User name: Greg-hidden
  • Gender: Male (2)
  • Zip (Postal) code: K1Z6P9
  • City: Ottawa
  • Country: Canada (2)
  • Date of Birth: 1974-11-11
  • Account Created: May 31st 2014
  • Last Updated: June 1st 2014
  • Last mail: Never
  • Last chat: Never
  • Last mail reply: Never
  • Caption: ola ladies
  • Open to: I am white African American archaeologist, focusing on northern Canadian inuit culture. I also work for the Conservative party and like to run, bike and hit the gym when I have the time.
  • Seeking: Male seeking Females (3)
  • Relationship: Single Male seeking Females (3)
  • Body type: Muscular
  • Weight: 215 Pounds
  • Longitude: 45.39941
  • Latitude: -75.75595
Well, at least the Country is accurate...

Analysis

Now that we have all of the relevant information associated with the account that used the e-mail address of Eve Adams, lets do some analysis on the profile data to determine the legitimacy of Eves account.

  • User name: Greg-hidden - 'Greg' isn't a very good alias for a female account.
  • Gender: Male - Eve is female.
  • Seeking: Male seeking Females -  Eve is female.
  • Relationship: Single Male seeking Females - Eve is female.
  • City/Country: Ottawa Canada - This matches technically since I'm assuming Eve would live in Ottawa during her tenure in Parliament. 
  • Date of Birth: 1974-11-11 - Inaccurate. Eve was born on 1974-11-07 (Source: wikipedia)
  • Weight: 215 Pounds - I try not to guess the weight of a Woman, but I think it is safe to say this is unrealistically high.
  • Sign up IP Address: 184.151.246.90 - You can determine from the IP address that the individual signed up for the account in Ottawa on the on Bell Mobility wireless network on May 31st 2014.
  • Postal Code: K1Z6P9 - This is where things get interesting. Canadian postal codes are set to a small block of residences (in this case, 10). This one code resides on Ferndale Ave in Ottawa.

Area of Postal Code K1Z-6P9
  • Longitude: 45.39941
  • Latitude: -75.75595 - I have no idea how this information is extrapolated when a user signs up, but it is in the same area of the postal code regardless. 
GPS Co-ordinates
Doing more online forensics I'm fairly certain that I have identified the exact location (house) and the individual that created this account. However, I'm going to refrain from publishing further granular data here since the point of this article isn't to track people down who give false data. I doubt they ever expected it to get leaked like this, and used her e-mail address because they thought it was funny.

I do however have enough details to confirm that this wouldn't be an account created by Eve Adams.

Conclusion


Just because an e-mail address shows up in the database, doesn't mean the account was created by the individual who owns the address. Don't just assume and accuse someone you know/love based on a simple unverified e-mail address, and do not publicly shame them either (I'm looking at you Huffington Post). How would you react if your e-mail was in the list when you never signed up? Even if the e-mail owner did sign up for the site, doing some basic analysis the majority of people never created a profile, or sent or received messages. Context is everything. One thing I've learned from doing many years of forensics is to avoid jumping to conclusions early. Try to prove yourself wrong.

I am definitely not suggesting that people grab this database and start digging into the data on your spouses or friends (it really isn't your business), but instead I'm hoping to raise awareness that you likely don't have the complete picture. As for those falsely accused of cheating based on an e-mail address, have at it!

4 comments:

  1. I believe I have noted in the timestamp field for Account Created there are no dates beyond February 2015. Is that true of all time-stamped fields - nothing post February 2015? Or am I missing something. Thank you.

    ReplyDelete
    Replies
    1. That is not true on all time-stamp fields from different tables, which means the am_am_member table was likely backed up in February, and the remainder was done in July.

      MariaDB [am]> SELECT DATE(createdon) FROM am_am_member ORDER BY createdon DESC LIMIT 5;
      +-----------------+
      | DATE(createdon) |
      +-----------------+
      | 2015-02-23 |
      | 2015-02-23 |
      | 2015-02-23 |
      | 2015-02-23 |
      | 2015-02-23 |
      +-----------------+
      5 rows in set (1 min 24.49 sec)

      MariaDB [am]> SELECT DATE(bc_mail_last_time) FROM aminno_member ORDER BY bc_mail_last_time DESC LIMIT 5;
      +-------------------------+
      | DATE(bc_mail_last_time) |
      +-------------------------+
      | 2015-07-11 |
      | 2015-07-11 |
      | 2015-07-11 |
      | 2015-07-11 |
      | 2015-07-11 |
      +-------------------------+
      5 rows in set (17.64 sec)


      MariaDB [am]> SELECT DATE(bc_chat_last_time) FROM aminno_member ORDER BY bc_chat_last_time DESC LIMIT 5;
      +-------------------------+
      | DATE(bc_chat_last_time) |
      +-------------------------+
      | 2015-07-11 |
      | 2015-07-11 |
      | 2015-07-11 |
      | 2015-07-11 |
      | 2015-07-11 |
      +-------------------------+
      5 rows in set (16.12 sec)

      Delete
  2. So Custodela what do you think that potentially means in terms of pics and messages. Anything? If they grabbed "last 30 days"?

    ReplyDelete
    Replies
    1. All this means is they probably had many months of access to the network. An insider threat would be more likely to smash and grab as much data as fast as possible (i.e. connect an external drive and copy everything over a weekend) so I would also make an educated guess that this was remote access, where it takes time to take this amount of data, and where persistent access has far less risk.

      With months of access they could have copied pretty much anything, and if the images were flat files (i.e. not database binary objects) it would be trivial to extract.

      Delete

About