Backend Web Development

Last week we learned SQL on its own: a database sitting in a file, and a sqlite3 prompt where we typed SELECT and INSERT by hand. A database nobody can reach is not worth much. Today we put that database behind a webpage, so that anyone with a browser can read from it and write to it without ever seeing a line of SQL.

The same arrangement sits behind Facebook, Twitter, Reddit, and almost every interactive site you use. Underneath the styling, each of them is a database and a Python (or PHP, or Ruby) program that turns web requests into SQL queries and query results back into HTML. This is the last topic before the final project, where you build a working Twitter clone, so everything here is a piece you will assemble next.

CRUD apps

Most interactive sites do the same four things to their data, and there is a name for the pattern: a CRUD app. CRUD stands for the four operations, and each one is a SQL statement you already know:

  • Create a row: INSERT
  • Read rows: SELECT
  • Update a row: UPDATE
  • Destroy a row: DELETE

Posting a tweet is a CREATE, loading your feed is a READ, editing your bio is an UPDATE, and deleting a post is a DESTROY. Learn to wire those four onto a webpage and you can build most of the internet.

We store the data in SQL rather than in a flat file like JSON or CSV for three reasons. It is much faster on large data, because SQL only loads the rows a query actually needs instead of reading the whole file into memory. It is far less verbose, because one line of SQL can replace hundreds of lines of Python that loop and filter by hand. And a real database gives ACID guarantees: once it tells you a write succeeded, that data is really saved, even if the power dies a millisecond later.

The backend

There are two halves to a web app, and it is worth naming which half we are in. The frontend is the HTML and CSS (and sometimes JavaScript) that the browser draws. The backend is the Python that talks to the database and generates that HTML. Someone who writes both is doing full-stack work, which is exactly what the final project asks of you.

We are not going to write the backend from raw network sockets. Instead we use a web framework, which is an abstraction that hides the messy details of the network and lets us write one plain Python function per URL.

SpongeBob holding his hands up in front of a rainbow, captioned 'ABSTRACTION'; the framework lets us ignore the network plumbing and just write functions.

Python has several such frameworks. Django is the most popular and powers Instagram, Pinterest, and Spotify, but it does so much automatically that its machinery is a lot to learn at once. Flask was for years the standard lightweight choice, behind sites like Reddit and Netflix. Its name is a joke worth explaining: many Python web frameworks (Flask among them) follow a standard called WSGI, which is pronounced “whiskey”, and the simplest way to carry whiskey is in a flask. Programmers love an obscure pun.

We will use FastAPI, the framework the industry is currently migrating to and the one you already met when we built LLM endpoints. It is close enough to Flask to be nearly a drop-in replacement, and it is built to make web APIs easy. By the end of this course you will know everything Mark Zuckerberg used to build the first version of TheFacebook in his dorm room. He wrote it in PHP against a MySQL database, but the principles are identical, and our Python, FastAPI, and SQLite do the same job with less code.

The smallest server

Before the database and the login forms, let’s get the smallest possible server running, so we have something to build on. A FastAPI app is an app object plus one function per URL, and the function is attached to its URL with a decorator, the @ line that modifies the function below it:

from fastapi import FastAPI, Request
from fastapi.responses import HTMLResponse
import uvicorn

app = FastAPI()

@app.get('/', response_class=HTMLResponse)
async def root(request: Request):
    return 'hello <b>world</b>'

if __name__ == '__main__':
    uvicorn.run('webserver:app', host='127.0.0.1', port=8000, reload=True)

The @app.get('/') decorator says “when a browser asks for the path /, call the function below.” Such a function is a route, and response_class=HTMLResponse tells FastAPI the string it returns is HTML the browser should render. The last line hands the app to uvicorn, the program that actually listens for connections, and reload=True restarts the server whenever you save the file.

Save that as webserver.py and run it, and uvicorn prints the address it is listening on:

$ python3 webserver.py
INFO:     Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)

The address 127.0.0.1 always means “this same computer”, and :8000 is the port, the numbered door the server is waiting behind. Open that URL in a browser, or ask for it from the terminal with curl, which just downloads a page and prints it:

$ curl http://127.0.0.1:8000/
hello <b>world</b>

The server answered our request with the exact string the root function returned. Every route we add from here is another function that returns some HTML.

Reading the request

A page that always returns the same string is a poster, not an app. To do anything interesting the server has to read what the user sent, and a request carries user data in three different places.

The first is query parameters, the ?name=value pairs on the end of a URL like /login?username=haxor. The second is form data, which is what an HTML <form> sends when the user clicks submit. The third is cookies, small pieces of text the browser stores and sends back on every request, which we will use in a moment to remember who is logged in.

