Validate Hashed Password for CockroachDB

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

Introduction

In this instructional article we will learn how to validate a hashed password for a CockroachDB database by using SQL and the hashlib function built into Python. This is a feature used when you want to build a login screen. This is what you can expect to learn:

  • What is hashing, validation, and the hashlib function?
  • We will build an HTML form for user input.
  • We will use Python to utilize the “Render_template” and “Request” functions to show the user an HTML form and retrieve data. Then we will hash the password and insert it into a table in our CockroachDB database.

Prerequisites

  • Some understanding of the basics of SQL code writing for Cockroach, using PG’s free dBeaver tool or other database admin tool and writing applications with languages like PHP, Python, Java, C#, ASP.Net, VB.Net, Node.js, Ruby, etc. In this case, we have used Python.
  • Using SQL statements like “SELECT” and “WHERE”.
  • Some experience with HTML codeing. If not, that’s fine because you will learn enough here.

Before we learn to use hashlib, which is the best function for hashing a password in Python, let’s examine how the request and render_template functions behave, because they are quite necessary for password hashing. For example, what we will do in this instructional article:

  • Build an HTML login form for the user to enter an email and password.
  • Hash the password the user submitted.
  • Compare that hashed password to the one in the Cockroach database.
  • Redirect the user accordingly.

First, we will learn how to build that HTML login form we mentioned above. This involves (a) writing some HTML, which we will write for you; and (b) using Python’s “render_template” function to display the HTML page.

Build HTML form

Below is some HTML, the minimum you will need. Later, you may want to make it more modular by referencing a css file, header file, and footer file, but for now, this will work on all web browsers. Call this page “user-register.html” and save it in a folder called “/templates”, which is the default for Python’s render_template function:

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
<html>
    <head>
        <link rel="shortcut icon" href="favicon.ico">
        <title>Template: Python Validate Hashed Password for Cockroach</title>
    </head>
<body>
<!-- The message below encapsulated in an "h1" tag -->
<!-- is why we call this page a template. It is what -->
<!-- makes this page "dynamic" because the server fills -->
<!-- in at the spot where you see {{txtMessage}} with -->
<!-- data you sent when you used the -->
<!-- render_template function. -->
<h1>{{txtMessage}}</h1>
<!-- div to contain form -->
<div>
    <!-- Set up a form and the file to process user input -->
    <form name='frmLogin' action='' method='post' onsubmit='return true;'>

    <!-- input field for the user to enter their user ID -->
    <!-- Our input below has a 'name' of 'txtNameUser'. -->
    <!-- This is how our request function chooses which data to get. -->
    <div class="form-input">
      User ID: <input type="text"  name="txtNameUser">
    </div>

    <div class="form-input">
      Password: <input type="text"  name="txtPassword">
    </div>

    <!-- button for user to submit the form -->
    <div>
      <input type="submit" name="btnSubmit_add_user" value='LOGIN'>
    </div>
    </form>
</div>
</body>
</html>

Flask Render_Template

Next we increase our understanding of how Python’s Render_Template function can be used. CockroachDB’s Render_Template function displays an HTML page for the user, which can be filled with dynamic content we supply with parameters in the function.

RENDER_TEMPLATE function syntax

1
return render_template(txtName_page, txtMessage)

In this case, we supplied the RENDER_TEMPLATE function with two parameters:

  • txtName_page: This is the name of the HTML page we created to be our template.
  • txtMessage: The contents of “txtMessage” will be sent, along with the user, to name_of_web_web-page.html, an html template you create, that is set up to receive and use txtMessage. Note: instead of “txtMessage”, you can call your variable anything you want. You can even send more variables. Look for our tutorial document that goes into detail on this function.

Flask Request function

The next function we will have to know how to use is the Request function. It is used to pull data that was submitted either by form “post” or by “get” (SQL codestring).

REQUEST function syntax

1
2
variable = request.args.get(field name, default value) # for GET
variable = request.form.get(field name, default value) # for POST.

Analysis

  • field name: This parameter determines the name of the field (how it was named in your SQL code or form). For example, “txtEmailAddr” or “txtNameUser”.
  • default value: In case no data was sent for that field, this is the value your variable will receive. This is an easy way to prevent 404 errors. We often put an empty string here, like ” “.

Request function examples

Here are some examples of how the syntax above could appear when used in a web/database project. We will leave out “get” as it has no relevance to our current learning:

1
2
txtNameUser = request.form.get("txtNameUser", "")
txtPassword = request.form.get("txtPassword", "")

Analysis

  • txtNameUser: This line of scripts grabs the value the user put into the HTML field named “txtNameUser” and places that value in the local Python text variable we called “txtNameUser”.

