Hash Password for CockroachDB

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

Introduction

In this tutorial document we learn how to use Python to hash a password for a CockroachDB via the hashlib function built into Python’s hashlib library and then use SQL to insert the hashed password into our CockroachDB database. Here’s how we will do it:

  • What? What is the hashlib function and what is its syntax?
  • How? We will set up a Python environment to use the Render_template and Request Flask functions to send the user to an HTML form and retrieve data from the user. Then we will hash the password and insert it into a table in our CockroachDB database.

Prerequisites

  • Some experience with the basics of writing SQL in CockroachDB, using PG’s free dBeaver tool or other database admin tool. And/or writing applications with languages like Python, PHP, C#, Java, ASP.Net, VB.Net, Node.js, Ruby, etc. In this case, we have used Python.
  • Using simple SQL statements, including INSERT INTO.
  • Familiarity with HTML forms. If not, that’s perfectly fine as you will learn the important parts here.

Before we get to using hashlib, which is a function for hashing a password in Python using the protocol of your choice, let’s briefly go over the basics of how the “request” and “render_template” functions work, because they are often used in conjunction with password hashing since we must GET the password from the user before working on it. For example, you build an HTML [template] form for the user to enter an email address and password for adding to a Cockroach database, you receive that data they submitted, hash the password, and then insert it into a table in CockroachDB.

The first function we will learn 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
myVar = request.args.get(field name, default value)
myVar = request.form.get(field name, default value)

