Home

November 4, 2017

Database Fundamentals in SQLite

Overview

This exercise introduces students to the fundamentals of databases; specifically, the concept of tables and how to query them. Joins are also covered. Although this exercise doesn’t specifically involve hacking, it is a prerequisite for exploits involving SQL injection.

Prerequisites

  • A Kali Linux instance:
    • No extra programs beyond those provided by a clean install are needed to complete this exercise.
  • A SQLite database instance; I prepared and used this one: DatabasePractice.sqlite.

Background Info to Provide Students

I prepared a problem set document with examples and exercises for students to work through on their own. The LaTex source of that problem set is available here as DatabaseIntroProblemSet1.tex, and the PDF output is available here as DatabaseIntroProblemSet1.pdf.

Students were given the PDF file, asked to work through the examples in the provided SQLite database, then work together to solve the problems and record their solutions in a separate text file.

Solution

1) Write a SQL query to get the names of nations on the Asian continent.

SELECT name FROM nations WHERE continent = 'Asia';

2) Write a SQL query to get the names of nations that are in Europe or have a population greater than 300 million.

SELECT name FROM nations
WHERE continent = 'Europe'
   OR population > 300000000;

3) Write a SQL query to get the names of nations with a population less than 55 million or with a population greater than 200 million.

SELECT name FROM nations
WHERE population < 55000000
   OR population > 200000000;

4) Write a SQL query to get the names of everyone who is a student.

SELECT name FROM people
WHERE is_student = 'Y';

5) Write a SQL query to get the names of everyone who comes from a nation with a population of 90 million or more.

SELECT people.name FROM people
JOIN nations ON people.nation = nations.id
WHERE population > 90000000;

6) Write a SQL query to get the names and nation of everyone who lives in Africa.

SELECT people.name, nations.name FROM people
JOIN nations ON people.nation = nations.id
WHERE nations.continent = 'Africa';

7) Write a SQL query to get the names and nation of everyone who lives in Asia or Europe.

SELECT people.name, nations.name FROM people
JOIN nations ON people.nation = nations.id
WHERE nations.continent = 'Asia'
   OR nations.continent = 'Europe';

8) Write a SQL query to get the name of everyone in Tanzania who is not a student.

SELECT people.name FROM people
JOIN nations ON people.nation = nations.id
WHERE nations.name = 'Tanzania'
  AND people.is_student = 'N';

Reflections

Students worked through the first four problems with relative ease and without instructor assistance. As expected, JOINs proved to be trickier for them to incorporate in their answers, as they initially resorted to selecting on the numeric IDs of the nations themselves rather than on the nations’ names. Despite that being a valid approach, the goal is for them to acclimate to the concept of joining so that they can understand and work with a broad range of queries in the future.