City Lights

Challenge:

De Monne wants to know how many branch offices were included in the database leak. This can be found by figuring out how many unique cities the employees live in. Submit the flag as flag{#}.

Use the MySQL database dump from Body Count.

Solution:

From the Body Count challenge we know there is an employees table so we start by reviewing the table structure:

mysql> SHOW COLUMNS IN employees;
  Field	Type	Null	Key	Default	Extra
  employee_id	smallint	NO	PRI	NULL	auto_increment
  last_name	tinytext	NO		NULL	
  first_name	tinytext	NO		NULL	
  email	tinytext	NO		NULL	
  street	tinytext	NO		NULL	
  city	tinytext	NO		NULL	
  state	tinytext	NO		NULL	
  country	tinytext	NO		NULL	
  postal	tinytext	NO		NULL	
  gender	tinytext	NO		NULL	
  employee_code	tinytext	NO		NULL	

We can then use the COUNT DISTINCT functions to get a count of unique cities:

mysql> SELECT COUNT(DISTINCT(city)) FROM employees;
COUNT(DISTINCT(city))
444

The accepted flag was, flag{444}