Your Pokemon Guide for Essential SQL Pen Test Commands

As a pen tester, it's not enough to exploit targets and get shells. That's great (and it's a big part of what we do), but the real value to the customer is to demonstrate what the effective risk is from the successful exploitation of a vulnerability. In order to answer this question, I find myself interrogating data on compromised systems, trying to make sense of what's available and what the disclosure of the data will mean to my customer.

Often, I find myself looking at a bunch of data in a SQL database. This might be a native SQL database (usually MSSQL, SQLite3, MySQL, Oracle, etc.), but sometimes it's a database I've created by importing CSV files, JSON data, or other data formats. As a former DBA (a long, long time ago) I feel really comfortable at a SQL> prompt. In this article I'll offer some quick tips on getting useful data out of a database.

I'm going to use a Pokémon Pokedex SQLite3 database as my data source for examples. This database is the labor-of-love project from Eevee. Special thanks to Eevee for making this complex database available.

Data Structures

Identify the structure of tables, indexes, and other objects using the .schema  command:

sqlite> .schema
CREATE TABLE item_pockets (
id INTEGER NOT NULL,
identifier VARCHAR(79) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE pokeathlon_stats (
id INTEGER NOT NULL,
identifier VARCHAR(79) NOT NULL,
PRIMARY KEY (id)
);
...

This database has several tables. If you want to look at the structure of a single table, specify a table:

sqlite> .schema
CREATE TABLE item_pockets (
id INTEGER NOT NULL,
identifier VARCHAR(79) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE pokeathlon_stats (
id INTEGER NOT NULL,
identifier VARCHAR(79) NOT NULL,
PRIMARY KEY (id)
);
...

Note that the .schema command is very SQLite-specific. Other databses use SHOW TABLES  and DESC tablename  instead. Most of the other commands shown in this article are generic enough to work across any database.

Retrieving Some or All

We retrieve data from the database using the SQL SELECT  statement (I've also issued the SQLite3 .headers command to turn on column labels:

sqlite> select * from pokemon;
id|identifier|species_id|height|weight|base_experience|order|is_default
1|bulbasaur|1|7|69|64|1|1
2|ivysaur|2|10|130|142|2|1
3|venusaur|3|20|1000|236|3|1
4|charmander|4|6|85|62|5|1
5|charmeleon|5|11|190|142|6|1
6|charizard|6|17|905|240|7|1
7|squirtle|7|5|90|63|10|1
8|wartortle|8|10|225|142|11|1
9|blastoise|9|16|855|239|12|1
10|caterpie|10|3|29|39|14|1
...

By specifying the *, we note that we want to retrieve all of the columns. If you only want a few specific columns, specify the columns you want by name in the order you want them to appear:

sqlite> select id, identifier, weight, height from pokemon;
id|identifier|weight|height
1|bulbasaur|69|7
2|ivysaur|130|10
3|venusaur|1000|20
4|charmander|85|6
5|charmeleon|190|11
6|charizard|905|17
7|squirtle|90|5
8|wartortle|225|10
9|blastoise|855|16
10|caterpie|29|3
...

The pokemon table has a lot of records in it. If you only want the first 5 records, add LIMIT 5 to the end of your query:

sqlite> select id, identifier, weight, height, "order" from pokemon limit 5;
id|identifier|weight|height|order
1|bulbasaur|69|7|1
2|ivysaur|130|10|2
3|venusaur|1000|20|3
4|charmander|85|6|5
5|charmeleon|190|11|6

Unique Data

Individual rows of data will often have duplicate values present. We can get a unique list of the values present by using the DISTINCT modifier in a query. For example, consider the structure of the Pokedex contest_type_names table:

sqlite> .schema contest_type_names
CREATE TABLE contest_type_names (
contest_type_id INTEGER NOT NULL,
local_language_id INTEGER NOT NULL,
name VARCHAR(79),
flavor TEXT,
color TEXT,
PRIMARY KEY (contest_type_id, local_language_id),
FOREIGN KEY(contest_type_id) REFERENCES contest_types (id),
FOREIGN KEY(local_language_id) REFERENCES languages (id)
);
CREATE INDEX ix_contest_type_names_name ON contest_type_names (name);
sqlite> select * from contest_type_names;
contest_type_id|local_language_id|name|flavor|color
1|5|Sang-froid|Épicé|Rouge
1|9|Cool|Spicy|Red
2|5|Beauté|Sec|Bleu
2|9|Beauty|Dry|Blue
3|5|Gr'ce|Sucré|Rose
3|9|Cute|Sweet|Pink
4|5|Intelligence|Amère|Vert
4|9|Smart|Bitter|Green
5|5|Robustesse|Acide|Jaune
5|9|Tough|Sour|Yellow
1|10||Ostrá|
5|10|Síla||
2|10|Krása|Suchá|

The return data set is fairly small, but we can focus it further. What if we are only interested in the unique color assignments given to Pokémon contest types?

sqlite> select distinct(color) from contest_type_names;
Rouge
Red
Bleu
Blue
Rose
Pink
Vert
Green
Jaune
Yellow

Conditional Expressions

Often you'll want to filter the returned data with a conditional expression, denoted with a WHERE  clause. Using a WHERE clause allows you to specify the nature of the data you want returned, matching one or more columns to values you specify. For example, what if we only want to see information about Pikachu in the pokemon  table?

sqlite> .schema pokemon
CREATE TABLE pokemon (
id INTEGER NOT NULL,
identifier VARCHAR(79) NOT NULL,
species_id INTEGER,
height INTEGER NOT NULL,
weight INTEGER NOT NULL,
base_experience INTEGER NOT NULL,
"order" INTEGER NOT NULL,
is_default BOOLEAN NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(species_id) REFERENCES pokemon_species (id),
CHECK (is_default IN (0, 1))
);
CREATE INDEX ix_pokemon_order ON pokemon ("order");
CREATE INDEX ix_pokemon_is_default ON pokemon (is_default);
sqlite> select * from pokemon where identifier = "pikachu";
id|identifier|species_id|height|weight|base_experience|order|is_default
25|pikachu|25|4|60|112|32|1

In addition to using matching expressions, SQLite3 also supports common comparison operators, shown here:

Operator	Meaning
=	Equal to
!=	Not equal to
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to

Let's use this to find out if there are any Pokémon smaller than Pikachu (who appears to be 4 decimeters from the previous query):

sqlite> select identifier from pokemon where height < 4;
caterpie
weedle
pidgey
rattata
spearow
paras
...

We can combine multiple WHERE expressions together too. For example, what Pokémon are taller than Pikachu but weigh less? (Note that here I've pressed Enter to start a new line, prompting SQLite3 to produce a continuation prompt of ...>.)

sqlite> select identifier, height, weight from pokemon
...> where height < 4 and weight > 190;
klink|3|210
durant|3|330

Wildcards

SQL allows you to specify wildcards in your WHERE clauses, using the keyword LIKE  and _  to match any single character, or LIKE  and % to match a group of characters. Using the pokemon_species_name  table, let's identify all the genus values that match Dr at the beginning of the line:

sqlite> .schema pokemon_species_names
CREATE TABLE pokemon_species_names (
pokemon_species_id INTEGER NOT NULL,
local_language_id INTEGER NOT NULL,
name VARCHAR(79),
genus TEXT,
PRIMARY KEY (pokemon_species_id, local_language_id),
FOREIGN KEY(pokemon_species_id) REFERENCES pokemon_species (id),
FOREIGN KEY(local_language_id) REFERENCES languages (id)
);
CREATE INDEX ix_pokemon_species_names_name ON pokemon_species_names (name);
sqlite> select name, genus from pokemon_species_names where genus like 'Dr%';
name|genus
Nidoqueen|Drill
Nidoking|Drill
Rhydon|Drill
Hypotrempe|Dragon
Seeper|Drache
Horsea|Dragón
Horsea|Drago
Horsea|Dragon
...
Simipour|Drenaje
Munna|Dream Eater
Musharna|Drowsing
Muplodocus|Dragon
Viscogon|Drache
Goodra|Dragón
...

Notice that some of the genus types are Dragon and Dragón. If we want to match either of those two, we can use the _ modifier to match the o and ó characters:

sqlite> select name, genus from pokemon_species_names where genus like 'Drag_n';
name|genus
Hypotrempe|Dragon
Horsea|Dragón
Horsea|Dragon
Hypocéan|Dragon
Seadra|Dragón
Seadra|Dragon
Minidraco|Dragon
Dratini|Dragón
Dratini|Dragon
Draco|Dragon
...

You can also search for the middle portion of a matching string by adding % to the beginning and the end of the expression. For example, we can search for any genus with the word eat:

sqlite> select name, genus from pokemon_species_names
...> where genus like '%eat%';
name|genus
Castform|Weather
Munchlax|Big Eater
Munna|Dream Eater
Heatmor|Anteater

Although it doesn't show in this output, %eat% will match values starting or ending with eat as well (e.g. there doesn't have to be preceding or following characters to be a match).

Ordering Data

Sometimes you want to change the order of the data being returned. No problem, simply enter ORDER BY followed by the column you want to use. You can add multiple comma-separated columns in the  ORDER BY  clause as well. By default, values are listed in ascending order, but this can be modified by adding the keyword DESCENDING  to the end of the  ORDER BY expression.

For example, the abilities  table in the Pokémon database discloses the identifier information, sorted by default using the id  field. If you want to change the sort order to the generation_id  column, add an  ORDER BY clause:

sqlite> .schema abilities
CREATE TABLE abilities (
id INTEGER NOT NULL,
identifier VARCHAR(79) NOT NULL,
generation_id INTEGER NOT NULL,
is_main_series BOOLEAN NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(generation_id) REFERENCES generations (id),
CHECK (is_main_series IN (0, 1))
);
CREATE INDEX ix_abilities_is_main_series ON abilities (is_main_series);
sqlite> select * from abilities order by generation_id;
id|identifier|generation_id|is_main_series
1|stench|3|1
2|drizzle|3|1
3|speed-boost|3|1
4|battle-armor|3|1
5|sturdy|3|1
6|damp|3|1
7|limber|3|1
...
162|victory-star|5|1
163|turboblaze|5|1
164|teravolt|5|1
10001|mountaineer|5|0
10002|wave-rider|5|0
10003|skater|5|0
10004|thrust|5|0
10005|perception|5|0
...
189|primordial-sea|6|1
190|desolate-land|6|1
191|delta-stream|6|1

Cross-table Queries

When people start out designing databases, people try and stuff everything into a single table, even if it's not efficient or performance-wise to do so. Later, data that changes less frequently, or should be isolated from related records gets placed into a different table, sharing an identifier that allows us to query both tables to produce one set of results.

For example, consider the pokemon_species and pokemon_species_names tables shown below in SQL and visualized forms (courtesy of WWW SQL Designer.

sqlite> .schema pokemon_species
CREATE TABLE pokemon_species (
id INTEGER NOT NULL,
identifier VARCHAR(79) NOT NULL,
generation_id INTEGER,
evolves_from_species_id INTEGER,
evolution_chain_id INTEGER,
color_id INTEGER NOT NULL,
shape_id INTEGER NOT NULL,
habitat_id INTEGER,
gender_rate INTEGER NOT NULL,
capture_rate INTEGER NOT NULL,
base_happiness INTEGER NOT NULL,
is_baby BOOLEAN NOT NULL,
hatch_counter INTEGER NOT NULL,
has_gender_differences BOOLEAN NOT NULL,
growth_rate_id INTEGER NOT NULL,
forms_switchable BOOLEAN NOT NULL,
"order" INTEGER NOT NULL,
conquest_order INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(generation_id) REFERENCES generations (id),
FOREIGN KEY(evolves_from_species_id) REFERENCES pokemon_species (id),
FOREIGN KEY(evolution_chain_id) REFERENCES evolution_chains (id),
FOREIGN KEY(color_id) REFERENCES pokemon_colors (id),
FOREIGN KEY(shape_id) REFERENCES pokemon_shapes (id),
FOREIGN KEY(habitat_id) REFERENCES pokemon_habitats (id),
CHECK (is_baby IN (0, 1)),
CHECK (has_gender_differences IN (0, 1)),
FOREIGN KEY(growth_rate_id) REFERENCES growth_rates (id),
CHECK (forms_switchable IN (0, 1))
);
CREATE INDEX ix_pokemon_species_order ON pokemon_species ("order");
CREATE INDEX ix_pokemon_species_conquest_order ON pokemon_species (conquest_order);
sqlite> .schema pokemon_species_names
CREATE TABLE pokemon_species_names (
pokemon_species_id INTEGER NOT NULL,
local_language_id INTEGER NOT NULL,
name VARCHAR(79),
genus TEXT,
PRIMARY KEY (pokemon_species_id, local_language_id),
FOREIGN KEY(pokemon_species_id) REFERENCES pokemon_species (id),
FOREIGN KEY(local_language_id) REFERENCES languages (id)
);

In these two tables, the data for name  and genus  is in a different table than the primary Pokémon species information. The pokemon_species_names data probably changes less often then the data in pokemon_species, and is a pretty reasonable design. However, how do we formulate a query across the two tables that returns the Pokémon identifier and genus in the same query?

The answer is in the SQL join capability. Take a look:

sqlite> select identifier, genus from pokemon_species, pokemon_species_names where
...> pokemon_species.id = pokemon_species_names.pokemon_species_id and
...> local_language_id = 9;
bulbasaur|Seed
ivysaur|Seed
venusaur|Seed
charmander|Lizard
charmeleon|Flame
charizard|Flame
squirtle|Tiny Turtle
wartortle|Turtle
blastoise|Shellfish
caterpie|Worm
...

Here I've selected columns from two tables. The WHERE clause tells SQL to associate records from pokemon_species with records from pokemon_species_id when the id and species_id columns match. I've also limited the output where local_language_id is 9, which is English.

In this example, 'identifier' and 'genus' are both unique names in the two different tables. If the column names match across two tables, you can use dot-notation to specify the full table name followed by the column name (e.g. pokeon_species.identifier, pokemon_species_id.genus)

Grouping Data 

One powerful SQL statement we haven't yet covered is the GROUP BY operator. Using GROUP BY, we can order the return results into groups. For example, adding GROUP BY genus to the previous query will order the results by the genus column:

sqlite> select identifier, genus from pokemon_species, pokemon_species_names
...> where pokemon_species.id = pokemon_species_names.pokemon_species_id and
...> local_language_id = 9 GROUP BY genus;
landorus|Abundance
seedot|Acorn
arceus|Alpha
chinchou|Angler
trapinch|Ant Pit
heatmor|Anteater
dedenne|Antenna
marill|Aqua Mouse
azumarill|Aqua Rabbit
hariyama|Arm Thrust

You may be thinking "this doesn't really seem that useful? couldn't we get the same result with ORDER BY instead?" You'd be correct, except that GROUP BY is often used with aggregate functions, one of the most powerful tools in your SQL arsenal.

Aggregate Functions

Aggregate functions are a kind of virtual column, allowing you to calculate simple operations on data in tables. Use an aggregate function to calculate a value using one of the following functions:

Function	Meaning
COUNT	Count the total records
MAX	Identify the largest value
MIN	Identify the smallest value
SUM	Add the values together
AVG	Calculate an average value

An aggregate function that I use all the time is COUNT. Want to know how many Pokémon fall into the genus of mouse?

sqlite> select count(genus) from pokemon_species_names
...> where genus = "Mouse" and local_language_id = 9;
6

OK, it was 6. That seems a little anti-climactic.

In this example, 'count(genus)'  is used to count the result set that matches the SELECT  statement with the return clause. This is often used to simply count the total number of records in a table:

sqlite> .headers on
sqlite> select count(*) as "Total" from pokemon_species_names;
Total
6094

Here I also took the opportunity to demonstrate how we name an aggregate function column. Using the syntax AS Total after the aggregate function allows me to reference the aggregate column by name.

Aggregate functions become very useful when you combine them with the GROUP BY operator. Previously we looked for the number of Pokémon in the mouse genus, but what if we want to get a count of how many Pokémon belong to each of the genera? (NB: I had to Google "plural of genus" for that.)

sqlite> select count(name) count, genus from pokemon_species_names
...> where local_language_id = 9 group by genus order by count desc;
count|genus
8|Dragon
6|Mouse
6|Mushroom
5|Balloon
5|Flame
5|Fox
4|Bagworm
4|Bat
4|Cocoon
4|Drill
4|Fairy
4|Poison Pin
4|Seed
4|Tadpole
3|Big Jaw
3|Bivalve
3|Cottonweed
3|EleFish
3|Electric
3|Flower

The GROUP BY operator here groups each of the return results into a set by the specified GENUS column. By naming the aggregate function column count, I was able to reference it later in the ORDER BY clause as well.

Understanding GROUP BY, and aggregate functions is what gives you powerful tools for analyzing SQL data. Use it to identify the number of credit cards disclosed in a compromise, or credit card breach counts grouped by state that have mandatory breach reporting requirements, for example.

Whew! That was a lot of SQL for one article. Master these techniques though, and you'll be well on your way to getting useful and meaningful data out of SQL databases in your pen-test post-compromise phase. For more interesting SQL lessons specifically using the Pokémon Pokedex database, check out Dandelion Mané's PokemonSQLTutorial project too!

-Josh Wright
@joswr1ght