Middleware User account API using MySql database.

1. API documentation

The API includes:

• Connection to the database and automatic initialization and creation of the database tables. Calling the function init, five tables will be created with the user table name as a prefix. The user table name has to be chosen with precaution to not erase your current tables if not a fresh database. Init has to be called once otherwise it will drop and recreate the tables every time. You use connectToDatabase every time the application starts. Using tableExisting, you can test the existence of the tables before calling init.

    (return bool) function connectToDatabase({connectionLimit, host, port, database, user, password}, userTableName)
      
    function init({connectionLimit, host, port, database, user, password}, userTableName, resultCallback(error, initDone))
      
    function tableExisting(tableName, resultCallback(error, isExisting)) 
      
    function disconnectFromDatabase(resultCallback(error))
          

• Email validation to check if the email address is well-written

    (return bool) function isEmailValid(email)
          

• User existence verification through email.

    function isEmailAlreadyTaken(email, resultCallback(error, isEmailTaken, userId))
          

• Password validation. If just the password is given as argument then minimum 8 characters is required. The second argument is optional and can be a PasswordValidator object (see npm module called "password-validator" for the object creation usage)

    (return bool) function isPasswordValid(password, [passwordValidator])            
          

• User creation with encrypted (hashed) password using the npm package "bcrypt". The email address is the login. The user creation includes a JSON object to store user data. You can add the fields you need in this object. The user data object is stringified in the database and stored in a text field. You can modify or get the user data by calling setUserData or getUserData. At user creation time, a confirmToken is created and added to a confirmation table if user creation needs to be confirmed (i.e. by email). You can confirm user creation by calling confirmCreateUser. The confirmToken includes the userId and a hash phrase (both separated by an hyphen '-'). The confirmation function will remove the confirmation from the user creation confirmation table.

    function createUser(email, pass, userData, resultCallback(error, loginData, userData, confirmToken))
      
    function confirmCreateUser(confirmToken, resultCallback(error, isConfirmTokenValid, isConfirmed)) 
       
    function deleteUser(userId, resultCallback(error, isDeleted))
      
    function getUserDataById(userId, resultCallback(error, userData))
      
    function setUserDataById(userId, userData, resultCallback(error, isUserDataUpdated))         
      

• User login and logout. A new authentication token is created for every login and sent back to the client application. This makes possible multiple device connections. An authentication token is composed of a userId and a generated string of characters using the npm package "uid-generator", both separated by an hyphen '-'. When logout is called, the authentication token linked to this session will simply be deleted. You can check the number of login session the user account did by using getNumberOfLoginSessions. You can logout all other sessions of the user by calling logoutOtherSessions which is useful if the user do not remember or do not have access anymore to the device it used to login.

    login(email, password, resultCallback(error, isLoginCorrect, isPassCorrect, authenticationToken, loginData)) 
      
    function logout(authenticationToken, resultCallback(error, , isAuthTokenValid, isLoggedOut)) 
      
    function getNumberOfSessions(authenticationToken, resultCallback(error, isAuthTokenValid, numberOfSessions)));
   
    function logoutOtherSessions(authenticationToken, resultCallback(error, isAuthTokenValid, areSessionsLoggedOut))   

• User authentication token verification. Simply check if this authentication token exists and is linked to a user account. The accessToken return is the second part of the authenticationToken. The first part of the authenticationToken is the userId.

    function verifyAuthenticationToken(authenticationToken, resultCallback(error, isAuthTokenValid, userId, accessToken, loginData))

• You can set or get the user data using the authenticationToken by calling getUserData or setUserData.

    function getUserData(authenticationToken, resultCallback(error, authTokenValid, userData))
      
    function setUserData(authenticationToken, userData, resultCallback(error, authTokenValid, isUserDataUpdated))  

• Password modification and verification.

    function modifyPassword(authenticationToken, newPassword, resultCallback(error, isPasswordModified))
      
    function verifyPassword(email, password, resultCallback(error, loginValid, passwordValid, loginData)) 

