Use Password Authentication with Node.js and MySQL

Published on September 1, 2022•Updated on November 21, 2023

Introduction

Authentication is the verification of the users' credentials before granting users access to a resource on a server, but storing passwords in plain readable text in a database is dangerous in case of a data breach. Luckily, Node.js comes with inbuilt modules for hashing/encrypting passwords before storage. The hashing function also verifies if a given password matches the hash stored in a database.

In a Node.js application, the authentication credentials include the username and password. These credentials bind to a particular user and are permanently saved in a database like MySQL alongside the users' profile information. Before your application grants access to a user, a backend service compares the users' credentials with the database values. Provided there is a match, the application logs in the user. Otherwise, the user receives an invalid username/password error.

This guide implements basic authentication with Node.js and MySQL on an Ubuntu 20.04 server.

Prerequisites

To complete this guide:

1. Set Up a MySQL Database

Any data-driven application stores information permanently to disk. In a typical Node.js application, you might have a few to a hundred users. The number of users depends on the application's use case. This step focuses on setting up a MySQL database, a database user account, and a system user's table.

Enter your password and press Enter to proceed. Then, run the following SQL commands to create an erp database and an erp_user account. Replace EXAMPLE_PASSWORD with a secure password.

 mysql> CREATE DATABASE erp;
        CREATE USER 'erp_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
        GRANT ALL PRIVILEGES ON erp.* TO 'erp_user'@'localhost';
        FLUSH PRIVILEGES;

Output.

 Query OK, 1 row affected (0.00 sec)
 ...
 Query OK, 0 rows affected (0.01 sec)

Switch to the new erp database.
```
 mysql> USE erp;
```
Output.
```
 Database changed
```
Create a new system_users table to store users' data, such as names, unique usernames, and passwords. Use the AUTO_INCREMENT keyword to instruct MySQL to assign user_ids for new users. The pwd field stores the hashed password. Later, this guide shows you how to hash passwords with Node.js before storage.
```
 mysql> CREATE TABLE system_users (
            user_id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
            first_name VARCHAR(50),
            last_name VARCHAR(50),
            username VARCHAR(50),
            pwd VARCHAR(255)
        ) ENGINE = InnoDB;
```
Output.
```
 Query OK, 0 rows affected (0.01 sec)
```
Log out from the MySQL server.
```
 mysql> QUIT;
```
Output.
```
 Bye
```

2. Create a `system_users` Class

This step shows you how to create a central class that handles the following database operations required by the application:

Connecting to the MySQL database.
Querying the database.
Creating new users.
Verifying users' accounts.

Follow the steps below to create the central class:

Create a project directory to avoid mixing your source code with the rest of the Linux files.
```
 $ mkdir project
```
Navigate to the new project directory.
```
 $ cd project
```
Open a new system_users.js file in a text editor for editing purposes.
```
 $ nano system_users.js
```

Enter the following information into the file. Replace EXAMPLE_PASSWORD with the correct password for the MySQL user account (erp_user).

 class system_users {

     getDbCon() {

         const mysql = require('mysql');

         const mysqlCon = mysql.createConnection({
             host: "localhost",
             user: "erp_user",
             password: "EXAMPLE_PASSWORD",
             database: "erp"
          });

         mysqlCon.connect(function(err) {
             if (err) {
                 console.log(err.message);
             }
         });

         return mysqlCon;
     }

     queryDb(sql, params, callBack){

        const mysqlCon = this.getDbCon();

        mysqlCon.query(sql, params, function (err, result) {
            if (err) {
                callBack(err, null);
            } else {
                callBack(null, result);
            }
        });

     }

     createUser(jsonData, callBack){

        const userData = JSON.parse(jsonData);

        const bcrypt = require('bcrypt');

        const passwordHash = bcrypt.hashSync(userData.pwd, 10);

        const params = [userData.first_name, userData.last_name, userData.username, passwordHash];

        const sql = "insert into system_users (first_name, last_name, username, pwd) values (?, ?, ?, ?)";

        this.queryDb(sql, params, callBack);

     }

     verifyUser(username, password, callBack){

        const bcrypt = require('bcrypt');

        const sql = "select * from system_users where username = ?";
        const params = [username];

        this.queryDb(sql, params, function(err, result) {

           if (err) {

               callBack(err, false);

           } else {

               if (result.length == 0) {

                   callBack(new Error("Invalid username."), null);

               } else {

                   var hashedPassword = result[0].pwd;
                   var response = bcrypt.compareSync(password, hashedPassword);

                   if (response == false) {
                       callBack(new Error("Password verification failed."), null);
                   } else {
                       callBack(null, result);
                   }
               }
           }
        });
     }
 }

 module.exports = system_users;

