Backend Web Development

Last week we used a database in a file and typed SELECT and INSERT by hand at a sqlite3 prompt. 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.

Interactive sites such as Facebook, Twitter, and Reddit use the same basic arrangement. A backend program turns web requests into database queries and query results into HTML. The final project uses this structure to build a working Twitter clone.

CRUD apps

A CRUD app supports four data operations, each corresponding to 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. We store the data in SQL rather than in a flat file such as JSON or CSV. SQL can retrieve selected rows without loading the whole file into memory, and one query can replace a Python loop that filters rows by hand. A database also provides ACID guarantees for reliable transactions: after a committed write succeeds, the database preserves it even if the program or computer stops.

The backend

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, as the final project requires.

A web framework handles network connections and lets us write one Python function per URL instead of working with raw network sockets.

The meme calls that framework layer an abstraction:

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 web frameworks. Django, used by sites including Instagram, Pinterest, and Spotify, includes many features but introduces more machinery at once. Flask, used by sites including Reddit and Netflix, is a lightweight alternative. Its name refers to WSGI, a standard used by many Python web frameworks and pronounced “whiskey.” Programmers love an obscure pun.

We will use FastAPI, the framework you already met when we built LLM endpoints. Its structure resembles Flask’s and it is designed for web APIs. The final project uses the same basic architecture as the first version of TheFacebook: a backend program connected to a SQL database. Mark Zuckerberg used PHP and MySQL; we will use Python, FastAPI, and SQLite.

The smallest server

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 starts the app with uvicorn, which listens for connections. reload=True restarts the server whenever you save the file.

Save that as webserver.py and run it. 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 where the server listens. Open that URL in a browser, or use curl to download the page and print it in the terminal:

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

curl prints the HTML string returned by root; a browser renders it.

Reading the request

A page that always returns the same string is a poster, not an app. The server reads user data from three parts of a request.

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 later requests.

Query parameters appear in the URL, so they show up in browser history and server logs. Form data appears in the request body. A login form uses form data because a password should not appear 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.) Read a cookie from the request object with request.cookies.get('username').

Templates

Building HTML by joining strings in Python is error-prone. A template is an HTML file with placeholders that Python fills in. FastAPI uses the Jinja2 template language: { ... } inserts a variable’s value, while {% ... %} runs logic such as a loop or an if. The template root.html 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') sets the template folder to templates. TemplateResponse receives the request, the template name, and a dictionary of variables needed by the template, here the messages list. The rendered HTML has the loop 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 is hard-coded in this example. Replacing it with rows from a SELECT query makes the page show data from the database.

Template inheritance

Every page on the site shares a menu. If the menu were copied into root.html, login.html, and every other template, each change would have to be repeated in every file. Jinja2 uses inheritance instead: one base.html holds the shared parts, and each page supplies its own content block. The base template contains the shared menu and a content block:

<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 includes creation and logout links for logged-in users; its else branch includes login and account-creation links. The {% block content %} marks where a child page inserts its content. 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 combines it with the parent and includes 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 inherits the title and menu. Changing the menu in base.html updates every page on the site.

Logging in

HTTP does not remember earlier requests. The server uses a cookie to connect the current request to an earlier login. Like a coat-check ticket, the browser stores a cookie after login and returns it with later requests.

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})

It checks the submitted username and password with a helper named are_credentials_good. 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.

Other routes check the cookies to determine whether the user is logged in:

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

base.html uses that logged_in value to choose which menu to show. The helper are_credentials_good checks the submitted credentials against the database.

SQL injection

Checking a password means asking the database whether a user with that name and password exists. An unsafe implementation pastes the username and password into a SQL 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

For ordinary input, a correct password returns True and a wrong one returns False:

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

An attacker can enter ' OR '1'='1 instead of a password, and the function lets them 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 closes our string early. Their OR '1'='1' makes the WHERE clause true, so the positive count logs them in without a password. This is SQL injection: user input escaping out of the data and becoming part of your code.

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 applies the same trick to a DROP TABLE statement. 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 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.

The lab

This week’s FastAPI app lab asks you to build the skeleton of a Twitter clone with five routes and a home page that reads messages from the database. It uses routes, request data, templates, inheritance, cookies, and parameterized SQL. The lab becomes the starting code for your final project.

For a lower-level account of the cables, network taps, and headers that carry requests, read how the internet works.