Query parameters ride in the URL, so they show up in browser history and server logs; form data rides in the hidden body of the request. That difference is why a login form uses form data and not query parameters: you do not want everybody’s password sitting in a URL. FastAPI hands you form fields as function arguments if you declare them with Form:

from fastapi import Form

@app.post('/login', response_class=HTMLResponse)
async def login(request: Request,
                username: str = Form(''),
                password: str = Form('')):
    ...

This route uses @app.post instead of @app.get, because forms submit with the POST method. Each Form('') argument pulls one field out of the submitted form, defaulting to the empty string if it is missing. (Reading form data needs one extra library, so run pip3 install python-multipart once.) Cookies are even simpler to read: they live on the request object as request.cookies.get('username').

Templates

Now the server needs to send back real HTML, and building HTML by gluing strings together in Python is miserable. Picture writing '<h2>' + title + '</h2>' for every tag on the page; one forgotten + and the whole thing collapses. The fix is a template: an HTML file with blanks in it that Python fills in. FastAPI uses the Jinja2 template language, and the blanks come in two shapes.

{ ... } drops the value of a Python variable into the page, and {% ... %} runs a bit of logic like a loop or an if. Here is a template, root.html, that loops over a list of messages and prints each one:

{% for message in messages %}
<p>
    <b>{{ message['username'] }}</b> ({{ message['age'] }})
    at {{ message['created_at'] }}:<br>
    {{ message['text'] }}
</p>
{% endfor %}

The {% for message in messages %} line repeats the <p> block once per message, and each { message['username'] } is replaced by that message’s actual username. To use the template, the route loads it and passes in the variables it needs:

from fastapi.templating import Jinja2Templates
templates = Jinja2Templates(directory='templates')

@app.get('/', response_class=HTMLResponse)
async def root(request: Request):
    messages = [
        {'username': 'Isaac', 'age': 4,
         'created_at': '2021-11-16 14:35:45', 'text': "I'm actually a toddler"},
        {'username': 'Evan', 'age': 7,
         'created_at': '2021-11-14 14:33:01', 'text': "I'm a baby"},
    ]
    return templates.TemplateResponse(request, 'root.html', {'messages': messages})

Jinja2Templates(directory='templates') says the templates live in a folder named templates, and TemplateResponse renders one, given the request, the template name, and a dictionary of any other variables the template needs, here the messages list. The rendered HTML that comes back has the loop already expanded:

<p>
    <b>Isaac</b> (4)
    at 2021-11-16 14:35:45:<br>
    I'm actually a toddler
</p>
<p>
    <b>Evan</b> (7)
    at 2021-11-14 14:33:01:<br>
    I'm a baby
</p>

The messages list started as hard-coded dictionaries so we could see the template work. Once it does, we swap the fake list for a real one read out of the database with SELECT, and the page shows live data.

Template inheritance

Every page on the site shares a menu, and copying that menu into root.html, login.html, and every other template is the kind of repetition that rots a codebase. Jinja2 solves this with inheritance: one base.html holds the parts every page shares, and each page fills in only what is unique. Here is base.html, with the menu and one hole for the page’s own content:

<html>
    <head>
        <title>CS40 Twitter Clone</title>
    </head>
    <body>
        <h1>CS40 Twitter Clone</h1>
        <ol>
            <li><a href='/'>Home</a></li>
            {% if logged_in %}
            <li><a href='/create_message'>Create Message</a></li>
            <li><a href='/logout'>Logout</a></li>
            {% else %}
            <li><a href='/login'>Login</a></li>
            <li><a href='/create_user'>Create User</a></li>
            {% endif %}
        </ol>
        {% block content %}
        {% endblock %}
    </body>
</html>

The {% if logged_in %} block shows the menu one way for logged-in users and another way for visitors, so the same file serves both. The {% block content %} is the hole a child page fills. A child page declares its parent with {% extends %} and puts its own HTML inside a matching content block:

{% extends 'base.html' %}
{% block content %}
<h2>Home</h2>
... the messages loop from before ...
{% endblock %}

When we render this child with logged_in=True, Jinja2 stitches it into the parent and the menu comes out with the logged-in links:

<ol>
    <li><a href='/'>Home</a></li>
    <li><a href='/create_message'>Create Message</a></li>
    <li><a href='/logout'>Logout</a></li>
</ol>
<h2>Home</h2>

The child never mentions the title or the menu; it inherits them. Change the menu in base.html once and every page on the site updates, which is the same shared-file discipline that made CSS worth separating from HTML.

Logging in

