The Faculty

Challenge:

How many of the compromised users in the database are not students?

Submit the flag as flag{#}.

Use the database from Counting Heads.

Solution:

Using the database we created during the Counting Heads challenge, we reviewed the user table but didn't find a column that seemed to designate a user as being a student or not:

mysql> DESCRIBE users;
Field	Type	Null	Key	Default	Extra
user_id	int	NO	PRI	NULL	auto_increment
username	varchar(52)	NO	UNI	NULL	
first	varchar(52)	NO		NULL	
last	varchar(52)	NO		NULL	
middle	varchar(24)	YES		NULL	
email	varchar(52)	NO	UNI	NULL	
street	varchar(52)	NO		NULL	
city	varchar(52)	NO		NULL	
state_id	int	NO	MUL	NULL	
zip	varchar(10)	NO		NULL	
gender	varchar(8)	NO		NULL	
dob	date	NO		NULL	

After reviewing the other available tables we found the 'roles' and 'roles_assigned' tables to be of interest:

mysql> SELECT * FROM roles;
role_id	role_name
5	Adjunct Professor
8	Administration
4	Associate Professor
2	Instructor
3	Professor
6	Research Assistant
7	Research Associate
1	Student

From the 'roles' table we know that students have a 'role_id' of 1, and after reviewing the 'roles_assigned' we see that it links a 'user_id' to a 'role_id':

mysql> DESCRIBE roles_assigned;
Field	Type	Null	Key	Default	Extra
role_assigned_id	int	NO	PRI	NULL	auto_increment
user_id	int	NO	MUL	NULL	
role_id	int	NO	MUL	NULL

Since the challenge is only asking for a count of non-students we can simply count the rows in 'roles_assigned' where 'role_id' is not equal to 1.

mysql> SELECT COUNT(*) FROM roles_assigned WHERE role_id != 1;
COUNT(*)
627

Or with a bit fancier query:

SELECT COUNT(user_id) FROM users WHERE user_id NOT IN (SELECT user_id FROM roles_assigned WHERE role_id = (SELECT role_id FROM roles WHERE role_name LIKE "Student"));

Or for better performance:

SELECT count(*) FROM users u JOIN roles_assigned ra ON ra.user_id = u.user_id JOIN roles r ON r.role_id = ra.role_id WHERE r.role_name <> 'Student';

The accepted flag was: flag{627}