Credentials in Database

Learning objectives

Knows how to store passwords in a non-plaintext format.
Knows how to store credentials in a database and knows how authentication could be implemented in an application that uses a database.

In the previous examples, we focused on principles of authentication. In the examples, the password was written in plaintext into the code, and there was no way to distinguish between users of the application. Here, we will look into using a database for authentication.

We use the following create table statement for creating the database table that stores the users. For each user, we store an email and a password. The email can be up to 320 characters long, while the password will be exactly 60 characters long (we will soon know why). In addition, we create a unique index on the email field, where in the index the emails are stored lowercase. This means that the database table can contain only one of each email.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(320) NOT NULL,
  password CHAR(60) NOT NULL
);

CREATE UNIQUE INDEX ON users((lower(email)));

Storing passwords

Before going into working with the database, let's first have a brief discussion on storing passwords. Never, ever store passwords in a plaintext format. Brief discussion done.

When storing passwords to a database, instead of storing the password, we store a one-way hash of the password. This means that during storing the password, the password is transformed to a hash using a cryptographic hash function, which should be non-trivial to invert. Then, when we check whether a password submitted by the user matches the one in the database, we use -- in principle -- the same cryptographic hash function to first transform the password submitted by the user to a hash, and then compare that hash with the one in the database.

In our case, we use the bcrypt function for hashing passwords. We won't go into the details of the actual function, and use a bcrypt library. The library provides two asynchronous functions -- hash and compare -- which we look into next.

In the example below, we import the library and then create a hash for the word "asparagus" that we have previously used as a password. Then, the hash is printed into the console. As we can see from the output below, guessing the password based on the hash can be quite difficult.

import * as bcrypt from "https://deno.land/x/bcrypt@v0.4.1/mod.ts";

const hash = await bcrypt.hash("asparagus");
console.log(hash);

deno run --unstable --allow-net app.js
$2a$10$qSnqTVlUngNpuXzuxvKHnO04NWUpjLiapfGdZykFA2WRQRKa2ELEK
deno run --unstable --allow-net app.js
$2a$10$wj5vJVNkDjoPT1Xt/LX85uCx2kXIridHuqvOnx1I80y8S961.MeRO
deno run --unstable --allow-net app.js
$2a$10$RWni5KxOdLkq6QIAYbLiqOZ0tNbtDhoGIPnToTfZBYS7q5.hqXmvK

When looking at the above outputs of the program, we see something interesting. The hash calculated during each run is different. Indeed, when we run the application again, the hash is again different.

deno run --unstable --allow-net app.js
$2a$10$/ydkQIaf5hvasjpz5W.0D.WtNBR5Wy82TeDwYz3g/nw0xIccwQMkO

As we earlier noted that we won't go into the details of the bcrypt algorithm, we will just note that the hash contains both a so-called salt and the actual hashed password. When a password (or, any string) is hashed using the bcrypt algorithm, the hashed output depends on the used salt, which changes over the calls to the hash function. In practice, this leads to a situation where two copies of the same password have different hashes.

Checking whether a given password matches a hashed password is done using the asynchronous compare function of the bcrypt library. The function is given two parameters -- the password and a hash, and the function returns whether the given password, when hashed, matches the existing hash.

In the following example, we hash the password 'asparagus', and then compare the hash with the strings 'password' and 'asparagus'.

import * as bcrypt from "https://deno.land/x/bcrypt@v0.4.1/mod.ts";

const hash = await bcrypt.hash("asparagus");

console.log('Comparing hash with password');
let result = await bcrypt.compare("password", hash);
console.log(`Were they the same? ${result}`);

console.log('Comparing hash with asparagus');
result = await bcrypt.compare("asparagus", hash);
console.log(`Were they the same? ${result}`);

When we run the application, the output is as follows.

deno run --unstable --allow-net app.js
Comparing hash with password
Were they the same? false
Comparing hash with asparagus
Were they the same? true

The hash which is produced by the hash function is always 60 characters (at least in the near future of the library that we use). Thus, when creating a database table that stores a password, we use 60 characters as the length of the password field.

Registration functionality

Let's look into implementing registration functionality. We first create a form that has an input field for email, and two input fields for the password. The second input field is used for verifying that the user types in the same password twice. A simple form with the required fields is as follows -- we assume that the form is stored into a file called register.eta that is in the views folder.

<h1>Register!</h1>
<form method="POST">
    <input type="email" name="email" />
    <input type="password" name="password" />
    <input type="password" name="verification" />
    <input type="submit" value="Submit!" />
</form>

Next, we create an application that allows adding an user to the database. In the following application, a GET request to the path '/registration' shows the registration form, while a POST request to the path '/registration' submits the data for registration. For simplicity, the data is not validated and possible errors related to the passwords not matching are shown in plaintext.

Without the database, the functionality for the registration looks as follows. Functionality not yet implemented is added to the code using comments.

import { Application, Router } from "https://deno.land/x/oak@v12.6.1/mod.ts";
import { renderMiddleware } from "./middlewares/renderMiddleware.js";
import * as bcrypt from "https://deno.land/x/bcrypt@v0.4.1/mod.ts";

