CODEWITHSUNDEEP
javascriptmysqlnode.jsexpress
Nilos
Nilos

Reputation: 359

How can I use a variable as column name in mysql / express?

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

Upvotes: 2

Views: 2582

Answers (5)

E962
E962

Reputation: 726

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

Upvotes: 1

Geraint Anderson
Geraint Anderson

Reputation: 3383

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.

Upvotes: 4

Tamilvanan
Tamilvanan

Reputation: 718

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.

Upvotes: 0

Nilos
Nilos

Reputation: 359

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)`);
            }
          );

Upvotes: 1

Evert
Evert

Reputation: 99525

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';

Upvotes: 0

Related Questions