How to Implement Login Stored Procedures with Node.js and MySQL?

Introduction 

In this article, we explore calling stored procedures in Node.js and creating a login stored procedure in MySQL. Stored procedures are named sets of SQL statements used for encapsulating complex queries within the database. The login stored procedure we create, called "CheckPassword," validates user credentials during login, checking if the provided password matches the user's stored password. By using stored procedures, we improve code maintainability and security, preventing direct exposure of sensitive information. The article guides you through setting up a Node.js project, creating a MySQL database and table, and implementing the login procedure. With this approach, you can develop a robust and reusable login system for your Node.js applications, ensuring safe user authentication.

Steps for creating a project in Node.js

Step 1. Setting up a Node.js project

Use the previous article for setting up Node.js, "How to upload file in Node.js,"  and creating a table in MySQL. In this article, we mentioned the necessary commands for uploading files in Node.js and creating tables in Mysql.

Step 2. Create a Database and table

Suppose we have created a schema named "mydb" within this database. In this schema, we have created a table named "employee" with columns named "empid," "empname," and "emppassword." If you are unsure how to create a schema and table, we have attached a link to an article providing a detailed explanation (DDL, DML, and DCL in MySQL).

Step 3. Creating login stored procedure in MYSQL

In MySQL, a named series of SQL statements used to validate user credentials during the login process is known as a stored procedure. The username and password are normally input parameters for the operation. The database is then searched for a matching username, and a check is made to see if the supplied password matches the user's stored password. The process may produce a success code or the user's unique identification if the login is successful. An error code indicating that the user does not exist may be returned if the username cannot be located.

It might produce an error code signifying an incorrect password if the password does not match. By preventing the direct disclosure of login information in application code, stored procedures help wrap the login logic in the database, making it more reusable and possibly more secure.

Query

CREATE DEFINER=`root`@`localhost` PROCEDURE `CheckPassword`(
    IN empid_in INT(8),
    IN emppassword_in VARCHAR(45),
    OUT yes_no CHAR(1)
)
BEGIN
    IF EXISTS(SELECT * FROM employee WHERE empid = empid_in AND emppassword = emppassword_in) THEN
        SET yes_no = '1'; -- Password found (successful login)
    ELSE
        SET yes_no = '0'; -- Password not found (incorrect login)
    END IF;
END

Explanation

Employee ID, password, and output parameter yes_no are the three parameters that are passed into the CheckPassword stored procedure. This procedure's goal is to see if the empid_in and emppassword_in inputs match any records in the employee table. The output parameter yes_no is set to '1', and the login attempt is successful if a match is discovered. Otherwise, the yes_no parameter is set to '0' if no match is discovered, denoting an unsuccessful login attempt.

This stored procedure can be used to check user login information against database-stored information. This logic is contained within a procedure, improving the code's security, maintainability, and reuse. The procedure can operate with various employee IDs and passwords on a dynamic basis thanks to the use of parameters. The procedure can be invoked from an application, and depending on the value of the output parameter yes_no, the right actions, such as giving access or showing an error message, can be taken.

Step 4. Project Structure

When successfully setting up a Node.js Project, it creates some folders such as node modules, package-lock.json, and package.json, but we need to create some files below the image attached. You want to create a 'views' folder. This folder has ejs or HTML files, and one more file is needed to create, which is 'index.js'.

index.js

// Importing the Express.js module
const express = require('express');
const mysql = require('mysql2');
const bodyParser = require('body-parser'); 

// Create an Express application
const app = express();

// Set EJS as the view engine
app.set('view engine', 'ejs');
// Set the views directory to the 'views' folder
app.set('views', 'views');
// Use the body-parser middleware
app.use(bodyParser.urlencoded({ extended: true }));

// MySQL database configuration
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'Your_UserName',
  password: 'Your_Dtabase_Password',
  database: 'Your_Database_name',
  multipleStatements: true, // Add this option
});
  // Establish MySQL database connection
  connection.connect((err) => {
    if (err) {
      console.error('Error connecting to the database:', err.stack);
      return;
    }
    console.log('Connected to the database as ID:', connection.threadId);
  });
