simple user login validation module with node

Question

I'm writing my first (non tutorial) node application and am at a point where I'm writing a function that should take the username and password as parameters and query them against the user table of my database to return either true or false. The database is setup, and the app is connecting to it successfully.

However, I haven't worked with SQL very much, nor node, and I'm unsure how to proceed with this function (and short surrounding script). Here it is:

console.log('validator module initialized');
var login = require("./db_connect");

function validate(username, password){

connection.connect();
console.log('Connection with the officeball MySQL database openned...');

connection.query(' //SQL query ', function(err, rows, fields) {
    //code to execute

});

connection.end();
console.log('...Connection with the officeball MySQL database closed.');


    if(){ //not exactly sure how this should be set up
        return true;
    }
    else{ //not exactly sure how this should be set up
        return false;
    }
}

exports.validate = validate;

This is using node-mysql. I'm looking for a basic example of how I might set the query and validation up.

Answer 1

I think you'll want to rethink your app into a more node-like way (i.e. one that recognizes that many/most things happen asynchronously, so you're not usually "returning" from a function like this, but doing a callback from it. Not sure what you plan to get from node-mysql, but I would probably just use the plain mysql module. The following code is still most likely not entirely what you want, but will hopefully get you thinking about it correctly.

Note that the use of 'return' below is not actually returning a result (the callback itself should not return anything, and thus its like returning undefined. The return statements are there so you exit the function, which saves a lot of tedious if/else blocks.

Hope this helps, but I'd suggest looking at various node projects on github to get a better feel for the asynchronous nature of writing for node.

function validate(username, password, callback){
    var connection = mysql.createConnection({ user:'foo',
                            password: 'bar',
                            database: 'test',
                            host:'127.0.0.1'});

    connection.connect(function (err){
        if (err) return callback(new Error('Failed to connect'), null);
        // if no error, you can do things now.

        connection.query('select username,password from usertable where username=?',
                username,
                function(err,rows,fields) {
                    //  we are done with the connection at this point), so can close it
                    connection.end();

                    // here is where you process results
                    if (err)
                        return callback(new Error ('Error while performing query'), null);
                    if (rows.length !== 1)
                        return callback(new Error ('Failed to find exactly one user'), null);

                    // test the password you provided against the one in the DB.
                    // note this is terrible practice - you should not store in the
                    // passwords in the clear, obviously. You should store a hash,
                    // but this is trying to get you on the right general path

                    if (rows[0].password === password) {
                        // you would probably want a more useful callback result than 
                        // just returning the username, but again - an example
                        return callback(null, rows[0].username);
                    } else {
                        return callback(new Error ('Bad Password'), null);
                    }

                });


    });
};