Connection issue while trying run my mysql query via nodejs

Question

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 1

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) {

If you have multiple variables there's two ways to do it: with an object, or with an array.

Object method:

let payload = {
  box_id: "Johannesburg",
  longestDimension: 12.4
};
let sql = 'INSERT INTO box SET ?';

connection.query(sql, payload, function(err, rows, fields) {
});

Array method:

let computerName = "Johannesburg";
let longestDimension = 12.4;
let sql = 'INSERT INTO box SET box_id = ?, longestDimension = ?';

// alternative, equivalent SQL statement:
// let sql = 'INSERT INTO box (box_id, longestDimension) VALUES (?, ?)';

connection.query(sql, [ computerName, longestDimension ], function(err, rows, fields) {
});

You can even combine them

let payload = {
  box_id: "Johannesburg",
  longestDimension: 12.4
};
let boxName = "Box A";
let sql = 'UPDATE box SET ? WHERE box_name = ?';

connection.query(sql, [ payload, boxName ], function(err, rows, fields) {
});

In this last example, the payload object is substituted for the first ? and the boxName variable is substituted for the second ?.