// Define a route to handle GET requests to the root URL ('/')
app.get('/', (req, res) => {
  res.render('first');
});
app.post('/login', (req, res) => {
  const id = req.body.id;
  const password = req.body.pwd;
  const sql = 'CALL Your_database_name.CheckPassword(?, ?, @output_value)';
  connection.query(sql, [id, password], (err, results) => {
    if (err) {
      console.error('Error executing query:', err.stack);
      return res.status(500).send('Error fetching data from the database');
    }

    // Fetch the output parameter value by executing another query
    connection.query('SELECT @output_value as outputValue', (err, result) => {
      if (err) {
        console.error('Error fetching output parameter:', err.stack);
        return res.status(500).send('Error fetching data from the database');
      }
      const outputParam = result[0].outputValue;

      if (outputParam === '1') {
        res.send('Login successful');
      } else {
        res.send('Login failed');
      }
    });
  });
});

// Start the server
const port = 3000; // You can use any available port you prefer
app.listen(port, () => {
  console.log(`Server running on http://localhost:${port}`);
});

Explanation

This Node.js application handles user login functionality using Express.js and MySQL. The program configures a server and creates a link to the MySQL database. It uses body-parser middleware to parse incoming data and EJS as the view engine to render a login page. The server receives the entered ID and password when a user submits the login form. The given ID and password are then passed as inputs to a MySQL-stored process called CheckPassword, which is then called by the server. The stored method determines whether the credentials match any employee table record. It gives the Node.js application an output parameter ('1' on success, '0' on failure).

The value of the output parameter is then fetched by the Node.js application using another query. The server responds to the client with "Login successful" or "Login failed" messages depending on the output. By utilizing MySQL stored procedures to handle the actual password verification logic in the database, this configuration enables secure login validation while protecting sensitive data and giving the user a clear result.

first.ejs

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Login Form</title>
  <style>
    body {
      font-family: Arial, sans-serif;
      background-color: #f2f2f2;
    }

    .login-container {
      max-width: 300px;
      margin: 100px auto;
      padding: 20px;
      border: 1px solid #ccc;
      background-color: #fff;
      border-radius: 5px;
    }

    .login-container h2 {
      text-align: center;
      margin-bottom: 20px;
    }

    .login-form {
      display: flex;
      flex-direction: column;
    }

    .login-form label {
      margin-bottom: 10px;
    }

    .login-form input[type="text"],
    .login-form input[type="password"] {
      padding: 10px;
      border: 1px solid #ccc;
      border-radius: 3px;
    }

    .login-form input[type="submit"] {
      padding: 10px;
      background-color: #007bff;
      color: #fff;
      border: none;
      border-radius: 3px;
      cursor: pointer;
    }

    .login-form input[type="submit"]:hover {
      background-color: #0056b3;
    }
  </style>
</head>
<body>
  <div class="login-container">
    <h2>Login</h2>
    <form class="login-form" action="/login" method="post">
      <label for="user_id">User ID:</label>
      <input type="number" id="user_id" name="id" required>

      <label for="password">Password:</label>
      <input type="password" id="password" name="pwd" required>

      <input type="submit" value="Login">
    </form>
  </div>
</body>
</html>

Explanation

An easy HTML login form for user authentication is provided here. It comprises a container in the middle that has fields for the user ID and password. Each input field on the form is labeled with the information that should be entered. For secure input, the user ID field is of type "number," while the password field is of type "password". A POST request is sent to the "/login" endpoint when the user submits the form. The form is aesthetically appealing and responsive across a range of devices thanks to its simple layout. The form input will be transferred to the server for additional processing and validation after successful submission.

Output

Conclusion

In this tutorial, we learned how to use Node.js and MySQL stored procedures to establish safe user authentication. We created a Node.js project and used EJS and Express.js as our view engines. To check user credentials, the CheckPassword stored procedure for MySQL was developed. This process is called by the Node.js application to determine whether the login attempt was successful or not. By keeping the login functionality within the database and avoiding direct exposure to critical information, this method improves code maintainability and security. Developers may create a strong and reusable login system for their Node.js applications by following this article, providing safe user authentication.

FAQs

Q.  What is a stored procedure in MySQL?

A. A named set of SQL statements used for encapsulating complex queries and tasks in the database.

Q. Why use stored procedures for user authentication in Node.js?

A. Enhances security and code reusability while preventing direct exposure of sensitive information.

Q. How do I create a login stored procedure in MySQL?

A. Define a procedure to check credentials against stored user data in the database.

Q. How can I call a MySQL stored procedure from Node.js?

A. Use mysql2 package to establish a connection and execute the procedure with connection.query().