• Reset password request or request a new user authentication token. After calling requestPasswordOrAccessReset, a confirmToken is created. you can call either confirmAccessTokenReset or confirmPasswordReset with this confirmToken as argument. confirmAccessTokenReset will create and return a new authenticationToken like in the login process. confirmPasswordReset will simply replace the user password by the new password given in argument and will not create nor return a new authenticationToken.

    requestPasswordOrAccessReset(email, resultCallback(error, isEmailExisting, confirmToken, wasAlreadySent))
  
    confirmAccessTokenRequest(confirmToken, resultCallback(error, confirmToken, authenticationToken));
      
    confirmPasswordReset(confirmToken, newPassword, resultCallback(error, isConfirmed))
              

• email modification request + confirmToken to confirm the modification.

    requestModifyEmail(authenticationToken, newEmail, resultCallback(error,  isAuthTokenValid, isEmailValid, isEmailTaken, confirmToken))
  
    confirmModifyEmail(confirmToken, resultCallback(error, isConfirmed))
      

• User account deletion request + confirmToken to confirm the deletion.

    function requestUserDeletion(authenticationToken, resultCallback(error, isAuthTokenValid, confirmToken))
      
    function confirmUserDeletion(confirmToken, resultCallback(error, isUserDeleted))
      

• Cleaning functions that will clean the confirmToken tables.
  For example, you can create a timer that call the cleanConfirmResetPassTable function every ten minutes. The function will check which confirmTokens are still valid by using the lifespanInMinutes argument. This lifespan is the time the confirm token is valid. All confirmTokens that are older than the lifespan will be deleted (invalidated).

    // this function will also delete the users that did not confirm on time (lifespan) their registrations    
      
    function cleanConfirmUserCreationTable(lifespanInMinutes, resultCallback(error, cleaningDone, nbCleanedRows, nbDeletedUsers, emailsOfDeletedUsers))
      
    // these functions will just invalidate the confirmation link by deleting it.
      
    function cleanConfirmNewEmailTable(lifespanInMinutes, resultCallback(error, cleaningDone, nbCleanedRows))
      
    function cleanConfirmResetPassTable(lifespanInMinutes, resultCallback(error, cleaningDone, nbCleanedRows))
      
    function cleanConfirmUserDeletionTable(lifespanInMinutes, resultCallback(error, cleaningDone, nbCleanedRows))

2. Example using web services

Examples using express.js in a Plesk system environment.

