EECS 485 Lab

Lab 3: Project 2 Setup

Goals

Project 2 Setup

Follow the Project 2 Setup Tutorial. You’ll need all of the setup tutorial completed without errors before you can move on to the database and Flask app setup.

Database Setup

Follow the Project 2 Database Setup. Stop after completing the database section.

If you’re not familiar with SQL, there’s a plentitude of resources available on the internet.

Schema Example

A schema describes the structure of different types of data in a database. Let’s walk through the schema for the one of the tables in Project 2.

The users table should have each of these columns:

Edit the file sql/schema.sql in your Project 2 and try to create a table with the schema described above.

Answer 
CREATE TABLE users(
	username VARCHAR(20) NOT NULL,
	fullname VARCHAR(40) NOT NULL,
	email VARCHAR(40) NOT NULL,
	filename CHARACTER(64) NOT NULL,
	password CHARACTER(256) NOT NULL,
	created DATETIME DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(username)
);

Next, add the table to your database. The schema input file is sql/schema.sql. The database output file is var/insta485.sqlite3.

$ pwd
/Users/awdeorio/src/eecs485/p2-insta485-serverside
$ sqlite3 var/insta485.sqlite3 < sql/schema.sql

Data Example

Now, let’s insert some data into the database. We will add all four users to the database. You can find the values for each record (row of data) at the end of the data dump file here. Open up data.sql in the sql folder and give it a shot!

Answer 
INSERT INTO users(username, fullname, email, filename, password)
VALUES ('awdeorio', 'Andrew DeOrio', 'awdeorio@umich.edu','e1a7c5c32973862ee15173b0259e3efdb6a391af.jpg','sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8');

INSERT INTO users(username, fullname, email, filename, password)
VALUES ('jflinn', 'Jason Flinn', 'jflinn@umich.edu','505083b8b56c97429a728b68f31b0b2a089e5113.jpg','sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8');

INSERT INTO users(username, fullname, email, filename, password)
VALUES ('michjc', 'Michael Cafarella', 'michjc@umich.edu','5ecde7677b83304132cb2871516ea50032ff7a4f.jpg', 'sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8');

INSERT INTO users(username, fullname, email, filename, password)
VALUES ('jag', 'H.V. Jagadish', 'jag@umich.edu','73ab33bd357c3fd42292487b825880958c595655.jpg', 'sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8');

Add the data described in data.sql to your database.

$ pwd
/Users/awdeorio/src/eecs485/p2-insta485-serverside
$ sqlite3 var/insta485.sqlite3 < sql/data.sql

Query Example

Now that we have data in the database, we can issue SQL queries to view it.

Dump the entire contents of the users table with the query SELECT * FROM users. Type .exit or Control-d to quit.

$ sqlite3 var/insta485.sqlite3
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite> SELECT * FROM users;
awdeorio|Andrew DeOrio|awdeorio@umich.edu|e1a7c5c32973862ee15173b0259e3efdb6a391af.jpg|sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8|2020-09-02 15:24:57
jflinn|Jason Flinn|jflinn@umich.edu|505083b8b56c97429a728b68f31b0b2a089e5113.jpg|sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8|2020-09-02 15:24:57
michjc|Michael Cafarella|michjc@umich.edu|5ecde7677b83304132cb2871516ea50032ff7a4f.jpg|sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8|2020-09-02 15:24:57
jag|H.V. Jagadish|jag@umich.edu|73ab33bd357c3fd42292487b825880958c595655.jpg|sha512$a45ffdcc71884853a2cba9e6bc55e812$c739cef1aec45c6e345c8463136dc1ae2fe19963106cf748baf87c7102937aa96928aa1db7fe1d8da6bd343428ff3167f4500c8a61095fb771957b4367868fb8|2020-09-02 15:24:57
sqlite> .exit

Try these queries:

Flask Setup

Follow the Project 2 Flask Setup Tutorial. Stop after completing the Testing section.

Flask Sessions

In project 2, you will make extensive use of sessions in Flask to maintain an active login session. Flask abstracts out much of the complex components of session management.

Login Route

The following example shows a simple login route. Setting the username key in flask.session establishes a session for the current user and a cookie is included in the response during redirection. When the browser receives the response, it will save the cookie in its local storage. If you would like to learn more about how flask.session works, check out the Flask session documentation.

@app.route("/login/", methods=["GET", "POST"])
def login():
    if flask.request.method == "POST":
        # Establish the session and redirect to index page
        flask.session["username"] = flask.request.form["username"]
        return flask.redirect(flask.url_for("index"))
    return """
        <form action="" method="POST">
            <p><input type="text" name="username"></p>
            <p><input type="submit" value="Login"></p>
        </form>
        """

In order for Flask to sign the session cookie to prevent tampering, a randomly generated and cryptographically secure secret key must be set. Directions for how to generate and set this secret key are included as part of the Flask setup tutorial above. The full session cookie encryption process looks as follows:

Logout Route

The following example shows how we can clear the current session by simply calling flask.session.clear() and redirecting:

@app.route("/logout/")
def logout():
    flask.session.clear()
    return flask.redirect(flask.url_for("index"))

This will invalidate the current user’s session but leave all other sessions intact.

Completion Criteria

Lab Quiz

Complete the lab quiz by the due date.