How to Create a Login Form with Postgres and Python - Part 5: Update DB With New Password
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. Using Python for this. If they verify, we send them on to the rest of the application. – Part 3: Email the user a link because they clicked the “I forgot my password” button. – Part 4: Serve the user a form to create a new password. – Part 5: Using Python and PostgreSQL, we handle the final updating of the database with their new password and then send them back to the login screen.
Overview of this article
In this part 5, we update the database with the new password the user gave us in the last form.
Assumptions and prerequisites
We’ll assume you have followed the instructions in parts 1, 2, 3, and 4, starting here: here.
The code
Final version of your Python code! Paste the code below into your favorite code editor, save the file as “_sign/in.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 | 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 from flask_mail import Mail from flask_mail import Message app = Flask(__name__) # 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": # UPDATE the database with new password s = "" s += "UPDATE users SET" s += " t_password = '" & t_password & "'" 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 = "Reset password: Database error: " + e + "/n SQL: " + s return render_template("sign_in.html", message = t_message) db_cursor.close() # They got this far, meaning they were found in the database # 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": # Show user they are done and they need to log in using their new password. t_message = "Login: Your password has been reset." return render_template("sign_in.html", message = t_message) # This is for command line testing if __name__ == "__main__": app.run(debug=True) |
Conclusion
Congratulations! You’ve now completed the tutorial on how to create a login for your Python / PostgreSQL application.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started