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. Follow one visit to the home page: the browser requests /, a Python route asks SQLite for messages, a template inserts those messages into HTML, and the server sends that HTML back. The browser receives the result, not the Python code or a connection to the database. 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
  • Delete a row: DELETE

Posting a tweet creates a row, loading your feed reads rows, editing your bio updates a row, and deleting a post deletes a row. 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 transaction groups related database changes into a unit that can be committed or rolled back. For example, creating an account and its initial settings should either save both rows or save neither; a failure halfway through should not leave half an account.

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. Leave the server terminal open while visiting the page and use a second terminal for curl. Starting the server only registers the route; each request causes its body to run again. request: Request tells FastAPI to supply an object describing that particular request, including its headers and cookies.

Reading the Request

To produce different responses for different visitors, the server needs input from each request. Three common sources are query parameters, form data, and cookies.

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. Create a folder named templates beside webserver.py. This form in templates/login.html will send those fields:

<form action="/login" method="post">
    <label>Username <input name="username"></label>
    <label>Password <input name="password" type="password"></label>
    <button type="submit">Log in</button>
</form>

action chooses the destination path and method chooses the HTTP method. The name attributes become the submitted field names; their spelling must match what the route reads. 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 because the form above specifies method="post". Forms can also send GET requests, but GET form fields go into the URL’s query string. 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. Save it under the templates folder and let the server render it; opening the template file directly in a browser does not execute its Jinja2 instructions. FastAPI uses the Jinja2 template language: { ... } inserts a variable’s value, while {% ... %} runs logic such as a loop or an if. Install its renderer once with pip3 install jinja2. 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&#39;m actually a toddler
</p>
<p>
    <b>Evan</b> (7)
    at 2021-11-14 14:33:01:<br>
    I&#39;m a baby
</p>

The &#39; in the HTML is an escaped apostrophe, which the browser displays as '. 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.

The SQL reading returned tuples, but this template looks up names such as 'username' and 'text'. Use sqlite3.Row for rows that support named lookups, and rename the SQL message column to text in the result. After importing sqlite3, replace the hard-coded list inside the route with this block:

connection = sqlite3.connect('social.db')
connection.row_factory = sqlite3.Row
messages = connection.execute('''
    SELECT users.username, users.age, messages.created_at,
           messages.message AS text
    FROM messages JOIN users ON messages.sender_id = users.id
    ORDER BY messages.created_at DESC, messages.id DESC
''').fetchall()
connection.close()

AS text names a column in the query result; it does not rename the stored database column. The template can now use the same field names for each row, and TemplateResponse receives the same messages variable as before. For HTML templates, FastAPI’s Jinja2 setup escapes inserted text so a message containing <b> displays those characters rather than creating a bold element. The FastAPI template guide documents this rendering setup.

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. Template inheritance happens on the server before the response is sent. The browser receives one completed HTML document, with neither the {% extends %} line nor the unfilled content block.

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. The response sets the cookie; subsequent requests send it back. A cookie is supplied by the client, so the server must verify what it represents before trusting it as proof of identity.

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.

This example exposes the mechanism by keeping credentials in cookies, which is unsuitable for real accounts. Use made-up passwords while studying it. A deployed application hashes passwords and gives the browser an unpredictable session token after login; the server checks that token on later requests instead of receiving the password again. Hiding a menu link also does not protect a route: the route itself must reject requests from users who are not authorized.

SQL Injection

Checking a password means asking the database whether a user with that name and password exists. For the following experiment, open the example database and create a cursor for its queries:

import sqlite3
connection = sqlite3.connect('social.db')
cur = connection.cursor()

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. fetchone() retrieves the one result row from count(*), and [0] extracts that row’s count. For the toy password table, a count above zero means the supplied values matched an account. 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.