query.on not returning any result in Node.js

Question

query.on not returning any result in Node.js

I am trying to validate user using below code:

function validateUser(adminEmailId, adminPassword) {
    try{
        console.log('email id: '+adminEmailId+' password: '+ adminPassword);
        var connection = mysql.createConnection({
                                                host: 'localhost',
                                                user: 'root',
                                                password: 'somePassword',
                                                database: 'someDatabase'
                                                });

        var query = connection.query('select id from AdminData where adminEmail = "'+adminEmailId+'" AND adminPassword = "'+adminPassword+'"');
        console.log('query: ' + query);

        query.on('error', function(error){
                 console.log('A db error occurred: '+error);
                 });
        query.on('result', function(result){
                 console.log('some result: '+ result);
                 if(result.id === null) {
                 console.log('user not found');
                 }
                 else
                 {
                 console.log('user found :)');
                 }
                 });
    }
    catch (ex) {
        console.log('some exception ' + ex);
    }

}

The last two logs which it is printing in console are -

email id: d@g.com password: gfdgdf 
query: [object Object]

It should have printed -

user not found 
// OR
user found :)

I tried verifying if SQL is running properly or not by firing this in command line:

mysql -h127.0.0.1 -uroot -p

It showed me error:

Command not found

So I updated the path by using below command:

export PATH="/usr/local/mysql/bin:$PATH"

Then I was able to use mysql from command line, but still there is no success achieved in Node.js code :(

Any suggestions?

mysql
node.js

Answer 1

If your query results in no error or result events, then that means the query resulted in an empty set. You should listen for the end event so that you know when the query is done executing. Then you can set some condition inside the result event handler that you check on end to see if the user was found or not.

Probably an easier way to do this is to just pass a callback to query() since it doesn't seem like you will need to be streaming rows in this case. Something like:

connection.query('select id from AdminData where adminEmail = "'+adminEmailId+'" AND adminPassword = "'+adminPassword+'"', function(err, rows) {
    if (err) {
      console.log('A db error occurred: ' + err);
      return;
    }
    if (rows && rows.length)
      console.log('user found :)');
    else
      console.log('user not found');
});

Also I should point out a couple of other things:

You should not concatenate user-submitted data into SQL query strings as it leaves you susceptible to SQL injection attacks. You should at least escape the values, but it's recommended to use real prepared statements. The mysql module currently does not support real prepared statements, but mysql2 does. mysql2 is API-compatible with mysql and is substantially faster overall.
You should probably have a callback parameter at the end of your validateUser() parameter list so that you can call a callback when the query is finished, allowing you to appropriately continue execution of your code. Otherwise the place where validateUser() is called will have no idea when the query finishes and what the result was.