Jay Jay - 4 months ago 12
Node.js Question

node.js and mySQL issue

I am running a query which gives me confusing results. I have a node.js server which takes some user input from a form on the client side and uses socket.io to get the input on the server. here is the function which runs after receiving user input

databaseCheck(data);

function databaseCheck(userInput){

var mysql = require('mysql');
var connection = mysql.createConnection({
host : '12.34.56.78',
user : 'user',
password : 'password',
database : 'db'
});
connection.connect();

var query = connection.query('SELECT * from `table` WHERE `email` = userInput', function(err, rows, fields) {
if (!err) console.log(rows[0].username);
else console.log("connection failure");
});

connection.end();
}


So when ever I use this code, it keeps printing "connection failure" to the screen. It doesn't happen when I replace userInput with the "example@email.com" so I'm guessing there is some problem with using the variable userInput in the query. Can someone tell me what is wrong with my code?

Answer

Not only do you need to pass the userInput by appending it to the string, you need to escape it so that the query recognizes it as a string:

connection.connect();

var query = 'SELECT * from `table` WHERE `email` = ' + JSON.stringify(userInput);

console.log(query);

connection.query(query, function(err, rows, fields) {
  if (!err) console.log(rows[0].username);   
  else console.log(err.name, err.message);
});

connection.end();

It also helps to make the error message more informative by displaying the actual error instead of a generic message.

Lastly, put connection.end(); inside the callback. According to what you said, it appears to work like you had it but it's generally a bad idea to end a connection before an asynchronous process using the connection has called back.

Ignore my last comment, it appears I was wrong in this particular case. According to the repository documentation, it says:

Closing the connection is done using end() which makes sure all remaining queries are executed before sending a quit packet to the mysql server.