What is a hash?

A Hash Value – or checksum – is a string (text) value that is the result of a “Hashing Algorithm”. The algorithm we are using here is SHA3-512. One use of Hashing is to determine the integrity of various values, like passwords.

Syntax of the HASHLIB() function

1
2
    txtHashed = hashlib.sha3_512(t_user_input.encode())
    txtPassword = txtHashed.hexdigest()

Now that you have a basic understanding of how to use the primary Python functions required for building a login form, we will show you how to build these functions into a Flask application for CockroachDB so we can validate and hash the password the user entered against the hashed password stored in the “tblUsers table in our Cockroach database.

Build login for Cockroach

We start by using Python’s render_template function to send the user to the HTML page we built above and use the Request function to receive the data given by the user.

1
2
3
4
5
@app.route("/login", methods=["POST","GET"])
def login():
    return RENDER_TEMPLATE("login.html", txtMessage = "Login here")
    txtNameUser = REQUEST.form.get("txtNameUser", "")
    txtPassword = REQUEST.form.get("txtPassword", "")

Next we will get a hash of that value stored in “txtPassword”.

1
2
    txtPassword_hashed = hashlib.sha3_512(txtPassword.encode())
    txtPassword = txtPassword_hashed.hexdigest()

Analysis

  • Starting with the value our user put into the password field on the HTML form, we hash it using Python’s hashlib() function, specifying the “sha3_512” algorithm, and then temporarily saving the resulting value (now hashed) in a variable we called “txtPassword_hashed”. NOTE: The value now in “txtPassword_hashed” will be in byte format, so there is one more step to do.
  • We then use the Hexdigest function to convert the bytes into more readable hexadecimal format.

Now that we have explained the most complex parts required to build our login screen and validation, it’s time to examine a full listing of our source scripts:

Cockroach Project Source

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
from flask import Flask
from flask import render_template
from flask import request
import hashlib
import psycopg2

app = Flask(__name__)
@app.route("/")

# database credentials
txtDBname = "db name"
txtNameUser = "db user name"
txtSSLmode = "auto"
txtSSLrootCert = 'certifs/ca.crt'
t_sslkey = 'certifs/client.maxroach.key'
t_sslcert = 'certifs/client.maxroach.crt'
txtHostURL = "localhost"
txtPortNum = "26251"
connCRDB = psycopg2.connect(database=txtDBname, user=txtNameUser, sslmode=txtSSLmode, sslrootcert=txtSSLrootCert, sslkey=t_sslkey, sslcert=t_sslcert, host=txtHostURL, port=txtPortNum)
cursorCRDB = connCRDB.cursor()

@app.route("/login", methods=["POST","GET"])
def sign_in():
    return render_template("login.html", txtMessage = "Sign in here")
    txtNameUser = request.form.get("txtNameUser", "")
    txtPassword = request.form.get("txtPassword", "")

    # VALIDATION TO CHECK FOR EMPTY FIELDS
    # Check for user name field is empty
    if txtNameUser == "":
        txtMessage = "Login - found empty field: Please fill in your user ID."
        # Send user back to the HTML template with a message
        return render_template("login.html", txtMessage = txtMessage)

    if txtPassword == "":
        txtMessage = "Login - found empty field: Please fill in a unique password"
        return render_template("login.html", txtMessage = txtMessage)

    # Hash the password they entered into a encrypted hex string
    txtHashed = hashlib.sha256(txtPassword.encode())
    txtPassword = txtHashed.hexdigest()
    s = ""
    s += "SELECT"
    s += " ID"
    s += " FROM tblUsers"
    s += " WHERE"
    s += "("
    s += " txtNameUser = '(%txtNameUser)'"
    S += " AND"
    s += " txtPassword = '(%txtPassword)'"
    s += ")"
    # Catch and show any possible errors
    #   while TRYing to commit the SQL code.
    cursorCRDB.execute(s, [txtNameUser,txtPassword])
    try:
        array_record = cursorCRDB.fetchone()
    except psycopg2.Error as e:
        txtMessage = "Cockroach Database error: " + e + "/n SQL: " + s
        return render_template("login.html", txtMessage = txtMessage)
    cursorCRDB.close()

Conclusion

In this lesson we investigated using Python to validate a hashed password for a login screen, where our user data is in CockroachDB. Part of that process was to learn how to use the Render_template and Request() functions to retrieve data posted by the user to login using an HTML form and the Post property. The next thing we did is hash the user’s password and check it and the user name against data in the CockroachDB users table where the password is already hashed from when the user record was created in a previous article. We created a login application using HTML, Python, SQL, and Cockroach. Finally, we shared all the Python source scripts you need here.

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.