Use Password Authentication with Node.js and MySQL
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:
- Deploy an Ubuntu 20.04 server.
- Install a MySQL server.
- Install the latest Node.js package (Option 2: Install via PPA Version).
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.
Log in to the MySQL server as a
root
user.$ sudo mysql -u root -p
Enter your password and press Enter to proceed. Then, run the following SQL commands to create an
erp
database and anerp_user
account. ReplaceEXAMPLE_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 theAUTO_INCREMENT
keyword to instruct MySQL to assignuser_ids
for new users. Thepwd
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 asystem_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 themysql.createConnection(...)
andmysqlCon.connect(...)
functions. ThegetDbCon()
function then returns a re-usable database connection (return mysqlCon;
).queryDb(sql, params, callBack)
: This method takes three arguments. Thesql
argument is a parameterized query string you want to execute. Theparams
is an object containing the parameters' values. ThecallBack
function runs when thequeryDb()
function completes. This application uses thequeryDb()
function toINSERT
data (when registering users) and toSELECT
data (when verifying usernames and passwords).createUser(jsonData, callBack)
: This function accepts two arguments. ThejsonData
is a payload from an HTTP client. The application receives the payload during a system user registration process. ThecallBack
function executes when thecreateUser(...)
function completes. Because the HTTP client passes the system user's password in plain text during registration, you're using thebcrypt
(require('bcrypt');
) module to hash out the password before storage (bcrypt.hashSync(userData.pwd, 10);
). The10
is the number of rounds you require to secure the hash. Thethis.queryDb(sql, params, callBack);
function sends the user's credentials to the database.verifyUser(username, password, callBack)
: This function takes theusername
andpassword
as supplied by an HTTP client through theAuthorization
request header. TheverifyUser()
function then constructs an SQL statement (select * from system_users where username = ?
) to query the database for a user matching the providedusername
. If there is no match, the function throws theInvalid username.
error. If theverifyUser()
function finds a matching username, it uses thebcrypt.compareSync(password, hashedPassword);
function to compare the provided password and the hashed password. If the password matches the hash, theverifyUser()
function returns aresult
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 thesystem_users
class in other Node.js files using theconst 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 customsystem_users
modules. Thehttp
module provides HTTP functions in your application. Theurl
module allows you to parse the request URL and retrieve the requested resource from the resource path. Thesystem_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 port8080
.... 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 thehttpHandler()
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 thesystem_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 returnstest_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 followingsystem_users
method:register
route: This request runs thedg.createUser(jsonData, function(err, result) {...}
function to create a new user in the system.test_resource
route: This request runs thedg.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 theWelcome, " + result[0].first_name + " " + result[0].last_name;
statement.
Towards the end of the
index.js
file, thecallBack(...)
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 followingcurl
commands to register three new users into the system. ReplaceEXAMPLE_PASSWORD_1
,EXAMPLE_PASSWORD_2
, andEXAMPLE_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.