John John - 11 days ago 6
MySQL Question

Connection issue while trying run my mysql query via nodejs

I have a query and I am trying to run the query. The issue i think is that i have added a condition where an item from a column from the database must equal to the computer name of the user.

Hence, I created a variable called

computerName
that simply retrieves the host name of the computer via NodeJs.

var os = require("os");
var computerName = os.hostname(); // Detect the computer name associated with the tablet


Below is the query

connection.query("SELECT box_id, longestDimension from box where longestDimension != '' AND LOWER(box_id) = LOWER(computerName)", function(err, rows, fields) {


computerName
seems to be the problem because when the query is run with a generic name such as box45 it works.

I am getting connection error. I guess the better question is how do I include a defined variable into the query

Answer

It looks like you're trying to insert computerName directly into your SQL statement. At minimum, you'd need to write something like

connection.query("SELECT box_id, longestDimension from box where longestDimension != '' AND LOWER(box_id) = LOWER('" + computerName + "')", function(err, rows, fields) {

But you should be escaping the value of computerName. You don't know what value it might contain.

connection.query("SELECT box_id, longestDimension from box where longestDimension != '' AND LOWER(box_id) = LOWER('" + connection.escape(computerName) + "')", function(err, rows, fields) {

But a better way to do it is with ? substitution:

connection.query("SELECT box_id, longestDimension from box where longestDimension != '' AND LOWER(box_id) = LOWER(?)", computerName, function(err, rows, fields) {

Also, if the collation of the box_id column is case insensitive, which is usually the default, then you can skip the lowercasing the values.

I'd write it like this, for readability

let sql = "SELECT box_id, longestDimension FROM box WHERE longestDimension != '' AND box_id = ?";
connection.query(sql, computerName, function(err, rows, fields) {

Or if your node version supports template literals

let sql = `SELECT box_id, longestDimension
           FROM box
           WHERE longestDimension != ''
           AND box_id = ?`;
connection.query(sql, computerName, function(err, rows, fields) {
Comments