Databases

Databases are where serious applications and servers hold all their data. A program that allows databases to be created and modified is called a Database Management System (DBMS).

The language to communicate with a DBMS is SQL: Structured Query Language.Most DBMSes accept all SQL commands. Some have quirks for SQL commands. Some have non standard SQL commands. But for basic stuff, any DBMS will do.

There are a number of floss DBMSes:

You may have also heard of some proprietary ones:

Inside a database you will find a number of tables (think spreadsheets, not furniture). Each table has some fields/columns that store different types of data, and a number of entries/rows which hold that data. Tables will also have one or more Primary Keys, which are used to find a unique row. Often the primary key will just be a number, like a customer ID.

To make the utterly boring topic of databases interesting, let's mess about with the Ashley Madison database. Ashley Madison is/was a dating site for married people. It was owned in 2015 and there is a torrent available.

Installing a DBMS

MariaDB is cross platform, so let's have a play. First step is to grab the binaries:

With the binaries downloaded and extracted, you need to do some initial setup:

Now it's time to run the mysqld daemon. This will run the DBMS as a service on your machine.

You can also set some security parameters in Linux:

At this point the DBMS is installed, configured, and running.

Now let's import the Ashley Madison data to our new "am" database.

With the .dump extracted, import it into the databasse.

Basic SQL Syntax

With one or more tables imported into the database we can have a look at what we have.

Grab yourself a MariaDB prompt by running:

Tables and Table Properties

The commands to show tables and their properties differ from DBMS to DBMS. Here we'll be using MariaDB's cxommands.

show tables;

This will list all the tables in the database. If you've imported the aminno_member_email.dump file, you should see the aminno_member_email table listed;

describe aminno_member_email;

This will list the fields (columns) in the table.

Basic Searching

Now that we know what tables we have and what they look like, we can view some of the entries.

select * from aminno_member_email limit 5;

This command will list the first 5 entries in the table. Let's break it down:

select pnum, email from aminno_member_email limit 5;

This query is the same as the above one, except we're only listing the pnum and email fields in the results.

select pnum, email from aminno_member_email where email="darren@housecapades.com";

This time we're searching for a specific email address. This query will return any entries that have this exact email address. Everything in inverted commas is case sensitive. We've also dropped the limit on search results, since we're not expecting to see too many results.

select pnum, email from aminno_member_email where email like "%@housecapades.com";

This time we're searching for email addresses which match a pattern. This query will take a little longer to do it's work.

Searching Across Multiple Tables

If you've imported multiple tables into the database, you can search across multiple tables to find more useful data. This is known as "joining" the tables.

You need to tell the DBMS how to match up data from one table to another. This is where Keys come in.

Earlier we saw a field in the aminno_member_email table called pnum which was listed as the Primary Key. Other tables are likely to refer to this pnum, or even use an identical pnum field themselves.

If you have only imported on table so far, import the member_details.dump file. We'll use it as an example here, but if you have imported something else, the steps will be roughly the same.

describe member_details;

Here we can see that the Primary Key is also pnum, so our two tables will be dead easy to match up. If you're working with different tables, there could be a pnum field which isn't the Primary Key. This is known as a Foreign Key and it's there to match up data from other (foreign) tables.

In member_details there is a dob (date of birth) field, and a profile caption. Let's look at these for our good friend darren@housecapades.com who has a pnum of 9.

select dob, profile_caption, email from aminno_member_email, member_details where aminno_member_email.pnum=member_details.pnum and aminno_member_email.pnum = 9;

So now we know that Darren had a shitty childhood because his birthday is close to christmas, and that he's too old to be using "4" instead of "for" on the internet.

Other fields in the member_details are worth noting:

Darren's email was used to create two accounts: 9 and 1796677. Let's have a look at his other account, but this time rather than specifying his pnum, let's just search by his email address. This query will be a little slower than using his pnum, but it will serve as a nice little wrapup of basic sql queries and will also serve as a copy/paste for tl;dr types:

select dob, profile_caption, email from aminno_member_email, member_details where aminno_member_email.pnum=member_details.pnum and email="darren@housecapades.com";

Perhaps his childhood wasn't so shitty. Perhaps our mate Darren is smart enough not to give ashley madison his true dob. Nice one Dazza.

This article is issued from Installgentoo. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.