Plesk includes node.js, mysql databases (or mariaDB) and integrate automatic SSL generation (using Let's encrypt) to protect the webservice through https.

Using node.js, you just need to call http module. Plesk services takes care of the encrypted communication if you have activated Let's encrypt on your domain.

Add an environment variable to node.js called SetupMode=DevelopmentMode or SetupMode=ProductionMode

config-test file

exports.DevelopmentMode = "DevelopmentMode";
exports.ProductionMode = "ProductionMode";

switch (process.env.SetupMode) {
    case exports.ProductionMode: {
        exports.sqlParams = {
            connectionLimit: 10,
            host: "localhost",
            port: "3306",
            database: "testing",
            user: "testinguser",
            password: "fdetij!wE"
        };
        exports.userTableName = "user";
        exports.clientAppToken = ["sdfls-fsdfl-2323D-dseer-qzlot-sgegq"];
    }
    break;

    case exports.DevelopmentMode: {
        exports.sqlParams = {
            connectionLimit: 10,
            host: "localhost",
            port: "3306",
            database: "testing",
            user: "testinguser",
            password: "fdetij!wE"
        };
        exports.userTableName = "user_testing";
        exports.clientAppToken = ["styfs-fs2nl-2333D-dse5r-ql12o-sge9f"];
    }
    break;

}

Node.js Module file for web services using JSON communication

The client application uses the web services using its clientAppToken. The client application is not a web browser but another backend application. The web browser application should then communicate directly with this client application.

const http = require("http");
const express = require("express");
const cors = require("cors");
const bodyParser = require("body-parser");
const logger = require("futurosenso-log");
const user = require("futurosenso-user-mysql");
const config = require("./config-test");

//// EXPRESS INIT ////

app = express();
app.use(cors());
app.use(bodyParser.text({limit: "1kb"}));
app.use(bodyParser.json());

//// DATABASE INIT ////

if(!user.connectToDatabase(config.sqlParams)) {
    logger.log("Error connecting to database");
    process.exit(1);
}

user.tableExisting(config.userTableName,  (error, existing)=>{
    if(!error && !existing)
        user.init(config.sqlParams, config.userTableName, (error, initDone) => {
            if (error || !initDone) {
                logger.log("Error occured during database initialization");
                process.exit(1);
            }
        });
});

// VERIFY CLIENT APPLICATION AUTHENTICATION BEFORE EACH WEB SERVICE CALL
// ONLY THE CLIENT APPLICATION CAN CALL THESE WEB SERVICES
app.use(function (req, res, next) {
    if (req.body.clientAppToken && (typeof req.body.clientAppToken === "string")) {
        if (config.clientAppToken.find((value) => {
            return value === req.body.clientAppToken;
        })) {
            next();
        } else {
            res.json({success: false, error: "client application token not valid"});
        }
    } else {
        res.json({success: false, error: "client application token not valid"});
    }
});

/////////////////////////////////////////////////////////
//// WEB SERVICES ///////////////////////////////////////
/////////////////////////////////////////////////////////

// USER CREATION
app.post("/user/create", function (req, res) {
    let info = req.body;
    if (user.isEmailValid(info.email)) {
        user.isEmailAlreadyTaken(info.email, (error, existing) => {
            if (error) {
                res.json({success: false, error: "system error"});
            } else if (existing) {
                res.json({success: false, error: "email not available"});
            } else {
                if (user.isPasswordValid(info.pass)) {
                    user.createUser(info.email, info.pass, info.userData ? info.userData : {},
                        (error, loginData, userData, confirmToken) => {
                            if (!error && confirmToken) {
                                res.json({success: true, confirmToken: confirmToken})
                            } else {
                                res.json({success: false, error: "user not created"})
                            }
                        });
                } else {
                    res.json({success: false, error: "password not valid"})
                }
            }
        });
    } else {
        res.json({success: false, error: "email not valid"})
    }
});

// USER CONFIRMATION CREATION
app.post("/user/create/confirm", function (req, res) {
    user.confirmCreateUser(req.body.confirmToken, (error, isConfirmTokenValid, userCreationConfirmed) => {
        if (userCreationConfirmed) {
            res.json({success: true})
        } else if (!isConfirmTokenValid) {
            res.json({success: false, error: "bad confirm token"})
        } else {
            res.json({success: false, error: "system error"})
        }
    });
});

// GET USER DATA
app.post("/user/data", function (req, res) {
    user.getUserData(req.body.authToken, (error, authTokenValid, userData) => {
        if (error) {
            res.json({success: false, error: "system error"});
        } else if (!authTokenValid) {
            res.json({success: false, error: "authentication token invalid - access denied"})
        } else if (userData) {
            res.json({success: true, userData: userData});
        } else {
            res.json({success: false, userData: {}});
        }
    });
});

// MODIFY USER DATA
app.post("/user/modify", function (req, res) {
    user.setUserData(req.body.authToken, req.body.userData, (error, authTokenValid, isModified) => {
        if (error) {
            res.json({success: false, error: "system error"})
        } else if (!authTokenValid) {
            res.json({success: false, error: "authentication token invalid - access denied"});
        } else if (isModified) {
            res.json({success: true})
        } else {
            res.json({success: false, error: "not modified"})
        }
    });
});

/////////////////////////////////////////////////////////
// USER LOGIN
app.post("/user/login", function (req, res) {
    let info = req.body;
    if (user.isEmailValid(info.email)) {
        if (user.isPasswordValid(info.pass)) {
            user.login(info.email, info.pass, (error, isLoginCorrect, isPassCorrect, authToken) => {
                if (error) {
                    res.json({success: false, error: "system error"})
                } else if (!isLoginCorrect) {
                    res.json({success: false, error: "login not existing"});
                } else if (!isPassCorrect) {
                    res.json({success: false, error: "password not correct"});
                } else if (authToken) {
                    res.json({success: true, authToken: authToken});
                }
            });
        } else {
            res.json({success: false, error: "password not valid"})
        }
    } else {
        res.json({success: false, error: "email not valid"});
    }
});

// GET USER NUMBER OF SESSIONS
app.post("/user/nbsessions", function (req, res) {
    if (req.body.authToken) {
        user.getNumberOfSessions(req.body.authToken, (error, isAuthTokenValid, nbSessions) => {
            if (!isAuthTokenValid) {
                res.json({success: false, error: "authentication token not valid"});
            }
            if (error) {
                res.json({success: false, error: "system error"});
            } else {
                res.json({success: true, nbSessions: nbSessions});
            }
        });
    } else {
        res.json({success: false, error: "authentication token needed"});
    }
});

// USER LOGOUT
app.post("/user/logout", function (req, res) {
    if (req.body.authToken) {
        user.logout(req.body.authToken, (error, isAuthTokenValid, isLoggedOut) => {
            if (!isAuthTokenValid) {
                res.json({success: false, error: "authentication token not valid"});
            }
            if (error) {
                res.json({success: false, error: "system error"});
            } else if (isLoggedOut) {
                res.json({success: true});
            } else {
                res.json({success: false, error: "logout not done"});
            }
        });
    } else {
        res.json({success: false, error: "access token not valid"});
    }
});

// LOGOUT USER FROM OTHER SESSIONS
app.post("/user/logout/sessions", function (req, res) {
    if (req.body.authToken) {
        user.logoutOtherSessions(req.body.authToken, (error, isAuthTokenValid, isLoggedOut) => {
            if (!isAuthTokenValid) {
                res.json({success: false, error: "authentication token not valid"});
            }
            if (error) {
                res.json({success: false, error: "system error"});
            } else if (isLoggedOut) {
                res.json({success: true});
            } else {
                res.json({success: false, error: "logout not done"});
            }
        });
    } else {
        res.json({success: false, error: "authentication token needed"});
    }
});

/////////////////////////////////////////////////////////
// NEW USER PASSWORD REQUEST
app.post("/user/reset/pass/request", function (req, res) {
    if (req.body.email) {
        user.requestPasswordOrAccessReset(req.body.email, (error, emailExisting, link, wasAlreadySent) => {
            if (error) {
                res.json({success: false, error: "system error"});
            } else if (!emailExisting) {
                res.json({success: false, error: "email not existing"});
            } else if (wasAlreadySent) {
                res.json({success: false, error: "email already sent"});
            } else if (link) {
                res.json({success: true});
            } else {
                res.json({success: false, error: "system error"});
            }
        });
    } else {
        res.json({success: false, error: "email not valid"});
    }
});

// CONFIRM NEW PASSWORD REQUEST
app.post("/user/reset/pass/confirm", function (req, res) {
    if (req.body.confirmToken && req.body.pass) {
        user.confirmPasswordReset(req.body.confirmToken, req.body.pass, (error, linkConfirmed, authToken) => {
            if (linkConfirmed && authToken) {
                res.json({success: true, authToken: authToken})
            } else {
                res.json({success: false, error: "system error"})

            }
        });
    } else {
        res.json({success: false, error: "confirm token or pass missing"});
    }
});

/////////////////////////////////////////////////////////
// NEW EMAIL REQUEST
app.post("/user/email/request", function (req, res) {
    user.requestModifyEmail(req.body.authToken, req.body.email, (error, isAuthTokenValid, isEmailValid, isEmailTaken, confirmToken) => {
        if (error) {
            res.json({success: false, error: "system error"});
        } else if (!isEmailValid) {
            res.json({success: false, error: "email not valid"});
        } else if (isEmailTaken) {
            res.json({success: false, error: "email taken"});
        } else if (!isAuthTokenValid) {
            res.json({success: false, error: "authentication token not valid - access denied"});
        } else if (confirmToken) {
            res.json({success: true, confirmToken: confirmToken});
        } else {
            res.json({success: false, error: "system error"});
        }
    });
});

// CONFIRM NEW EMAIL REQUEST
app.post("/user/email/confirm", function (req, res) {
    user.confirmModifyEmail(req.body.confirmToken, (error, isTokenConfirmed) => {
        if (error) {
            res.json({success: false, error: "system error"});
        } else if (isTokenConfirmed) {
            res.json({success: true})
        } else {
            res.json({success: false, error: "system error"})
        }
    });
});

/////////////////////////////////////////////////////////
// DELETE USER REQUEST
app.post("/user/delete/request", function (req, res) {
    user.requestUserDeletion(req.body.authToken, (error, isAuthTokenValid, confirmToken) => {
        if (error) {
            res.json({success: false, error: "system error"});
        } else if (!isAuthTokenValid) {
            res.json({success: false, error: "authentication token not valid"});
        } else if (confirmToken) {
            res.json({success: true, confirmToken: confirmToken});
        } else {
            res.json({success: false, error: "system error"});
        }
    });
});

// CONFIRM DELETE USER REQUEST
app.post("/user/delete/confirm", function (req, res) {
    user.confirmUserDeletion(req.body.confirmToken, (error, isTokenConfirmed) => {
        if (error) {
            res.json({success: false, error: "system error"});
        } else if (isTokenConfirmed) {
            res.json({success: true})
        } else {
            res.json({success: false, error: "confirmation not done"})
        }
    });
});

///////////////////////////////////////////////////////
// CLEAN USER CONFIRM
app.post("/user/clean/user", function (req, res) {
    user.cleanConfirmUserCreationTable(60, (error, cleaned, nbDeleted) => {
        if (error) {
            res.json({success: false, error: "system error"});
        } else if (cleaned) {
            res.json({success: true, numberDeleted: nbDeleted})
        } else {
            res.json({success: false, error: "system error"})
        }
    });
});

// CLEAN PASSWORD RESET CONFIRM
app.post("/user/clean/reset", function (req, res) {
    user.cleanConfirmResetPassTable(10, (error, cleaned, nbDeleted) => {
        if (error) {
            res.json({success: false, error: "system error"});
        } else if (cleaned) {
            res.json({success: true, numberDeleted: nbDeleted})
        } else {
            res.json({success: false, error: "system error"})
        }
    });
});

// CLEAN CHANGE EMAIL CONFIRM
app.post("/user/clean/email", function (req, res) {
    user.cleanConfirmNewEmailTable(30, (error, cleaned, nbDeleted) => {
        if (error) {
            res.json({success: false, error: "system error"});
        } else if (cleaned) {
            res.json({success: true, numberDeleted: nbDeleted})
        } else {
            res.json({success: false, error: "system error"})
        }
    });
});

// CLEAN USER CONFIRM
app.post("/user/clean/delete", function (req, res) {
    user.cleanConfirmUserDeletionTable(10, (error, cleaned, nbDeleted) => {
        if (error) {
            res.json({success: false, error: "system error"});
        } else if (cleaned) {
            res.json({success: true, numberDeleted: nbDeleted})
        } else {
            res.json({success: false, error: "system error"})
        }
    });
});

/////////////////////////////////////////////////////////////////////////////////////
// START SERVER LISTENING - PLESK SYSTEM STYLE ////

const httpServer = http.createServer(app);

switch (process.env.SetupMode) {
    case config.DevelopmentMode:
        let port = 8585;
        httpServer.listen(port);
        logger.log("Setup mode is: " + config.DevelopmentMode);
        logger.log("Start listening on port " + port);
        break;
    case config.ProductionMode:
        httpServer.listen(process.env.PORT);
        logger.log("Setup mode is: " + config.ProductionMode);
        logger.log("Start Listening on port " + process.env.PORT);
        break;
    default:
        httpServer.listen(8585);
        logger.log("Setup mode is default mode");
        logger.log("Start listening on port 8585");
        break;
}