How to Create a Login Form with Postgres and Python - Part 3: Email User Link to Reset Password

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this tutorial, we will build a registration system that allows users of your Postgres- and Python-based web application to create a username and password, add the new user to our database, send that new user a confirmation email, receive a response from their click on the link we sent them in that email, and set a flag in the PostgreSQL database that the user is confirmed. This article is part four of a five-part series. In this series, we will address the following:

  • Part 1: Build the sign-in form using HTML, CSS, and Javascript. Interaction with the database will be absent from this part 1.
  • Part 2: Validate user input of email and password, create a hash of the user’s password, and compare it to the hash we have in our PostgreSQL database. We’ll use Python for this part of the tutorial. If they verify (match what we have in the database for that user), we send them on to the rest of your application.
  • Part 3: Email the user a link because they clicked “I forgot my password.”
  • Part 4: Serve the user a form to create a new password.
  • Part 5: Validate, put hash of their new password into the database, and send user to a page with a message.

Overview of this article

In this part 3, we will get user ID from the database based on the email address they submitted, send the user an email with a reset my password link, and send the user to a HTML / Javascript screen with a message to remind them to check their email.

Assumptions and prerequisites

We’ll assume you have followed the instructions in parts 1 and 2, starting here: here.

The code

Study the code below, paste it into your favorite editor (we like Visual Studio Code), save the file as “sign/_in.py“, upload to your server, and try it out!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
from flask import Flask
from flask import render_template # to render the html form
from flask import request # to get user input from sign-in form
import hashlib # included in Python library, no need to install
import psycopg2 # for database connection
# NEW CODE - these two lines you see below that refer to mail
from flask_mail import Mail
from flask_mail import Message

app = Flask(__name__)

# NEW CODE - the lines you see below that refer to mail
# Mail creds
def smtp_config(config_name, smtp=1):
    with open(config_name) as f:
            config_data = json.load(f)
    if smtp not in {1,2}:
        raise ValueError("smtp can only be 1 or 2")
    if smtp==2:
        MAIL_USERNAME = config_data['MAIL_USERNAME'][1]
        MAIL_PASSWORD = config_data['MAIL_PASSWORD'][1]
    else:
        MAIL_USERNAME = config_data['MAIL_USERNAME'][0]
        MAIL_PASSWORD = config_data['MAIL_PASSWORD'][0]
    MAIL_SERVER = config_data['MAIL_SERVER']
    MAIL_PORT = config_data['MAIL_PORT']
    MAIL_USE_TLS = bool(config_data['MAIL_USE_TLS'])
    return [MAIL_USERNAME, MAIL_PASSWORD, MAIL_SERVER, MAIL_PORT, MAIL_USE_TLS]
# Set up the mail object we will use later.
mail = Mail()

# Database creds
t_host = "database address"
t_port = "5432" #default postgres port
t_dbname = "database name"
t_user = "database user name"
t_pw = "database user password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw)
db_cursor = db_conn.cursor()

@app.route("/")

def showForm():
    # Show our html form to the user.
    t_message = "Login Application"
    return render_template("sign_in.html", message = t_message)

