How to Create a Login Form with Postgres and Python - Part 2: Password Hash and Database Validation
Introduction
In this tutorial, we are building a sign-in system that allows users of your Postgres- and Python-based web application to securely sign in to your application with a username and password, log the user’s visit in the database, send that “change your password” link when requested by a user, receive a response from their click on the link we sent them in that email, and allow the user to enter a new password that gets hashed and put in the users table of your PostgreSQL database. This article is part two 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 (we are using this as their username) 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 their email and password match the database, we send them on to the rest of the application. – Part 3: Email the user a link if 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 2 of our tutorial, we will get user input from _sign/in.html, validate their input (in case a clever hacker skipped our submission form) using the email address and hash of password they entered, log the date of their visit in the users table in Postgres. This part of the tutorial will exclusively use server-side Python to do all of the above, including use of SQL statements to first read and then update a record in the users table.
Assumptions and prerequisites
We’ll assume you have followed the instructions in part 1, starting here: here.
The code
Study the code below, paste it into your favorite editor, 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 | 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 # IMPORTANT # In a later part of this article series we will add code here # IMPORTANT from flask_mail import Message app = Flask(__name__) # Mail creds # IMPORTANT # In a later part of this article series we will add code here # IMPORTANT # 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", "") # The test for "reset" we see here will become relevant later in this article series. if t_stage == "login" OR t_stage == "reset": t_password = request.form.get("t_password", "") # Check for email field left empty if t_email == "": # "forgot" test below is for later part of our multi-part article, fine here for now: 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 a later part 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": # IMPORTANT # In a later part of this article series we will add code here # IMPORTANT # If they have used the link in the email we sent them then t_stage is "reset" if t_stage == "reset": # IMPORTANT # In a later part 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 parts 1 and 2 of building login for a web application, which is to (a) create a login form using HTML, CSS, and Javascript; (b) compare user input of email and password to what is in the PostgreSQL database; and (c) route the user accordingly. We used Python for this part of the tutorial. If the user’s submitted username and password verified with what is in the PostgreSQL database, we send them on to the rest of your application.
Look for part 3 where we email the user a link because they clicked the “I forgot my password” link.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started