Blog Exercise in MySQL

In this version, I'll give you files to create tables in your personal db, which you'll have to do before running the example.

Copy the Example

cp -r ~cs304/pub/downloads/blog-ex-mysql blog-ex-mysql
cd blog-ex-mysql

Initializing Your Database

The SQL code to (re)-create the tables and insert the data is in logins.sql:

mysql < logins.sql

Running the Blog Exercise Demo

Before we attack the blogging application, we'll download it and I'll demo. You can also run this on your own. Here are the steps:

  1. Go to your cs304 folder
  2. Activate your venv
  3. CD to the blog folder
  4. start done.py
  5. open the SSH tunnel to Tempest or start your VPN
  6. In the browser, visit the main page
  7. Note the listing of current blog entries
  8. Post something as someone. The blog doesn't check passwords, so anything will work
  9. Login as someone
  10. Post once or twice as that person; notice that you don't have to give your name each time.
  11. You can even try logging in as different people on different browsers

Video of the Blog Exercise

Here's a video of how the completed blog

Starting Code

Unlike the cookie example in the reading, where I did all the cases in one Flask route, in the blog example, I decided to parcel out the various cases to different routes. I actually like this code better, though it is more spread out. Let's consider the code in pieces:

Recent Entries

Every page view shows recent blog entries:

def recent_entries(conn,limit=10):
    '''Returns the most recent 'limit' entries from the blog'''
    curs = dbi.dict_cursor(conn)
    curs.execute('''SELECT entered as time,user,entry
                    FROM blog_entry
                    ORDER BY entered DESC
                    LIMIT %s''',[limit])
    return curs.fetchall()

Main Route

The main route retrieves the latest entries and displays the page with the forms and buttons in it:

@app.route('/')
def index():
    ## TODO: get bloguser value from cookie
    bloguser = ''
    conn = dbi.connect()
    entries = recent_entries(conn)
    return render_template('blog.html',
                           allCookies=request.cookies,
                           bloguser=bloguser,
                           rows=entries)

Note the TODO comment, indicating something you should implement.

Main Template

The blog.html template is pretty straightforward, but notice the urls for routes to process logins, logout and posting of blog entries:

{% extends "base.html" %}
{% block page_content %}

{% if bloguser %}

   <form method="post" action="{{url_for('logout')}}">
        <button>logout {{bloguser}}</button>
   </form>

{% else %}
   <form method="post" action="{{url_for('login')}}">
        <p><label>User: <input type=text name="loginname"></label>
        <p><label>Pass: <input type=password name="loginpass"></label>
        <p><input type="submit" value="login">
  </form>

{% endif %}

<form method=post action="{{url_for('post_comment')}}">
     {% if bloguser %}
          <p>Name: {{bloguser}}</p>
     {% else %}
          <p><label>Name: <input type=text name="blogname"></label></p>
     {% endif %}
     <p><label>Comment: <br>
         <textarea name="comment" rows="5" cols="30"></textarea></label></p>
     <p><input type="submit" value="post"></p>
  </form>

<dl>
     {% for row in rows %}
         <dt>{{row.user}} at {{row.time}}</dt><dd>{{row.entry}}</dd>
     {% endfor %}
</dl>
{% endblock %}

Base Template

The main template inherits some important stuff from a base.html, such as displaying the cookies, reporting a message, and a little CSS styling.

<!doctype html>
<html>
<head>
<title>Commenting</title>
<style>
    form { border: 1px solid black; }
    #msg { font-weight: bold; color: red; }
</style>
</head>
<body>

<h1>Blog App</h1>

        <p>From base: These are all the cookies the application can see:</p>
        <ul>
            {% for key in allCookies %}
            <li>{{key}} : {{allCookies[key]}}</li>
            {% endfor %}
        </ul>

{% if msg %}
   <p id="msg">{{msg}}</p>
{% endif %}

{% block page_content %}
{% endblock %}        

</body>
</html>

