SQL injection lab: attack a weak shop search, fix the query and prove the fix

This lab gives each student a small shop with a product search that is vulnerable to SQL injection. The student attacks it from the lab’s terminal with curl, making the search show products that are not for sale and the shop’s users and passwords, then fixes the code inside the running container so the query is parameterised, restarts the shop and watches the same attacks find nothing while normal searches still work.

It is designed by us from the public Python image alone, so there is no third-party application to attack: only a short program of ours that is wrong on purpose. The one rule is the usual one for an attack lab: attack your own copy and nothing else. The copy has no route to the internet and none to any other student, and a student must never point a tool at a site they do not own.

What students learn

How SQL injection happens: a program pastes what a visitor typed into the text of its database question, so a quote mark ends the question early and the rest is written by the visitor. They break out of the question with an OR that is always true, read another table with a UNION, find the line responsible, and replace it with a parameterised query, which sends the question and the value separately so a value can never be part of the question. They also check that the fix did not break normal use, which includes a product whose name contains a quote mark. It suits secure coding and web application security units, and the legal side of one: authorisation and scope. Check your own awarding body’s unit for the exact wording.

Run it in your browser

The shop is the service called app, and the lab’s terminal reaches it by that name. A docker command in the terminal can look at, enter, restart and read the logs of the lab’s service: it cannot create, pull or remove anything. The program is in a named volume, so what the student saves is still there after the lab is stopped and started again.

  1. Open the Container page. Open Build on the bar and choose Run a container. Anybody may open it, and the Git lab and the SQL lab start with no account. Every other lab needs a free account: create one, sign in, then press Start.
  2. Find the lab. The Sample labs card lists every sample. Find SQL injection: find it and fix it.
  3. Press Start. Start runs it as it is, in one press. Edit the file first opens the compose file so you can change it before you start.
  4. Open it. On the lab’s page, open the terminal beside the lab. Run curl -s -G --data-urlencode "q=pen" http://app:8000/search to search the shop, then make it show too much with a search for x' OR 1=1 -- . docker exec -it app vi /tmp/app/app.py opens the program to edit, docker restart app runs the new version, and docker exec app sh /tmp/app/lab-check prints PASS or FAIL for four things. The lab’s link, Corner Shop search page, opens the search form in a tab.

What to ask students to hand in

The output of the attack before the fix and after it, and of docker exec app sh /tmp/app/lab-check at the end, so the change is visible in the student’s own terminal. Then the line they changed and a sentence in their own words on why the new line cannot be injected, what an attacker could have done with the old one, and what else would help (the account the application connects as, passwords that are never stored as plain text). The check says whether the shop behaves as it should; only the explanation says whether it was understood.

The check is the lab’s own: lab-check asks the running shop over its own address, so it reports what the shop does and not what a file says, and it exits with success only when all four lines pass. Nothing is marked automatically. These labs are often used with students aged 16 and 17: a copy is private to the student, a member of staff who teaches the module can open it, and opening a student’s work is written to the access log, which the student can read.

A basic lesson on this lab

SQL Injection Fix Basics is a small lesson worked in this lab, with each step explained and a Run button that types its command into the student’s terminal. It begins with the one rule, searches the shop, makes it show every product, fixes the line with one command, restarts the shop and attacks it again, then runs the check. Reading the code and typing the fix by hand are left to the student.

A free personal account is given it on the Basics module, which is on Home, every college can copy it from the Library’s Basics folder, and a new college opens with it in a module called Lab Basics (Sample). It is deliberately small: it shows a teacher what a lesson worked in a lab is, and the lab does the rest. Copy it into a module of your own to change it.

The code on GitHub

The compose file runs the official Python image, pinned to a version, as the only service, and writes the shop’s program when it starts. Python is under the Python Software Foundation licence and is not distributed by us. The repository’s test plays a student through the attack, the fix, a stop and a start, and starting again, under the same limits the platform applies.

Set it as work

Say in the task what to attack, which line to fix and what to hand in.

  1. Make the lab. In your module add a task and choose the Container lab card, then In a GitHub repository. Give https://github.com/mitsurutech/lab-sqli-fix with the branch main. The compose file is the default, compose.yaml. Fork the repository first if you want to change it.
  2. Publish and try it. Publish pins the commit you were shown, so a later change in the repository does not alter a lab you have already set. Start your own copy first.
  3. Hand it out. Assign it like any task, with a due date if you want one, and say in the task what to do and what to hand in.
  4. Mark what is handed in. Open a student’s copy and the work they handed in, mark it, and send it back with a reason if it needs more.

Questions people ask

What if a student saves a mistake in the program?
The lab still starts. A program that does not compile when the lab starts is kept beside it as app.py.broken and the weak one is written again. To start from the weak program on purpose, run docker exec app rm /tmp/app/app.py and then docker restart app.
Is there a part about database accounts and privileges?
No. The shop uses SQLite, which has no accounts to grant to, and a lab has no internet to install a client for a server database from. Least privilege is a good thing to ask for in the written explanation.
Does the student need to install anything or reach the internet?
No. Python and curl are in the lab’s own images, the terminal is in the browser, and the lab copy has no way out to the internet.