Save and close the system_users.js file.

The system_users.js file explained:

The system_users.js file wraps different database methods in a system_users class.

 class system_users {

     getDbCon(){...}
     queryDb(...){...}
     createUser(...){...}
     verifyUser(...){...}
 }

The four database methods perform the following operations:
- getDbCon(): This method uses the Node.js MySQL module (require('mysql');) to connect to the database using the mysql.createConnection(...) and mysqlCon.connect(...) functions. The getDbCon() function then returns a re-usable database connection (return mysqlCon;).
- queryDb(sql, params, callBack): This method takes three arguments. The sql argument is a parameterized query string you want to execute. The params is an object containing the parameters' values. The callBack function runs when the queryDb() function completes. This application uses the queryDb() function to INSERT data (when registering users) and to SELECT data (when verifying usernames and passwords).
- createUser(jsonData, callBack): This function accepts two arguments. The jsonData is a payload from an HTTP client. The application receives the payload during a system user registration process. The callBack function executes when the createUser(...) function completes. Because the HTTP client passes the system user's password in plain text during registration, you're using the bcrypt (require('bcrypt');) module to hash out the password before storage (bcrypt.hashSync(userData.pwd, 10);). The 10 is the number of rounds you require to secure the hash. The this.queryDb(sql, params, callBack); function sends the user's credentials to the database.
- verifyUser(username, password, callBack): This function takes the username and password as supplied by an HTTP client through the Authorization request header. The verifyUser() function then constructs an SQL statement (select * from system_users where username = ?) to query the database for a user matching the provided username. If there is no match, the function throws the Invalid username. error. If the verifyUser() function finds a matching username, it uses the bcrypt.compareSync(password, hashedPassword); function to compare the provided password and the hashed password. If the password matches the hash, the verifyUser() function returns a result object containing the user's details. Otherwise, the application raises an error. See the following logic.
```
  ...
  if (response == false) {
      callBack(new Error("Password verification failed."), null);
  } else {
      callBack(null, result);
  }
  ...
```
The module.exports = system_users; statement allows you to use the system_users class in other Node.js files using the const system_users = require('./system_users.js'); statement.

Your system_users class is ready. Proceed to the next step to create the application's entry point.

3. Create an `index.js` File

Every Node.js application requires a main file that runs when the application starts. Create a new index.js file in this application as the entry point to your application by following the steps below.

Open a new index.js file in a text editor.
```
 $ nano index.js
```

Enter the following information into the index.js file.

 const http = require('http');
 const url  = require("url");
 const system_users = require('./system_users.js');

 const hostname = '127.0.0.1';
 const port = 8080;

 const server = http.createServer(httpHandler);

 server.listen(port, hostname, () => {
     console.log(`Server running at http://${hostname}:${port}/`);
 });

 function httpHandler(req, res) {

     const b64AuthString = (req.headers.authorization || '').split(' ')[1] || '';
     const userCredentials = Buffer.from(b64AuthString, 'base64').toString();

     authString = userCredentials.split(":");

     const username = authString[0];
     const password = authString[1];

     const dg = new system_users();

     var pathname = url.parse(req.url).pathname;
     var resourcePath = pathname.split("/");

     var route = "";

     if (resourcePath.length >= 2) {
         route = resourcePath[1];
     }

     var jsonData = "";

     req.on('data', function (data) {
         jsonData += data;
     });

     req.on('end', function () {

         if (route == "register") {

             dg.createUser(jsonData, function(err, result) {

                 if (err) {
                     callBack(err, null);
                 } else {

                     console.log(result);

                     callBack(null, "The user # " + result.insertId + " was created successfully.");
                 }
             });
         }

         if (route == "test_resource") {

             dg.verifyUser(username, password, function(err, result) {

                 if (err) {

                     callBack(err, null);

                 } else {

                     const welcomeString = "Welcome, " + result[0].first_name + " " + result[0].last_name;

                     callBack(null, welcomeString);
                 }

             });
       }
     });

     function callBack(err, resp){

         var response;

         if (err) {

             response = err.message;

         } else {

             response = resp;

         }

         res.statusCode = 200;
         res.setHeader('Content-Type', 'text/plain');

         res.end(response + "\r\n");
     }
 }

