johan johan - 3 months ago 13
MySQL Question

Socket.io and Node.js with mysql not returning results as expeted

EDIT: I'm rephrasing my question: Socket.io not waiting for Callback and connection never accepted. Please see Edit 2/Attempt 1 below

This is to check authentication token against database. Can someone spot what is wrong here?

var checkauth = function(auth) {
var rs = 0;
var sql = 'SELECT * FROM clients WHERE pword=\''+auth+'\'';
// Copied from debug session and got results: SELECT * FROM clients WHERE pword='d98e623c7a74a178703d17e1fd536b1488724acd41e71f178331c768c385bda2c82d2bcb60cbb4650be375ad4734c63fb694bd164c138f9abe0c51f37f9a7e33'
var query = connection.query(sql);
query
.on('error', function(err) {
console.log( err );
})
.on('result', function( row ) {
rs = Number(row.client_id); // This never fires!!?!?!
})
.on('end',function(){

});
if (rs == 0) {
sql = 'SELECT * FROM users WHERE pword=\''+auth+'\'';
query = connection.query(sql);
query
.on('error', function(err) {
console.log( err );
})
.on('result', function( row ) {
rs = Number(row.client_id); // This never fires!!?!?!
})
.on('end',function(){
});
}
return rs;
}


Edit 1: I use the follwoing loop to authenticate socket.io connections which runs the function, ok so I understand that I have to wait for the db to finish, where would I put the callback?

io.use(function(socket, next){
console.log("Query: ", socket.handshake.query);
// return the result of next() to accept the connection.
socket.clientid = 0;
socket.clientid = checkauth(socket.handshake.query.auth);
console.log("CID:"+socket.clientid);
if (socket.clientid != 0) {
return next();
}
// call next() with an Error if you need to reject the connection.
next(new Error('Authentication error'));
});


EDIT 2/ Attempt 1 according to Johannes' suggest:

var checkauth = function(auth, cb) {
var rs = 0;
var sql = 'SELECT * FROM clients WHERE pword=?';
var query = connection.query(sql, [auth]);
query
.on('error', function(err) {
console.log(err);
})
.on('result', function(row) {
rs = Number(row.client_id);
})
.on('end', function() {
if (rs == 0) {
sql = 'SELECT * FROM users WHERE pword=?';
query = connection.query(sql, [auth]);
query
.on('error', function(err) {
console.log(err);
})
.on('result', function(row) {
rs = Number(row.client_id);
})
.on('end', function() {
cb(rs);
});
}
});
}

io.use(function(socket, next){
console.log("Query: ", socket.handshake.query);
// return the result of next() to accept the connection.
socket.clientid = 0;
var auth = socket.handshake.query.auth;

checkauth(auth, function(clientid){
socket.clientid = clientid;
if (clientid != 0) {
return next();
}
console.log('CLIENID', clientid);
next(new Error('Authentication error'));
});

// if (socket.clientid != 0) {
// return next();
// }
// // call next() with an Error if you need to reject the connection.
// next(new Error('Authentication error'));
});

Answer

This whole process is async. You have to do 2 things:

Move everything into the event callback and add a callback or promise to your checkAuth function. You also should escape your inserted data.

EDITED SOLUTION

var checkauth = function(auth, cb) {
    var rs = 0;
    var sql = 'SELECT * FROM clients WHERE pword=?';
    var query = connection.query(sql, [auth], (err, client) => {
        if(err || !client || client.length < 1) return cb(err || new Error('unkown client')); 
        sql = 'SELECT * FROM users WHERE pword=?';
        query = connection.query(sql, [auth], (err, user) => {
            if(err || !user || user.length < 1) return cb(err || new Error('unkown user'));
            cb(null, client[0].client_id);
        });
    });
}

Note: why are you doing 2 queries in the first place and storing the password for each client of the user?

ORIGINAL ANSWER:

var checkauth = function(auth, cb) {
    var rs = 0;
    var sql = 'SELECT * FROM clients WHERE pword=?';
    var query = connection.query(sql, [auth]);
    query
        .on('error', function(err) {
            console.log(err);
        })
        .on('result', function(row) {
            rs = Number(row.client_id); // This never fires!!?!?!
        })
        .on('end', function() {
            if (rs == 0) {
                sql = 'SELECT * FROM users WHERE pword=?';
                query = connection.query(sql, [auth]);
                query
                    .on('error', function(err) {
                        console.log(err);
                    })
                    .on('result', function(row) {
                        rs = Number(row.client_id); // This never fires!!?!?!
                    })
                    .on('end', function() {
                        cb(rs);
                    });
            }
        });
}

checkauth(123, function(clientId){
    console.log('CLIENID', clientId);
});