Lecture 10

Don’t forget that we can import a function from CS50’s Python library called eprint, which helps us debug our program by printing the filename and line number from where it was called.

There are many websites like Facebook where we log in when we first visit, and not have to log in every time we move around that website after. Somehow, the website must remember who we are. And they store information on the server, like in CSV files or local variables like we saw last time. The server can’t necessarily rely on the IP address, since one household or network might have one public IP address that many devices share.

A cookie (in this context) is a small piece of information unique to our login that a web server gives to our browser, that our browser can send back in the future, to verify who we are. We can think of it like a digital handstamp that we might get at an amusement park, so we can get in or out easily.

Our browser stores cookies for us and sends it back as part of an HTTP request in the headers. When we first log in, some of the headers in the reply from the server might look like this:

HTTP/1.1 200 OK
Content-Type: text/html
Set-Cookie: session=value
...

Now our browser, in future requests, will include that cookie in the headers:

GET / HTTP/1.1
Host: www.facebook.com
Cookie: session=value
...

As individual users, we might want to delete our cookies. If someone has access to the cookies our browser is storing, they could pretend to be us by including them in requests.

HTTPS encrypts the messages between our browser and the receiving server, so someone along the network won’t be able to steal our cookies. A few years ago, a security researcher created a program that anyone can run, to capture all wireless traffic in the room, and show all the unencrypted cookies that were sent. That motivated most websites to use only HTTPS for sending information like this. And we ourselves might clear the cookies our browser has stored, so there’s no trace of which websites we’ve visited in the past.

Let’s take a look at application.py from our store demo:

from flask import Flask, redirect, render_template, request, session
from flask_session import Session

# Configure app
app = Flask(__name__)

# Configure sessions
app.config["SESSION_PERMANENT"] = False
app.config["SESSION_TYPE"] = "filesystem"
Session(app)


@app.route("/")
def index():
    return render_template("index.html")


@app.route("/update", methods=["POST"])
def update():
    for item in request.form:
        session[item] = int(request.form.get(item))
    return redirect("/cart")


@app.route("/cart")
def cart():
    return render_template("cart.html", cart=session)

If we visit our store, we see this page:

To see how, we can open Chrome’s Developer Tools, and use the Network tab to see that our server is indeed responding with some values for setting a cookie with a session in the Response Headers:

And if we go back to the relevant section of our server’s code, we can see how we update the quantities stored every time the form is submitted, with the update route:

@app.route("/update", methods=["POST"])
def update():
    for item in request.form:
        session[item] = int(request.form.get(item))
    return redirect("/cart")

And if we looked in the directory where our store application is, we would see a directory called flask_session, in which there are files for each session our server has created.

Many web applications, including the ones we’ve demonstrated in lectures, follow a certain paradigm called MVC, Model-View-Controller. We have been writing Python code that act as controllers, the logic that controls where the user goes and what our application actually does with their inputs, etc. The HTML templates, in which we placed our data to display to the user, are views. This separates the logic of our application from the aesthetics and formatting. And finally, today we will see how models, or data, are stored and accessed.

SQL, Structured Query Language, is a programming language that we use to talk to a database, a program that stores data and responds to requests for data, like a web server. And it has advanced features like searching and filtering data quickly.

Spreadsheet programs like Excel or Google Sheets allow us to store data in rows and columns, and this layout is shared with relational databases, which we will be using. We commonly use the columns in the top row for headers like "Name" and "Dorm", and each row after will be an entry:

The database program we will use is SQLite, which stores our data in binary format and provides advanced, efficient features.

We can use yet another program, called phpLiteAdmin, to manage our databases, or use a command-line program, sqlite3.

And we can connect and talk to our database with Python code.

In SQL, we’ll use a few basic operations:

With those verbs, we can write statements like these, and run them one at a time:

And our database, SQLite, can have other data types, too:

SQL has functions to help us figure out the time, like date, time, and datetime.

For phone numbers and zip codes, we might want to store the data as text, since leading zeroes are usually removed from numbers.

