Make a login and registration system using Azure functions and Azure SQL server

Question

I want to make a Dating application using node.js and javascript with Azure functions and an Azure sql server. I can create a user so it appears in my database, but how do I make a login system that "checks" if the users email and password is in the database and is correct.

This is what I have so far:

**Login.js:**

var form = document.getElementById("form")


form.addEventListener('submit', function(e) {
    e.preventDefault()

    var email = document.getElementById("email").value
    var password = document.getElementById("password").value


    fetch("http://localhost:7071/api/login", {
        method: 'POST',
        body: JSON.stringify({
            email: email,
            password: password,
        }), 
        headers: {
            "Content-Type": "application/json; charset-UTF-8"
        }
    })
    .then((response) => {
        return response.text()
    })
    .then((data) => {
        console.log(data)
    }).catch((err) =>{ // catcher fejl, hvis noget går galt
        console.log("wuups: " + err)
    })
})


**DB.js connect:**

function login (payload) {
    return new Promise((resolve, reject) => {
    const sql = 'SELECT * FROM [user] where email = @email AND password = @password'
    const request = new Request(sql,(err,rowcount) =>{
        if (err){
            reject(err)
            console.log(err)
        } else if( rowcount == 0){
            reject({messsage:"user does not exit"})
        }
    });
  
      request.addParameter('email', TYPES.VarChar, payload.email)
      request.addParameter('password', TYPES.VarChar, payload.password)

      request.on('row',(colums) => {
        resolve(colums)
    })
      connection.execSql(request)
      return "you are now logged in"
    });
  } 

module.exports.login = login;

Answer 1

You're on the right track. Consider an updated version of db.sql:

function login(payload, connection) {
    return new Promise((resolve, reject) => {

        const sql = 'SELECT * FROM [user] where email = @email AND password = @password'
        const request = new Request(sql, (err, rowCount) => {
            if (err) {
                reject(err)
                console.error(err)
            }
            else {
                if (rowCount == 1) {
                    resolve(payload.email)
                }
                else {
                    reject('Invalid credentials')
                }
            }
        });

        request.addParameter('email', TYPES.VarChar, payload.email)
        request.addParameter('password', TYPES.VarChar, payload.password)

        connection.execSql(request)
    });
}

Since we can infer a successful login from the amount of returned rows, we don't need access to the actual rows in the row callback.

However: as pointed out by Robert in the comments, storing passwords in plain text is a security concern (since access to the database immediately unveils user passwords).

Better approach

The better approach is to store hashed passwords instead. Imagine this simple user table schema in MSSQL:

CREATE TABLE [User] (
  [Email] [varchar](max) NOT NULL UNIQUE,
  [PasswordHash] [varchar(max)] NOT NULL
)

The login procedure will remain almost the same. Instead of comparing passwords we now compare hashed passwords. Without going into too much detail, you would usually use a library for this purpose (to handle salts, mitigate timing attacks, etc.). I chose bcryptjs for the example below:

var bcrypt = require('bcryptjs');

function login(email, password, connection) {
    return new Promise((resolve, error) => {
        const sql = 'SELECT * FROM [user] where email = @email' // Note that the password comparison no longer lives here
        const request = new Request(sql, (err, rowCount) => {
            if (err) {
                reject(err)
            }
        })
        request.addParameter('email', TYPES.VarChar, email)

        let userRow = null

        // This time we need the 'row' callback to retrieve the password hash
        request.on('row', row => {
            userRow = {
                email = row[0].value,
                passwordHash = row[1].value
            }
        })
    
        // .. and the 'done' callback to know, when the query has finished
        request.on('done', rowCount => {
            if (rowCount == 0) {
                reject('User not found')
            }
            else {
                bcrypt.compare(password, userRow.passwordHash) // Password comparison
                    .then(passwordsMatch => {
                        if (passwordsMatch) {
                            resolve(email)
                        }
                        else {
                            reject('Invalid credentials')
                        }
                    })
            }
        })
    
        connection.execSql(request)
    })
}

And here's an example of how to create new users with this approach using the same library:

var bcrypt = require('bcryptjs');

const PASSWORD_SALT_ROUNDS = 10 // Learn more at ex. https://stackoverflow.com/questions/46693430/what-are-salt-rounds-and-how-are-salts-stored-in-bcrypt

function createNewUser(email, password, connection) {
    return bcrypt.hash(password, PASSWORD_SALT_ROUNDS).then(passwordHash => {
        const sql = 'INSERT INTO [user] (Email, PasswordHash) VALUES (@email, @passwordHash)'
        const request = new Request(sql, err => {
            if (err) {
                error(err)
            }
            else {
                resolve()
            }
        })
        request.addParameter('Email', TYPES.VarChar, email)
        request.addParameter('PasswordHash', TYPES.VarChar, passwordHash)

        connection.execSql(request)
    })
}

Consider this a pragmatic proposal to get started. Please note, that the code is illustrative, since I haven't actually executed it, and it is made under certain assumptions.