SQL Injection Fix Basics

SQL injection is what happens when a program builds a database question by pasting what a visitor typed into the text of the question. The visitor can then end the question early and write their own. You will search a small shop, make its search page hand over things it should not, fix the one line of code that allows it, and show the attack no longer works.

The shop's search is a web address. curl -s asks for it, and -G --data-urlencode puts what you search for after the address with any quotes and spaces made safe. Searching for pen prints one line, Pen 1.2: the name and the price.

Inside the program your search is pasted between two quote marks. A quote mark in your search ends them early, OR 1=1 is true for every row, and -- makes the rest of the question a comment. The shop lists seven products, including Staff discount card and Unreleased drone, which are not for sale and should never be shown. The same trick with UNION SELECT can ask for another table altogether. Try that yourself: the shop has a users table.

The program is in the container, and the weak line is the one that starts rows =. It joins your search into the question with +. A parameterised query sends the question and your search to the database separately, so what you typed can only ever be a value and never part of the question. The ? marks where the value goes, and the search is passed beside it. To do it by hand, open the program with docker exec -it app vi /tmp/app/app.py and replace that line (i to type, Esc then :wq to save). The next command makes the same change for you, with this line:

rows = db.execute("SELECT name, price FROM products WHERE public = 1 AND name LIKE ?", ("%" + term + "%",)).fetchall()

This replaces that one line. The shop reads its program once when it starts, so docker restart runs the new version. It takes about ten seconds and then prints app: if its box says it is still running, wait for app in the terminal before you go on.

The same attack as step 2 now prints nothing at all: the database looked for a product whose name contains the whole of x' OR 1=1 --, and there is none.

A fix that breaks the shop is not a fix. lab-check tries a normal search, a search for Baker's (a product with a quote mark in its name, which made the weak program fail with a database error), the OR and the UNION, and prints PASS or FAIL for each. All four say PASS and the last line is All checks pass. Press Stop the lab and then Start the lab on the lab bar and run it again: your fix is still there, because the program lives in a named volume and the platform saves volumes.