@app.route("/sign_in", methods=["POST","GET"])
def sign_in():
    t_stage = request.args.get("forgot")
    ID_user = request.args.get("ID_user")
    t_email = request.form.get("t_email", "")
    if t_stage == "login" OR t_stage == "reset":
        t_password = request.form.get("t_password", "")

    # Check for email field left empty
    if t_email == "":
        if t_stage == "forgot":
            t_message = "Reset Password: Please fill in your email address"
        else:
            t_message = "Login: Please fill in your email address"
        # If empty, send user back, along with a message
        return render_template("sign_in.html", message = t_message)

    # Check for password field left empty
    # Note we are checking the t_stage variable to see if they are signing in or they forgot their password
    #   If they forgot their password, we don't want their password here. We only want their email address
    #   so we can send them a link in the next part of this article.
    # In both 1st stage and 3rd, we harvest password, so t_stage is "login" or "reset"
    if (t_stage == "login" OR t_stage == "reset") AND t_password == "":
        t_message = "Login: Please fill in your password"
        # If empty, send user back, along with a message
        return render_template("sign_in.html", message = t_message)

    # In both 1st stage and 3rd, we harvest password, so t_stage is "login" or "reset"
    if t_stage == "login" OR t_stage == "reset":
        # Hash the password
        t_hashed = hashlib.sha256(t_password.encode())
        t_password = t_hashed.hexdigest()

    # Get user ID from PostgreSQL users table
    s = ""
    s += "SELECT ID FROM users"
    s += " WHERE"
    s += " ("
    s += " t_email ='" + t_email + "'"
    if t_stage != "login":
        s += " AND"
        s += " t_password = '" + t_password + "'"
    s += " AND"
    s += " b_enabled = true"
    s += " )"

    db_cursor.execute(s)

    # Here we catch and display any errors that occur
    #   while TRYing to commit the execute our SQL script.
    try:
        array_row = cur.fetchone()
    except psycopg2.Error as e:
        t_message = "Database error: " + e + "/n SQL: " + s
        return render_template("sign_in.html", message = t_message)

    # Cleanup our database connections
    db_cursor.close()
    db_conn.close()

    ID_user = array_row(0)

    # If they have used the link in the email we sent them then t_stage is "reset"
    if t_stage == "reset":
        # IMPORTANT
        # In part 5 of this article series we will add code here
        # IMPORTANT

    # First stage. They have filled in username and password, so t_stage is "login"
    if t_stage == "login":
        # UPDATE the database with a logging of the date of the visit
        s = ""
        s += "UPDATE users SET"
        s += " d_visit_last = '" & now() & "'"
        s += "WHERE"
        s += "("
        s += " ID=" + ID_user
        s += ")"
        # IMPORTANT WARNING: this format allows for a user to try to insert
        #   potentially damaging code, commonly known as "SQL injection".
        #   In a later article we will show some methods for
        #   preventing this.

        # Here we are catching and displaying any errors that occur
        #   while TRYing to commit the execute our SQL script.
        db_cursor.execute(s)
        try:
            db_conn.commit()
        except psycopg2.Error as e:
            t_message = "Login: Database error: " + e + "/n SQL: " + s
            return render_template("sign_in.html", message = t_message)
        db_cursor.close()

        # Redirect user to the rest of your application
        return redirect("http://your-URL-here", code=302)

    # If they have clicked "Send me a password reset link" then t_stage is "forgot"
    if t_stage == "forgot":
        # Send email to the user
        smtp_data = smtp_config('config.json', smtp=1)
        app.config.update(dict(
        MAIL_SERVER = smtp_data[2],
        MAIL_PORT = smtp_data[3],
        MAIL_USE_TLS = smtp_data[4],
        MAIL_USERNAME = smtp_data[0],
        MAIL_PASSWORD = smtp_data[1],
        ))
        mail.init_app(app)
        # Set up smtp (send mail) configuration
        t_subject = "Password reset link"
        t_recipients = t_email
        t_sender = "server@yourapplication.poop"

        # Build message body here
        s = ""
        s += "Dear " + s_email + "<br>"
        s += "<br>"
        s += "Thank you for playing!" + "<br>"
        s += "<br>"
        s += "Here is your password reset link. Please click on the following link or paste it into your web browser:" + "<br>"
        s += "<br>"
        s += "<a href='http://YOUR APP DOMAIN NAME HERE/sign_in.py?forgot=step2&ID_user=" + ID_user
        s += "'>https://YOUR APP DOMAIN NAME HERE/sign_in.py?forgot=step2&ID_user=" + ID_user
        s += "</a>" + "<br>"
        s += "<br>"
        s += "If you have any questions, feel free to reply to this message." + "<br>"
        s += "<br>"
        # Set up our mail message
        msg = Message(
            body = s,
            subject = t_subject,
            recipients = [t_recipients],
            sender = t_sender,
            reply_to = t_sender
            )

        # Send the email
        mail.send(msg)

        # Show user they are done and remind them to check their email.
        t_message = "Login: Password reset link was sent to your email address."
        return render_template("sign_in.html", message = t_message)

    # If they have used the link in the email we sent them then t_stage is "reset"
    if t_stage == "reset":
        # IMPORTANT
        # In part 5 of this article series we will add code here
        # IMPORTANT

# This is for command line testing
if __name__ == "__main__":
    app.run(debug=True)

Conclusion

You’ve now completed part 3 of 5 of creating a login for a web application. In part four we will write more Javascript and HTML to make our original form dual-purpose.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.