We also have a few options we can apply to storing data like names and addresses. We might want separate first name and last name columns, as well as street, city, and state, so we can search or filter on each of those pieces individually.

And if we have lots of people sharing the same address, we’ll have redundant information in our database. With just the zip code, for example, we should be able to figure out the city and state.

So if we were using a spreadsheet program, we can have another sheet, "cities", and store that data separately:

Now, our "customers" sheet has less redundancy:

And we can assign an ID to each row in our cities sheet, and only refer to each one in our customers sheet by the ID, in case any of the other values change.

Now, to find the actual city and state of someone, we need to take the added step of looking up their zip code in the cities sheet, but we gain the advantage of using less space and not having to update all the rows if something changes.

In today’s lecture code, we have a file called lecture.db. If we upload that to our IDE, and double-click it, it opens in a program called phpLiteAdmin:

This shows us the content of that database, and allows us to visualize it. Now we’ll see all the tables, and all the rows in each of the tables. We can click the SQL tab, and run commands:

And we can have more complex relations. A many-to-many relationship might be between our Playlist table and our Song table, where a playlist has many songs, and a particular song might be on many playlists. The PlaylistTrack table represents this, by indicating the PlaylistId and TrackID for every song on a playlist:

We can also assign other attributes to columns:

Let’s run a command to create an empty file, touch customers.db. Now if we double-click it, phpLiteAdmin will open with a blank database, and we can create a new table called users:

We can specify the columns in our table like so:

After we click the create button, phpLiteAdmin also tells us the SQL command of what it did:

We can do the same in the terminal by running the sqlite3 command. We’ll run sqlite3 alternative.db to use a different database file, and run the same CREATE command:

By running .schema, a built-in SQLite command, we can see what our tables look like. We can do the same for our lecture.db file:

Now that we have an idea of how databases work, let’s see how we can access them from our web applications.

Let’s write a simple program to test out accessing databases:

from cs50 import SQL

db = SQL("sqlite:///lecture.db")

# Query database for all albums
rows = db.execute("SELECT * FROM Album")

# For each album in database
for row in rows:

    # Print title of album
    print(row["Title"])

We can also pass variables into the command we want to execute, which will then be substituted:

rows = db.execute("SELECT * FROM Album WHERE Title = :t", t=sys.argv[1])

We pass in the variables individually so the CS50 Library can sanitize, or remove dangerous characters from the user’s input, for us. If we had passed in a formatted string like so:

rows = db.execute(f"SELECT * FROM Album WHERE Title = '{sys.argv[1]}'")

The name for this class of issues is an SQL injection attack. For example, when we log in to some website we might fill out the username and password fields of some form, and the back-end might try to select from the database a row that has a matching username and password.

But if the server uses SQL, then what we pass in to the form might be directly included in the query. For example, suppose we pass in me@examplemailprovider.com as the username and ' OR '1' = '1 as our password:

SQL happens to have the keyword OR, and it looks like this password value will change the meaning of a SQL query, if it’s included directly with single quotes.

Suppose the back-end code looks like this:

username = request.form.get("username")
password = request.form.get("password")
db.execute(f"SELECT * FROM users
WHERE username = '{username}' AND password = '{password}'"

If we sanitize our input by passing in variables to the CS50 Library separately, the query after the input is substituted will look like this, with dangerous characters escaped with backslashes:

db.execute("SELECT * FROM users
WHERE username = 'me@examplemailprovider.com' AND password = '\' OR \'1\' = \'1'")

Let’s combine this into a web application, with lecture0 in the source directory:

from flask import Flask, render_template, request

from cs50 import eprint, SQL

app = Flask(__name__)

db = SQL("sqlite:///lecture.db")

@app.route("/")
def index():
    rows = db.execute("SELECT * FROM Album")
    return render_template("index.html", albums=rows)

We can change the query to be from the URL:

@app.route("/")
def index():
    query = request.args.get("q")
    eprint(query)
    rows = db.execute("SELECT * FROM Album WHERE Title = :q", q=query)
    return render_template("index.html", albums=rows)

With these building blocks, we’ll build a web application in Problem Set 7 that simulates buying and selling stocks, with real prices!