Last Day

Rehearse the final demo by letting someone else use your Twitter clone. Start with a successful visit, investigate a missing message, and try a login that should fail. Each visit uses Python routing, SQL storage, and pages rendered with HTML, CSS, and Jinja2.

Tracing a Request

Open the home page and follow one request. The browser asks for the page’s path, FastAPI chooses the route, Python queries SQLite, and Jinja2 inserts the results into HTML. The browser then draws that HTML with your CSS. A saved message should appear beside its author. After a refresh, it should still be there.

Log in and make another request. The login response gives the browser a cookie, and later requests send it back so the server can check who is visiting.

Web cookies are small files that a site stores in your browser to remember you between visits. The comic makes that term literal:

A friendly webcomic: a monitor-headed character in a 'Grab a BYTE' apron greets a visitor on a 'Home Sweet Homepage' doormat and offers them a cookie.

Debugging a Missing Message

Suppose the first visit works, but a message disappears during your rehearsal.

A four-panel meme captioned 'Programmers. Everyday.': a man reads a page saying 'Why? Why? Why?' in growing frustration, then calmly says 'Oh, that's why.'

For a missing message on your home page, follow the request through the parts you built. First check that the browser requested the expected path, then look for the request in the server terminal. Inspect the SQL result before rendering: if the row is missing there, investigate the query or saved data. If it is present, check the field names and loop in the template. Each check narrows the problem to one part of the program.

Once you find the cause, keep a small input that reproduces it and turn that into a test. An empty result, a missing field, and a message containing an apostrophe each exercise a different assumption. Rerunning that test after the fix checks both your explanation of the bug and whether it returns during later changes.

SQL Injection

Try a login that should fail: submit SQL syntax in place of the password. A site that accepts it has a SQL injection vulnerability.

Use the real-password, wrong-password, and injection cases from the backend reading on your own app. The first should succeed, and the other two should fail without a server error. Inspect every query that receives user input for separately bound values, then keep these cases with your other regression tests. The example below shows how the injected text changes a query.

SQL injection refresher

The login route takes a username and password from a form and checks them against the database. Open the Python file containing your login route and find its database query. An unsafe query would paste the submitted input straight into a string:

# username and password came straight from the login form
cursor.execute(
    "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'"
)

String concatenation makes the user’s username and password part of the SQL statement. Normal input works, but a user can also submit SQL syntax.

Suppose someone types bob'-- as their username. Our string becomes:

SELECT * FROM users WHERE username = 'bob'--' AND password = '...'

The -- starts a SQL comment, so the database ignores the password check and runs only SELECT * FROM users WHERE username = 'bob'. If Bob’s account exists and the application accepts a returned row as a successful login, that query bypasses his password check.

SQL injection can also modify or delete data, as in this xkcd example:

The xkcd 'Exploits of a Mom' comic: a school calls about lost student records after a mother explains she named her son 'Robert'); DROP TABLE Students;--', and advises them to sanitize their database inputs.

A mother names her son Robert'); DROP TABLE Students;--. In a system that permits several statements in one call, the '); ends the intended statement and DROP TABLE Students runs as a second command, deleting the student records. Python’s sqlite3.execute rejects multiple statements in one call, but it is still vulnerable to the single-statement login attack above when input is concatenated.

A company in the UK has a registered legal name that is a SQL injection string, chosen so that careless systems might break themselves just by storing it. A careless program can therefore interpret an official government record as SQL.

Never build queries by concatenating user input. Use SQLite’s ? placeholders instead:

cursor.execute(
    "SELECT * FROM users WHERE username = ? AND password = ?",
    (username, password),
)

Each ? marks a value supplied separately in the tuple, so the database treats username and password as data rather than SQL. If someone sends bob'--, it looks for that literal username and the supplied password, so the comment characters cannot remove the password check. Check that every execute call passes user input as separate values. Rerun the three login tests after repairing the query.

Offense and Defense

Defending the login requires understanding how an attacker breaks it.

The password-cracking lab demonstrated why you should use long passwords and never reuse one across important accounts. Be skeptical of confident security advice from someone who cannot write code: understanding how an attack works is part of defending against it.

Security testing can also be legal, paid work. Companies run bug bounty programs that pay researchers who find a vulnerability and report it privately. Stripe runs a bug bounty program, and platforms such as HackerOne connect companies with security researchers.

The optional Capture the Flag lab supplies a local FastAPI target, two flags, and a repair to test. That page gives the current extra-credit submission instructions.

Stripe’s old Capture the Flag competition arranged planted vulnerabilities in a ladder of websites. Level 3 of their 2012 CTF preserves a small Flask app with the same SQL injection hole. Read its login code as a historical comparison and trace how an attacker could log in as bob. The archive expects Python 2. Running that archived application is outside the current lab. More competitions are listed at ctftime.org.

Course Projects

Automating tedious work is an old hacker-culture principle:

Laziness is good.
Boredom is evil.
So automate the boring stuff.

You built and published a webpage from raw HTML and CSS (Project 0). You wrote a program that turns Markdown into HTML, a compiler of your own (Project 1). You sent Python out to read pages you did not write and pull data out of them by the thousands (Project 2). You built a tool that answers questions about your own documents, a small ChatGPT you control (Project 3), and then added tools that let it take actions (Project 4). And you finished by cloning Twitter (the final project), a database-backed web application that ties Python, Markdown, HTML, CSS, Jinja2, and SQL into one running program.

If you are curious about software-industry salaries, see levels.fyi.

Further Courses

The missing-message test and the hostile-login test establish behavior for particular inputs. This course concentrated on making programs work. Later computer science courses ask how to prove that a program is correct and what no computer can ever do, regardless of its speed. Mathematics provides precise answers to these questions:

The 'always has been' astronaut meme: one astronaut looks at Earth labeled 'Computer Science' and asks 'Wait, it's all math?', while a second astronaut points a gun and answers 'Always has been.'

A data structures and algorithms course teaches ways to organize data and measure how efficiently a program runs. Further courses cover the theory of computation, machine learning, systems, and security.

Keep Automating

Your rehearsal also uncovered work worth automating: rerun the tests whenever the app changes. Keep using code for other repetitive tasks: scrape data you copy by hand, script a rename you do every week, or build a small site. You can look up additional tools as you need them.

Thanks for a good term. Grab a byte.