There is a problem hiding in that logged_in variable: HTTP has no memory. Each request arrives as if the server has never seen you before, so how does the server know, on this request, that you logged in on a previous one? The answer is the cookie. When you log in, the server hands your browser a cookie, and the browser politely hands it back on every future request, like a coat-check ticket.

A friendly monitor-headed character labeled 'HOME sweet HOMEPAGE' greets a visitor and offers them a 'Cookie?', the way a web server hands the browser a cookie.

Logging in takes two routes at the same path /login. A GET request shows the empty form, and a POST request handles what the form submitted:

@app.get('/login', response_class=HTMLResponse)
async def login_form(request: Request):
    return templates.TemplateResponse(request, 'login.html')

@app.post('/login', response_class=HTMLResponse)
async def login(request: Request,
                username: str = Form(''),
                password: str = Form('')):
    if are_credentials_good(username, password):
        response = templates.TemplateResponse(request, 'login.html',
            {'logged_in': True})
        response.set_cookie('username', username)
        response.set_cookie('password', password)
        return response
    else:
        return templates.TemplateResponse(request, 'login.html',
            {'bad_credentials': True})

Read the POST route back. It checks the submitted username and password with a helper are_credentials_good, which we write next. On success it builds the response, then calls response.set_cookie to store the username and password in the browser, so future requests will carry them. On failure it re-renders the form with bad_credentials=True, which the template uses to show an error message.

Every other route learns who you are by reading those cookies back:

username = request.cookies.get('username')
password = request.cookies.get('password')
logged_in = are_credentials_good(username, password)

That logged_in is the same variable base.html uses to choose which menu to show. The only piece left undefined is are_credentials_good, and writing it carelessly is how you get hacked.

SQL injection

Checking a password means asking the database whether a user with that name and password exists. The obvious way is to build the SQL query by pasting the username and password into a string:

def are_credentials_good(username, password):
    sql = "SELECT count(*) FROM users WHERE username='" + username + "' AND password='" + password + "';"
    cur.execute(sql)
    return cur.fetchone()[0] > 0

It looks reasonable, and for honest input it works. Calling it in the REPL, a correct password returns True and a wrong one returns False:

>>> are_credentials_good('Isaac', 'soccer')
True
>>> are_credentials_good('Isaac', 'hunter2')
False

Now watch what an attacker types into the password box. Instead of a real password, they enter the text ' OR '1'='1, and the function lets them straight in:

>>> are_credentials_good('Isaac', "' OR '1'='1")
True

The reason is the query that the string concatenation built:

SELECT count(*) FROM users WHERE username='Isaac' AND password='' OR '1'='1';

The attacker’s quote closed our string early, and their OR '1'='1' turned the whole WHERE clause into something always true, so the count comes back positive and the server logs them in with no password at all. This is SQL injection: user input escaping out of the data and becoming part of your code. It is one of the oldest and most common ways real websites get broken.

xkcd comic 'Exploits of a Mom': a school calls because a mother named her son Robert'); DROP TABLE Students;-- which deletes the school's student records; the last panel advises sanitizing database inputs.

The comic is the same trick aimed at a DROP TABLE, and the last panel gives the fix: never build a query by pasting user input into a string. Instead, hand the query and the values to execute separately, marking each slot with a ?:

def are_credentials_good(username, password):
    sql = "SELECT count(*) FROM users WHERE username=? AND password=?;"
    cur.execute(sql, [username, password])
    return cur.fetchone()[0] > 0

The ? placeholders are parameterized queries, and sqlite3 fills them in a way that treats every value as pure data, never as SQL. The exact same attack now fails, while the real password still works:

>>> are_credentials_good('Isaac', 'soccer')
True
>>> are_credentials_good('Isaac', "' OR '1'='1")
False

Use ? placeholders for every value that comes from a user, on every query, without exception. The final project takes this seriously: a Twitter clone that can be broken by a SQL injection loses ten points, so make are_credentials_good and every other query parameterized from the start.

Looking forward

You now have every part of a web app: routes that answer URLs, three ways to read what the user sent, templates that build HTML, inheritance that shares a layout, cookies that remember a login, and parameterized SQL that ties in the database safely. That is exactly the toolkit for this week’s lab, the FastAPI app, which asks you to build the skeleton of a Twitter clone with five routes and a home page that reads its messages out of the database. The lab is not throwaway practice: it becomes the starting code for your final project, which extends it into the full Twitter clone.

If you want to see what sits below all of this, the cables and wiretaps and headers that carry your requests around the world, read how the internet works as a companion. Next week is the last class, where we tie the whole course together and turn this skeleton loose.