Posting Comments

The starter version, start.py, has working code to post a blog comment. Let's see how that works:

@app.route('/post_comment/', methods=['POST'])
def post_comment():
    ## TODO: get bloguser value from cookie
    bloguser = ''
    if ('comment' in request.form):
        if 'blogname' in request.form:
            user=request.form['blogname']
        else:
            user=bloguser
        entry = request.form['comment']
        conn = dbi.connect()
        curs = dbi.cursor(conn) # a tuple cursor
        # MySQL's current_timestamp() function returns the current time
        # on the server in the local timezone
        curs.execute('''INSERT INTO blog_entry(entered,user,entry) VALUES
                       (CURRENT_TIMESTAMP(),%s,%s)''',
                     [user,entry])
        conn.commit()           # don't forget to commit!
        return redirect(url_for('index'))
    else:
        return render_template('go_home.html',
                               msg='comment form missing comment input')

This code works because it either pulls the username out of the form (starter version) or uses the cookie value (which you will implement). Give those two values, it inserts the database entry, commits it, and redirects to the main route.

It uses the current_timestamp() function which returns the current date and time on the server date (local timezone). There's also a utc_timestamp() function if you want that.

Login

Let's look at the starter code for logins. There's work you need to add to this, but the code itself is mostly working. The basic login strategy is to take the username and password from the form, and count the number of entries in the table where both match. If the count is 0, the username and password were not correct. If the count is 1, they were. The count can't be more than 1 because the username is a key and therefore must be unique.

@app.route('/login/', methods=['POST'])
def login():
    if ('loginname' in request.form and
        'loginpass' in request.form):
        conn = dbi.connect()
        curs = dbi.cursor(conn)
        curs.execute('''SELECT count(*)
                          FROM blog_user
                          WHERE user=%s AND pass=%s''',
                       (request.form['loginname'],
                        request.form['loginpass']))
        row = curs.fetchone()
        # remember, we are using a tuple cursor this time
        if row[0] == 1:
            bloguser=request.form.get('loginname')
            ## TODO: make a response object and set the cookie
            resp = render_template('go_home.html',
                                   allCookies=request.cookies,
                                   msg='logged in as '+bloguser)
            return resp
        else:
            errmsg = 'incorrect login'
            # we are not using flash, otherwise we would definitely do this
            # flash(errmsg)
            # instead, we use a message
            return render_template('go_home.html',
                                   allCookies=request.cookies,
                                   msg=errmsg)

Note the TODO lines where your code needs to remember the username using a cookie.

Logout

For completeness, we'll look at the logout code:

@app.route('/logout/', methods=['POST'])
def logout():
    ## TODO: make a response, saying they are logged out,
    ## expire the cookie and return the response object
    return render_template('go_home.html',
                           allCookies=request.cookies,
                           msg='logged out')

It doesn't do much yet. You'll implement code to remove the cookie.

Goals/Approach

  • "posted by" (commenter) field should come from cookie, rather than form
  • login action should set the cookie
  • various routes should read the cookie

Create a login session for the comment app.

Here's how to approach the problem:

  1. Re-read the start.py code to make sure you understand it. The code was described above.
  2. Notice the places where it says TODO and suggests what you need to do.
  3. Copy the start.py file to your own file: 
    cp start.py mycode.py
  4. Modify the mycode.py file application to set a cookie when someone logs in.
  5. Modify the application to read the cookie and use it when adding blog entries, so they are no longer anonymous, yet the user doesn't have to keep authenticating.
  6. Test, test, test
  7. sigh in amazed satisfaction that it really works
  8. Check solutions below.
  9. If you finish before the end of class, you can:
    • Bask in the glow of your success
    • Implement a "logout" button
    • Use the JavaScript console to hack the application: login as, say, "dopey," and then change the username to "grumpy" and continue posting.

Solutions

The solutions are in the done.py.

I strongly suggest using the Unix diff command to compare the start.py with done.py