SQL Injection Lab [10 points]

These exercises are based heavily on those developed at Seed Labs.

Exercises

  1. [2 points] From the VM, start Firefox and go to http://www.seedlabsqlinjection.com. When the page loads you will see a log in screen. Use a SQL injection attack on this screen to log in as the user admin, even though you do not know the admin's password.

    Provide a reasonable guess about the structure of the SQL statement that will run when the Login button is clicked. Assume your guess about the SQL statement is correct, provide the string you'd enter into the username and password fields to log onto with the username of admin without knowing the admin's password. Also provide a screen shot of the page that follows the log in screen when logged in as admin. Will this type of attack work with any username or just the admin account?

  2. [2 points] Using the VM, start Firefox and go to http://www.seedlabsqlinjection.com. Log in using username alice and password seedalice. Use a SQL injection attack while logged in as Alice to give Alice a raise even though she is not an admin. Can Alice give anyone a raise (or pay cut)?

    Next, use a SQL injection attack after logging in as Alice to change Boby's password to something Alice knows so she can log in as Boby using the normal web log in page without a SQL injection attack. A catch, however, is that during log in the server hashes whatever was entered in password field using SHA-1. You'll have to account for that wrinkle.

    Provide the command used as Alice to give herself a raise as well as the answers to the questions. Provide the command used to change Boby's password and the plaintext password you used.

  3. [6 points] For this question you'll need install a few things on your VM:

    • MySQL connector (to connect to database via Python): sudo pip install mysql-connector-python
    • TKinter (to create a GUI): sudo apt-get install python3-tk
    • Copy this Python program to a directory in your VM (uses the MySQL connector to connect to the database and TKinter to provide a GUI): sql_attack.py
    • Copy this SQL script to the same directory: restaurants.sql
    • Run this command from the same directory to create a new database (also known as a 'schema') called cs55 MySQL with a table called Restaurants (and at least one more table): mysql -uroot -pseedubuntu < restaurants.sql. The new database is queried by the sql_attack.py Python program. Note that MySQL can host more than one database (schema) at the same time.

    Run the Python program with: python3 sql_attack.py. This will start a GUI that allows you to search the database for restaurants that have the string you provide anywhere in their name. Assume this is an application written to help customers find restaurants in their city. The database is only loaded with a few restaurants, but to test this try entering "nobu" in the GUI and you'll Nobu has two locations in New York City.

    Unfortunately, the creator of the Python application made a mistake and allowed user input in the SQL query, opening the application to SQL injection attacks. One way this vulnerability can exploited is to use the SQL UNION command. UNION joins the results of two queries allowing you to query two tables with one SQL command. You can read more about the UNION command here (feel free to consult other sources also).

    MySQL stores system data in tables in a database (schema) called information_schema, primarily (for our purposes) in tables called tables and columns. You can see the structure of theses tables by issuing DESCRIBE information_schema.tables or DESCRIBE information_schema.columns from a MySQL command prompt. The system tables in the database are just like user tables and you can query them using SQL SELECT commands. Use the SQL injection vulnerability in sql_attack.py and the UNION command to learn about other data in the database -- more than you would be able to see by just using the Python program without the injection attack. In particular:

    • List all restaurants in the Restaurants table (there are only a small number)
    • List all of the tables in the cs55 database
    • Show the column name and data type for each column in the Restaurants table
    • Steal the user names and hashed passwords from all users in the cs55.Users table.
    • Extra credit: crack the passwords in the Users table. (Hint: check the hash length to guess the hashing algorithm used, it is not SHA-256!)

    For your convenience, sql_attack.py also outputs the result of each database query to the terminal.

    Submit your SQL injection attack commands and a screen shot of the GUI results.

Submission Instructions

Create a single .pdf file with your answers to these exercises. Zip your code files together with your .pdf into a single file and submit that file via Canvas. In the text box on your Canvas submission, provide the names of your partners. Only one partner need submit.

Grading rubric

Total of 10 points.

Exercise 1 [2 points]

  • 1 point: estimated SQL command and SQL injection string entered into web form
  • 1 point: screen shot and answer to accounts that can be attacked.

Exercise 2 [2 points]

  • 1 point: give Alice a raise and answer questions
  • 1 point: change Boby's password.

Exercise 3 [6 points]

  • 1 point: List all restaurants in the Restaurants table
  • 1 point: List all tables in the cs55 database
  • 1 points: List the data type for each column in the Restaurants table
  • 3 points: List the username and hashed password for each user in the cs55.Users table
  • Extra credit: crack the passwords in the Users table.