How to Add User Registration using PostgreSQL and Python - Part 3: Database Insert and Password Hash
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 three of a five-part series. In this series, we will address the following: – Part 1: Add a users table to our Postgres database. – Part 2: Build the registration form using HTML, CSS, and Javascript. Interaction with the database will be absent from this part 2. – Part 3: Validate user input, create a hash of the user’s password, and insert this data into PostgreSQL. We’ll use Python for this part of the tutorial. – Part 4: Give the user feedback on the screen and send them a confirmation email. Again, we’ll use Python and Postgres for this part of the tutorial. – 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 3 of our series, we will get user input from register.html, validate their input (in case a clever hacker skipped our submission form), hash the user’s password, and add them to our 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 write a record to the users table.
Assumptions and prerequisites
We’ll assume the following: – You followed the instructions in parts 1 and 2, starting here: here.. – You have a Python-ready web server and site set up, whether local or remote, where you can test your work. – You have installed and know how to use PIP. – You have a basic knowledge of SQL and Python. A high level understanding should be all you need, as we will be providing the code you need 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!
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 | 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 app = Flask(__name__) @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(): # get user input from the html form 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() # database insert t_host = "database server address here" t_port = "5432" t_dbname = "practice" t_user = "remote" t_pw = "password here" db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw) db_cursor = db_conn.cursor() # We take the time to build our SQL query string so that # (a) we can easily & quickly read it # (b) we can easily & quickly edit or add/remote lines # The more complex the query, the greater the benefits s = "INSERT INTO public.users " s += "(" s += " t_email" s += ", t_password" s += ") VALUES (" s += " '" + t_email + "'" s += ",'" + t_password + "'" 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. # 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) t_message = "Your user account has been added." return render_template("register.html", message = t_message) # this is for command line testing if __name__ == "__main__": app.run(debug=True) |
Conclusion
That’s it! You’ve completed this part 3 of creating a registration feature for a web application, which is 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, (c) send them back to the form if they left either field empty, (d) hash the password, (e) insert email address and password into the relational database, (f) check for errors, and (g) send the user back to the original screen with a success message.
Look for part 4 where we will write some Python code to add some cool features, primarily, sending the user a confirmation email.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started