Save and close the index.js file.

The index.js file explained:

The first three lines allow you to import the http, url, and the custom system_users modules. The http module provides HTTP functions in your application. The url module allows you to parse the request URL and retrieve the requested resource from the resource path. The system_users is the custom module you created earlier.
```
 const http = require('http');
 const url  = require("url");
 const system_users = require('./system_users.js');
 ...
```
The following lines define the listening interface and port for the HTTP server. For this guide, you're listening on the localhost (127.0.0.1) on port 8080.
```
 ...
 const hostname = '127.0.0.1';
 const port = 8080;
 ...
```
The code block below creates an HTTP server and instructs it to listen on the given port and hostname. Then, the http.createServer(...) function forwards the HTTP request to the httpHandler() function.
```
 ...
 const server = http.createServer(httpHandler);

 server.listen(port, hostname, () => {
     console.log(`Server running at http://${hostname}:${port}/`);
 });
 ...
```
The httpHandler() function runs different HTTP operations required by your application.
```
 ...
 function httpHandler(req, res) {
     ...
 }
 ...
```

The code block below retrieves the username and password combination as received from the HTTP Authorization header.

     ...
     const b64AuthString = (req.headers.authorization || '').split(' ')[1] || '';
     const userCredentials = Buffer.from(b64AuthString, 'base64').toString();

     authString = userCredentials.split(":");

     const username = authString[0];
     const password = authString[1];
     ...

The const dg = new system_users(); statement initialize the system_users class that provides different database functions to your application.
The code block below splits the request URL to retrieve the requested resource. For instance, if an HTTP client requests the http://localhost:8080/test_resource URL, the following statement returns test_resource as the route.
```
 ...
 var pathname = url.parse(req.url).pathname;
 var resourcePath = pathname.split("/");

 var route = "";

 if (resourcePath.length >= 2) {
    route = resourcePath[1];
 }
 ...
```

The code below retrieves the HTTP body, a JSON payload provided by the HTTP client.

 ...
 var jsonData = "";

 req.on('data', function (data) {
     jsonData += data;
 });

 req.on('end', function () {
 ...
 }
 ...

Under the req.on('end', function () {...} function, you're using the logical if (...) {...} statement to route the HTTP request to the following system_users method:
- register route: This request runs the dg.createUser(jsonData, function(err, result) {...} function to create a new user in the system.
- test_resource route: This request runs the dg.verifyUser(username, password, function(err, result) {...} to verify the authenticity of the HTTP user before granting access to the application. If the user's credentials match the database values, the application greets the user by displaying their first name and last name using the Welcome, " + result[0].first_name + " " + result[0].last_name; statement.
Towards the end of the index.js file, the callBack(...) function executes and displays output to the HTTP client.
```
 ...
 function callBack(err, resp){
     ...
     res.end(response + "\r\n");
 }
 ...
```

Your source code files are now in place. Test the application in the next step.

4. Test the Application

You now have all the required functions to run your application. This step shows you how to import the required application's modules, initialize the project directory, and run a few tests with the Linux curl command.

Ensure you've got the latest npm package.

 $ sudo npm install npm -g

Output.

 changed 41 packages, and audited 206 packages in 4s
 ...

Initialize your project directory.
```
 $ npm init