const app = new Application();
const router = new Router();

app.use(renderMiddleware);

const showForm = async ({ render }) => {
  render("register.eta");
};

const register = async ({ request, response }) => {
  const body = request.body();
  const params = await body.value;

  const email = params.get("email");
  const password = params.get("password");
  const verification = params.get("verification");

  // here, we would validate the data, e.g. checking that the
  // email really is an email

  if (password !== verification) {
    response.body = "The entered passwords did not match";
  } else {
    // check if there already exists such an email in the database
    // -- if yes, respond with a message telling that the user
    // already exists

    // otherwise, store the details in the database
    const hash = await bcrypt.hash(password);
    // when storing a password, store the hash

    response.body = "Registration successful!";
  }
};

router.get("/register", showForm);
router.post("/register", register);

app.use(router.routes());

app.listen({ port: 7777 });

When we try out the application, it works as expected. A GET request to the path '/register' returns the registration form, while a POST request to the same path checks the content of the request, indicating whether the entered passwords matched or not. If the passwords match, the application prints a registration successful message.

curl http://localhost:7777/register
<h1>Register!</h1>
<form method="POST">
    <input type="email" name="email" />
    <input type="password" name="password" />
    <input type="password" name="password-verification" />
    <input type="submit" value="Submit!" />
</form>%
curl -X POST -d "email=my@email.net&password=easy&verification=peasy" http://localhost:7777/register
The entered passwords did not match%
curl -X POST -d "email=my@email.net&password=easy&verification=easy" http://localhost:7777/register
Registration successful!%

Let's add in the functionality for storing the password to the database. For this purpose, we use the sql function that we have previously implemented. We assume that the functionality is in a file called database.js that resides in the database-folder.

When adding a user, we need to first check whether the email is already in use. If so, we inform the user and do not add the user to the database. Checking whether an email is already in use is straightforward in SQL -- if a query that looks for users with the given email returns rows, then the email is already in use.

SELECT * FROM users WHERE email = '...';

If the user is not in the database, we insert the user to the database. For this, we use a simple insert statement.

INSERT INTO users (email, password) VALUES ('...', '...');

When plugging the database queries in place, the application looks as follows.

import { Application, Router } from "https://deno.land/x/oak@v12.6.1/mod.ts";
import * as bcrypt from "https://deno.land/x/bcrypt@v0.4.1/mod.ts";
import { renderMiddleware } from "./middlewares/renderMiddleware.js";
import { sql } from "./database/database.js";

const app = new Application();
const router = new Router();

app.use(renderMiddleware);

const showForm = async ({ render }) => {
  render("register.eta");
};

const register = async ({ request, response }) => {
  const body = request.body();
  const params = await body.value;

  const email = params.get("email");
  const password = params.get("password");
  const verification = params.get("verification");

  // here, we would validate the data, e.g. checking that the
  // email really is an email

  if (password !== verification) {
    response.body = "The entered passwords did not match";
    return;
  }

  // check if there already exists such an email in the database
  // -- if yes, respond with a message telling that the user
  // already exists
  const existingUsers = await sql`SELECT * FROM users WHERE email = ${email}`;
  if (existingUsers.length > 0) {
    response.body = "The email is already reserved.";
    return;
  }

  // otherwise, store the details in the database
  const hash = await bcrypt.hash(password);
  // when storing a password, store the hash
  await sql`INSERT INTO users (email, password) VALUES (${email}, ${hash})`;
  response.body = "Registration successful!";
};

router.get("/register", showForm);
router.post("/register", register);

app.use(router.routes());

app.listen({ port: 7777 });

Now, when we try out the application, it works as expected. A GET request to the path '/register' shows a registration form. The first POST request to the path '/register' with correct data produces a response indicating that the registration was successful. The next request, when using the same email, does not -- this is due to the check that verifies that the same email should not be entered to the database twice.

curl http://localhost:7777/register
<h1>Register!</h1>
<form method="POST">
    <input type="email" name="email" />
    <input type="password" name="password" />
    <input type="password" name="verification" />
    <input type="submit" value="Submit!" />
</form>%
curl -X POST -d "email=email@email.net&password=easy&verification=easy" http://localhost:7777/register
Registration successful!%
curl -X POST -d "email=email@email.net&password=easy&verification=easy" http://localhost:7777/register
The email is already reserved.%

Authentication functionality

Let's next look into adding authentication (that is, login) functionality. For authentication, we use a form that has two fields, one for email and one for password. A simple version of such a form could look as follows -- let's store the following form into a file called login.eta.

<h1>Login!</h1>
<form method="POST">
    <input type="email" name="email" />
    <input type="password" name="password" />
    <input type="submit" value="Submit!" />
</form>

Let's create an application that allows logging in using the above form. A GET request to the path '/login' returns the above form, while a POST request to the path '/login' sends the credentials to the server, where the credentials are verified against those in the database. We call the respective functions showLoginForm and authenticate.