Analysis

  • field name: This parameter determines the name of the field (how it was named in your SQL codestring or HTML 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 as you will see in the examples below.

Request function examples

Here are some examples of how the syntax above might look when used in your Python/CockroachDB project. We will leave out “get” (request.args) as it has no relevance to our current situation:

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

Analysis

  • txtNameUser: This line of Python scripts is like instructing the server to: “Look at the URL, find the ‘?’, after that symbol, look for ‘txtNameUser=’ and place the value that comes after that ‘=’ into a local variable we are naming txtNameUser.”

Render_template

Flask’s Render_Template function displays an HTML page for the user, which can be filled with dynamic content we control with parameters in the function. Note: Please don’t confuse it with the redirect function, which sends the user away without waiting for a return.

Render_template Syntax

1
return render_template("name_of_web_web-page.html", txtMessage = "some data")

In this example, we supplied the RENDER_TEMPLATE function with the txtMessage parameter:

  • txtMessage: The contents of “txtMessage” (“some data”) 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. By “use”, we mean the server fills in the spot in the HTML template allocated for display of the contents of txtMessage. Be aware that instead of “txtMessage”, you can call your variable anything you want. You can even send many more variables. Look for one of our other tutorial documents that go into greater detail on the ways to use this function.

Back on track…

Hashing

A Hash Value, otherwise known as a checksum, is a string type (“text” in CockroachDB parlance) value which is the result of calculation of a Hashing Algorithm. The algorithm we will use here is called SHA3-512 because 512 is better than 256. A primary use of a Hash is to determine the Integrity of Data, which in this case is a password.

HASHLIB syntax

Syntax of the HASHLIB function

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

NECESSARY PYTHON SCRIPT

You will have to install and reference these Python flask libraries in order to use the request and render_template functions, as well as the database insert we will do.

Once they are installed, you can “import” them into your Python project, as you see here:

1
2
3
4
5
from flask import Flask
from flask import render_template
from flask import request
import hashlib
import psycopg2

Build an HTML “template”

Below is the minimum-needed HTML. Later, you may want to make it more modular by referencing a header, css file, and footer, but for now, this will work on all web browsers. Call this page “user-register.html” and save it in a folder called “/templates” under the root folder for your project’s py files.:

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>Python Hash Password and Save in CockroachDB Template</title>
    </head>
<body>
<!-- The txtMessage below in an "h1" tag as you see below. -->
<!-- This 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 form to allow user input -->
    <form id='frmTesty' name='frmTesty' action='' method='post' onsubmit='return true;'>
    <!-- input box for the user to enter their user name -->
    <!-- Notice our input has a name -->
    <!-- (parameter of the HTML input type) 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='Join Our Cult'>
    </div>
    <!-- Close the form -->
    </form>
<!-- Close the container div -->
</div>
<!-- Close the body of the HTML document -->
</body>
</html>

Analysis

  • : Div is how we are encapsulating content in this HTML document. Often times we use div to assign styles to content via an inline “style” tag or with a “class” tag pointing to some predefined CSS.
  • For the rest, use the comments we liberally placed in the HTML above.

From HTML to Cockroach

Let’s start out by using Python’s render_template function to send the user to the HTML page we built above and then retrieve the data submitted by the user using the Request function.

1
2
3
4
5
@app.route("/register", methods=["POST","GET"])
def sign_in():
    return RENDER_TEMPLATE("user-register.html", txtMessage = "Register Yourself Here")
    txtNameUser = REQUEST.form.get("txtNameUser", "")
    txtPassword = REQUEST.form.get("txtPassword", "")

Now we will do the actual hashing of the user’s input.

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

Analysis

  • We start out with the value a user put into “txtPassword”, hash it with the hashlib function, using the sha3_512 algorithm, and store the resulting – hashed – value in a variable we called “txtHashed”. This looks like a long series of bytes.
  • Then we use the hexdigest function to convert the sequence of bytes returned by hashlib into hex data.

Now, since this tutorial document is not about inserting rows into a Cockroach database as much as it is about hashing passwords, we will skip in-depth analysis of the SQL but we will provide the full source scripts below.

Article source code

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

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

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("/register", methods=["POST","GET"])
def sign_in():
    return render_template("user-register.html", txtMessage = "Register Yourself Here")
    txtNameUser = request.form.get("txtNameUser", "")
    txtPassword = request.form.get("txtPassword", "")

    # Check for user name field is empty
    if txtNameUser == "":
        txtMessage = "Register - empty field: Please fill in your user ID."
        # Send user back to the html page (template), with a friendly message.
        return render_template("user-register.html", txtMessage = txtMessage)

    if txtPassword == "":
        txtMessage = "Please fill in your unique and hard-to-hack password"
        return render_template("user-register.html", txtMessage = txtMessage)

    # Hash the password they entered into a encrypted hex string
    txtHashed = hashlib.sha256(txtPassword.encode())
    txtPassword = txtHashed.hexdigest()

    # We take the time to build a SQL string so that
    #   (a) we can easily and briefly read it; and
    #   (b) we can easily and briefly edit or add/remote lines.
    #   The more complex the SQL, the greater the benefits of the following line-by-line approach.
    s = ""
    s += "INSERT INTO tblUsers "
    s += "("
    s += " txtNameUser"
    s += ",txtPassword"
    s += ") VALUES ("
    s += " '(%txtNameUser)'"
    s += ",'(%txtPassword)'"
    s += ")"
    # Here we catch and display any possible errors
    #   while TRYing to commit the SQL code.
    cursorCRDB.execute(s, [txtNameUser, txtPassword])
    try:
        connCRDB.commit()
    except psycopg2.Error as e:
        txtMessage = "Database error: " + e + "/n SQL: " + s
        return render_template("user-register.html", txtMessage = txtMessage)
    cursorCRDB.close()

    # Clean up
    cursorCRDB.close()

TIP: Use Cockroach Procedures

Protect the integrity of your users’ data by learning about Cockroach Procedures, often known as “Stored Procedures”.

Conclusion

In this instructional article your knowledge grew about how to use the Python hash password function and save it in CockroachDB. We also used the Flask Request and Render_template functions to get data posted by the user to register using an HTML form and “Post”. Next we hashed their password and inserted it – along with username – as a new record in our CockroachDB database table. We built a simple application using Python, HTML, and CockroachDB. Finally, we gave you all of the Python source scripts you can learn about and copy into your own projects.

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.