```

Enter the following responses. Some questions require you only to press the Enter to proceed.

 package name: (project) :key_enter:

 version: (1.0.0) :key_enter:

 description: Node.js authentication with MySQL :key_enter:

 entry point: (index.js) :key_enter:

 test command: :key_enter:

 git repository: :key_enter:

 keywords: Node.js, authentication, mysql :key_enter:

 author: Test author :key_enter:

 license: (ISC) :key_enter:

 About to write to ....
 ...
 Is this OK? (yes) yes

Install the MySQL module for Node.js.

 $ npm install mysql

Output.

 added 11 packages, and audited 12 packages in 1s
 ...

Install the bcrypt module, a library for hashing passwords.

 $ npm install bcrypt

Output.

 added 54 packages, and audited 66 packages in 4s
 ...

Run the application.
```
 $ node index.js
```
The application starts a web server and listens for incoming HTTP connections on port 8080. Don't enter any other command on your active terminal window.
```
 Server running at http://127.0.0.1:8080/
```

Establish a new SSH connection in a new terminal window and run the following curl commands to register three new users into the system. Replace EXAMPLE_PASSWORD_1, EXAMPLE_PASSWORD_2, and EXAMPLE_PASSWORD_3 with strong passwords.

 $ curl -X POST http://localhost:8080/register -H 'Content-Type: application/json' -d '{"first_name": "JOHN", "last_name": "DOE", "username": "john_doe", "pwd": "EXAMPLE_PASSWORD_1"}'

 $ curl -X POST http://localhost:8080/register -H 'Content-Type: application/json' -d '{"first_name": "HENRY", "last_name": "JACKSON", "username": "henry_jackson", "pwd": "EXAMPLE_PASSWORD_2"}'

 $ curl -X POST http://localhost:8080/register -H 'Content-Type: application/json' -d '{"first_name": "MARY", "last_name": "SMITH", "username": "mary_smith", "pwd": "EXAMPLE_PASSWORD_3"}'

Output.

 The user # 1 was created successfully.
 The user # 2 was created successfully.
 The user # 3 was created successfully.

Try requesting access to the test_resource using the correct user's credentials.

 $ curl -X GET -u john_doe:EXAMPLE_PASSWORD_1 http://localhost:8080/test_resource
 $ curl -X GET -u henry_jackson:EXAMPLE_PASSWORD_2 http://localhost:8080/test_resource
 $ curl -X GET -u mary_smith:EXAMPLE_PASSWORD_3 http://localhost:8080/test_resource

The application welcomes you to the system and displays the users' full names.

 Welcome, JOHN DOE
 ...
 Welcome, HENRY JACKSON
 ...
 Welcome, MARY SMITH

Try requesting the test_resource using an invalid username (peter_james). That is a user not registered in the database.
```
 $ curl -X GET -u peter_james:EXAMPLE_PASSWORD_1 http://localhost:8080/test_resource
```
Output.
```
 Invalid username.
```

Try again with a correct username but use an invalid password (INVALID_PASSWORD).

 $ curl -X GET -u john_doe:INVALID_PASSWORD http://localhost:8080/test_resource

Output.

 Password verification failed.

Try connecting without a username or a password

 $ curl -X GET http://localhost:8080/test_resource

Output.

 Invalid username.

The above outputs confirm that your application is working as expected.

Conclusion

This guide demonstrates implementing an authentication mechanism with Node.js and MySQL database on Ubuntu 20.04. A production application might have tens or hundreds of resources. Remember to route all requests to the dg.verifyUser(username, password, ...) function before granting access to the application's resources.

Use Password Authentication with Node.js and MySQL

Introduction

Prerequisites

1. Set Up a MySQL Database

2. Create a `system_users` Class

3. Create an `index.js` File

4. Test the Application

Conclusion

Products

Features

Marketplace

Resources

Company

Use Password Authentication with Node.js and MySQL

Introduction

Prerequisites

1. Set Up a MySQL Database

2. Create a system_users Class

3. Create an index.js File

4. Test the Application

Conclusion

2. Create a `system_users` Class

3. Create an `index.js` File