Backend Web Development

The SQL reading ended with a browser displaying messages from social.db. Now someone else needs to use that message board: browse the feed, find the login page, submit credentials, and return as a recognized user. The final project needs that feed, a shared menu, and a login form connected to the database.

Keep the practice social.db from the SQL reading beside webserver.py. Open that same folder and activate its Python environment before running Python or installing libraries. Keep using its requirements.txt as we add dependencies. Replace the route for / as the examples develop, keeping one handler for that path. Add imports, helpers, and other routes above the if __name__ == '__main__': block, which stays at the bottom of webserver.py.

Frontend and Backend

A visitor asks for /, the server reads the messages, and the browser receives a completed HTML page. 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.

Routes

Start with a greeting so we can check the browser-to-server connection before adding the database and templates. The SQL reading installed fastapi and uvicorn. This version also receives the request itself. 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, RedirectResponse
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:

$ python 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.

Templates

The SQL reading joined message text into <p> strings, mixing Python, HTML, and user content. Building HTML by joining strings in Python is error-prone. Create a folder named templates beside webserver.py. 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. Add jinja2 to the existing requirements.txt. In a second terminal in the project folder, activate the same .venv and run python -m pip install -r requirements.txt, then restart the server. First use two fixed messages to check the layout before reconnecting SQLite. Save this as templates/root.html. It 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

A shared menu lets visitors reach login and message creation from any page. 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. Some links below name routes we have not built yet. We will connect the login next. For now, pass a sample logged_in value to check the menu’s two states. Save the shared menu and content block as templates/base.html:

<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. Update templates/root.html to declare its parent with {% extends %} and put its HTML inside a matching content block. Replace the placeholder below with the actual message loop from earlier:

{% 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.

Forms and Request Data

The menu gives the visitor a login link. Following it should show a form. Submitting that form should give the server a username and password to check. 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. 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: add python-multipart to requirements.txt, run python -m pip install -r requirements.txt in the activated environment, and restart the server. Read a cookie from the request object with request.cookies.get('username'). The ... is a placeholder for the login work. Replace this route with the complete handler below.

Login Cookies

The form can send credentials, but the next visit to the feed is a new request. HTTP does not remember earlier requests. The server uses a cookie to connect the current request to an earlier login. 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. These handlers call are_credentials_good, which we will implement and test against SQLite in the next section:

@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 = RedirectResponse('/', status_code=303)
        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 a RedirectResponse to /, then calls response.set_cookie on that response so the browser saves the username and password. The HTTP status 303 asks the browser to follow the successful POST with a GET of Home. The sequence is POST /login → 303 with a destination and cookies → GET / with those cookies. Specify 303 here: a redirect that repeats the POST would send it to a home route that accepts GET. On failure it re-renders the form with bad_credentials=True. To make that feedback visible, add this condition above the form in login.html:

{% if bad_credentials %}
<p>That username and password did not match.</p>
{% endif %}

With bad_credentials=True, the rendered page includes that paragraph. Otherwise it omits it.

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)

Put those three lines inside the root route, after loading messages. Replace its return statement so the template receives the result of the cookie check:

    return templates.TemplateResponse(request, 'root.html', {
        'messages': messages,
        'logged_in': logged_in,
    })

base.html uses that logged_in value to choose which menu to show. Wrap the form and error condition in login.html with the same {% extends 'base.html' %} and {% block content %} structure, so the login page also shows the shared menu. The helper are_credentials_good checks the submitted credentials against the database. Finish its safe version in the next section before testing a login and returning to the home page.

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

The form and cookie code both depend on one decision: whether these credentials match an account. Test that decision on its own before trusting the full login flow. Checking a password means asking the database whether a user with that name and password exists. Create a separate login_check.py beside social.db for the query experiment. Start it by opening the example database and creating a cursor:

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

Append this unsafe implementation to login_check.py. It 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

Append two calls and run python login_check.py. A correct password returns True and a wrong one returns False:

print(are_credentials_good('Isaac', 'soccer'))
print(are_credentials_good('Isaac', 'hunter2'))

The output is:

True
False

Add a third call. An attacker can enter ' OR '1'='1 instead of a password, and the function lets them in:

print(are_credentials_good('Isaac', "' OR '1'='1"))

The added call prints:

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: the database interprets user input as part of the SQL command.

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. Replace the function in login_check.py with this version, which passes the query and values to execute separately and marks each value 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. Rerun the script. The three existing calls now print:

True
False
False

The real password still works, and both the wrong password and the injection fail. Copy the connection setup and safe are_credentials_good function into webserver.py above the routes. Keep the test calls in login_check.py. Restart the server and log in as Isaac with soccer. The browser should arrive at / automatically and show the logged-in menu. A wrong password should leave the form visible with its error message.

Use ? placeholders for every value that comes from a user, on every query, without exception. The final project awards one point for rejecting SQL-injection login attempts.

Inserting User Input

The same risk appears when an account-creation form inserts a username. If we concatenate an input such as ada', 'first'), ('grace into a quoted SQL value, the completed statement could become:

INSERT INTO users (username, password)
VALUES ('ada', 'first'), ('grace', 'second');

One submitted username has changed the command into a two-row insert. Each parenthesized group after VALUES supplies one row. The account-creation code should use parameters just as the login code does:

sql = 'INSERT INTO users (username, password) VALUES (?, ?)'
cur.execute(sql, [username, password])

This inserts one row containing the supplied values. Pass one value for each unquoted ?, in order. A quoted '?' is the literal question-mark string and takes no parameter. Python string concatenation and .format(...) both construct SQL text before SQLite sees it, so neither protects user input. Read the completed SQL to trace those unsafe examples, then use placeholders in your own code.

CRUD Operations

Posting, editing, and deleting messages also require routes that run database commands. 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 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.