Syntax Error when using multiple parameter substitutions in a MYSQL Query

I need to Update MYSQL data using JS after I receive an AJAX Post request

I made a variable for the MYSQL Update Query and I’m passing in the field to be updated, new value, row to be updated as an array. But for some reason those variables are read with single quotes(') which, I believe, is causing me a syntax error.

var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var MYSQL = require('mysql');
var server = require('http').createServer(app);

//declaring var 'conn' for MYSQL.createPool
let columns = new Array();
// Piece of code Starting the Server
// Routing
app.use(bodyParser.json()); // for parsing application/json
app.use(bodyParser.urlencoded({ extended: true })); // for parsing application/x-www-form-urlencoded
app.use(express.static(path.join(__dirname, 'public')));

app.post('/', function (req, res) {         
  updateWorkbook(req.body);
  res.send('Thanks for the data.');
});

//This is the function extracts the row, field value that need to be updated from the AJAX request
function updateWorkbook( data ){    
  getcolumns().then( function (columns) {
    console.log("Columns got returned to Updateworkbook function")

    for (let d = 0; d < data.length; d++) {
      let rowToUpdate = data[d].id.replace('row_', '').split('_')[0];
      let fieldToUpdate = data[d].id.replace('row_', '').split('_')[1];
      let newValue = data[d].value;
      console.log('row,field,value: ' + rowToUpdate + '|' + fieldToUpdate + '|' + newValue);
      let key_to_replace;

      for(let i = 0; i < columns.length; i++) {
        let looper = columns[i].toLowerCase()
        if (looper === fieldToUpdate) {
          key_to_replace = columns[i]
        }
      }
      let field_to_replace = key_to_replace.toString();
      console.log(field_to_replace) //It prints out a normal string value here

      updatemysql(field_to_replace, newValue, rowToUpdate);
    }
  });
};

//This is the function which updates MYSQL data
function updatemysql(field, newval, row) {
  var sql = "UPDATE mydb.mytable SET ? = ? WHERE ROW_ID = ?;";
  conn.getConnection( function (err, connection) {
    if (err){
      return cb(err);
      connection.release();
    }
    console.log("Connection got established")
    conn.query(sql, [field, newval, row], function (error, results){
      if (error){
        throw error;
        connection.release();   
      }
      console.log('Data Updated');
      connection.release();
    });
  });
}

//Function to extract all columns from MYSQL and stores them in an array
function getcolumns() {  
  return new Promise(function(resolve, reject) {   
    console.log("getcolumns got initiated")
    conn.getConnection( function (err, connection) {
      if (err){
        return cb(err);
        connection.release();
        return reject(err);
      }
      else {      
        var sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'mytable';"
        conn.query(sql, function (error, results){ 
          for (let i = 0; i < results.length; i++) {
            columns.push(results[i]['COLUMN_NAME'])   
          }
          resolve(columns);
          console.log("Extracted columns")
          connection.release();  
        });
      }
    });
  });
};

Here’s the error I receive:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”Source_of_Phone_Number_’ = ‘Test’ WHERE ROW_ID = ‘1” at line 1`

Source_of_Phone_Number_ is the key_to_replace. Test is the newValue. 1 is the Row_ID.

Answer

There is a problem in function updatemysql(), which uses the following SQL :

var sql = "UPDATE mydb.mytable SET ? = ? WHERE ROW_ID = ?;";

You cannot pass a column name as a parameter.

You would need to change this to :

var sql = "UPDATE mydb.mytable SET " + field + " = ? WHERE ROW_ID = ?;";

Accordingly, only two parameters should be passed to the query :

conn.query(sql, [newval, row], function (error, results){ ... });

Advertisement

Answer