How to Add User Registration Using PostgreSQL and Python - Part 5: Receive Email Confirmation, Update DB

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: Add a users table to the Postgres database.
  • Part 2: Build the registration form using HTML, CSS, and Javascript.
  • Part 3: Validate user input, create a hash of the user’s password, and insert this data into PostgreSQL.
  • Part 4: Give the user feedback on the screen and send them a confirmation email.
  • Part 5: Receive email confirmation from the user and set a flag in the PostgreSQL database using Python.

Overview of this article

In this part 5, we get user ID from the confirmation link as a querystring, update the benabled flag in the database to _true, and send the user to an HTML / Javascript page to say they are done.

Assumptions and prerequisites

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

The code

Study the code below, paste it into your favorite editor, save the file as “register.py“, upload to your server, and try it out! IMPORTANT: When we say “study the code below,” this is important for more reasons than one. For example, you may need to install some new Python libraries.

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
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
from flask import Flask
from flask import render_template # to render our html page
from flask import request # to get user input from form
import hashlib # included in Python library, no need to install
import psycopg2 # for database connection
# NEW for this part 4 of our series on adding user registration to an application:
from flask_mail import Mail
from flask_mail import Message

app = Flask(__name__)

# New for this part 4:
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 = "server address"
    t_port = "5432"
    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 = "Python and Postgres Registration Application"
    return render_template("register.html", message = t_message)

@app.route("/register", methods=["POST","GET"])
def register():
    # NEW code for Part 5 of the article series:
    # Get user input from the html form AND check for them arriving
    #    here via email confirmation link.
    ID_user = request.args.get("confirm", "")
    # New fork in the code for Part 5
    # Here if there is no querystring sent (ID_user), we get fields from the form
    #   if they are coming from the link in the confirmation email, look down below
    #   at the code under the else.
    if ID_user == "":
        t_email = request.form.get("t_email", "")
        t_password = request.form.get("t_password", "")

        # Check for blanks
        if t_email == "":
            t_message = "Please fill in your email address"
            return render_template("register.html", message = t_message)

        if t_password == "":
            t_message = "Please fill in your password"
            return render_template("register.html", message = t_message)

        # Hash the password they entered
        t_hashed = hashlib.sha256(t_password.encode())
        t_password = t_hashed.hexdigest()

        # We take the time to build our SQL query string so that
        #   (a) we can easily and quickly read it; and
        #   (b) we can easily and quickly edit or add/remote lines.
        #   The more complex the query, the greater the benefits of this approach.
        s = ""
        s += "INSERT INTO users "
        s += "("
        s += " t_email"
        s += ",t_password"
        s += ",b_enabled"
        s += ") VALUES ("
        s += " '" + t_email + "'"
        s += ",'" + t_password + "'"
        s += ", false"
        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 = "Database error: " + e + "/n SQL: " + s
            return render_template("register.html", message = t_message)
        db_cursor.close()

        # NOTE WE CHANGED THIS AND MOVED IT TO THE BOTTOM
        # t_message = "Your user account has been added."

        # ------------------------------------#
        # NEW for part 4 of our series on adding user registration to an application:
        # ------------------------------------#

        # Get user ID from PostgreSQL users table
        s = ""
        s += "SELECT ID FROM users"
        s += "WHERE"
        s += "("
        s += " t_email ='" + t_email + "'"
        s += " AND"
        s += " b_enabled = false"
        s += ")"
        # 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.
        # Another item we'll save for another article:
        #   using another field, along with t_email and b_enabled,
        #   to be sure to get the new user ID

        db_cursor.execute(s)

        # Here we are catching and displaying 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("register.html", message = t_message)

        ID_user = array_row(0)

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

        # Send confirmation 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 = "IMPORTANT: Confirmation link"
        t_recipients = t_email
        t_sender = "server@ourapplication.com"

        # Build message body here
        s = ""
        s += "Dear " + s_email + "<br>"
        s += "<br>"
        s += "Thank you for beginning the registration process." + "<br>"
        s += "<br>"
        s += "STEP ONE - To COMPLETE the process,  you MUST please click on the following link:" + "<br>"
        s += "<br>"
        s += "<a href='https://YOUR APP DOMAIN NAME HERE/register.py?confirm=" + ID_user
        s += "'>https://YOUR APP DOMAIN NAME HERE/register.py?confirm=" + ID_user
        s += "</a>" + "<br>"
        s += "<br>"
        s += "This action will COMPLETE the process and verify your email address and password." + "<br>"
        s += "<b>IMPORTANT: YOUR USERNAME IS YOUR EMAIL ADDRESS.</b><br>"
        s += "<br>"
        s += "NOTE: If you clicked on the link above and for some reason it did not take you to a web page "
        s += "confirming your account has been enabled, please use your mouse to highlight the link "
        s += "above and copy the link and then paste it into the address bar of your web browser and "
        s += "press the ENTER key to be taken to the page on our site that will enable your account."
        s += "<br>"
        s += "STEP TWO - Please save the user name (email address) you see below." + "<br>"
        s += "<br>"
        s += "User Name: " + t_email + "<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 = "Your user account has been added. Check your email for confirmation link."
        return render_template("register.html", message = t_message)
    else:
        # The code below here is for Part 5 of the article.

        # Update the users table to show confirmation.
        s = ""
        s += "UPDATE users SET"
        s += ",b_enabled = flase"
        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 = "Database error: " + e + "/n SQL: " + s
            return render_template("register.html", message = t_message)
        db_cursor.close()

        # Show user they are done and email is confirmed.
        t_message = "Your user account has been added. Thanks for verifying your email address!"
        return render_template("register.html", message = t_message)

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

Conclusion

You’ve now completed part 5 of 5 of creating a registration feature for a web application, which is – so far – to (a) Show the user a registration form that has fields for email address and password; (b) test their input using javascript on the front end; (c) process their input using Python; (d) send them back to the form if they left either field empty; (e) hash the password; (f) insert email address and password into the SQL-compliant database; (g) check for errors; (h) from the database, get the ID of the user we created; (i) send the user a confirmation email; (j) update the original screen with a success message and reminder to check their email; (k) receive email confirmation from the user; (l) set a flag in the PostgreSQL database using Python; and (m) Send the user to a screen with a message their account is enabled.

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.