The function showLoginForm is rather straightforward. It simply renders the login.eta to the user.

const showLoginForm = ({ render }) => {
  render("login.eta");
};

The function authenticate is a bit more complex. We first create it without sessions to demonstrate how the authentication with the database should be handled. When a user attempts to authenticate, we extract the email and the password from the data sent to the server. Then, we search for the details of the user from the database using the email; if an email is found, we compare the password hash associated with the email against the password sent to the server. The comparison is done using the compare function of the BCrypt library.

const authenticate = async ({ request, response }) => {
  const body = request.body();
  const params = await body.value;

  const email = params.get("email");
  const password = params.get("password");

  // check if the email exists in the database
  const rows = await sql`SELECT * FROM users WHERE email = ${email}`;
  if (rows.length === 0) {
    response.status = 401;
    return;
  }

  // take the first row from the results
  const userObj = rows[0];

  const hash = userObj.password;

  const passwordCorrect = await bcrypt.compare(password, hash);
  if (!passwordCorrect) {
    response.status = 401;
    return;
  }

  response.body = "Authentication successful!";
};

In the above example, the status code 401 is used to both inform that the email was not found and that the password was not correct. It could be possible to indicate that the email was not found from the database, but this would also create a flaw to the application. If web applications would specifically signal that an email was not found, this would effectively also signal that an email was found. Malicious users could then use this information to determine which services are used by a user whose email they have found. That's a topic for another course though.

Adding a session to the above application would require small changes; in addition to importing the session library and taking sessions into use, we would want to add details about the user having authenticated to the session. This is straightforward to do.

const authenticate = async ({ request, response, state }) => {
  // ...

  await state.session.set("authenticated", true);
  response.body = "Authentication successful!";
};

In practice, we would also want to store details of the actual user to the session. These details likely would include the user id and the email.

const authenticate = async ({ request, response, state }) => {
  // ...

  await state.session.set("authenticated", true);
  await state.session.set("user", {
    id: userObj.id,
    email: userObj.email,
  });
  response.body = "Authentication successful!";
};

This would lead to a situation where information about the current user would be available for the application to use in other parts as well.

Combining registration and authentication

As a whole, an application that has both the registration and authentication functionality could look as follows (although it should be structured into smaller pieces). The Eta files are the same as shown previously.

import { Application, Router } from "https://deno.land/x/oak@v12.6.1/mod.ts";
import { Session } from "https://deno.land/x/oak_sessions@v4.1.9/mod.ts";
import * as bcrypt from "https://deno.land/x/bcrypt@v0.4.1/mod.ts";
import { renderMiddleware } from "./middlewares/renderMiddleware.js";
import { sql } from "./database/database.js";

const app = new Application();
const router = new Router();
app.use(Session.initMiddleware());

app.use(renderMiddleware);

const showForm = async ({ render }) => {
  render("register.eta");
};

const register = async ({ request, response }) => {
  const body = request.body();
  const params = await body.value;

  const email = params.get("email");
  const password = params.get("password");
  const verification = params.get("verification");

  // here, we would validate the data, e.g. checking that the
  // email really is an email

  if (password !== verification) {
    response.body = "The entered passwords did not match";
    return;
  }

  // check if there already exists such an email in the database
  // -- if yes, respond with a message telling that the user
  // already exists
  const existingUsers = await sql`SELECT * FROM users WHERE email = ${email}`;
  if (existingUsers.length > 0) {
    response.body = "The email is already reserved.";
    return;
  }

  // otherwise, store the details in the database
  const hash = await bcrypt.hash(password);
  // when storing a password, store the hash
  await sql`INSERT INTO users (email, password) VALUES (${email}, ${hash})`;
  response.body = "Registration successful!";
};

const showLoginForm = ({ render }) => {
  render("login.eta");
};

const authenticate = async ({ request, response, state }) => {
  const body = request.body();
  const params = await body.value;

  const email = params.get("email");
  const password = params.get("password");

  // check if the email exists in the database
  const rows = await sql`SELECT * FROM users WHERE email = ${email}`;
  if (rows.length === 0) {
    response.status = 401;
    return;
  }

  // take the first row from the results
  const userObj = rows[0];

  const hash = userObj.password;

  const passwordCorrect = await bcrypt.compare(password, hash);
  if (!passwordCorrect) {
    response.status = 401;
    return;
  }

  await state.session.set("authenticated", true);
  await state.session.set("user", {
    id: userObj.id,
    email: userObj.email,
  });
  response.body = "Authentication successful!";
};

router.get("/register", showForm);
router.post("/register", register);

router.get("/login", showLoginForm);
router.post("/login", authenticate);

app.use(router.routes());

app.listen({ port: 7777 });

When using the application, both registration and authentication seem to work as expected.

curl -X POST -d "email=my@mail.net&password=easy&verification=easy" http://localhost:7777/register
Registration successful!%
curl --X POST -d "email=my@mail.net&password=easy" http://localhost:7777/login
Authentication successful!%