How to update SQL with Parameterized Query?

user_10120957 0
Viewed 355

I have this SQL statement:

var sql = UPDATE tableName SET ? = ? WHERE ? = ?";

db.query(
    sql,
    [asset.getColOne(), asset.getValOne(), asset.getColTwo(), asset.getValTwo()],
    callback
);

and

{
        "colOne": "colName",
        "valOne": 1,
        "colTwo": "colName",
        "valTwo": 0
}

But when I try to execute this, I get a syntax error stating that my SQL statement is:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''colName' = 1 WHERE 'colName' = 0' at line 1

Is there any way to specify column names or overcome the single quote? Thanks

where-clause sql-update mysql sql javascript
1 Answers

You can't pass a table or column name as a parameter to the query. The prepared statement mechanism is meant to pass literal values, not such metadata.

Here, you need to use string concatenation:

var sql = "UPDATE tableName SET `" + asset.getColOne() + "` = ? WHERE `" + asset.getColTwo() + "` = ?";
db.query(sql, [asset.getValOne(), asset.getValTwo()], callback);

Note that I added backticks around the table and column name, in case they are not valid as unquoted identifiers.

Keep in mind, though, that using such technique opens up your code to SQL injection, if the variables come from outside your code. If so, then you need to manually validate the column names before concatenating in the query (for example by checking them against a fixed list of values).

user_106767160
Related