Currently I have this issue, the problem is that the table name gets a set of quotation marks (ad it's a string) and this makes the server crash.

const update =  'the name of my column';
const UpdateQuery = `UPDATE scores
    SET ${mysql.escape(update)} = ${mysql.escape(newValue)}
    WHERE score_id = ${mysql.escape(singleScore.score_id)}`;

mysql.escape() works fine for everything except for the column name.

This is what I get if I console.log the query after injecting the variables:

UPDATE scores
SET 'the name of my column' = 1
WHERE score_id = 1

Currently I have this issue, the problem is that the table name gets a set of quotation marks (ad it's a string) and this makes the server crash.

const update =  'the name of my column';
const UpdateQuery = `UPDATE scores
    SET ${mysql.escape(update)} = ${mysql.escape(newValue)}
    WHERE score_id = ${mysql.escape(singleScore.score_id)}`;

mysql.escape() works fine for everything except for the column name.

This is what I get if I console.log the query after injecting the variables:

UPDATE scores
SET 'the name of my column' = 1
WHERE score_id = 1

• javascript
• mysql
• node.js
• express

Share Improve this question Follow edited Sep 20, 2018 at 15:47 Barry 3,32877 gold badges2525 silver badges4343 bronze badges asked Sep 20, 2018 at 15:18 NilosNilos 35911 gold badge55 silver badges1313 bronze badges 8

• I am but only in the example I wrote here, not in my actual code – Nilos Commented Sep 20, 2018 at 15:21
• Have you checked the UpdateQuery after the const applied using console log or something to print? – Tamilvanan Commented Sep 20, 2018 at 15:26
• Yeah, I added it now – Nilos Commented Sep 20, 2018 at 15:28
• So, the problem is single quote !? – Tamilvanan Commented Sep 20, 2018 at 15:30
• Yeah that's what's causing the issue – Nilos Commented Sep 20, 2018 at 15:32

 |  Show 3 more ments

5 Answers 5

Sorted by: Reset to default 4

It looks like you are using the mysql NPM package.

The escape method is used for escaping query values. To escape query identifiers (like column names) you should use the escapeId method instead. Your code should look like this:

const update =  'the name of my column';
const UpdateQuery = `UPDATE scores
    SET ${mysql.escapeId(update)} = ${mysql.escape(newValue)}
    WHERE score_id = ${mysql.escape(singleScore.score_id)}`;

Similarly, if you are using replacements, use a double question mark instead of a single to escape identifiers.

const update =  'the name of my column';
const UpdateQuery = `UPDATE scores
    SET ?? = ?
    WHERE score_id = ?`;
const replacements = [update, newValue, singleScore.score_id];

See the mysql docs for more details.

Tamilvanan solution with a tiny change fixes the issue

 db.query(
            'UPDATE scores SET '+update+' = ? Where score_id = ?',
            [newValue, singleScore.score_id],
            (err, result) => {
              if (err) throw err;
              console.log(`Changed ${result.changedRows} row(s)`);
            }
          );

So, I had this problem myself yesterday. I found the solution by accidentally searching for variable table names.

The solution is to query like this:

const columnName =  'the name of my column';
query("UPDATE `scores` SET ?? = ? WHERE `score_id` = ?;", [columnName, singleScore.score_id, newValue]);

Let me know if this works for you

For weird MySQL column names, you can't put single quotes around them. Single quotes just turn the value into a string.

The backtick is used for this in MySQL. For example

UPDATE `table with space` SET `column with space` = 'bar';

Check the below code. It might work,

con.query(
  'UPDATE scores SET '+update+' = ? Where score_id = ?',
  // Old - [${mysql.escape(newValue)}, ${mysql.escape(singleScore.score_id)}],
  /* Update - */ [newValue,singleScore.score_id],
  (err, result) => {
    if (err) throw err;
    console.log(`Changed ${result.changedRows} row(s)`);
  }
);

As per your query, ${mysql.escape(update)} includes the single quote from the value.